One of our ApEx developers sent me an email asking “could you please speed up my query?”. The query is using bind variable and the problem is the query took less than 1 second executed on Production but took 7-8 secs on Development database. So the first approach for this kind of performance tuning is to compare both execution plan.
Execution plan from development database
------------------------------------------------------------------------------- | Id | Operation | Name | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | |* 2 | FILTER | | |* 3 | HASH JOIN OUTER | | |* 4 | HASH JOIN OUTER | | |* 5 | HASH JOIN OUTER | | | 6 | NESTED LOOPS OUTER | | | 7 | NESTED LOOPS | | | 8 | NESTED LOOPS | | | 9 | NESTED LOOPS OUTER | | |* 10 | HASH JOIN | | | 11 | NESTED LOOPS | | |* 12 | TABLE ACCESS FULL | PE_ELIGIBILITY | |* 13 | INDEX UNIQUE SCAN | PS_PERSONNEL_PK | |* 14 | VIEW | index$_join$_004 | |* 15 | HASH JOIN | | | 16 | INDEX FAST FULL SCAN | EP_MAIN_FK1 | | 17 | INDEX FAST FULL SCAN | EP_MAIN_FK2 | | 18 | TABLE ACCESS BY INDEX ROWID| EP_STATUS_TABLE | |* 19 | INDEX UNIQUE SCAN | EP_STATUS_TABLE_PK | |* 20 | TABLE ACCESS BY INDEX ROWID | PS_PERS_IDENTITY | |* 21 | INDEX RANGE SCAN | PS_PERS_IDENTITY_NK1 | |* 22 | TABLE ACCESS BY INDEX ROWID | AUTH_ACCOUNT_USER_DETAILS | |* 23 | INDEX UNIQUE SCAN | AUTH_ACCOUNT_USER_DETAILS_UK1 | |* 24 | INDEX UNIQUE SCAN | STUDENT_UNX | | 25 | VIEW | PS_PERS_STATUS_VR2 | | 26 | HASH GROUP BY | | | 27 | INDEX FULL SCAN | PS_PERS_STATUS_UK1 | | 28 | VIEW | MR_PS_PERS_CLEVEL_VR3 | | 29 | HASH GROUP BY | | | 30 | INDEX FAST FULL SCAN | PS_PERS_CLEVEL_IFK1 | | 31 | VIEW | PS_PERS_NAME_VR1 | | 32 | HASH GROUP BY | | | 33 | INDEX FAST FULL SCAN | PS_PERS_NAME_NK1 | | 34 | NESTED LOOPS OUTER | | |* 35 | MAT_VIEW ACCESS FULL | HR_OU_EMP_JOB_NOW_RANK_VM1 | |* 36 | INDEX UNIQUE SCAN | HR_CODES_CLEVEL5_SECTIO_VM1_PK | | 37 | SORT UNIQUE | | | 38 | UNION-ALL | | |* 39 | FILTER | | | 40 | FAST DUAL | | |* 41 | TABLE ACCESS FULL | MR_AUTHORISATION | | 42 | NESTED LOOPS OUTER | | |* 43 | MAT_VIEW ACCESS FULL | HR_OU_EMP_JOB_NOW_RANK_VM1 | |* 44 | INDEX UNIQUE SCAN | HR_CODES_CLEVEL5_SECTIO_VM1_PK | |* 45 | TABLE ACCESS BY INDEX ROWID| HR_OU_EMP_JOB_OCCUP_HIST | |* 46 | INDEX RANGE SCAN | HR_OU_EMP_JOB_OCCUP_HIST_PK | -------------------------------------------------------------------------------
Execution plan from production database
--------------------------------------------------------------------------------- | Id | Operation | Name | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | |* 2 | FILTER | | |* 3 | HASH JOIN OUTER | | |* 4 | HASH JOIN OUTER | | |* 5 | HASH JOIN OUTER | | | 6 | NESTED LOOPS SEMI | | | 7 | NESTED LOOPS OUTER | | | 8 | NESTED LOOPS | | | 9 | NESTED LOOPS | | | 10 | NESTED LOOPS OUTER | | |* 11 | HASH JOIN | | | 12 | NESTED LOOPS | | |* 13 | TABLE ACCESS FULL | PE_ELIGIBILITY | |* 14 | INDEX UNIQUE SCAN | PS_PERSONNEL_PK | |* 15 | VIEW | index$_join$_004 | |* 16 | HASH JOIN | | | 17 | INDEX FAST FULL SCAN | EP_MAIN_FK1 | | 18 | INDEX FAST FULL SCAN | EP_MAIN_FK2 | | 19 | TABLE ACCESS BY INDEX ROWID | EP_STATUS_TABLE | |* 20 | INDEX UNIQUE SCAN | EP_STATUS_TABLE_PK | |* 21 | TABLE ACCESS BY INDEX ROWID | PS_PERS_IDENTITY | |* 22 | INDEX RANGE SCAN | PS_PERS_IDENTITY_NK1 | |* 23 | TABLE ACCESS BY INDEX ROWID | AUTH_ACCOUNT_USER_DETAILS | |* 24 | INDEX UNIQUE SCAN | AUTH_ACCOUNT_USER_DETAILS_UK1 | |* 25 | INDEX UNIQUE SCAN | STUDENT_UNX | |* 26 | TABLE ACCESS BY INDEX ROWID | HR_OU_EMP_JOB_OCCUP_HIST | |* 27 | INDEX RANGE SCAN | HR_OU_EMP_JOB_OCCUP_HIST_PK | | 28 | VIEW | PS_PERS_STATUS_VR2 | | 29 | HASH GROUP BY | | | 30 | INDEX FULL SCAN | PS_PERS_STATUS_UK1 | | 31 | VIEW | MR_PS_PERS_CLEVEL_VR3 | | 32 | HASH GROUP BY | | | 33 | INDEX FAST FULL SCAN | PS_PERS_CLEVEL_IFK1 | | 34 | VIEW | PS_PERS_NAME_VR1 | | 35 | HASH GROUP BY | | | 36 | INDEX FAST FULL SCAN | PS_PERS_NAME_NK1 | | 37 | NESTED LOOPS OUTER | | |* 38 | MAT_VIEW ACCESS BY INDEX ROWID | HR_OU_EMP_JOB_NOW_RANK_VM1 | |* 39 | INDEX RANGE SCAN | HR_OU_EMP_JOB_NOW_RANK_VM1_NX1 | |* 40 | INDEX UNIQUE SCAN | HR_CODES_CLEVEL5_SECTIO_VM1_PK | | 41 | SORT UNIQUE | | | 42 | UNION-ALL | | |* 43 | FILTER | | | 44 | FAST DUAL | | |* 45 | TABLE ACCESS FULL | MR_AUTHORISATION | | 46 | NESTED LOOPS OUTER | | |* 47 | MAT_VIEW ACCESS BY INDEX ROWID| HR_OU_EMP_JOB_NOW_RANK_VM1 | |* 48 | INDEX RANGE SCAN | HR_OU_EMP_JOB_NOW_RANK_VM1_NX1 | |* 49 | INDEX UNIQUE SCAN | HR_CODES_CLEVEL5_SECTIO_VM1_PK | ---------------------------------------------------------------------------------
P.S : Due to the canvas width limitation I’ve to remove the cost,bytes,cardinality, etc columns
Can you see the diff between the good and the evil? Yes on Production the Materialized View executed using index by rowid however on Development the execution plan choose to use Full scan. The next step is to check if there is index on the MV and viola! it does’t exist. After the index created the query now executed as fast as Production.
On performance tuning task whenever possible always compare the good and the evil and you’ll see the light
Cheers
Fiedi Zheng
A Journey in The Cloud 