Performance Improvement on Dictionary Views in Oracle 21c

Nenad Noveljic Leave a Comment

V$SQL

The V$SQL view is queried to retrieve information about SQLs in the shared pool. Let’s examine the execution plan that retrieves SQL data for a specific SQL_ID:

select * from v$sql where sql_id = 'a';

-----------------------------------------------------------------------------------------------------
| Id  | Operation		| Name			    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	|			    |	  1 | 20404 |	  0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) |	  1 | 20404 |	  0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("KGLOBT03"='a' AND "INST_ID"=USERENV('INSTANCE'))

From the above execution plan, the following conclusions can be drawn:

  • V$SQL selects from the fixed table X$KGLCURSOR_CHILD.
  • The view column SQL_ID is mapped to the column KGLOBT03 in the fixed table.
  • The access path over SQL_ID is indexed.

In conclusion, it is advisable to access V$SQL over SQL_ID whenever possible, and the optimizer should access it over the index.

Oracle 19c and lower versions

Suboptimal plan

To illustrate the issue, I’m performing a join between the table VSESSION and the view V$SQL. I generated the VSESSION table using the following command:

create table vsession as select * from v$session

The rationale behind utilizing the table VSESSION instead of the view V$SESSION is that the latter involves a join across three fixed tables, introducing unnecessary complexity to the execution plan.

When joining VSESSION and V$SQL, the optimizer doesn’t use the indexed path:

SELECT sq.sql_fulltext
FROM vsession s, v$sql sq
WHERE
  sq.sql_id(+)   = s.sql_id
  AND sq.child_number(+) = s.sql_child_number
;

----------------------------------------------------------------------------------------
| Id  | Operation	   | Name	       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		       |    62 |   994K|     3	 (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |		       |    62 |   994K|     3	 (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| VSESSION	       |    62 |  1302 |     3	 (0)| 00:00:01 |
|   3 |   VIEW		   | V$SQL	       |     1 | 16405 |     0	 (0)| 00:00:01 |
|*  4 |    FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 | 16418 |     0	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SQ"."SQL_ID"(+)="S"."SQL_ID" AND
	      "SQ"."CHILD_NUMBER"(+)="S"."SQL_CHILD_NUMBER")
   4 - filter("INST_ID"=USERENV('INSTANCE'))

Bypassed JPPD transformation

The index wasn’t used because the join predicate SQ.SQL_ID(+) = S.SQL_ID was applied during the join (step 1). The optimizer didn’t push the join predicate into the view, which would have been more efficient. This transformation is called Join Predicate Pushdown (JPPD), and can be tracked in the optimizer trace. The optimizer decided to bypass the transformation without providing a reason:

JPPD:  Considering Cost-based predicate pushdown from query block SEL$1 (#1)
Cost-based predicate pushdown (JPPD)
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD:   Checking validity of push-down from query block SEL$1 (#1) to query block SEL$335DD26A (#2)
JPPD:     JPPD bypassed: No valid join condition found.
JPPD:   No valid views found to push predicate into.
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD:   No valid views found to push predicate into.

Workaround

The transformation can be enforced with the PUSH_PRED hint:

SELECT /*+ push_pred(sq) */ sq.sql_fulltext
FROM vsession s, v$sql sq
WHERE
  sq.sql_id(+)   = s.sql_id
  AND sq.child_number(+) = s.sql_child_number
;

-------------------------------------------------------------------------------------------------------
| Id  | Operation		  | Name		      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	  |			      |    62 |   993K|     3	(0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER	  |			      |    62 |   993K|     3	(0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL	  | VSESSION		      |    62 |  1302 |     3	(0)| 00:00:01 |
|   3 |   VIEW PUSHED PREDICATE   | V$SQL		      |     1 | 16388 |     0	(0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) |     1 | 16418 |     0	(0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("KGLOBT09"="S"."SQL_CHILD_NUMBER" AND "KGLOBT03"="S"."SQL_ID" AND
	      "INST_ID"=USERENV('INSTANCE'))

The plan now shows that the filter is pushed into the V$SQL view, allowing the index to be used.

Oracle 21c

Optimal plan

The situation improved in Oracle 21c. A good plan with the index is achieved even without the hint:


-------------------------------------------------------------------------------------------------------
| Id  | Operation		  | Name		      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	  |			      |    61 |   973K|     3	(0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER	  |			      |    61 |   973K|     3	(0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL	  | VSESSION		      |    61 |  1281 |     3	(0)| 00:00:01 |
|*  3 |   VIEW PUSHED PREDICATE   | V$SQL		      |     1 | 16317 |     0	(0)| 00:00:01 |
|*  4 |    FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) |     1 | 16336 |     0	(0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("SQ"."CHILD_NUMBER"(+)="S"."SQL_CHILD_NUMBER")
   4 - filter("KGLOBT03"="S"."SQL_ID" AND "INST_ID"=USERENV('INSTANCE'))

JPPD transformation

The optimizer trace confirms that Join Predicate Pushdown (JPPD) transformation has been performed:


JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD:   Checking validity of push-down from query block SEL$1 (#1) to query block SEL$335DD26A (#2)
JPPD:     Passed validity checks
JPPD: JPPD:   Pushdown from query block SEL$1 (#1) passed validity checks.
JPPD: Using search type: linear
JPPD: Considering join predicate push-down
JPPD: Starting iteration 1, state space = (2) : (0)
...
JPPD: Performing join predicate push-down (final phase) from query block SEL$1 (#1) to query block SEL$335DD26A (#2)
JPPD:   Pushing predicate "SQ"."SQL_ID"(+)="S"."SQL_ID"

Improvement

To identify what led to the improvement, we can list all the bug fixes related to JPPD:


select bugno, value, sql_feature, description, optimizer_feature_enable 
  from v$system_fix_control 
  where sql_feature like '%JPPD%' and optimizer_feature_enable like '2%' ;

     BUGNO	VALUE SQL_FEATURE						       DESCRIPTION							OPTIMIZER_FEATURE_ENABLE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------
  28173995	    1 QKSFM_JPPD_28173995					       prevent Cartesian Merge Join with partial join predicate pushdow 20.1.0
  28776811	    1 QKSFM_JPPD_28776811					       no replacement by transitive predicate for pushed join predicate 20.1.0
  29302565	    1 QKSFM_JPPD_29302565					       JPPD index access heuristics correction and fixed index support	20.1.0
  29867728	    1 QKSFM_JPPD_29867728					       do not unset partial JPPD info in additional phase		20.1.0
  30786641	    1 QKSFM_JPPD_30786641					       relax restriction on

Bug fix 29302565 “JPPD index access heuristics correction and fixed index support” is the main suspect: the JPPD transformation was discarded without calculating the cost, and only the fixed tables are affected. (We can easily prove that JPPD on regular tables are working fine.)

To verify this, the improvement can be temporarily switched off:

ALTER SESSION SET "_FIX_CONTROL"='29302565:OFF';

This results in the suboptimal plan, confirming the improvement with the bug fix.


----------------------------------------------------------------------------------------
| Id  | Operation	   | Name	       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		       |    61 |   973K|     3	 (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |		       |    61 |   973K|     3	 (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| VSESSION	       |    61 |  1281 |     3	 (0)| 00:00:01 |
|   3 |   VIEW		   | V$SQL	       |     1 | 16323 |     0	 (0)| 00:00:01 |
|*  4 |    FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 | 16336 |     0	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SQ"."SQL_ID"(+)="S"."SQL_ID" AND
	      "SQ"."CHILD_NUMBER"(+)="S"."SQL_CHILD_NUMBER")
   4 - filter("INST_ID"=USERENV('INSTANCE'))

Summary

In summary, Oracle’s heuristics previously avoided considering fixed tables for Join Predicate Pushdown (JPPD) transformation, leading to suboptimal plans. Enforcing predicate pushing into the view with the PUSH_PRED hint was a workaround. Oracle addressed this in the 21c release, evaluating fixed tables for JPPD. The improvement can be attributed to bug fix 29302565, which corrected JPPD fixed index heuristics.

Thanks for sharing

Nenad Noveljic

More Posts Twitter LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.