Oracle Magazine - November/December 2008

select count(subobject_name) from big_table.big_table

3 ( waits => true );

4 end;

5 /

PL/SQL procedure successfully completed.

call count cpu elapsed disk query current rows ----------------- ------------- ------------- ----------------- ------------------ -------------------- ----------------- ----------- Parse 1 0.00 0.01 0 3 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 99.36 262.10 1840758 1840797 0 1 ----------------- ------------- ------------- ----------------- ------------------ -------------------- ----------------- ----------- total 499.36262.1118407581840800 0 1

SQL> select count(subobject_name) from big_table.big_table;

COUNT(SUBOBJECT_NAME) -----------------------------------------------------

688256

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 344

Rows Row Source Operation

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

1 SORT AGGREGATE (cr=1840797 pr=1840758 pw=0 time=262104893 us)

128000000 TABLE ACCESS FULL BIG_TABLE (cr=1840797 pr=1840758 pw=0 time=384004887 us)

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 14425 0.22 195.87 db file sequential read 13 0.01 0.06 SQL*Net message from client 2 0.00 0.00

an employees table and you hash-partition it into 16 partitions by EMPNO. Further suppose you have an index on last_name and you only locally partition that index on last_name.

Now, when you query

select from employees where last_name=:x

Oracle Database Data Warehousing Guide 11g Release 1, especially chapter 5 ( download.oracle.com/docs/cd/ B28359_01/server.111/b28313/parpart .htm#i1007993). Also, in my book Expert Oracle Database Architecture, I discuss all of this in some detail, including why you want to know why you are partitioning, how to approach partitioning, and what not to do.

you will do 16 index range scans— 16 times the work!

In this case, you would have wanted to do one of the following: O Not partition that index at all. The size of the index in a single partition was fine with you. O Partition that index by range. For example, A–K go into one partition and L–Z go into another. Use whatever ranges make sense for your data. O Hash-partition it. This presumes that you always use equality in your searches, because hash-partitioned indexes are not suitable for LIKE and <, > searches.

To gain an understanding of partitioning, you will want to review the

USING TKPROF

How can I use TKPROF to find problematic queries? The only point I know is that when elapsed time is longer, it means that the query spent more time waiting for something. But I am not sure how to use TKPROF for query tuning.

First, you use TKPROF in general to identify queries in your application that consume “lots of resources,” including CPU time, elapsed time, and so on. Suppose you do this (enable tracing in your application and then run bits of your application):

SQL> begin

2 dbms_monitor.session_trace_enable

TKPROF will produce a report that looks much like the content of Listing 2. Now we have a bunch of facts:

O The query took a long time—about four and a half minutes.

O We did a lot of physical I/O.

O We did a lot of logical I/O.

O We used about 100 CPU seconds.

O The query took about 262 seconds of elapsed time but only 99 seconds of CPU time, so we waited lots of seconds for something.

O We waited for DB file scattered read for a long time—this was the read of a full scan.

O We did a full scan on a huge table.

O We can see our query.

O We have the plan that was used.

Now, what can we do with these facts? We can use our knowledge of the data and how Oracle Database works to “tune.” What are some obvious things to think about there?

Well, we see that the query result returned about 688,256 rows out of 128,000,000. That in itself gives us another fact or two: O We needed a very small subset of rows from this table to answer this query. O The table’s high-water mark is probably OK. Given that we read 1. 8 million blocks and processed 128 million rows, we must have about 70 rows per block, so the table seems to be well packed. So, shrinking or reorganizing the table isn’t going to do anything.