ASK TOM
codeLISTING 3: TKPROF report, second run
select count(subobject_name)
from
big_table.big_table
call count cpu elapsed disk query current rows
----------------- ------------- --------- ----------------- ----------- ------------ ---------------- ---------
Parse 10.00 0.04 0 0 0 0
Execute 10.00 0.00 0 0 0 0
Fetch 2 0.28 0.52 3342 3355 0 1
----------------- ------------- --------- ------------------ ----------- ------------ ---------------- -----------
total 4 0.28 0.56 3342 3355 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 344
Rows Row Source Operation
------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 SORT AGGREGATE (cr=3355 pr=3342 pw=0 time=520528 us)
688256 INDEX FAST FULL SCAN BIG_TABLE_SO_IDX (cr=3355 pr=3342 pw=0 time=730570 us)
(object id 161278)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
----------------------------------------------------------------------------------- Waited ------------------------ -----------------------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 42 0.01 0.27
SQL*Net message from client 2 0.00 0.00
compressing the table.
2. Because we need only about 0.54
percent of the table’s rows to answer our
query (count the non-null occurrences
of subobject_name), maybe indexing
would improve query performance.
Let’s try option 2 first, because it will
have the least impact.
SQL> create index big_table_so_idx
on big_table(subobject_name);
Index created.
After we do that and rerun the query,
TKPROF shows the report in Listing 3.
Now in real life, it’ll be more complicated than this, of course. You’ll have
a multitable query to deal with and
complex data relationships. To tune that
query, you either
O Submit the query to a tool (such as
the tuning/performance pack of Oracle
Enterprise Manager) and let the tool
process it. It will apply the rules and tell
you which indexes, materialized views,
query rewrites, and—in Oracle Database
11g—partitioning schemes would be
useful for that query.
O Use the facts you can derive from
TKPROF and other sources and apply
your knowledge of the data, the data
patterns, and Oracle Database—
including the indexes it has to offer, the
schema structures (clusters, index-organized tables, heap tables, partitioning, and so on) it has to offer, and the
SQL it supports (because you can rewrite
a query more efficiently in many cases).
So when you use TKPROF for query
tuning, you are getting half of what you
need—the facts. The other half is all
about you and your knowledge of the
database. If you don’t have or don’t want
to have that other half, you’ll be using
tools such as the performance pack in
Oracle Enterprise Manager.
I want to know if there is a simple way to
find out which queries use the index, because
I want to re-create the index with fewer
columns, but I do not want to degrade the performance of the queries.
As for the question about how to find
out which queries use an index, you
can query V$SQL_PLAN to see which
queries that are in the shared pool right
now (that last bit is important—to see
which queries that are in the shared
pool right now) use that index.
But more importantly, you wrote,
“. . . the other five [columns] are not
selective. . . .” That fact has nothing to
do with whether they are useful or not.
I don’t care if they are constant or have
two distinct values—that they are not
selective has nothing to do with whether
they should be in the index. Selectivity
has nothing to do, really, with whether a
column should be indexed. The questions
you ask and the data patterns dictate
what columns make sense in an index.
The online version of this column, at
otn.oracle.com/oramag/oracle/08-nov/
o68asktom.html, includes a demonstration on selecting the best columns for an
index for a given query. O
Tom Kyte is a database evangelist in Oracle’s Server
Technology division and has worked for Oracle since
1993. He is the author of Expert Oracle Database
Architecture: 9i and 10g Programming Techniques and
Solutions (Apress, 2005) and Effective Oracle by Design
(Oracle Press, 2003), among others.
NONSELECTIVE COLUMNS IN AN INDEX
My company runs a third-party application on
Oracle Database 10g Release 2 ( 10. 2.0.2). A
developer created a huge index, with seven
columns, on a huge table, some time ago.
Analyzing the usefulness of the index, I found
out that it would be much better if the index
had only two columns (because the other five
are not selective).
nextSTEPS
ASK Tom
Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.
asktom.oracle.com
READ online-only column content
otn.oracle.com/oramag/oracle/08-nov/
o68asktom.html
READ more about
Oracle Database 11g
otn.oracle.com/products/database/oracle11g
READ more Tom
Expert Oracle Database Architecture: 9i and 10g
Programming Techniques and Solutions
amazon.com/exec/obidos/tg/detail/-/1590595300/
DOWNLOAD
Oracle Database 11g
otn.oracle.com/software/products/database
