TEST!!! - _optimizer_cartesian_enabled and _optimizer_mjc_enabled

After I posted Just learn more from V$LOCK and Excessive waited 'SQL*Net message from client' - when query V$LOCK. I found "MERGE JOIN CARTESIAN" in Explain Plan, that made excessive response time from V$LOCK.
I learned some idea from My Oracle Support to eliminate the merge join cartesian Rule. Set both "_optimizer_cartesian_enabled" and "_optimizer_mjc_enabled" hidden parameters.

SQL> SELECT ksppinm name,
2 ksppstvl value,
3 ksppdesc description
4 FROM x$ksppi x, x$ksppcv y
5 WHERE (x.indx = y.indx)
6 AND x.inst_id=userenv('instance')
7 AND x.inst_id=y.inst_id
8 AND ksppinm LIKE '\_optimizer\_cartesian\_enabled' ESCAPE '\'
9 /

NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------------------------------------
_optimizer_cartesian_enabled TRUE optimizer cartesian join enabled

SQL> SELECT ksppinm name,
2 ksppstvl value,
3 ksppdesc description
4 FROM x$ksppi x, x$ksppcv y
5 WHERE (x.indx = y.indx)
6 AND x.inst_id=userenv('instance')
7 AND x.inst_id=y.inst_id
8 AND ksppinm LIKE '\_optimizer\_mjc\_enabled' ESCAPE '\'
9* ORDER BY name
SQL> /

NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------------------------------------
_optimizer_mjc_enabled TRUE enable merge join cartesian

Test it... more
Case1: No change anything

SQL> show parameter "_optimizer_cartesian_enabled"
SQL> show parameter "_optimizer_mjc_enabled"
SQL> explain plan for select * from v$lock;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 554400005
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 144 | 1 (100)| 00:00:01 |
| 2 | VIEW | GV$_LOCK | 10 | 680 | 0 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | VIEW | GV$_LOCK1 | 2 | 136 | 0 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | FIXED TABLE FULL| X$KDNSSF | 1 | 94 | 0 (0)| 00:00:01 |
|* 8 | FIXED TABLE FULL| X$KSQEQ | 1 | 94 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KTADM | 1 | 94 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTATRFIL | 1 | 94 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFSL | 1 | 94 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATL | 1 | 94 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTSTUSC | 1 | 94 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSS | 1 | 94 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSG | 1 | 94 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTCXB | 1 | 94 | 0 (0)| 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 100 | 7600 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 30 | 0 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 100 | 4600 | 0 (0)| 00:00:01 |
| 20 | FIXED TABLE FULL | X$KSQRS | 100 | 4600 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> select * from v$lock;
.
.
.
Elapsed: 00:00:00.94

Case 2: Disabled merge join cartesian

SQL> alter system set "_optimizer_cartesian_enabled"=FALSE;

System altered.

SQL> alter system set "_optimizer_mjc_enabled" = FALSE;

System altered.

SQL> explain plan for select * from v$lock;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3634456900
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 144 | 1 (100)| 00:00:01 |
| 2 | VIEW | GV$_LOCK | 10 | 680 | 0 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | VIEW | GV$_LOCK1 | 2 | 136 | 0 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | FIXED TABLE FULL| X$KDNSSF | 1 | 94 | 0 (0)| 00:00:01 |
|* 8 | FIXED TABLE FULL| X$KSQEQ | 1 | 94 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KTADM | 1 | 94 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTATRFIL | 1 | 94 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFSL | 1 | 94 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATL | 1 | 94 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTSTUSC | 1 | 94 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSS | 1 | 94 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSG | 1 | 94 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTCXB | 1 | 94 | 0 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 100 | 7600 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 30 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 100 | 4600 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> select * from v$lock;
.
.
.
Elapsed: 00:00:01.26

Response time from both cases, not different. see "NESTED LOOPS" from both "FIXED TABLE FULL" (X$KSUSE) and "FIXED TABLE FULL" (X$KSQRS). It's bad!!! So, back to using ORDERED hint again.

SQL> explain plan for select /*+ ordered */ * from v$lock;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3524752130
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 144 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 144 | 1 (100)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 98 | 1 (100)| 00:00:01 |
| 3 | VIEW | GV$_LOCK | 10 | 680 | 0 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
|* 5 | FILTER | | | | | |
| 6 | VIEW | GV$_LOCK1 | 2 | 136 | 0 (0)| 00:00:01 |
| 7 | UNION-ALL | | | | | |
|* 8 | FIXED TABLE FULL| X$KDNSSF | 1 | 94 | 0 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL| X$KSQEQ | 1 | 94 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL | X$KTADM | 1 | 94 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTATRFIL | 1 | 94 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATRFSL | 1 | 94 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTATL | 1 | 94 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTSTUSC | 1 | 94 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSS | 1 | 94 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTSTUSG | 1 | 94 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTCXB | 1 | 94 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 30 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 100 | 4600 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SQL> select /*+ ordered */ * from v$lock;
.
.
.
Elapsed: 00:00:00.06

So, Don't need to use both hidden parameters. just know both _optimizer_cartesian_enabled and _optimizer_mjc_enabled hidden parameter be able to eliminate the merge join cartesian Rule. & ORDERED hint still work -)

Related Posts:
Just learn more from V$LOCK
Excessive waited 'SQL*Net message from client' - when query V$LOCK