Home » RDBMS Server » Performance Tuning » Pl Help: to Tune This query.taking 10 min
Pl Help: to Tune This query.taking 10 min [message #65843] Mon, 10 January 2005 23:40
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
My prob is when I am using Connect by clause my tables main_trn1 and main_trn2 scaned full.But when I have removed connect by clause my plan has been changed dramatically.

--With connect by clause

select
c.trans_id,c.cop_id, c.parent_id, c.entry_date, c.user_id,c.l1e1_id,c.l1e1_name,c.l2e1_id,
c.l2e1_name, c.l1e2_id,c.l1e2_name,c.l2e2_id,c.l2e2_name,c.l1e3_id,c.l1e3_name, c.l2e3_id,c.l2e3_name,c.l1e4_id,
c.l1e4_name,c.l2e4_id,c.l2e4_name, c.l1e5_id,c.l1e5_name,c.l2e5_id,c.l2e5_name,c.l1e7_id,c.l1e6_id, c.l1e6_name,
c.l1e8_id,c.l1e8_name,c.logentry,c.user_id as assignedTo
from (
select
rownum r, main_trn1.trans_id,main_trn1.cop_id,
main_trn1.parent_id,to_char(main_trn1.entry_date,'MM/DD/YYYY HH:MM:SS') as entry_date, main_trn1.user_id, main_trn1.l1e1_id,
main_trn1.l1e1_name,main_trn1.l2e1_id,main_trn1.l2e1_name, main_trn1.l1e2_id,main_trn1.l1e2_name,main_trn1.l2e2_id,
main_trn1.l2e2_name, main_trn1.l1e3_id,main_trn1.l1e3_name,main_trn1.l2e3_id,main_trn1.l2e3_name, main_trn1.l1e4_id,
main_trn1.l1e4_name,main_trn1.l2e4_id,main_trn1.l2e4_name, main_trn1.l1e5_id,main_trn1.l1e5_name,main_trn1.l2e5_id,main_trn1.l2e5_name,
main_trn1.l1e7_id, main_trn1.l1e6_id,main_trn1.l1e6_name, main_trn1.l1e8_id,main_trn1.l1e8_name, main_trn2.logentry,
main_trn2.user_id as assignedTo
from
main_trn1,main_trn2
where
main_trn2.trans_id = main_trn1.trans_id and main_trn1.cop_id =3
and l1e1_id=14
connect by prior main_trn1.trans_id = main_trn1.parent_id start with main_trn1.parent_id is null )
c where c.r between 1 and 15

Exexution Plan :

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=568 Card=3632 Bytes=
8676848)

1 0 VIEW (Cost=568 Card=3632 Bytes=8676848)
2 1 COUNT
3 2 FILTER
4 3 CONNECT BY (WITH FILTERING)
5 4 FILTER
6 5 COUNT
7 6 MERGE JOIN (Cost=568 Card=3632 Bytes=1569024)
8 7 SORT (JOIN) (Cost=473 Card=3632 Bytes=133294
4)

9 8 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=6
6 Card=3632 Bytes=1332944)

10 7 SORT (JOIN) (Cost=95 Card=3634 Bytes=236210)
11 10 TABLE ACCESS (FULL) OF 'MAIN_TRN1' (Cost=1
3 Card=3634 Bytes=236210)

12 4 HASH JOIN
13 12 CONNECT BY PUMP
14 12 COUNT
15 14 MERGE JOIN (Cost=568 Card=3632 Bytes=1569024)
16 15 SORT (JOIN) (Cost=473 Card=3632 Bytes=133294
4)

17 16 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=6
6 Card=3632 Bytes=1332944)

18 15 SORT (JOIN) (Cost=95 Card=3634 Bytes=236210)
19 18 TABLE ACCESS (FULL) OF 'MAIN_TRN1' (Cost=1
3 Card=3634 Bytes=236210)

Statistics
----------------------------------------------------------
0 recursive calls
9160 db block gets
3216 consistent gets
6045 physical reads
0 redo size
7977 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
7 sorts (disk)
15 rows processed

--without connect by clause..

select
c.trans_id,c.cop_id, c.parent_id, c.entry_date, c.user_id,c.l1e1_id,c.l1e1_name,c.l2e1_id,
c.l2e1_name, c.l1e2_id,c.l1e2_name,c.l2e2_id,c.l2e2_name,c.l1e3_id,c.l1e3_name, c.l2e3_id,c.l2e3_name,c.l1e4_id,
c.l1e4_name,c.l2e4_id,c.l2e4_name, c.l1e5_id,c.l1e5_name,c.l2e5_id,c.l2e5_name,c.l1e7_id,c.l1e6_id, c.l1e6_name,
c.l1e8_id,c.l1e8_name,c.logentry,c.user_id as assignedTo
from (
select
rownum r, main_trn1.trans_id,main_trn1.cop_id,
main_trn1.parent_id,to_char(main_trn1.entry_date,'MM/DD/YYYY HH:MM:SS') as entry_date, main_trn1.user_id, main_trn1.l1e1_id,
main_trn1.l1e1_name,main_trn1.l2e1_id,main_trn1.l2e1_name, main_trn1.l1e2_id,main_trn1.l1e2_name,main_trn1.l2e2_id,
main_trn1.l2e2_name, main_trn1.l1e3_id,main_trn1.l1e3_name,main_trn1.l2e3_id,main_trn1.l2e3_name, main_trn1.l1e4_id,
main_trn1.l1e4_name,main_trn1.l2e4_id,main_trn1.l2e4_name, main_trn1.l1e5_id,main_trn1.l1e5_name,main_trn1.l2e5_id,main_trn1.l2e5_name,
main_trn1.l1e7_id, main_trn1.l1e6_id,main_trn1.l1e6_name, main_trn1.l1e8_id,main_trn1.l1e8_name, main_trn2.logentry,
main_trn2.user_id as assignedTo
from
main_trn1,main_trn2
where
main_trn2.trans_id = main_trn1.trans_id and main_trn1.cop_id =3
and l1e1_id=14
)
c
where c.r between 1 and 15

Execution Plan :

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=68 Card=1 Bytes=2402
)

1 0 VIEW (Cost=68 Card=1 Bytes=2402)
2 1 COUNT
3 2 NESTED LOOPS (Cost=68 Card=1 Bytes=432)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'MAIN_TRN1' (Cost=1
Card=1 Bytes=65)

5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP INDEX (SINGLE VALUE) OF 'MAIN_TRN1_COPID'
7 3 TABLE ACCESS (FULL) OF 'MAIN_TRN2' (Cost=66 Card=1 B
ytes=367)

--------------------
So My prob is when I am using Connect by clause my tables main_trn1 and main_trn2 scaned full.But when I have removed connect by clause my plan has been changed dramatically.
Now I don't want to do full table scan with connect by clause .. Is there any way..I have to use connect by clause but i don't want full table scans..

main_trn1 :
SQL> desc main_trn1
Name Null? Type
----------------------- -------- ---------------
TRANS_ID NOT NULL NUMBER(10)
COP_ID NOT NULL NUMBER(9)
USER_ID NOT NULL VARCHAR2(20)
ENTRY_DATE NOT NULL DATE
L1E1_ID NUMBER(9)
L1E2_ID NUMBER(9)
L1E3_ID NUMBER(9)
L1E4_ID NUMBER(9)
L1E5_ID NUMBER(9)
L2E1_ID NUMBER(9)
L2E2_ID NUMBER(9)
L2E3_ID NUMBER(9)
L2E4_ID NUMBER(9)
L2E5_ID NUMBER(9)
L1E1_NAME NUMBER(9)
L1E2_NAME NUMBER(9)
L1E3_NAME NUMBER(9)
L1E4_NAME NUMBER(9)
L1E5_NAME NUMBER(9)
L2E1_NAME NUMBER(9)
L2E2_NAME NUMBER(9)
L2E3_NAME NUMBER(9)
L2E4_NAME NUMBER(9)
L2E5_NAME NUMBER(9)
L1E7_ID NUMBER(9)
L1E8_ID NUMBER(9)
L1E6_ID NUMBER(9)
L1E7_NAME NUMBER(9)
L1E8_NAME NUMBER(9)
L1E6_NAME NUMBER(9)
PARENT_ID NUMBER(10)
L2E6_ID NUMBER(9)
L2E7_ID NUMBER(9)
L2E8_ID NUMBER(9)
L2E6_NAME NUMBER(9)
L2E7_NAME NUMBER(9)
L2E8_NAME NUMBER(9)

main_trn2 :
Name Null? Type
----------------------- -------- ----------------
TRANS_ID NOT NULL NUMBER(10)
LOGENTRY VARCHAR2(4000)
USER_ID VARCHAR2(20)

Pl. help ..Thax..
Previous Topic: Views or Tables?
Next Topic: checking fragmentation
Goto Forum:
  


Current Time: Thu Mar 28 11:26:50 CDT 2024