RE: sql tuning help

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-----
> From: Robson, Peter [SMTP:pgro_at_bgs.ac.uk]
> Sent: Friday, December 06, 2002 10:30 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: sql tuning help
>
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Koivu, Lisa
  INET: Lisa.Koivu_at_efairfield.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).