Tune SQL - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 35

Thread: Tune SQL

  1. #21
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Quote Originally Posted by tamilselvan
    Is it because of BITAND operation?

    Tamil
    No Tamil, It is not BITAND. I see no difference by eliminating it.
    Last edited by rshivagami; 01-16-2007 at 11:09 AM.

  2. #22
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    r u sure .. ur CPU is going to 100% when you fire this query? or is that ur CPU is already busy with other processes?
    Last edited by abhaysk; 01-17-2007 at 02:46 AM.

  3. #23
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Quote Originally Posted by abhaysk
    r u sure .. ur CPU is going to 100% when you fire this query? or is that ur CPU is already busy with other processes?
    I am sure. I am testing this in a dedicated stress environment with production data and stats. There is no other process running at this time.

  4. #24
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    First of all the sql can be re-written as below, the join on CORR.content_id with ci.LOCATOR, ci.ancestor_locator (resp sub queries) have been removed and have been added in column select logic to avoid scanning of tables twice. I assume the index CSI.IDX_CORR_ASSET_CONTENT_MLO is on CLG_CORRELATION.asset_type_id.


    Code:
    SELECT SUM(assignments_mapped) assignments_mapped FROM
    (
        SELECT /*+ index(cbs pk_cb_standard) */
                CASE WHEN CORR.content_id IN (ci.LOCATOR, ci.ancestor_locator) THEN
                          CASE WHEN ci.LOCATOR = ci.ancestor_locator THEN
                                    2
                               ELSE
                                    1
                          END
                     ELSE
                          0
                END  AS assignments_mapped
                FROM CLG_ASSESSMENT_RESULT car,
                     CLG_ITEM_RESULT cir,
                     CLG_ITEM ci,
                     CLG_CORRELATION CORR,
                     CLG_ASSESSMENT_INSTANCE cai,
                     CB_STANDARD cbs,
                     CLG_USER u
               WHERE car.assessment_result_id = cir.assessment_result_id
                 AND car.status = 20
                 AND car.completed_date >= TO_DATE ('09052006', 'mmddyyyy')
                 AND car.completed_date <= TO_DATE ('10132006', 'mmddyyyy')
                 AND car.assessment_instance_id = cai.assessment_instance_id
                 AND cai.assignor_id = u.user_id
                 AND u.org_unit_id = 21296
                 AND cir.item_locator = ci.LOCATOR
                 AND CORR.asset_type_id = 113
                 AND CORR.cbs_id = cbs.cbs_id
                 AND cbs.LOCATOR like '1.1026%'  -- INPUT: subject Writing; grade 6
                 AND BITAND (cbs.internal_grade_levels, 2) > 0
    );

    The gets should go down by 2 or even more.. check the CPU now.


    Rgds
    Abhay

  5. #25
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Abhaysk, Thanks for the suggestion. I like the way you have written the new query - unfortunately this one is running for more than 50 secs(and it is still running) with appr. 100% CPU usage throughout.

    There are about 3004 corr.content_id and 49034 ci.locator. So when you do a cartesian join between these two, it is producing 14,72,98,136 rows to which we are applying the CASE equation.

    Here is the explain plan for your query :
    Code:
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	39  	 	      	             	 
      SORT AGGREGATE		1  	205  	 	 	      	             	 
        NESTED LOOPS		4  	820  	39  	 	      	             	 
          MERGE JOIN CARTESIAN		1  	170  	35  	 	      	             	 
            NESTED LOOPS		3  	438  	29  	 	      	             	 
              NESTED LOOPS		3  	237  	23  	 	      	             	 
                NESTED LOOPS		1  	38  	20  	 	      	             	 
                  NESTED LOOPS		3  	84  	14  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_RESULT	3  	54  	11  	 	      	             	 
                      INDEX RANGE SCAN	CSI.IDX_ASMNTRES_COMPDATE	38  	 	3  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	CSI.CLG_ASSESSMENT_INSTANCE	1  	10  	1  	 	      	             	 
                      INDEX UNIQUE SCAN	CSI.PK_ASSESSMENT_INSTANCE	1  	 	 	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	CSI.CLG_USER	1  	10  	2  	 	      	             	 
                    INDEX UNIQUE SCAN	CSI.PK_USER	1  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM_RESULT	13  	533  	3  	 	      	             	 
                  INDEX RANGE SCAN	CSI.PK_ITEM_RESULT	13  	 	2  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	CSI.CLG_ITEM	1  	67  	2  	 	      	             	 
                INDEX UNIQUE SCAN	CSI.PK_ITEM	1  	 	1  	 	      	             	 
            BUFFER SORT		1  	24  	33  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	CSI.CB_STANDARD	1  	24  	2  	 	      	             	 
                INDEX RANGE SCAN	CSI.UK_CB_STANDARD_LOCATOR	1  	 	1  	 	      	             	 
          INDEX RANGE SCAN	CSI.IDX_CORR_MLO_CONTENT_ASSET	28  	980  	4
    Yes you are right in assuming that index CSI.IDX_CORR_ASSET_CONTENT_MLO is on CLG_CORRELATION.asset_type_id.

  6. #26
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    well actually i removed that substr on cbs.locator and again it started using merge cartisian.. well try this..

    Code:
    SELECT SUM(assignments_mapped) assignments_mapped FROM
    (
        SELECT /*+ index(cbs pk_cb_standard) */
                CASE WHEN CORR.content_id IN (ci.LOCATOR, ci.ancestor_locator) THEN
                          CASE WHEN ci.LOCATOR = ci.ancestor_locator THEN
                                    2
                               ELSE
                                    1
                          END
                     ELSE
                          0
                END  AS assignments_mapped
                FROM CLG_ASSESSMENT_RESULT car,
                     CLG_ITEM_RESULT cir,
                     CLG_ITEM ci,
                     CLG_CORRELATION CORR,
                     CLG_ASSESSMENT_INSTANCE cai,
                     CB_STANDARD cbs,
                     CLG_USER u
               WHERE car.assessment_result_id = cir.assessment_result_id
                 AND car.status = 20
                 AND car.completed_date >= TO_DATE ('09052006', 'mmddyyyy')
                 AND car.completed_date <= TO_DATE ('10132006', 'mmddyyyy')
                 AND car.assessment_instance_id = cai.assessment_instance_id
                 AND cai.assignor_id = u.user_id
                 AND u.org_unit_id = 21296
                 AND cir.item_locator = ci.LOCATOR
                 AND CORR.asset_type_id = 113
                 AND CORR.cbs_id = cbs.cbs_id
                 AND SUBSTR (cbs.LOCATOR, 1, 6) = '1.1026'           -- INPUT: subject Writing; grade 6
                 AND BITAND (cbs.internal_grade_levels, 2) > 0
    );
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #27
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Can you please collect stats on all the tables using dbms_stats and then post the plan here without any changes to the sql and without any hints.

    regards
    Hrishy

  8. #28
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Quote Originally Posted by hrishy
    Hi

    Can you please collect stats on all the tables using dbms_stats and then post the plan here without any changes to the sql and without any hints.

    regards
    Hrishy

    Hrishy,

    I gathered stats on the schema, and ran the simplified version of the query without any hints. This is still using 90% CPU. Here are the details from the trace file.

    Code:
    ********************************************************************************
    
    SELECT COUNT (cai.assessment_instance_id) AS assignments_mapped
    FROM CLG_ASSESSMENT_RESULT car,
    CLG_ITEM_RESULT cir,
    CLG_ITEM ci,
    CLG_CORRELATION CORR,
    CLG_ASSESSMENT_INSTANCE cai,
    CB_STANDARD cbs,
    CLG_USER u
    WHERE car.assessment_result_id = cir.assessment_result_id
    AND car.status = 20
    AND car.completed_date >= TO_DATE ('09052006', 'mmddyyyy')
    AND car.completed_date <= TO_DATE ('10132006', 'mmddyyyy')
    AND car.assessment_instance_id = cai.assessment_instance_id
    AND cai.assignor_id = u.user_id
    AND u.org_unit_id = 21296
    AND cir.item_locator = ci.LOCATOR
    AND CORR.content_id = ci.ancestor_locator
    AND CORR.asset_type_id = 113
    AND CORR.cbs_id = cbs.cbs_id
    AND SUBSTR(cbs.LOCATOR,1,6)= '1.1026'-- INPUT: subject Writing; grade 6
    AND BITAND (cbs.internal_grade_levels, 2) > 0
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        4      0.01       0.01          0          0          0           0
    Execute      4      0.00       0.00          0          0          0           0
    Fetch        4      5.44       5.31          0    2585652          0           4
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       12      5.45       5.33          0    2585652          0           4
    
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 127  
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          0   NESTED LOOPS  
      38620    NESTED LOOPS  
      38620     NESTED LOOPS  
      49034      NESTED LOOPS  
       2870       NESTED LOOPS  
      61129        NESTED LOOPS  
      61129         TABLE ACCESS BY INDEX ROWID CLG_ASSESSMENT_RESULT 
      61318          INDEX RANGE SCAN IDX_ASMNTRES_COMPDATE (object id 54920)
      61129         TABLE ACCESS BY INDEX ROWID CLG_ASSESSMENT_INSTANCE 
      61129          INDEX UNIQUE SCAN PK_ASSESSMENT_INSTANCE (object id 54912)
       2870        TABLE ACCESS BY INDEX ROWID CLG_USER 
      61129         INDEX UNIQUE SCAN PK_USER (object id 55127)
      49034       TABLE ACCESS BY INDEX ROWID CLG_ITEM_RESULT 
      49034        INDEX RANGE SCAN PK_ITEM_RESULT (object id 55033)
      38620      TABLE ACCESS BY INDEX ROWID CLG_ITEM 
      49034       INDEX UNIQUE SCAN PK_ITEM (object id 55017)
      38620     INDEX RANGE SCAN IDX_CORR_ASSET_CONTENT_MLO (object id 54995)
          0    TABLE ACCESS BY INDEX ROWID CB_STANDARD 
      38620     INDEX UNIQUE SCAN PK_CB_STANDARD (object id 54866)
    
    ********************************************************************************

  9. #29
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Fetch 4 5.44 5.31 0 2585652 0 4
    ------- ---- -------- ---------- -------- ---------- ---------- -----

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 NESTED LOOPS
    Strange behaviour. The fetch line shows 4 rows returned, where as ROW Source says 0 rows.

    The row source shows that joining with CB_STANDARD returns 0 rows.
    Why don't you start this table, CB_STANDARD as a driving table. Use leading hint.

    Do you have an index on LOCATOR column?
    Also, change SUBSTR(cbs.LOCATOR,1,6) to LOCATOR like '1.1026%'

    It's worth to try.

    Tamil

  10. #30
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by tamilselvan
    Also, change SUBSTR(cbs.LOCATOR,1,6) to LOCATOR like '1.1026%'
    Been there.

    Is that column actually text, or is it a number?

    Also, use this method for getting the explain plan ...

    Code:
    set autotrace off
    
    explain plan for select ...
    /
    
    select * from table(dbms_xplan.display)
    /
    It may reveal column constraints that are also being applied as filters -- that can be responsible for high CPU usage and a poor execution plan due to poorly estimated statistics.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width