Home » RDBMS Server » Performance Tuning » Slow running query -Explain plan attached-pls help! (ORacle,11g,windows server2003)
Slow running query -Explain plan attached-pls help! [message #531531] Wed, 16 November 2011 10:42 Go to next message
aviana
Messages: 106
Registered: July 2007
Senior Member
Hi
I am creating a table from a view.
CREATE TABLE GEO_TB_Localities
 TABLESPACE &table_ts
   STORAGE(INITIAL 3M NEXT 500K PCTINCREASE 0)
   AS 
   SELECT * 
      FROM GEO_V_Localities;

Th create table statement taking around 3and 1/2 hours roughly.
Select statement for view:
CREATE OR REPLACE VIEW GEO_V_Localities
 (
   locality_id, post_town_id, county_id,
   county_name, post_town_name, locality_name, changed_date,
   delivery_office_id, delivery_office_name, 
   route_id, route_name,
   locality_default_code,
   commercial_delivery_points,
   residential_delivery_points
 )
   AS
   SELECT loc.locality_id, dof.post_town_id, cou.county_id, 
          cou.name, pto.name, loc.name, loc.changed_date,
          DECODE
           (
             -- if all buildings in locality are served by the same
             -- delivery office, get this delivery office id,
             -- else null as no default code can be defined 
             MIN (dof.outward_code), 
             MAX (dof.outward_code), 
             MIN (dof.delivery_office_id),
             NULL
           ),
          DECODE
           (
             -- if all buildings in locality are served by the same
             -- delivery office, get this delivery office name,
             -- else null as no default code can be defined
             MIN (dof.outward_code),
             MAX (dof.outward_code), 
             MIN (dof.name),
             NULL
           ),
          DECODE
           (
             MIN (rou.route_id),
             MAX (rou.route_id),
             MIN (rou.route_id),
             NULL
           ),
          DECODE
           (
             MIN (rou.route_id),
             MAX (rou.route_id),
             MIN (NVL(rou.automation_route_code, rou.name)),
             NULL
           ),
          DECODE
           (
             -- if all buildings in locality are served by the same route,
             -- return delivery office outward code and route_id as sortcode
             -- else if all buildings are served by routes of the same
             -- delivery office,
             -- return delivery office outward code as sortcode
             -- else no default code can be defined
             MIN (rou.route_id), 
             MAX (rou.route_id), 
             MIN (LPAD(dof.outward_code,3,'0') ||
                  LPAD (rou.route_id, 5, '0')  ||  '2' || '0000'),
             DECODE
              (
                MIN (dof.outward_code),
                MAX (dof.outward_code),
                MIN (LPAD(dof.outward_code,3,'0') ||
                     '00000'                      || '2' || '0000'),
                NULL
              )
           ),
          SUM (NVL(bld.commercial_delivery_points, 0)),
          SUM (NVL(bld.residential_delivery_points, 0))
      FROM T_Counties         cou,
           T_Post_Towns       pto,
           T_Localities       loc,
           T_Buildings        bld,
           T_Thoroughfares    str,
           T_Routes           rou,
           T_Delivery_Offices dof
-- References to T_Thoroughfares(str) added by F. Dibley Version 1 Software April 11th 2002
      WHERE cou.county_id          = pto.county_id
      AND   pto.post_town_id       = dof.post_town_id
      AND   str.thorfare_id        = bld.thorfare_id
      AND 
       (
         loc.locality_id = bld.locality_id
         OR
         loc.locality_id = bld.secondary_locality_id
         OR
         loc.locality_id = str.locality_id
       )
      AND   bld.route_id           = rou.route_id
      AND   rou.delivery_office_id = dof.delivery_office_id
      AND   NVL(bld.invalid, 'N')  = 'N'
      AND   NOT EXISTS
       (
         SELECT 1
            FROM T_Delivery_offices dof_in,
                 T_Routes           rou_in,
                 T_Buildings        bld_in
            WHERE dof_in.post_town_id          = dof.post_town_id
            AND   dof_in.delivery_office_id    = rou_in.delivery_office_id
            AND   rou_in.route_id              = bld_in.route_id
            AND DECODE
                (
                 bld_in.secondary_locality_id,
                 NULL,
                 loc.secondary_locality_id,
                 bld_in.secondary_locality_id
                )
                IS NOT NULL
            AND   bld_in.locality_id           = loc.locality_id 
            AND   NVL(bld_in.invalid, 'N')     = 'N' 
       )
      GROUP BY loc.locality_id, dof.post_town_id, cou.county_id,
               cou.name, pto.name, loc.name, loc.changed_date;

By going through the explain plan attached, can someone please tell where exactly it is going too slow?
Plan
CREATE TABLE STATEMENT  ALL_ROWSCost: 996,292,096,796                                                  
    46 LOAD AS SELECT                                              
        45 HASH GROUP BY  Bytes: 143,338,734  Cardinality: 930,771                                          
            44 CONCATENATION                                      
                28 FILTER                                  
                    18 NESTED LOOPS                              
                        16 NESTED LOOPS  Cost: 4  Bytes: 154  Cardinality: 1                          
                            14 NESTED LOOPS  Cost: 4  Bytes: 122  Cardinality: 1                      
                                11 NESTED LOOPS  Cost: 3  Bytes: 111  Cardinality: 1                  
                                    8 NESTED LOOPS  Cost: 2  Bytes: 94  Cardinality: 1              
                                        5 NESTED LOOPS  Cost: 1  Bytes: 72  Cardinality: 1          
                                            2 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_BUILDINGS Cost: 0  Bytes: 31  Cardinality: 1      
                                                1 INDEX FULL SCAN INDEX AUTOMATION.BUILDING_2LOCALITY_I Cost: 0  Cardinality: 1  
                                            4 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_ROUTES Cost: 1  Bytes: 41  Cardinality: 1      
                                                3 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.ROUTE_PK Cost: 0  Cardinality: 1  
                                        7 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_DELIVERY_OFFICES Cost: 1  Bytes: 22  Cardinality: 1          
                                            6 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.DELIVERY_OFFICE_PK Cost: 0  Cardinality: 1      
                                    10 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_POST_TOWNS Cost: 1  Bytes: 17  Cardinality: 1              
                                        9 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.POST_TOWN_PK Cost: 0  Cardinality: 1          
                                13 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_COUNTIES Cost: 1  Bytes: 11  Cardinality: 1                  
                                    12 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.COUNTY_PK Cost: 0  Cardinality: 1              
                            15 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.LOCAL_PK Cost: 0  Cardinality: 1                      
                        17 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_LOCALITIES Cost: 0  Bytes: 32  Cardinality: 1                          
                    27 NESTED LOOPS                              
                        25 NESTED LOOPS  Cost: 17  Bytes: 41  Cardinality: 1                          
                            23 NESTED LOOPS  Cost: 16  Bytes: 33  Cardinality: 1                      
                                20 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_BUILDINGS Cost: 15  Bytes: 25  Cardinality: 1                  
                                    19 INDEX RANGE SCAN INDEX AUTOMATION.BUILDING_LOCALITY_FK_I Cost: 3  Cardinality: 33              
                                22 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_ROUTES Cost: 1  Bytes: 8  Cardinality: 1                  
                                    21 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.ROUTE_PK Cost: 0  Cardinality: 1              
                            24 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.DELIVERY_OFFICE_PK Cost: 0  Cardinality: 1                      
                        26 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_DELIVERY_OFFICES Cost: 1  Bytes: 8  Cardinality: 1                          
                43 FILTER                                  
                    41 HASH JOIN  Cost: 325,360  Bytes: 8,588,281,331,476  Cardinality: 55,768,060,594                              
                        29 TABLE ACCESS FULL TABLE AUTOMATION.T_LOCALITIES Cost: 241  Bytes: 1,917,312  Cardinality: 59,916                          
                        40 HASH JOIN  Cost: 16,057  Bytes: 115,019,160  Cardinality: 942,780                          
                            38 HASH JOIN  Cost: 56  Bytes: 391,027  Cardinality: 4,297                      
                                36 HASH JOIN  Cost: 38  Bytes: 23,350  Cardinality: 467                  
                                    34 MERGE JOIN  Cost: 19  Bytes: 3,556  Cardinality: 127              
                                        31 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_POST_TOWNS Cost: 2  Bytes: 2,176  Cardinality: 128          
                                            30 INDEX FULL SCAN INDEX AUTOMATION.POST_TOWN_COUNTY_FK_I Cost: 1  Cardinality: 127      
                                        33 SORT JOIN  Cost: 17  Bytes: 286  Cardinality: 26          
                                            32 TABLE ACCESS FULL TABLE AUTOMATION.T_COUNTIES Cost: 16  Bytes: 286  Cardinality: 26      
                                    35 TABLE ACCESS FULL TABLE AUTOMATION.T_DELIVERY_OFFICES Cost: 18  Bytes: 10,362  Cardinality: 471              
                                37 TABLE ACCESS FULL TABLE AUTOMATION.T_ROUTES Cost: 18  Bytes: 177,571  Cardinality: 4,331                  
                            39 TABLE ACCESS FULL TABLE AUTOMATION.T_BUILDINGS Cost: 15,995  Bytes: 29,456,727  Cardinality: 950,217                      
                    42 NESTED LOOPS                              

Pls let me know if any more information needed.
Thanks

[Updated on: Wed, 16 November 2011 10:59]

Report message to a moderator

Re: Slow running query -Explain plan attached-pls help! [message #531541 is a reply to message #531531] Wed, 16 November 2011 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE;

the resultant trace file will contain details show where time is actually being spent.
Re: Slow running query -Explain plan attached-pls help! [message #531552 is a reply to message #531531] Wed, 16 November 2011 13:54 Go to previous message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Try working on this part of he query:

-- Etc --
                43 FILTER                                  
                    41 HASH JOIN  Cost: 325,360  Bytes: 8,588,281,331,476  Cardinality: 55,768,060,594                              
                        29 TABLE ACCESS FULL TABLE AUTOMATION.T_LOCALITIES Cost: 241  Bytes: 1,917,312  Cardinality: 59,916                          
                        40 HASH JOIN  Cost: 16,057  Bytes: 115,019,160  Cardinality: 942,780                          
                            38 HASH JOIN  Cost: 56  Bytes: 391,027  Cardinality: 4,297                      
                                36 HASH JOIN  Cost: 38  Bytes: 23,350  Cardinality: 467                  
                                    34 MERGE JOIN  Cost: 19  Bytes: 3,556  Cardinality: 127              
                                        31 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_POST_TOWNS Cost: 2  Bytes: 2,176  Cardinality: 128          
                                            30 INDEX FULL SCAN INDEX AUTOMATION.POST_TOWN_COUNTY_FK_I Cost: 1  Cardinality: 127      
                                        33 SORT JOIN  Cost: 17  Bytes: 286  Cardinality: 26          
                                            32 TABLE ACCESS FULL TABLE AUTOMATION.T_COUNTIES Cost: 16  Bytes: 286  Cardinality: 26      
                                    35 TABLE ACCESS FULL TABLE AUTOMATION.T_DELIVERY_OFFICES Cost: 18  Bytes: 10,362  Cardinality: 471              
                                37 TABLE ACCESS FULL TABLE AUTOMATION.T_ROUTES Cost: 18  Bytes: 177,571  Cardinality: 4,331                  
                            39 TABLE ACCESS FULL TABLE AUTOMATION.T_BUILDINGS Cost: 15,995  Bytes: 29,456,727  Cardinality: 950,217                      
                    42 NESTED LOOPS                              


[Updated on: Wed, 16 November 2011 13:55] by Moderator

Report message to a moderator

Previous Topic: Simple inserts Slow (merged 6)
Next Topic: Need help for query tunning the below query
Goto Forum:
  


Current Time: Fri Mar 29 03:53:48 CDT 2024