 |
|
An Actual Case-Study in SQL Tuning
Oracle Tips by Burleson
|
The first activity of most SQL tuning sessions
is to identify and remove unnecessary full table scans. This SQL
tuning activity can make a huge difference in SQL performance, since
unnecessary full table scans can take 20 times longer than using an
index to service the query. Again, here are the basic steps in
locating and fixing full table scans:
1. Run the full table scan report to locate SQL statements that
produce full table scans.
2.
Then query the v$sqltext view to locate the individual SQL
statements.
3.
Explain the statement to see the execution plan.
4.
Add indexes or hints to remove the full table scan.
5.
Change the SQL source or store the outline to make the change
permanent.
Let's quickly step through these activities and
see how easy it is to improve the performance of SQL statements.
Get the Full Table Scan Report
First, we run the access.sql script to
extract and explain all of the SQL in the library cache. Here is a
sample from an actual report:
L 11-47
Mon Jan
29
page 1
full table scans and counts
Note that “C” indicates in the table is cached.
OWNER NAME NUM_ROWS C K BLOCKS
NBR_FTS
-------------- ------------------------ ------------ - - --------
--------
SYS DUAL N 2
97,237
SYSTEM SQLPLUS_PRODUCT_PROFILE N K 2
16,178
DONALD PAGE 3,450,209 N 932,120
9,999
DONALD RWU_PAGE 434 N
8 7,355
DONALD PAGE_IMAGE 18,067 N
1,104 5,368
DONALD SUBSCRIPTION 476 N K
192 2,087
DONALD PRINT_PAGE_RANGE 10 N K
32 874
ARSD JANET_BOOKS 20 N
8 64
PERFSTAT STATS$TAB_STATS N
65 10
Here we see a clear problem with large-table full table
scans against the page_image table. The page_image table has 18,067
rows and consumes 1,104 blocks. The report shows 5,368 full table
scans against this table. Next, we can run a quick query to display
the SQL source from v$sqlarea for the page_image table, looking for a
SQL statement that has been executed about 5,000 times:
L 11-48
set lines 2000;
select
sql_text,
disk_reads,
executions,
parse_calls
from
v$sqlarea
where
lower(sql_text) like '%page_image%'
and
executions > 100
order by
disk_reads desc
;
In the result from this query, we will look for SQL
statements whose values for executions (5,201) approximate the value
in the full table scan report (5,368).
From the output, we clearly see the offensive
SQL statement:
L 11-49
SELECT IMAGE_BLOB FROM PAGE_IMAGE WHERE (BOOK_UNIQUE_ID
= :b1
AND PAGE_SEQ_NBR = :b2 AND IMAGE_KEY = :b3 )
833 5201 148
Now that we have the SQL, we can quickly explain it and
verify the full table scan:
L 11-50
delete from plan_table where statement_id =
'test1';
explain plan set statement_id = 'test1'
for
SELECT IMAGE_BLOB FROM PAGE_IMAGE WHERE (BOOK_UNIQUE_ID = :b1
AND PAGE_SEQ_NBR = :b2 AND IMAGE_KEY = :b3 )
;
Here we run the execution plan showing our full table
scan:
L 11-51
OPERATION
----------------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
168
TABLE ACCESS
FULL PAGE_IMAGE
1
Since this is a very simple query against a single
table, we can look directly at the WHERE clause to see the problem.
The only condition in the WHERE clause references upper(book_unique_id
), and the Oracle optimizer has not detected a usable index on
this column. Since we are in post-Oracle8, we can create a
function-based index using the upper function:
L 11-52
create unique index book_seq_image_idx
on page_image
(book_unique_id,
page_seq_nbr,
image_key)
tablespace bookx
pctfree 10
storage (initial 128k next 128k maxextents 2147483645 pctincrease
0);
Now we rerun the execution plan and see the full table
scan is replaced by an index scan:
L 11-53
OPERATION
--------------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ------------------------------
----------
SELECT STATEMENT
TABLE ACCESS
BY INDEX ROWID PAGE_IMAGE
1
INDEX
UNIQUE SCAN BOOK_SEQ_IMAGE_IDX
1
Problem solved! The query went from an original
execution time of 3 minutes to less than 10 seconds.
Now that we see the iterative process of
locating and tuning SQL statements, let's look at how third-party GUI
tools can speed up the process. This can be very important when the
DBA must tune hundreds of SQL statements.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
