I use a few SQL scripts based on v$session (which uses x$ksuse) to look at active sessions in an Oracle instance.  These scripts need to perform very quickly so that I can sample activity rapidly to generate a good picture of what is happening.

select ....
from v$session s, v$process p
where addr=paddr
and sid != (select distinct sid from v$mystat)
and ....
order by type, s.sid
/

After an upgrade to Oracle 11g (11.2), I found that these scripts were sluggish.

I examined the explain plan and compared the estimated number of rows to the actual rows processed.

----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                 |      1 |        |      0 |00:00:01.15 |
|   1 |  SORT ORDER BY             |                 |      1 |      1 |      0 |00:00:01.15 |
|   2 |   NESTED LOOPS             |                 |      1 |      1 |      0 |00:00:01.15 |
|   3 |    NESTED LOOPS            |                 |      1 |      1 |    332 |00:00:01.15 |
|   4 |     MERGE JOIN CARTESIAN   |                 |      1 |      5 |    111K|00:00:00.19 |
|*  5 |      FIXED TABLE FULL      | X$KSUPR         |      1 |      1 |    334 |00:00:00.01 |
|   6 |      BUFFER SORT           |                 |    334 |    100 |    111K|00:00:00.11 |
|   7 |       FIXED TABLE FULL     | X$KSLWT         |      1 |    100 |    333 |00:00:00.01 |
|*  8 |     FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |    111K|      1 |    332 |00:00:00.82 |
|   9 |      HASH UNIQUE           |                 |      1 |      1 |      1 |00:00:00.01 |
|* 10 |       FIXED TABLE FULL     | X$KSUMYSTA      |      1 |      1 |    635 |00:00:00.01 |
|  11 |        FIXED TABLE FULL    | X$KSUSGIF       |      1 |    100 |      1 |00:00:00.01 |
|* 12 |    FIXED TABLE FIXED INDEX | X$KSLED (ind:2) |    332 |      1 |      0 |00:00:00.01 |
----------------------------------------------------------------------------------------------

The optimizer is estimating that there is only one process (in X$KSUPR) and one session (in X$KSUSE).
This results in the use of a cartesian join between the processes and session wait information, which would be fine if there really was only one process.
Unfortunately the number of rows processed balloons to 111,222 before the join to the session information which reduces the row set to the actual number of sessions.

The equivalent plan in 10.2 (before the upgrade), which performed well, was:

---------------------------------------------------------------
| Id  | Operation                  | Name            | E-Rows |
---------------------------------------------------------------
|   1 |  SORT ORDER BY             |                 |      1 |
|*  2 |   HASH JOIN                |                 |      1 |
|   3 |    NESTED LOOPS            |                 |      1 |
|*  4 |     FIXED TABLE FULL       | X$KSUSE         |      1 |
|   5 |      HASH UNIQUE           |                 |      1 |
|*  6 |       FIXED TABLE FULL     | X$KSUMYSTA      |      1 |
|   7 |        FIXED TABLE FULL    | X$KSUSGIF       |    100 |
|*  8 |     FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |      1 |
|*  9 |    FIXED TABLE FULL        | X$KSUPR         |      1 |
---------------------------------------------------------------

Note the absence of the cartesian join.

Oracle’s best practice guide for upgrading to 11gR2 says to gather fixed statistics, but a quick check of tab_stats$ showed that this hadn’t been done.

So one solution is to gather statistics on the X$ objects during a typical workload:

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);

When the SQL is hard parsed next, the explain plan will look similar to this:

----------------------------------------------------------------
| Id  | Operation                   | Name            | E-Rows |
----------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |        |
|   1 |  SORT ORDER BY              |                 |      1 |
|   2 |   NESTED LOOPS              |                 |      1 |
|*  3 |    HASH JOIN                |                 |    145 |
|*  4 |     FIXED TABLE FULL        | X$KSUPR         |    141 |
|   5 |     NESTED LOOPS            |                 |    159 |
|   6 |      FIXED TABLE FULL       | X$KSLWT         |    159 |
|*  7 |      FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) |      1 |
|   8 |       HASH UNIQUE           |                 |      1 |
|*  9 |        FIXED TABLE FULL     | X$KSUMYSTA      |     32 |
|  10 |         FIXED TABLE FULL    | X$KSUSGIF       |      1 |
|* 11 |    FIXED TABLE FIXED INDEX  | X$KSLED (ind:2) |      1 |
----------------------------------------------------------------

If you aren’t free to gather fixed statistics in a database, (eg you are a consultant at a client site), the alternative is to use a hint/parameter to disable cartesian joins, eg:

select /*+ opt_param('_optimizer_cartesian_enabled','false') */ ....
from v$session s, v$process p
....
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s