Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Understanding Hints

    I have been reading Dan Tow's book on SQL Tuning (an O'Reilly book), and he suggests allowing the Optimizer to generate query plans, except in those rare circumstances where it is piccking a poor plan. I've found a query like that, and I've been following his instructions, which revolve primarily around ordering the tables in the FROM clause and using the /*+ ORDERED */ hint. In applying his methodology in the past, I've sped up several queries in a predictable way, until now.

    I started with a query

    Code:
    SELECT...
    FROM UA_AGENT_LICENSES LIC,         
         UA_AGENTS AGT,         
        (SELECT PVV.PVV_INTERNAL_VALUE,                 
                PVV.PVV_EXTERNAL_VALUE AS COUNTY_NAME            
         FROM SHARED.PACE_VALID_VALUES PVV           
         WHERE PVV.PVV_TABLE_NAME = 'UA_COMPLAINTS'             
               AND PVV.PVV_FIELD_NAME = 'COUNTY') COUNTIES,         
         UA_AGENT_CLASS_N_LICENSES CNL
    WHERE...
    I altered the order of the tables and added the hint

    Code:
    SELECT /*+ ORDERED */ ...
    FROM UA_AGENT_LICENSES LIC,        
         UA_AGENTS AGT,      
         UA_AGENT_CLASS_N_LICENSES CNL,     
        (SELECT PVV.PVV_INTERNAL_VALUE,                 
                PVV.PVV_EXTERNAL_VALUE AS COUNTY_NAME            
         FROM SHARED.PACE_VALID_VALUES PVV           
         WHERE PVV.PVV_TABLE_NAME = 'UA_COMPLAINTS'             
               AND PVV.PVV_FIELD_NAME = 'COUNTY') COUNTIES     
    WHERE...
    But the query plan I get starts with the UA_AGENT_CLASS_N_LICENSES table, not UA_AGENT_LICENSES and UA_AGENTS

    Code:
    Execution Plan----------------------------------------------------------   
     0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5538 Card=56 Bytes=12264)   
     1    0   SORT (GROUP BY) (Cost=5538 Card=56 Bytes=12264)   
     2    1     HASH JOIN (Cost=5537 Card=56 Bytes=12264)   
     3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PACE_VALID_VALUES' (TABLE) (Cost=3 Card=1 Bytes=38)   
     4    3         INDEX (RANGE SCAN) OF 'PACE_VALID_VALUES_IX' (INDEX)(Cost=1 Card=14)   
     5    2       HASH JOIN (Cost=5533 Card=2232 Bytes=403992)   
     6    5         HASH JOIN (Cost=4494 Card=2115 Bytes=332055)   
     7    6           TABLE ACCESS (FULL) OF 'UA_AGENTS' (TABLE) (Cost=2191 Card=4062 Bytes=434634)   
     8    6           HASH JOIN (Cost=2300 Card=62343 Bytes=3117150)   
     9    8             SORT (UNIQUE) (Cost=1040 Card=77928 Bytes=1870272)  
    10    9               TABLE ACCESS (FULL) OF 'UA_AGENT_CLASS_N_LICENSES' (TABLE) (Cost=1040 Card=77928 Bytes=1870272)  
    11    8             TABLE ACCESS (FULL) OF 'UA_AGENT_LICENSES' (TABLE) (Cost=394 Card=122627 Bytes=3188302)  
    12    5         TABLE ACCESS (FULL) OF 'UA_AGENT_CLASS_N_LICENSES' (TABLE) (Cost=1034 Card=161783 Bytes=3882792)
    The query has gone from about 4 minutes 50 seconds down to 16 though. I'd like to use the rewritten version, but I'm concerned about the strange manner in which the HINT was converted into a mismatching query plan.

    -Thanks
    Chuck

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    4 minutes to 16 minutes?
    or
    4 minutes to 16 seconds?

    regardless, I notice a good deal of FULL table scans. Perhaps we should look into your indexes and your WHERE clause
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Sorry, down to 16 seconds. The original query plan seems similar, but the number of 'consistent gets' is far higher

    original query
    Code:
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1809 Card=1 Bytes=219)
       1    0   SORT (GROUP BY) (Cost=1809 Card=1 Bytes=219)
       2    1     TABLE ACCESS (BY INDEX ROWID) OF 'UA_AGENT_CLASS_N_LICENSES' (TABLE) (Cost=3 Card=1 Bytes=24)
       3    2       NESTED LOOPS (Cost=1808 Card=1 Bytes=219)
       4    3         NESTED LOOPS (Cost=1805 Card=1 Bytes=195)
       5    4           MERGE JOIN (CARTESIAN) (Cost=1803 Card=1 Bytes=88)
       6    5             HASH JOIN (RIGHT SEMI) (Cost=1800 Card=1 Bytes=50)
       7    6               TABLE ACCESS (FULL) OF 'UA_AGENT_CLASS_N_LICENSES' (TABLE) (Cost=1040 Card=77928 Bytes=1870272)
       8    6               TABLE ACCESS (FULL) OF 'UA_AGENT_LICENSES' (TABLE) (Cost=394 Card=122627 Bytes=3188302)
       9    5             BUFFER (SORT) (Cost=763 Card=1 Bytes=38)
      10    9               TABLE ACCESS (BY INDEX ROWID) OF 'PACE_VALID_VALUES' (TABLE) (Cost=3 Card=1 Bytes=38)
      11   10                 INDEX (RANGE SCAN) OF 'PACE_VALID_VALUES_IX'(INDEX) (Cost=1 Card=14)
      12    4           TABLE ACCESS (BY INDEX ROWID) OF 'UA_AGENTS' (TABLE) (Cost=2 Card=1 Bytes=107)
      13   12             INDEX (UNIQUE SCAN) OF 'UA_AGENTS_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
      14    3         INDEX (RANGE SCAN) OF 'UA_AGENT_CLASS_N_LICENSES_PK'(INDEX (UNIQUE)) (Cost=2 Card=1)
    
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
        2968264  consistent gets
              0  physical reads
              0  redo size
          80508  bytes sent via SQL*Net to client
           1054  bytes received via SQL*Net from client
             53  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
    with hint and re-ordered FROM clause
    Code:
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5538 Card=56 Bytes=12264)
       1    0   SORT (GROUP BY) (Cost=5538 Card=56 Bytes=12264)
       2    1     HASH JOIN (Cost=5537 Card=56 Bytes=12264)
       3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PACE_VALID_VALUES' (TABLE) (Cost=3 Card=1 Bytes=38)
       4    3         INDEX (RANGE SCAN) OF 'PACE_VALID_VALUES_IX' (INDEX)(Cost=1 Card=14)
       5    2       HASH JOIN (Cost=5533 Card=2232 Bytes=403992)
       6    5         HASH JOIN (Cost=4494 Card=2115 Bytes=332055)
       7    6           TABLE ACCESS (FULL) OF 'UA_AGENTS' (TABLE) (Cost=2191 Card=4062 Bytes=434634)
       8    6           HASH JOIN (Cost=2300 Card=62343 Bytes=3117150)
       9    8             SORT (UNIQUE) (Cost=1040 Card=77928 Bytes=1870272)
      10    9               TABLE ACCESS (FULL) OF 'UA_AGENT_CLASS_N_LICENSES' (TABLE) (Cost=1040 Card=77928 Bytes=1870272)
      11    8             TABLE ACCESS (FULL) OF 'UA_AGENT_LICENSES' (TABLE) (Cost=394 Card=122627 Bytes=3188302)
      12    5         TABLE ACCESS (FULL) OF 'UA_AGENT_CLASS_N_LICENSES' (TABLE) (Cost=1034 Card=161783 Bytes=3882792)
    
    
    
    
    
    Statistics
    ----------------------------------------------------------
             15  recursive calls
              0  db block gets
          18362  consistent gets
            249  physical reads
            116  redo size
          80508  bytes sent via SQL*Net to client
           1054  bytes received via SQL*Net from client
             53  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
    The range of queries I've tried this across (the WHERE clause has some dynamic content) all benefitted from the HINT. The fastest running query went from 30 seconds down to 2
    Code:
       WHERE LIC.SSN = AGT.SSN
         AND LIC.SSN = CNL.SSN
         AND LIC.AGENTS_LICENSE_NMBR = CNL.AGENTS_LICENSE_NMBR
         AND AGT.BUS_COUNTY = COUNTIES.PVV_INTERNAL_VALUE
         AND LIC.LICENSE_STATUS IN ('01', '04', '07', '08')
         AND CNL.DATE_CLASS_REMOVED IS NULL
         AND AGT.MAIL_BUS_ZIP LIKE '97223'
         AND EXISTS (
                SELECT *
                  FROM UA_AGENT_CLASS_N_LICENSES CNL2
                 WHERE LIC.SSN = CNL2.SSN
                   AND LIC.AGENTS_LICENSE_NMBR = CNL2.AGENTS_LICENSE_NMBR
                   AND CNL2.DATE_CLASS_REMOVED IS NULL
                   AND CNL2.CLASS_ISSUED IN ('21', '22', '43'))
    and the longest went from ~5 minutes down to ~15 seconds

    Code:
       WHERE LIC.SSN = AGT.SSN
         AND LIC.SSN = CNL.SSN
         AND LIC.AGENTS_LICENSE_NMBR = CNL.AGENTS_LICENSE_NMBR
         AND AGT.BUS_COUNTY = COUNTIES.PVV_INTERNAL_VALUE
         AND LIC.LICENSE_STATUS IN ('01', '04', '07', '08')
         AND CNL.DATE_CLASS_REMOVED IS NULL
         AND (   AGT.BUS_COUNTY IN '0','1','2','3', ...'36')
         AND EXISTS (
                SELECT *
                  FROM UA_AGENT_CLASS_N_LICENSES CNL2
                 WHERE LIC.SSN = CNL2.SSN
                   AND LIC.AGENTS_LICENSE_NMBR = CNL2.AGENTS_LICENSE_NMBR
                   AND CNL2.DATE_CLASS_REMOVED IS NULL
                   AND CNL2.CLASS_ISSUED IN
                                       ('21', '22', '30', '40', '41', '42', '43'))
    -cf

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1. Are your stats on these tables correct.Also what options are you using for stats collecting, do you have histograms where the data is skewed etc. Check your stats in user_tab_columns etc to make sure they are reasonable, dbms_stats can get things wrong .
    2. Can you rewrite your query to get good performance, like replacing subqueries with joins.
    3. Do you have the correct indexes on your tables.

    If after all the above you still cant get good performance then you could consider using hints. However it can be dangerous, if your data distribution can change then your hint might give you a worse execution plan than the CBO in the future. If your happy your data distribution/volumes arent going to change much then hints maybe the best way.

    You will also have to review your queries when you change Oracle versions as the CBO can change considerably between major Oracle versions.

    Alan

Posting Permissions

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