Home » RDBMS Server » Performance Tuning » Need help in Optimizing this query (oracle 8i)
Need help in Optimizing this query [message #379963] Thu, 08 January 2009 12:12 Go to next message
RiverX
Messages: 9
Registered: March 2008
Junior Member
Hello all,

I've been trying really hard to optimize the following query for the past day or two and i just can't.
A little help would be really appreciated.

Here is the Query.

SET AUTOTRACE ON
SELECT   DISTINCT p.chi_Id,
                  p.Status,
                  TRIM(s.sch_Name),
                  t.TownName2,
                  p.hs_College,
                  p.Junior_Senior,
                  p.chi_Name,
                  p.chi_Addr1,
                  p.chi_Addr2,
                  p.chi_Addr3,
                  e.Worked,
                  e.Precinct,
                  e.Position,
                  e.Technical_Judge,
                  p.Date_Train,
                  p.Date_tech_Train,
                  p.Date_e1_Train,
                  p.Date_e2_Train,
                  p.Date_e3_Train,
                  p.eMail_Addr,
                  p.HomepHone,
                  p.WorkpHone,
                  p.CellpHone,
                  p.chi_bDate,
                  s.sch_Precinct,
                  p.Live_pre,
                  (SELECT COUNT(* )
                   FROM   ej_el_Asg e2
                   WHERE  e2.chi_Id = e.chi_Id
                          AND e2.Worked = '1'),
                  (SELECT MAX(cl.Class_Date)
                   FROM   ej_cl_Asg cl
                   WHERE  cl.chi_Id = e.chi_Id
                          AND cl.Election = e.Election)
FROM     Cook.ej_el_Asg e,
         Cook.pwMast p,
         Cook.sch_Mast s,
         Cook.Township t
WHERE    p.chi_Id IS NOT  NULL 
         AND p.chi_Id LIKE 'ST%'
         AND p.chi_Id = e.chi_Id (+) 
         AND e.Election (+)  = '110408'
         AND p.sch_num = s.sch_num (+) 
         AND Substr(s.sch_Precinct,1,2) = t.Township (+) 
ORDER BY 3



Here's what i got when i did a autotrace on it

Execution Plan                                                                                
----------------------------------------------------------                                    
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=224 Card=1 Bytes=126)                                                                                                                                                                             
   1    0   SORT (UNIQUE) (Cost=223 Card=1 Bytes=126)                                         
   2    1     NESTED LOOPS (OUTER) (Cost=221 Card=1 Bytes=126)                                
   3    2       NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=109)                                
   4    3         NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=99)                               
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'PWMAST' (Cost=3 Card=1 Bytes=65)        
   6    5             INDEX (RANGE SCAN) OF 'PWCHI' (NON-UNIQUE) (Cost=2 Card=1)              
   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'SCH_MAST' (Cost=1 Card=150 Bytes=5100)  
   8    7             INDEX (UNIQUE SCAN) OF 'SCH_MAST1' (UNIQUE)                             
   9    3         TABLE ACCESS (FULL) OF 'TOWNSHIP' (Cost=1 Card=30 By tes=300)               
  10    2       TABLE ACCESS (BY INDEX ROWID) OF 'EJ_EL_ASG' (Cost=216 Card=9842 Bytes=167314)
  11   10         INDEX (RANGE SCAN) OF 'EJ_TECH1' (NON-UNIQUE) (Cost=30 Card=9842)           


Statistics                                                
----------------------------------------------------------
        197  recursive calls                              
      25995  db block gets                                
   77980060  consistent gets                              
        150  physical reads                               
       4180  redo size                                    
     648026  bytes sent via SQL*Net to client             
      22958  bytes received via SQL*Net from client       
        205  SQL*Net roundtrips to/from client            
          6  sorts (memory)                               
          1  sorts (disk)                                 
       3048  rows processed             


Thanks in advance

[Updated on: Thu, 08 January 2009 12:18]

Report message to a moderator

Re: Need help in Optimizing this query [message #379965 is a reply to message #379963] Thu, 08 January 2009 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your statistics are out of date, gather them and retry.

Regards
Michel
Re: Need help in Optimizing this query [message #379972 is a reply to message #379965] Thu, 08 January 2009 14:57 Go to previous messageGo to next message
RiverX
Messages: 9
Registered: March 2008
Junior Member
how would i go about doing that?

Take care
Re: Need help in Optimizing this query [message #379973 is a reply to message #379963] Thu, 08 January 2009 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I am sorry to see that both the SEARCH function on this forum and GOOGLE are broken for you.
Please wait patiently for repairs to be completed.
Additional details will be added here when they become available.
Re: Need help in Optimizing this query [message #379975 is a reply to message #379972] Thu, 08 January 2009 15:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_STATS

Regards
Michel
Re: Need help in Optimizing this query [message #380376 is a reply to message #379963] Sun, 11 January 2009 08:05 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Do you really have to use outer join in
p.chi_Id = e.chi_Id (+) 
         AND e.Election (+)  = '110408'
?

2. What is the data type of e.Election column?

3. What are the columns of 'EJ_TECH1' index (theit order )?

4. Check if you have indexes with following columns:
4.1. Table ej_el_Asg - Index ON (chi_Id , Election )
4.2. Table ej_el_Asg - Index ON (chi_Id , Worked )
4.3. Table ej_cl_Asg - Index on (chi_Id, Election )
4.4. Table Township - Index on ( Township )


HTH.
Previous Topic: Purge recyclebin
Next Topic: Performance Prob with Group and Orader by clause
Goto Forum:
  


Current Time: Mon Dec 05 03:05:56 CST 2016

Total time taken to generate the page: 0.15355 seconds