AWR Global RAC report: awrgrpt.sql

Nice site:

http://www.dba-oracle.com/t_rac_awr_awrgrpt_global_report.htm

 SQL> @?/rdbms/admin/awrgrpt.sql

How to avoid Cartesian join between data dictionar views?

In one of our databases, which is 11.2.0.4, the below join is doing some merge join cartesian and it takes a very long time to complete.

SELECT
distinct sid, serial#, substr(username,1,12) username,
       substr(osuser,1,10) osuser, process, program, a.sql_id,hash_value,command
FROM v$session  a,v$sql b
WHERE sid > 7
  AND status = 'ACTIVE' and username like '$USER_NAME'
and a.sql_id=b.sql_id
/

Execution Plan

----------------------------------------------------------

Plan hash value: 4038803543

--------------------------------------------------------------------------------

| Id  | Operation                  | Name              | Rows  | Bytes | Cost  |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |                   |     1 |    78 |     5 |

|   1 |  HASH UNIQUE               |                   |     1 |    78 |     5 |

|   2 |   NESTED LOOPS             |                   |     1 |    78 |     1 |

|   3 |    NESTED LOOPS            |                   |     1 |    74 |     1 |

|   4 |     MERGE JOIN CARTESIAN   |                   |    32 |  1344 |     1 |

|*  5 |      FIXED TABLE FULL      | X$KGLCURSOR_CHILD |     1 |    34 |     1 |

|   6 |      BUFFER SORT           |                   |    32 |   256 |       |

|*  7 |       FIXED TABLE FULL     | X$KSLWT           |    32 |   256 |     1 |

|*  8 |     FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)   |     1 |    32 |     1 |

|*  9 |    FIXED TABLE FIXED INDEX | X$KSLED (ind:2)   |     1 |     4 |     1 |

--------------------------------------------------------------------------------

 Since we don't want to collect fresh stats for the fixed objects, a quick fix is to run the alter session below:

SQL> alter session set "_optimizer_cartesian_enabled" =false;

 The new execution plan is below and the query is very fast.

Execution Plan

----------------------------------------------------------

Plan hash value: 2088405718

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name                      | Rows  | Bytes | Cost  |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                           |     1 |    78 |     4 |

|   1 |  HASH UNIQUE                |                           |     1 |    78 |     4 |

|   2 |   NESTED LOOPS              |                           |     1 |    78 |     1 |

|   3 |    NESTED LOOPS             |                           |     1 |    74 |     1 |

|   4 |     NESTED LOOPS            |                           |     1 |    40 |     1 |

|*  5 |      FIXED TABLE FULL       | X$KSLWT                   |    32 |   256 |     1 |

|*  6 |      FIXED TABLE FIXED INDEX| X$KSUSE (ind:1)           |     1 |    32 |     1 |

|*  7 |     FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) |     1 |    34 |     1 |

|*  8 |    FIXED TABLE FIXED INDEX  | X$KSLED (ind:2)           |     1 |     4 |     1 |

-----------------------------------------------------------------------------------------