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: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 06 Dec 2002 09:44:02 -0800
Message-ID: <F001.005149F2.20021206094402@fatcity.com> 





bad news for me I guess - doing a Production Install right now!
 



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


Sent: Friday, December 06, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L





That is very wise advice.  Don't touch production on Fridays has been a rule
in previous shops I worked at.  Happy Friday all!  <pow>



Lisa Koivu 


Oracle Database Supermom to 4 Boys. 


Fairfield Resorts, Inc. 


5259 Coconut Creek Parkway 


Ft. Lauderdale, FL, USA  33063 




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


Sent:   Friday, December 06, 2002 10:30 AM 
To:     Multiple recipients of list ORACLE-L 




        Hmmmmmmm - this is a Friday afternoon, you know. My suggestion is to
forget 


it until Monday - don't spoil your weekend.... 




        peter 


edinburgh 




        > -----Original Message----- 


> From: Rick_Cale_at_teamhealth.com [ mailto:Rick_Cale_at_teamhealth.com

<mailto:Rick_Cale_at_teamhealth.com> ] 


> Sent: 06 December 2002 12:54 

> To: Multiple recipients of list ORACLE-L 

> Subject: sql tuning help 

> 

> 

> 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

<http://www.orafaq.com>  


> -- 

> Author: 

>   INET: Rick_Cale_at_teamhealth.com 

> 

> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

<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). 

> 



        




This  e-mail   message,  and  any  files  transmitted   with  it, are 
confidential  and intended  solely for the  use of the  addressee. If 
this message was not addressed to  you, you have received it in error 
and any  copying,  distribution  or  other use  of any part  of it is 
strictly prohibited. Any views or opinions presented are solely those 
of the sender and do not  necessarily represent  those of the British 
Geological  Survey. The  security of e-mail  communication  cannot be 
guaranteed and the BGS  accepts no liability  for claims arising as a 
result of the use of this medium to  transmit messages from or to the 
BGS. The BGS cannot accept any responsibility  for viruses, so please 

scan all attachments.                            http://www.bgs.ac.uk


<http://www.bgs.ac.uk>  





        -- 


Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>  

-- 
Author: Robson, Peter 
  INET: pgro_at_bgs.ac.uk 

	Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<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: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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 - 11:44:02 CST












Original text of this message













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