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 |
-----------------------------------------------------------------------------------------
Another way to do it is to add the below hint:
ReplyDeleteselect /*+ OPT_PARAM('_optimizer_cartesian_enabled' 'FALSE') */