Oracle-L: RE: sql tuning help

From: Robson, Peter <pgro_at_bgs.ac.uk>
Date: Fri, 06 Dec 2002 07:29:55 -0800
Message-ID: <F001.00514617.20021206072955@fatcity.com>

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

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
*********************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robson, Peter
  INET: pgro_at_bgs.ac.uk

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 - 09:29:55 CST