Home » RDBMS Server » Performance Tuning » Pl Help To Tune Full table Scans.
Pl Help To Tune Full table Scans. [message #65666] Thu, 25 November 2004 23:01 Go to next message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Dear frends,

we r using ora 9.2. I have sql and execution  plan for that.. I wanted to know that can I tu ne any more This sql..

sql:

EXPLAIN PLAN
  SET STATEMENT_ID = '1'
  FOR
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_trn1.trans_id = main_trn2.trans_id 
 and main_trn1.cop_id =1 
connect by prior main_trn1.trans_id = main_trn1.parent_id start  with main_trn1.parent_id is null) c
where c.r between 5 and 10

Execution Plan :

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
| Id  | Operation                    &#124  Name       | Rows  | Bytes &#124TempSpc| Cost 
------------------------------------------------------------------------------------
&#124   0 | SELECT STATEMENT             &#124             &#124  3632 &#124  8519K&#124       &#124   568
&#124*  1 &#124  VIEW                        &#124             &#124  3632 &#124  8519K&#124       &#124   568
&#124   2 &#124   COUNT                      &#124             &#124       &#124       &#124       &#124      
&#124*  3 &#124    FILTER                    &#124             &#124       &#124       &#124       &#124      
&#124*  4 &#124     CONNECT BY WITH FILTERING&#124             &#124       &#124       &#124       &#124      
&#124*  5 &#124      FILTER                  &#124             &#124       &#124       &#124       &#124      
&#124   6 &#124       COUNT                  &#124             &#124       &#124       &#124       &#124      

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
&#124   7 &#124        MERGE JOIN            &#124             &#124  3632 &#124  1532K&#124       &#124   568
&#124   8 &#124         SORT JOIN            &#124             &#124  3632 &#124  1301K&#124  2916K&#124   473
&#124   9 &#124          TABLE ACCESS FULL   | MAIN_TRN2   &#124  3632 &#124  1301K&#124       &#124    66
&#124* 10 &#124         SORT JOIN            &#124             &#124  3634 &#124   230K&#124   980K&#124    95
&#124  11 &#124          TABLE ACCESS FULL   | MAIN_TRN1   &#124  3634 &#124   230K&#124       &#124    13
&#124  12 &#124      HASH JOIN               &#124             &#124       &#124       &#124       &#124      
&#124  13 &#124       CONNECT BY PUMP        &#124             &#124       &#124       &#124       &#124      
&#124  14 &#124       COUNT                  &#124             &#124       &#124       &#124       &#124      
&#124  15 &#124        MERGE JOIN            &#124             &#124  3632 &#124  1532K&#124       &#124   568
&#124  16 &#124         SORT JOIN            &#124             &#124  3632 &#124  1301K&#124  2916K&#124   473
&#124  17 &#124          TABLE ACCESS FULL   | MAIN_TRN2   &#124  3632 &#124  1301K&#124       &#124    66

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
&#124* 18 &#124         SORT JOIN            &#124             &#124  3634 &#124   230K&#124   980K&#124    95
&#124  19 &#124          TABLE ACCESS FULL   | MAIN_TRN1   &#124  3634 &#124   230K&#124       &#124    13
------------------------------------------------------------------------------------

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

   1 - filter("C"."R"<=10 AND "C"."R">=5)
   3 - filter("MAIN_TRN1"."COP_ID"=1)
   4 - filter("MAIN_TRN1"."PARENT_ID" IS NULL)
   5 - filter("MAIN_TRN1"."PARENT_ID" IS NULL)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
  10 - access("MAIN_TRN1"."TRANS_ID"="MAIN_TRN2"."TRANS_ID")
       filter("MAIN_TRN1"."TRANS_ID"="MAIN_TRN2"."TRANS_ID")
  18 - access("MAIN_TRN1"."TRANS_ID"="MAIN_TRN2"."TRANS_ID")
       filter("MAIN_TRN1"."TRANS_ID"="MAIN_TRN2"."TRANS_ID")

Note: cpu costing is off

39 rows selected.

Frends Here main_trn1.trans_id is primary key.

Bhavin.....

Report message to a moderator

Re: Pl Help To Tune Full table Scans. [message #65667 is a reply to message #65666] Fri, 26 November 2004 03:45 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
The FTS's are over 3634 and 3632 rows, shouldn't be too big a problem.

Maybe you could index main_trn1.cop_id if less then 10-20% of all rows contain 1

hth

Report message to a moderator

Previous Topic: Interpretation Trace file
Next Topic: which version of tomcat is compatible with oracle 9i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 00:02:15 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.