Re: query performance -- another try

From: Doug <dfult_at_econs.umass.edu>
Date: 3 Jun 2003 11:57:57 -0700
Message-ID: <358a7114.0306031057.1054ca9c@posting.google.com>

Here is the tkprof output (first, where the index is used -- 42 OR clauses; second, where it isn't -- 43 OR clauses):

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.01       0.01          0          0          0  
        0
Execute      1      0.01       0.01          0          0          0  
        0
Fetch        2      0.01       0.02          0        282          0  
        1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      0.03       0.04          0        282          0  
        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20 (ADMIN)

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE

    501 INLIST ITERATOR
    501 TABLE ACCESS BY INDEX ROWID ADDRESS     544 INDEX RANGE SCAN (object id 3860)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)
    501    INLIST ITERATOR
    501     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
'ADDRESS'
    544      INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                 'ADDRESS_POSTALCODE' (NON-UNIQUE)

---

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.06       0.06          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      3.06       3.12          0       1015          4  
        1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      3.12       3.18          0       1015          4  
        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20 (ADMIN)

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE

    502 TABLE ACCESS FULL ADDRESS Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   SORT (AGGREGATE)

    502 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'ADDRESS' I did the following to interrogate the presence of a histogram on Country (from the Tuning doc):

  1 select ENDPOINT_NUMBER, ENDPOINT_VALUE FROM DBA_HISTOGRAMS   2* WHERE TABLE_NAME = 'ADDRESS' AND COLUMN_NAME = 'COUNTRY' SQL> / ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------

              0     3.3917E+35
              1     6.0984E+35

It doesn't look like a meaningful histogram exists, here (even to my untrained eye). I tried the same thing with postalCode and got:

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------

              0     2.3469E+35
              1     6.5525E+35

This also looks unhelpful. Perhaps I am doing the wrong thing to verify the histograms.

FYI: I created statistics with the following: analyze table Address compute statistics;

By the way, there is no index on country (the index on postalCode does not include country). Almost everything we do is for US, and selects either include postalCode (which I assumed to be selective enough -- certainly not aided by country except in rare cases) or on the primary key.

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:<zOPCa.13$YX6.31_at_news.oracle.com>...

> "Doug" <dfult_at_econs.umass.edu> wrote in message
> news:358a7114.0306021313.3be7b66f_at_posting.google.com...
> > I simplified the query as suggested and here is the two versions of
> > the plan:
> >
> > Execution Plan
> > ----------------------------------------------------------
> >    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=154 Card=1 Bytes=9)
> >    1    0   SORT (AGGREGATE)
> >    2    1     INLIST ITERATOR
> >    3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (Cost=154 C
> >           ard=3 Bytes=27)
> >
> >    4    3         INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE' (NON-UNIQ
> >           UE) (Cost=2 Card=3)
> >
> >
> > Execution Plan
> > ----------------------------------------------------------
> >    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=155 Card=1 Bytes=9)
> >    1    0   SORT (AGGREGATE)
> >    2    1     TABLE ACCESS (FULL) OF 'ADDRESS' (Cost=155 Card=3 Bytes=
> >           27)
> 
> We knew that the plan would switch like this at some threshold already
> (Cost=154->155 in your case).  What we don't know is the runtime performance
> difference between the 2 cases. And the number of rows processed at each
> rowsource (to compare those at the estimated cardinalities). This is why I
> asked tkprof output.
> 
> > There are 119 different values for country (including 815 nulls); the
> > vast majority 110K have country = "USA".
> 
> Therefore, the column is highly skewed. Please, doublecheck if you have the
> histogram.
> 
> > "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
>  news:<FetBa.10$No4.124_at_news.oracle.com>...
> > > First, you can reduce the case significantly.
> > >
> > > SELECT count(1)
> > > FROM Address a
> > > where ((a.postalCode = '07010'
> > >  OR a.postalCode = '07020'
> > >  OR a.postalCode = '07022'
> > >  ...
> > >  OR a.postalCode = '10007'
> > > ) and a.country = 'USA'
> > >
> > > Similar to your original case, optimiser would switch to FTS in this
>  reduced
> > > case as well.
> > >
> > > Next, please, run the query in both cases -- with long OR-predicates
>  list,
> > > and with short list -- and let us know what tkprof says. Also please
>  make
> > > sure that you have a histogram on country if you have more than one
>  value
> > > there.

Received on Tue Jun 03 2003 - 13:57:57 CDT