Oracle

March 29, 2012

Tuning sql with predicates on Scalar Subqueries

Filed under: Uncategorized — srivenu @ 12:13 am

Due to lack of standardization on technical terms, i want to mention at the outset that by “Scalar subqueries” i meant subqueries appearing in the FROM clause. The Scalar subquery executes for each row of the driving query and returns one row. The scalar subqueries could be converted into outer joins, but if there are predicates in the scalar subqueries or on values returned by them, we need to take care of how we write the predicates using outer joins lest they become outer join killers.

Following is one sql that i worked on recently.


SELECT	....
FROM	(
	SELECT	TA.ID,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'FT'
		) 	TY,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'EOI'
		)	EOI,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'IG'
		)	IG,
		....
	FROM 	TASK TA
	WHERE	TA.NAME = 'HPE'
	)
WHERE	IG = 'N'
AND	EOI = '123456'
AND	TY = 'PP'
/

Following is the plan and some relevant execution stats


------------------------------------------------------------------------------------
| Id  | Operation            | Name                    | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |                         |      1 |      1 |    5003 |
|*  2 |   INDEX RANGE SCAN   | IDX_TASK_NA_ID          |      1 |      0 |    5003 |
|*  3 |    INDEX RANGE SCAN  | IDX_PROP_NA_ID_VA       |    902 |    902 |    2801 |
|*  4 |     INDEX RANGE SCAN | IDX_PROP_NA_ID_VA       |    613 |    613 |    1910 |
|*  5 |      INDEX RANGE SCAN| IDX_PROP_NA_ID_VA       |      0 |      0 |       0 |
------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / TA@SEL$2
   3 - SEL$5        / PROP@SEL$5
   4 - SEL$4        / PROP@SEL$4
   5 - SEL$3        / PROP@SEL$3

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

   2 - access("TA"."NAME"='HPE')
       filter((='N' AND ='123456' AND ='PP'))
   3 - access("PROP"."NAME"='IG' AND "PROP"."ID"=:B1)
   4 - access("PROP"."NAME"='EOI' AND "PROP"."ID"=:B1)
   5 - access("PROP"."NAME"='FT' AND "PROP"."ID"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   3 - "PROP"."VAL"[VARCHAR2,254]
   4 - "PROP"."VAL"[VARCHAR2,254]
   5 - "PROP"."VAL"[VARCHAR2,254]

The following select on TASK table retrieves 902 rows.


SELECT	TA.ID, ......
FROM	TASK TA
WHERE	TA.NAME = 'HPE'

The IDX_TASK_NA_ID index on TASK table is on columns NAME, ID, …(and a few other in that order)
The IDX_PROP_NA_ID_VA index on PROP table is on the columns NAME, ID, VAL (in that order)

The code path of the plan can be deduced as follows


Step 1 - Get columns ID, .. from IDX_TASK_NA_ID where TA.NAME = 'HPE'
Step 2 -  for each row from Step 1, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'IG' and PROP.ID = TA.ID
Step 3 -   for each row from Step 2, check if IG = 'N' if true go to Step 4 else go to Step 1 and retrieve next row
Step 4 -    for each row from Step 3, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'EOI' and PROP.ID = TA.ID
Step 5 -     for each row from Step 4, check if EOI = '123456', if true go to Step 6 else go to Step 1 and retrieve next row
Step 6 -      for each row from Step 5, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'PP' and PROP.ID = TA.ID
Step 7   -     for each row from Step 6, check if TY = 'PP', if true select the row else go to Step 1 and retrieve next row

Steps 2, 4 & 6 may in fact not result in an actual index access due to something called “scalar subquery caching”. You could refer to this “exchange” between the masters for more info.
So from the execution stats, we can make out that,
At Step 1, 902 rows were retrieved from TASK table.
At Step 2, the IDX_PROP_NA_ID_VA index was accessed 902 times to retrieve PROP.NAME for that ID, out of the 902 rows, only 613 rows had PROP.NAME = ‘IG’.
At Step 4, the IDX_PROP_NA_ID_VA index was accessed 613 times to retrieve PROP.NAME for that ID, out of the 613 rows, 0 rows had PROP.NAME = ‘EOI’.
The rest of the steps were not executed at all.

The key to tuning the above query is to see that the predicates are ordered in the right way and see that the max throw away occurs at the first occassion itself. This would have been easier to control if outer joins were used instead of the scalar subquery variant. I couldn’t find (atleast in 10g) any hint or outline to order the evaluation of scalar subqueries. But we are not without hope as we could either use the ORDERED_PREDICATE hint or put the predicates in required order and the CBO automatically takes the order into consideration.

Following are the stats for the different variations in the order of predicates. Using the predicate EOI = ‘123456’ (which has the max throwaway) at the start will result in the best performance.




********************************************************************************

WHERE   TY = 'PP'
AND     EOI = '123456'
AND     IG = 'N'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.04          0       5914          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.04          0       5914          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5914 pr=0 pw=0 time=42585 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=5914 pr=0 pw=0 time=42556 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2808 pr=0 pw=0 time=19535 us)(object id 169317)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=16792 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   TY = 'PP'
AND     IG = 'N'
AND     EOI = '123456'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.05          0       7816          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.06          0       7816          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=7816 pr=0 pw=0 time=59340 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=7816 pr=0 pw=0 time=59308 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2808 pr=0 pw=0 time=21716 us)(object id 169317)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2806 pr=0 pw=0 time=18735 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=11329 us)(object id 169317)

********************************************************************************

WHERE   IG = 'N'
AND     TY = 'PP'
AND     EOI = '123456'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.04          0       6905          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.04          0       6905          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=6905 pr=0 pw=0 time=41378 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=6905 pr=0 pw=0 time=41352 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2801 pr=0 pw=0 time=14822 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1902 pr=0 pw=0 time=9199 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=9992 us)(object id 169317)

********************************************************************************

WHERE   IG = 'N'
AND     EOI = '123456'
AND     TY = 'PP'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.03          0       5003          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.03          0       5003          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5003 pr=0 pw=0 time=31018 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=5003 pr=0 pw=0 time=30988 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2801 pr=0 pw=0 time=14333 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=10980 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   EOI = '123456'
AND     IG = 'N'
AND     TY = 'PP'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.02          0       3106          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          0       3106          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3106 pr=0 pw=0 time=22878 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=3106 pr=0 pw=0 time=22853 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=17653 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   EOI = '123456'
AND     TY = 'PP'
AND     IG = 'N'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0       3106          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0       3106          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3106 pr=0 pw=0 time=18483 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=3106 pr=0 pw=0 time=18454 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=14361 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

Advertisements

1 Comment »

  1. Good One

    Comment by vkompally — June 17, 2015 @ 1:29 am | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: