Re: query performance

Note that often the optimizer makes mistakes because it fails to properly compute the number of rows produced at intermediate steps. This can happen for many reasons, some of them fundamental (see Wolfgang's "fallacy" paper), or the others, which are mere technicalities (for example if a histogram is missing). You can check if the optimizer is making mistakes by verifying the cardinality of intermediate results. For example, try:

select count(*)
from Address a
where
<single table predicates applied at the node "TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS'" and above it>

If you use NO_EXPAND hint, then your plan wouldn't be concatenated anymore, and you can immediately know that only "Address" single table predicates are applied at the first step. (Otherwise, we can't tell which "Address" single table predicates belong to which concatenation branch).

In oracle 9.2 predicates are conveniently put into access_predicates and filter_predicates columns of the table, so that you don't have to guess which ones are applied at what plan node.

Then explain this query and check that the cardinality estimated by the optimizer is not too far off. If it is very different, check which predicate is causing the problem by removing predicates one by one (the problem could come from more than one predicate). Once you find the predicate, you might be lucky if the problem is really a histogram missing. Repeat this exercise for all tables involved in your query.

"Doug" <dfult_at_econs.umass.edu> wrote in message news:358a7114.0305281429.73dcee20_at_posting.google.com...
> I am having trouble with a query that includes a bunch of ORs. My
> example, below, is with 25 ORs, but we have scenarios in the
> application with hundreds. It would be difficult to re-work the
> query, since it is created by code internal to an application server.
> But if need be, we will send raw sql, skipping the app server's
> adaptor. Note: I have a similar issue with IN if the number of
> elements gets large. In either case (OR or IN), there are NO table
> scans if the number of OR/IN elements is relatively small (seven, for
> example). I haven't tested the threshold, so far.
>
> Anyway, any insights to the query and query plan, below?
>
> Thanks,
> Doug
>
> SELECT count(pr.board_certification)
> FROM Practitioner pr, AddressXPerson axp, Address a,
> OrganizationXPerson oxp, Person T1, Specialty s
> where (a.postalCode = '07010'
> OR a.postalCode = '07020'
> OR a.postalCode = '07022'
> OR a.postalCode = '07024'
> OR a.postalCode = '07030'
> OR a.postalCode = '07032'
> OR a.postalCode = '07047'
> OR a.postalCode = '07071'
> OR a.postalCode = '07072'
> OR a.postalCode = '07073'
> OR a.postalCode = '07074'
> OR a.postalCode = '07086'
> OR a.postalCode = '07087'
> OR a.postalCode = '07093'
> OR a.postalCode = '07094'
> OR a.postalCode = '07096'
> OR a.postalCode = '07097'
> OR a.postalCode = '07302'
> OR a.postalCode = '07303'
> OR a.postalCode = '07304'
> OR a.postalCode = '07305'
> OR a.postalCode = '07306'
> OR a.postalCode = '07307'
> OR a.postalCode = '07308'
> OR a.postalCode = '07309'
> OR a.postalCode = '07310'
> AND (oxp.role = 'Practitioner' AND axp.role = 'Location' AND a.country
> = 'USA' A
> ND s.englishDescription = 'Urology' AND pr.remove_from_mdlocator <>
> 1)) AND T1.p
> ersonOID = axp.personOID AND axp.addressOID = a.addressOID AND
> T1.personOID = ox
> p.personOID AND pr.personOID = T1.personOID AND pr.specialtyOID =
> s.specialtyOID
> ;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=1 Bytes=74)
> 1 0 SORT (AGGREGATE)
> 2 1 CONCATENATION
> 3 2 NESTED LOOPS (Cost=757 Card=122737 Bytes=7486957)
> 4 3 NESTED LOOPS
> 5 4 NESTED LOOPS (Cost=755 Card=122738 Bytes=5523210)
> 6 5 NESTED LOOPS (Cost=329 Card=172984
> Bytes=2421776)
>
> 7 6 NESTED LOOPS (Cost=479 Card=113219
> Bytes=3170132)
>
> 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (
> Cost=93 Card=201 Bytes=2613)
>
> 9 8 INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE'
> (NON-UNIQUE) (Cost=2 Card=201)
>
> 10 7 TABLE ACCESS (BY INDEX ROWID) OF
> 'ADDRESSXPERSON' (Cost=104
> Card=188431 Bytes=2638034)
>
> 11 10 INDEX (RANGE SCAN) OF
> 'ADDRESSXPERSON_ADDRESSOID' (NON-UNIQUE)
>
> 12 6 INDEX (UNIQUE SCAN) OF 'PERSON_PK' (UNIQUE)
> (Cost=131 Card=287899
> Bytes=1151596)
>
> 13 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'ORGANIZATIONXPERSON' (Cost=220
> Card=312104 Bytes=5305768)
>
> 14 13 INDEX (RANGE SCAN) OF
> 'ORGANIZATIONXPERSON_PERSON' (NON-UNIQUE)
>
> 15 4 TABLE ACCESS (BY INDEX ROWID) OF 'PRACTITIONER'
> (Cost=160 Card=172984
> Bytes=1729840)
>
> 16 15 INDEX (UNIQUE SCAN) OF 'PRACTITIONER_PK'
> (UNIQUE)
>
> 17 3 TABLE ACCESS (BY INDEX ROWID) OF 'SPECIALTY' (Cost=1
> Card=37 Bytes=592)
>
> 18 17 INDEX (UNIQUE SCAN) OF 'SPECIALTY_PK' (UNIQUE)
> 19 2 HASH JOIN (Cost=851 Card=222 Bytes=16428)
> 20 19 INLIST ITERATOR
> 21 20 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS'
> (Cost=93 Card=201 Bytes=2613)
>
> 22 21 INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE'
> (NON-UNIQUE) (Cost=2
> Card=201)
>
> 23 19 HASH JOIN (Cost=757 Card=122737 Bytes=7486957)
> 24 23 TABLE ACCESS (FULL) OF 'SPECIALTY' (Cost=1 Card=37
> Bytes=592)
>
> 25 23 HASH JOIN (Cost=755 Card=122738 Bytes=5523210)
> 26 25 HASH JOIN (Cost=479 Card=113219 Bytes=3170132)
> 27 26 HASH JOIN (Cost=329 Card=172984 Bytes=2421776)
> 28 27 TABLE ACCESS (FULL) OF 'PRACTITIONER'
> (Cost=160 Card=172984
> Bytes=1729840)
>
> 29 27 INDEX (FAST FULL SCAN) OF 'PERSON_PK'
> (UNIQUE) (Cost=131 Card=287899
> Bytes=1151596)
>
> 30 26 TABLE ACCESS (FULL) OF 'ADDRESSXPERSON'
> (Cost=104 Card=188431
> Bytes=2638034)
>
> 31 25 TABLE ACCESS (FULL) OF 'ORGANIZATIONXPERSON'
> (Cost=220 Card=312104
> Bytes=5305768)
Received on Wed May 28 2003 - 18:30:15 CDT