Hi,
I have a query performance issue in production.
Oracle 9208 on Sun solaris 5.10
We have VPD enabled.
Developer complains till yesterday the below query ran fine, result within 2secs. Today it is taking more than 7min.
select unique ql.st_cd, ql.sys_lic_type_cd, lc.shrt_dscr, r2.end_dte, r2.use_ind, ql.sys_qual_cd, qc.shrt_dscr, r1.end_dte, r1.use_ind
from qual_lic ql,
(select r.st_cd, r.cddval_cd, r.end_dte, r.use_ind, r.extrnl_use_ind from cddval_rltn r where r.cddmn_name = 'SYS_QUAL_CD') r1,
(select r.st_cd, r.cddval_cd, r.end_dte, r.use_ind, r.extrnl_use_ind from cddval_rltn r where r.cddmn_name = 'SYS_AGY_LIC_TYPE_CD') r2,
(select c.cddval_cd, c.shrt_dscr from cddval c where c.cddmn_name = 'SYS_QUAL_CD') qc,
(select c.cddval_cd, c.shrt_dscr from cddval c where c.cddmn_name = 'SYS_AGY_LIC_TYPE_CD') lc
where ql.st_cd in ('AK','AL','AR','AS','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS
','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','UT','VA','VI','VT','WA','WI','WV','WY')
and r1.cddval_cd = ql.sys_qual_cd
and r1.st_cd = ql.st_cd
and qc.cddval_cd = ql.sys_qual_cd
and r2.cddval_cd = ql.sys_lic_type_cd;
and r2.st_cd = ql.st_cd and lc.cddval_cd = ql.sys_lic_type_cd order by 1, 2, 6;
When I run the query without a context (VPD) which is on cust_id, the query still returns the result in a second but when I set the context it takes a long time and explain plan changes too. I will put the traced session output which might help.
Without Context
select unique ql.st_cd, ql.sys_lic_type_cd, lc.shrt_dscr, r2.end_dte, r2.use_ind, ql.sys_qual_cd, qc.shrt_dscr, r1.end_dte, r1.use_ind
from qual_lic ql,
(select r.st_cd, r.cddval_cd, r.end_dte, r.use_ind, r.extrnl_use_ind from cddval_rltn r where r.cddmn_name = 'SYS_QUAL_CD') r1,
(select r.st_cd, r.cddval_cd, r.end_dte, r.use_ind, r.extrnl_use_ind from cddval_rltn r where r.cddmn_name = 'SYS_AGY_LIC_TYPE_CD') r2,
(select c.cddval_cd, c.shrt_dscr from cddval c where c.cddmn_name = 'SYS_QUAL_CD') qc,
(select c.cddval_cd, c.shrt_dscr from cddval c where c.cddmn_name = 'SYS_AGY_LIC_TYPE_CD') lc
where ql.st_cd in ('AK','AL','AR','AS','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS
','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','UT','VA','VI','VT','WA','WI','WV','WY')
and r1.cddval_cd = ql.sys_qual_cd
and r1.st_cd = ql.st_cd
and qc.cddval_cd = ql.sys_qual_cd
and r2.cddval_cd = ql.sys_lic_type_cd
and r2.st_cd = ql.st_cd and lc.cddval_cd = ql.sys_lic_type_cd order by 1, 2, 6
call count cpu elapsed disk query current rows
Parse 1 0.36 0.35 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 248 0.79 0.80 133 13503 12 3703
total 250 1.15 1.16 133 13503 12 3703
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)
Rows Row Source Operation
3703 SORT UNIQUE
3703 TABLE ACCESS BY INDEX ROWID OBJ#(25576)
7407 NESTED LOOPS
3703 NESTED LOOPS
3703 HASH JOIN
9282 HASH JOIN
1783 TABLE ACCESS FULL OBJ#(24355)
9370 INDEX FAST FULL SCAN OBJ#(24918) (object id 24918)
688 TABLE ACCESS FULL OBJ#(24355)
3703 TABLE ACCESS BY INDEX ROWID OBJ#(25576)
3703 INDEX RANGE SCAN OBJ#(25577) (object id 25577)
3703 INDEX RANGE SCAN OBJ#(25577) (object id 25577)
Rows Execution Plan
0 SELECT STATEMENT GOAL: CHOOSE
3703 SORT (UNIQUE)
3703 HASH JOIN
7407 HASH JOIN
3703 HASH JOIN
3703 HASH JOIN
9282 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'QUAL_LIC_PK' (UNIQUE)
1783 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CDDVAL_RLTN'
9370 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CDDVAL_RLTN'
688 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CDDVAL'
3703 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CDDVAL'
With Context :
select unique ql.st_cd, ql.sys_lic_type_cd, lc.shrt_dscr, r2.end_dte, r2.use_ind, ql.sys_qual_cd, qc.shrt_dscr, r1.end_dte, r1.use_ind
from qual_lic ql,
(select r.st_cd, r.cddval_cd, r.end_dte, r.use_ind, r.extrnl_use_ind from cddval_rltn r where r.cddmn_name = 'SYS_QUAL_CD') r1,
(select r.st_cd, r.cddval_cd, r.end_dte, r.use_ind, r.extrnl_use_ind from cddval_rltn r where r.cddmn_name = 'SYS_AGY_LIC_TYPE_CD') r2,
(select c.cddval_cd, c.shrt_dscr from cddval c where c.cddmn_name = 'SYS_QUAL_CD') qc,
(select c.cddval_cd, c.shrt_dscr from cddval c where c.cddmn_name = 'SYS_AGY_LIC_TYPE_CD') lc
where ql.st_cd in ('AK','AL','AR','AS','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS
','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','UT','VA','VI','VT','WA','WI','WV','WY')
and r1.cddval_cd = ql.sys_qual_cd
and r1.st_cd = ql.st_cd
and qc.cddval_cd = ql.sys_qual_cd
and r2.cddval_cd = ql.sys_lic_type_cd
and r2.st_cd = ql.st_cd and lc.cddval_cd = ql.sys_lic_type_cd order by 1, 2, 6
call count cpu elapsed disk query current rows
Parse 1 0.23 0.22 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 248 548.06 535.71 67 37031710 12 3703
total 250 548.29 535.94 67 37031710 12 3703
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)
Rows Row Source Operation
3703 SORT UNIQUE
3703 NESTED LOOPS
6905 NESTED LOOPS
6992 NESTED LOOPS
335762 MERGE JOIN CARTESIAN
827 TABLE ACCESS BY INDEX ROWID CDDVAL
827 INDEX RANGE SCAN CDDVAL_PK (object id 25577)
335762 BUFFER SORT
406 TABLE ACCESS BY INDEX ROWID CDDVAL
406 INDEX RANGE SCAN CDDVAL_PK (object id 25577)
6992 INLIST ITERATOR
6992 INDEX RANGE SCAN QUAL_LIC_PK (object id 24918)
6905 TABLE ACCESS BY INDEX ROWID CDDVAL_RLTN
6905 INDEX UNIQUE SCAN CDDVAL_RLTN_PK (object id 24356)
3703 TABLE ACCESS BY INDEX ROWID CDDVAL_RLTN
3703 INDEX UNIQUE SCAN CDDVAL_RLTN_PK (object id 24356)
Rows Execution Plan
0 SELECT STATEMENT GOAL: CHOOSE
3703 SORT (UNIQUE)
3703 HASH JOIN
6905 HASH JOIN
6992 HASH JOIN
335762 HASH JOIN
827 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'QUAL_LIC_PK' (UNIQUE)
827 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CDDVAL_RLTN'
335762 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CDDVAL_RLTN'
406 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CDDVAL'
406 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CDDVAL'
Till now my research :
1. Checked the indexes.
2. Checked statistics (Analyzed again using dbms_stats)
3. Checked data size - it has grown not more than 0.1% since last 2-3days.
4. Tried to use No_BUFFER hint.
Please help if someone can.
Thanks,
Ankit.