Re: Request SQL Stmt Tuning Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 03 Nov 1999 08:30:56 -0500
Message-ID: <8TUgOL7gLN4wVSoxy3HOUE+CTLby@4ax.com>

A copy of this was sent to "Buck Turgidson" <jc_va_at_hotmail.com> (if that email address didn't require changing) On Tue, 2 Nov 1999 19:32:58 -0800, you wrote:

>I was doing some tuning on a very large SQL stmt, having a select of 41
>columns from a join of 7 tables, most of which also have a correlated
>sub-query. The statement also orders by SSN (employee id for you
>non-Americans). I don't have the flexibility of changing the statement, but
>that's another story.
>
>I erroneously thought that the selected columns didn't make a difference, so
>I only included 2 for my testing, ommitting the 39 other columns from
>the real select. I got the pared down version to work acceptably with a
>first_rows hint, but when I dropped all the columns in, it was dog-slow.
>
>When I remove the "order by" from the full query, it is much better, but
>still not as good as the 2 column version.
>

the 2 column version is fast because you avoided the "TABLE ACCESS BY ROWID" step in the query. Your 2 columns causes AT MOST 2 tables to be accessed by rowid after reading the index entries (in all probability -- NO tables were accessed -- it was all indexed reads).

To show how dramatic this difference can be, I created a table as such:

create table test as select user_id, username from all_users /
alter table test add constraint test_pk primary key (user_id) /

I have >18,000 named accounts in my database. test is 'fairly' large. I then ran the following:

tkyte_at_8.0> set autotrace on
tkyte_at_8.0> set termout off
tkyte_at_8.0> select user_id from test where user_id > 0

2 /

USER_ID

18648 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 INDEX (RANGE SCAN) OF 'TEST_PK' (UNIQUE) Statistics

          0  recursive calls
          0  db block gets
       1282  consistent gets
          0  physical reads
          0  redo size
     280054  bytes sent via SQL*Net to client
     138643  bytes received via SQL*Net from client
       1247  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      18648  rows processed

tkyte_at_8.0> select user_id, username from test where user_id > 0 2 /

USER_ID USERNAME

---------- ------------------------------
         5 SYSTEM
        17 DBSNMP
        .. .....
     18836 WEB$AMATCHAM_UK
     18837 WEB$WSCHOEFE_AT
     18838 WEB$JBARDYN

18648 rows selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' 2 1 INDEX (RANGE SCAN) OF 'TEST_PK' (UNIQUE) Statistics

          0  recursive calls
          0  db block gets
      19930  consistent gets
          0  physical reads
          0  redo size
     592398  bytes sent via SQL*Net to client
     138653  bytes received via SQL*Net from client
       1247  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      18648  rows processed

The two queries returned exactly the same number of rows but look at the stat:

       1282  consistent gets
      19930  consistent gets

The second one did an order of magnitude PLUS number of block reads (logical/physical). That is because we read an index block, get an index entry and then had to use the rowid from the index to look up the block in the table and access that block to get the other column(s). This means we will add a block read PER row returned (and why the optimizer will sometimes choose a full scan over an index scan -- this can be really slow doing lots of scattered IO's all over the place).

What some people do to tune this is to add some of the columns they select frequently to the index (at the end of the index). for example, if I added USERNAME to the index above -- we might be able to answer the query using just the index (no table access by rowid).

>Can anyone suggest any tricks so that I can use the order by, but still have
>decent performance? When Oracle does an order by, does it do so with
>all the data in a buffer, or just the ordered columns? Would it help if I
>order by SSN from a driving table, versus driven?
>

If you can order by the columns in the index being used to access the data in the first place -- you may be able to avoid creating a temporary (unsorted) result set that must be sorted after all rows have been fetched. avoiding the table access by rowid may be harder (if possible at all).

You need to use autotrace in sqlplus or "alter session set sql_trace = true" and TKPROF to see what your query is really doing and go from there.

>Thanks for any advice.
>
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Nov 03 1999 - 07:30:56 CST