SQL Tuning

[waitecj]

DB = 9.2.0.6
OS = Solaris 8
4CPU 8GBRAM

select count(*) from TABLE_X;

31968842

select count(*) from TABLE_Y;

31413058

I have a cursor in a procedure that is built on these two tables which returns results in around 25 minutes. Code and explain plan below:

select trn1.t_id, csi.i_id
FROM TABLE_X trn1,
TABLE_Y csi
WHERE TRUNC(trn1.modified_date) < Trunc(Sysdate - 295)
AND csi.i_id = trn1.i_id
AND trn1.y_id IN (30, 31)
AND NOT EXISTS (SELECT trn2.t_id
FROM TABLE_X trn2
WHERE (trn2.i_id = trn1.i_id
AND trn2.t_id <> trn1.t_id));

2588 rows selected.

Elapsed: 00:24:46.21

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=101391 Card=92419 Bytes=2680151)
1 0 FILTER
2 1 NESTED LOOPS (Cost=8972 Card=92419 Bytes=2680151)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=8970 Card=92230 Bytes=2121290)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP AND
6 5 BITMAP OR
7 6 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'y_t_FK6' (NON-UNIQUE) (Cost=6057 Card=3541359)
9 6 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'y_t_FK6' (NON-UNIQUE) (Cost=653 Card=3541359)
11 5 BITMAP CONVERSION (FROM ROWIDS)
12 11 SORT (ORDER BY)
13 12 INDEX (RANGE SCAN) OF 't_FI1' (NON-UNIQUE) (Cost=458 Card=3541359)
14 2 INDEX (UNIQUE SCAN) OF 'i_PK' (UNIQUE)
15 1 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=1 Card=1 Bytes=12)
16 15 INDEX (RANGE SCAN) OF 'i_t_FK4' (NON-UNIQUE) (Cost=3 Card=1)





Statistics
----------------------------------------------------------
3 recursive calls
257 db block gets
13589635 consistent gets
562588 physical reads
0 redo size
55454 bytes sent via SQL*Net to client
2391 bytes received via SQL*Net from client
174 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
2588 rows processed


I have tried the following:

y_ID has 80 distinct values so I created a histogram on that column with 80 buckets, but it didn't alter the plan in any way.

ALTER SESSION SET "_b_tree_bitmap_plans" = FALSE; The plan then looks like this:

select trn1.t_id, csi.i_id
FROM TABLE_X trn1,
TABLE_Y csi
WHERE TRUNC(trn1.modified_date) < Trunc(Sysdate - 295)
AND csi.i_id = trn1.i_id
AND trn1.y_id IN (30, 31)
AND NOT EXISTS (SELECT trn2.t_id
FROM TABLE_X trn2
WHERE (trn2.i_id = trn1.i_id
AND trn2.t_id <> trn1.t_id));

2588 rows selected.

Elapsed: 00:15:18.40

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=121716 Card=92419 Bytes=2680151)
1 0 FILTER
2 1 NESTED LOOPS (Cost=29297 Card=92419 Bytes=2680151)
3 2 INLIST ITERATOR
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=29296 Card=92230 Bytes=2121290)
5 4 INDEX (RANGE SCAN) OF 'y_t_FK6' (NON-UNIQUE) (Cost=1409 Card=3541359)
6 2 INDEX (UNIQUE SCAN) OF 'i_PK' (UNIQUE)
7 1 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=1 Card=1 Bytes=12)
8 7 INDEX (RANGE SCAN) OF 'i_t_FK4' (NON-UNIQUE) (Cost=3 Card=1)





Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
13603236 consistent gets
430254 physical reads
0 redo size
55454 bytes sent via SQL*Net to client
2391 bytes received via SQL*Net from client
174 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2588 rows processed


That takes 15 minutes. I'd like to try to re-write the query to take a few more minutes off the execution time, however, my attempts haven't been very sucessful so far. I thought getting rid of the NOT EXISTS part might help by doing:

select
from (select blah) trn1,
(select blah) trn2,
csi
joins;

But adding that additional join made it even slower. The real killer seems to be y_ID in(30,31) which returns 10% or so of the total rows from TABLE_X, but i'm not sure how else it can be written.

Does anyone have any ideas on how to tune this statement that they'd like to share with the class?

NOTES:
t_ID is the PK on TABLE_X, and i_ID is the PK on TABLE_Y.
y_t_FK6 Index is on column y_ID
t_FI1 is a Functional Based Index on TRUNC(MODIFIED_DATE)

Muchas Gratias.

[tamilselvan]

Create a concatenated index on I_ID+T_ID on TABLE_X.

And change the SQL to:


select trn1.t_id, csi.i_id
FROM TABLE_X trn1,
TABLE_Y csi
WHERE TRUNC(trn1.modified_date) < Trunc(Sysdate - 295)
AND csi.i_id = trn1.i_id
AND trn1.y_id IN (30, 31)
AND NOT EXISTS (SELECT /*+ FIRST_ROWS */ NULL
FROM TABLE_X trn2
WHERE (trn2.i_id = trn1.i_id
AND trn2.t_id <> trn1.t_id));



Tamil

[waitecj]

Thanks Tamil, i'll give it a try.

[waitecj]

I created the concatenated index on i_id+t_id (I_T_001), then re-ran the amended query:

SELECT trn1.t_id,
trn1.i_id
FROM TABLE_X trn1,
TABLE_Y csi
WHERE TRUNC(trn1.aud_modified_date) < Trunc(Sysdate - 290)
AND csi.i_id = trn1.i_id
AND trn1.y_id IN (30, 31)
AND NOT EXISTS (SELECT /*+ FIRST_ROWS */ NULL
FROM TABLE_X trn2
WHERE trn2.i_id = trn1.i_id
AND trn2.t_id <> trn1.t_id);

Elapsed: 00:21:47.49

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=101157 Card=92185 Bytes=2673365)
1 0 FILTER
2 1 NESTED LOOPS (Cost=8972 Card=92185 Bytes=2673365)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=8970 Card=91997 Bytes=2115931)
4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP AND
6 5 BITMAP OR
7 6 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'Y_T_FK6' (NON-UNIQUE) (Cost=6057 Card=3541359)
9 6 BITMAP CONVERSION (FROM ROWIDS)
10 9 INDEX (RANGE SCAN) OF 'Y_T_FK6' (NON-UNIQUE) (Cost=653 Card=3541359)
11 5 BITMAP CONVERSION (FROM ROWIDS)
12 11 SORT (ORDER BY)
13 12 INDEX (RANGE SCAN) OF 'T_FI1' (NON-UNIQUE) (Cost=458 Card=3541359)
14 2 INDEX (UNIQUE SCAN) OF 'I_PK' (UNIQUE)
15 1 INDEX (RANGE SCAN) OF 'I_T_001' (UNIQUE) (Cost=3 Card=1 Bytes=12)



Statistics
----------------------------------------------------------
9 recursive calls
261 db block gets
11148188 consistent gets
558672 physical reads
60 redo size
276 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
0 rows processed

It took almost 22 minutes. An improvement of 3 minutes. I then ran the same amended query after disabling bitmap plans for b-tree index access:

10:47:23 SQL> alter session set "_b_tree_bitmap_plans" = FALSE;

Session altered.

Elapsed: 00:00:00.02
10:47:46 SQL> set autotrace traceonly
10:47:56 SQL> SELECT trn1.t_id,
trn1.i_id
FROM TABLE_X trn1,
TABLE_Y csi
WHERE TRUNC(trn1.aud_modified_date) < Trunc(Sysdate - 290)
AND csi.i_id = trn1.i_id
AND trn1.y_id IN (30, 31)
AND NOT EXISTS (SELECT /*+ FIRST_ROWS */ NULL
FROM TABLE_X trn2
WHERE trn2.i_id = trn1.i_id
AND trn2.t_id <> trn1.t_id);


Elapsed: 00:09:59.80

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=121482 Card=92185 Bytes=2673365)
1 0 FILTER
2 1 NESTED LOOPS (Cost=29297 Card=92185 Bytes=2673365)
3 2 INLIST ITERATOR
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=29296 Card=91997 Bytes=2115931)
5 4 INDEX (RANGE SCAN) OF 'Y_T_FK6' (NON-UNIQUE) (Cost=1409 Card=3541359)
6 2 INDEX (UNIQUE SCAN) OF 'I_PK' (UNIQUE)
7 1 INDEX (RANGE SCAN) OF 'I_T_001' (UNIQUE) (Cost=3 Card=1 Bytes=12)



Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
11158978 consistent gets
421838 physical reads
120 redo size
276 bytes sent via SQL*Net to client
368 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


It ran in 10 minutes. Getting better. An improvement of 5 minutes over the last query when run without bitmap plans.

I'd like to improve it further still, if anyone has any ideas?

Merci

[tamilselvan]

Post the tkprof output here.

Tamil

[waitecj]

There you go...

[tamilselvan]

The indexes are not helpful for this SQL statement.

Try this

SELECT /*+ USE_HASH(csi) */
trn1.trn_id,
trn1.csi_id
FROM ( select /*+ NO_MERGE FULL(a) */
a.trn_id, a.csi_id,
a.aud_modified_date, a.tty_id
from customer_transaction a,
customer_transaction b
where TRUNC(a.aud_modified_date) < Trunc(Sysdate - 290) and
a.tty_id IN (30, 31)
a.csi_id = b.csi_id (+) and
a.trn_id <> b.trn_id(+) and
b.csi_id is NULL
) trn1,
customer_interaction csi
WHERE csi.csi_id = trn1.csi_id ;


Tamil

[waitecj]

The indexes are not helpful for this SQL statement.

After thinking about this comment, I decided to start playing around with some optimizer parameters at the session level. With your original code change above (/*+ FIRST_ROWS */ NULL) and new index I can get it to run in three minutes by altering dynamic sampling\index_caching\index_cost_adj.
Our database is deliberately skewed towards index use, which is good for our application in its current state, but some of the new code that is being implemented seems to favour less aggresive settings.

Thanks for your help :)