| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql tuning help
Rick,m
PS - I would bet that the FTS is from the last join - where:
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
etc etc
.
the NOT LIKE requires a FTS. no other way around it.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Friday, December 06, 2002 10:54 AM
To: Multiple recipients of list ORACLE-L
Thanks for the reply but I only see 1 FTS for the last inline view. Some of the info was truncated from previous post. I am resending output
Plan Table
| Operation | Name | Rows | Bytes| Cost | Pstart|Pstop |
| SELECT STATEMENT | | 2 | 2K| | NESTED LOOPS OUTER | | 2 | 2K| | NESTED LOOPS OUTER | | 1 | 1K| | NESTED LOOPS OUTER | | 1 | 1K| | NESTED LOOPS OUTER | | 1 | 1K| | NESTED LOOPS OUTER | | 1 | 924 | | NESTED LOOPS OUTER | | 1 | 809 | | NESTED LOOPS OUTER | | 1 | 694 | | NESTED LOOPS OUTER | | 1 | 579 | | NESTED LOOPS OUTER | | 1 | 464 | | NESTED LOOPS OUTER | | 1 | 349 | | NESTED LOOPS OUTER | | 1 | 234 | | NESTED LOOPS OUTER | | 1 | 119 | | INDEX UNIQUE SCAN |PHY_PK | 1 | 4 | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 443K| 48M| | SORT UNIQUE | | 443K| 34M| | HASH JOIN | | 443K| 34M| | INDEX FAST FULL SCAN |BOARD_NAM | 190 | 12K| | TABLE ACCESS FULL |PHY_BOARD | 443K|5M|
"Mark J.
Bobak" To: Multiple recipients of list
ORACLE-L <ORACLE-L_at_fatcity.com>
<mark_at_bobak.ne cc:
t> Subject: Re: sql tuning help
Sent by:
root_at_fatcity.c
om
12/06/2002
10:19 AM
Please respond
to ORACLE-L
My first thought would be that the plan shows a full table scan for phy_boards for each in-line view. This is almost certainly due to the 'pb.expiration_Date is null' condition in the where clause of each in-line view. Since Oracle does not store NULLs in an index (except for bitmaps), that condition requires a full table scan.
-Mark
On Fri, 2002-12-06 at 07:53, Rick_Cale_at_teamhealth.com wrote:
> 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. Thetables
> 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 statementit
> 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 EMERGENCYMEDICINE%')
> 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 INTERNALMEDICINE%')
> 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 OFEMERGENCY
> 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 OFINTERNAL
> 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 OFPHYSICIAN
> b.description not like 'AMERICAN BOARD OFANESTHESIOLOGY%'
> 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; >
> | 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 | | > | >
----------------------------------------------------------------------------
>
>
--
--
Mark J. Bobak
Oracle DBA
mark_at_bobak.net
"It is not enough to have a good mind. The main thing is to use it
well."
-- Rene
Descartes
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark J. Bobak
INET: mark_at_bobak.net
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:
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: 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 - 10:43:56 CST
 |
 |