Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql tuning help

RE: sql tuning help

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Fri, 06 Dec 2002 08:24:29 -0800
Message-ID: <F001.00514779.20021206082429@fatcity.com> 





I think the problem (without really getting into how they coded this) is the
nested-nested loops.



try this hint: 


/*+ no_merge use_hash(board_aaps) use_hash(board_aba) use_hash(board_abem)

use_hash(board_abfp)  use_hash(board_abim) use_hash(board_abp)
use_hash(board_abr) use_hash(board_aobem) use_hash(board_aobfp)
use_hash(board_aobim) use_hash(board_aobr) use_hash(board_other) */






HTH


Waleed





-----Original Message-----


Sent: Friday, December 06, 2002 7:54 AM


To: Multiple recipients of list ORACLE-L




Hi,



Oracle 8.1.6 NT 4.0



I have a rather complex query a developer gave to me to try to improve
performance.


There are 3 tables used. All relevant columns used are indexed. The tables
have been analyzed



SQLWKS> select count(*) from physicians;
COUNT(*)





    340043


1 row selected.


SQLWKS> select count(*) from boards;


COUNT(*)





       220


1 row selected.


SQLWKS> select count(*) from phy_boards;
COUNT(*)





    450674



Below is the sql statement and explain plan.
I see one FTS on 440,000+ records but cannot tell exactly what statement it
is and how to resolve



Any suggestions on how to optimize is appreciated.



Thanks


Rick



select board_other.description strBrdNameOtherTHQuest


       ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard

       ,decode(board_aba.description, null,' ','X') ysnABABoard

       ,decode(board_abem.description, null,' ','X') ysnABEMBoard

       ,decode(board_abfp.description, null,' ','X') ysnABFPoard

       ,decode(board_abim.description, null,' ','X') ysnABIMBoard

       ,decode(board_abp.description, null,' ','X') ysnABPBoard

       ,decode(board_abr.description, null,' ','X') ysnABRBoard

       ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard

       ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard

       ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard

       ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard

       ,decode(board_other.description, null,' ','X') ysnOtherBoard

   from physicians p


       ,(select distinct pb.phy_id, b.name, b.description


           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN
SPECIALIST%') board_aaps


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%')
board_aba


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%')
board_abem


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%')
board_abfp


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%')
board_abim


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN BOARD OF PEDIATRICS%')
board_abp


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN BOARD OF RADIOLOGY%')
board_abr


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY
MEDICINE%') board_aobem


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY
PHYSICIANS%') board_aobfp


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL
MEDICINE%') board_aobim


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and b.description like 'AMERICAN OSTEOPATHIC BOARD OF
RADIOLOGY%') board_aobr


       ,(select distinct pb.phy_id, b.name, b.description

           from phy_boards pb, boards b
          where pb.board_id = b.board_id
            and (pb.expiration_date >= sysdate or
                 pb.expiration_date is null)
            and (b.description not like 'AMERICAN ASSOCIATION OF PHYSICIAN
SPECIALIST%' and
                 b.description not like 'AMERICAN BOARD OF ANESTHESIOLOGY%'
and
                 b.description not like 'AMERICAN BOARD OF EMERGENCY
MEDICINE%' and
                 b.description not like 'AMERICAN BOARD OF FAMILY
PRACTICE%' and
                 b.description not like 'AMERICAN BOARD OF INTERNAL
MEDICINE%' and
                 b.description not like 'AMERICAN BOARD OF PEDIATRICS%' and
                 b.description not like 'AMERICAN BOARD OF RADIOLOGY%' and
                 b.description not like 'AMERICAN OSTEOPATHIC BOARD OF
EMERGENCY MEDICINE%' and
                 b.description not like 'AMERICAN OSTEOPATHIC BOARD OF
FAMILY PHYSICIANS%' and
                 b.description not like 'AMERICAN OSTEOPATHIC BOARD OF
INTERNAL MEDICINE%' and
                 b.description not like 'AMERICAN OSTEOPATHIC BOARD OF
RADIOLOGY%' and
                 b.description not like 'NO BOARDS%'
                 )) board_other
  where p.phy_id = board_aaps.phy_id (+)
    and p.phy_id = board_aba.phy_id (+)
    and p.phy_id = board_abem.phy_id (+)
    and p.phy_id = board_abfp.phy_id (+)
    and p.phy_id = board_abim.phy_id (+)
    and p.phy_id = board_abp.phy_id (+)
    and p.phy_id = board_abr.phy_id (+)
    and p.phy_id = board_aobem.phy_id (+)
    and p.phy_id = board_aobfp.phy_id (+)
    and p.phy_id = board_aobim.phy_id (+)
    and p.phy_id = board_aobr.phy_id (+)


    and p.phy_id = board_other.phy_id (+)
    and p.phy_id = 1870;



Plan Table





| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart|


Pstop |





| SELECT STATEMENT          |          |     2 |    2K|  88289 |       |
|
|  NESTED LOOPS OUTER       |          |     2 |    2K|  88289 |       |
|
|   NESTED LOOPS OUTER      |          |     1 |    1K|  28954 |       |
|
|    NESTED LOOPS OUTER     |          |     1 |    1K|  26322 |       |
|
|     NESTED LOOPS OUTER    |          |     1 |    1K|  23690 |       |
|
|      NESTED LOOPS OUTER   |          |     1 |  924 |  21058 |       |
|
|       NESTED LOOPS OUTER  |          |     1 |  809 |  18426 |       |
|
|        NESTED LOOPS OUTER |          |     1 |  694 |  15794 |       |
|
|         NESTED LOOPS OUTER|          |     1 |  579 |  13162 |       |
|
|          NESTED LOOPS OUTE|          |     1 |  464 |  10530 |       |
|
|           NESTED LOOPS OUT|          |     1 |  349 |   7898 |       |
|
|            NESTED LOOPS OU|          |     1 |  234 |   5266 |       |
|
|             NESTED LOOPS O|          |     1 |  119 |   2634 |       |
|
|              INDEX UNIQUE |PHY_PK    |     1 |    4 |      2 |       |
|
|              VIEW         |          |     8K|  922K|        |       |
|
|               SORT UNIQUE |          |     8K|  649K|   2632 |       |
|
|                NESTED LOOP|          |     8K|  649K|   2126 |       |
|
|                 INDEX FAST|BOARD_NAM |     3 |  207 |      2 |       |
|
|                 TABLE ACCE|PHY_BOARD |   443K|    5M|    708 |       |
|
|                  INDEX RAN|PBRD_BOAR |   443K|      |     22 |       |
|
|             VIEW          |          |     8K|  922K|        |       |
|
|              SORT UNIQUE  |          |     8K|  649K|   2632 |       |
|
|               NESTED LOOPS|          |     8K|  649K|   2126 |       |
|
|                INDEX FAST |BOARD_NAM |     3 |  207 |      2 |       |
|
|                TABLE ACCES|PHY_BOARD |   443K|    5M|    708 |       |
|
|                 INDEX RANG|PBRD_BOAR |   443K|      |     22 |       |
|
|            VIEW           |          |     8K|  922K|        |       |
|
|             SORT UNIQUE   |          |     8K|  649K|   2632 |       |
|
|              NESTED LOOPS |          |     8K|  649K|   2126 |       |
|
|               INDEX FAST F|BOARD_NAM |     3 |  207 |      2 |       |
|
|               TABLE ACCESS|PHY_BOARD |   443K|    5M|    708 |       |
|
|                INDEX RANGE|PBRD_BOAR |   443K|      |     22 |       |
|
|           VIEW            |          |     8K|  922K|        |       |
|
|            SORT UNIQUE    |          |     8K|  649K|   2632 |       |
|
|             NESTED LOOPS  |          |     8K|  649K|   2126 |       |
|
|              INDEX FAST FU|BOARD_NAM |     3 |  207 |      2 |       |
|
|              TABLE ACCESS |PHY_BOARD |   443K|    5M|    708 |       |
|
|               INDEX RANGE |PBRD_BOAR |   443K|      |     22 |       |
|
|          VIEW             |          |     8K|  922K|        |       |
|
|           SORT UNIQUE     |          |     8K|  649K|   2632 |       |
|
|            NESTED LOOPS   |          |     8K|  649K|   2126 |       |
|
|             INDEX FAST FUL|BOARD_NAM |     3 |  207 |      2 |       |
|
|             TABLE ACCESS B|PHY_BOARD |   443K|    5M|    708 |       |
|
|              INDEX RANGE S|PBRD_BOAR |   443K|      |     22 |       |
|
|         VIEW              |          |     8K|  922K|        |       |
|
|          SORT UNIQUE      |          |     8K|  649K|   2632 |       |
|
|           NESTED LOOPS    |          |     8K|  649K|   2126 |       |
|
|            INDEX FAST FULL|BOARD_NAM |     3 |  207 |      2 |       |
|
|            TABLE ACCESS BY|PHY_BOARD |   443K|    5M|    708 |       |
|
|             INDEX RANGE SC|PBRD_BOAR |   443K|      |     22 |       |
|
|        VIEW               |          |     8K|  922K|        |       |
|
|         SORT UNIQUE       |          |     8K|  649K|   2632 |       |
|
|          NESTED LOOPS     |          |     8K|  649K|   2126 |       |
|
|           INDEX FAST FULL |BOARD_NAM |     3 |  207 |      2 |       |
|
|           TABLE ACCESS BY |PHY_BOARD |   443K|    5M|    708 |       |
|
|            INDEX RANGE SCA|PBRD_BOAR |   443K|      |     22 |       |
|
|       VIEW                |          |     8K|  922K|        |       |
|
|        SORT UNIQUE        |          |     8K|  649K|   2632 |       |
|
|         NESTED LOOPS      |          |     8K|  649K|   2126 |       |
|
|          INDEX FAST FULL S|BOARD_NAM |     3 |  207 |      2 |       |
|
|          TABLE ACCESS BY I|PHY_BOARD |   443K|    5M|    708 |       |
|
|           INDEX RANGE SCAN|PBRD_BOAR |   443K|      |     22 |       |
|
|      VIEW                 |          |     8K|  922K|        |       |
|
|       SORT UNIQUE         |          |     8K|  649K|   2632 |       |
|
|        NESTED LOOPS       |          |     8K|  649K|   2126 |       |
|
|         INDEX FAST FULL SC|BOARD_NAM |     3 |  207 |      2 |       |
|
|         TABLE ACCESS BY IN|PHY_BOARD |   443K|    5M|    708 |       |
|
|          INDEX RANGE SCAN |PBRD_BOAR |   443K|      |     22 |       |
|
|     VIEW                  |          |     8K|  922K|        |       |
|
|      SORT UNIQUE          |          |     8K|  649K|   2632 |       |
|
|       NESTED LOOPS        |          |     8K|  649K|   2126 |       |
|
|        INDEX FAST FULL SCA|BOARD_NAM |     3 |  207 |      2 |       |
|
|        TABLE ACCESS BY IND|PHY_BOARD |   443K|    5M|    708 |       |
|
|         INDEX RANGE SCAN  |PBRD_BOAR |   443K|      |     22 |       |
|
|    VIEW                   |          |     8K|  922K|        |       |
|
|     SORT UNIQUE           |          |     8K|  649K|   2632 |       |
|
|      NESTED LOOPS         |          |     8K|  649K|   2126 |       |
|
|       INDEX FAST FULL SCAN|BOARD_NAM |     3 |  207 |      2 |       |
|
|       TABLE ACCESS BY INDE|PHY_BOARD |   443K|    5M|    708 |       |
|
|        INDEX RANGE SCAN   |PBRD_BOAR |   443K|      |     22 |       |
|
|   VIEW                    |          |   443K|   48M|        |       |
|
|    SORT UNIQUE            |          |   443K|   34M|  59335 |       |
|
|     HASH JOIN             |          |   443K|   34M|   3058 |       |
|
|      INDEX FAST FULL SCAN |BOARD_NAM |   190 |   12K|      2 |       |
|
|      TABLE ACCESS FULL    |PHY_BOARD |   443K|    5M|   3008 |       |
|
----------------------------------------------------------------------------







-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Fri Dec 06 2002 - 10:24:29 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US