 |
|
What Constitutes a
Significant SQL Statement?
Oracle Tips by
Burleson
|
There is some debate about which characteristic
of a SQL statement makes it the best candidate for tuning. Some people
always tune statements according to their values for executions,
while others always tune SQL in order of disk_reads, tuning
the statements with the highest disk_reads first. As a
review, we can extract SQL statements from the library cache or the stats$sql_summary
table, based on the following criteria:
-
Executions The more frequently executed
SQL statements should be tuned first, since they will have the
greatest impact on overall performance.
-
disk_reads High disk reads indicates a
query that is causing lots of disk I/O. While most SQL with high
disk_reads consists of reports, online summaries and
aggregations may have high disk_reads and should be carefully
examined.
-
rows_processed Queries that process a
large number of rows will have high I/O and may also have an impact
on the TEMP tablespace if sorting occurs. Queries with high values
for rows_processed may be reports or decision support queries
that are not online transactions and may benefit from using the
all_rows optimizer goal.
-
buffer_gets High buffer gets may
indicate a resource-intensive query.
-
memory_kb This metric shows the memory
used for each SQL statement. High memory_kb values are most
often associated with large sorting operations.
-
sorts Sorts can be a huge
slowdown, especially if the sorts are being done on disk in the TEMP
tablespace.
As you now
know, you can get lists of your SQL statements from the v$sqlarea
view or stats$sql_summary table in descending order of any of
these variables. You can also use third-party tools such as the Q
Diagnostic Center by Precise Software to quickly view SQL statements
according to these characteristics.
Figure 2: Displaying
SQL by rows_processed in the Q Diagnostic Center
Of course, you do not need to buy expensive tools
to perform this task. Let’s take a quick look at some common methods
for manually identifying high-impact SQL statements.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
