Identify the Resource-Intensive SQL
After identifying the top resource hogging sessions in a database,
the DBA’s attention can then be turned to the code they and others
are executing that is likely causing system bottlenecks.
Also see my notes on v$sess_io for
disk reads per session.
As with top
session monitors, many decent database monitors have a top SQL
feature that can help ferret out bad SQL code. Without access to
such tools, a script like the one shown in
awr_high_resource_sql.sql
can be used.
awr_high_resource_sql.sql
select sql_text,
username,
disk_reads_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
(select sql_text ,
b.username ,
round((a.disk_reads/decode(a.executions,0,1,
a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
a.parse_calls ,
a.sorts ,
a.executions ,
a.rows_processed ,
100 - round(100 *
a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in ('sys','system')
order by 3 desc)
where rownum < 21
SEE CODE DEPOT FOR FULL SCRIPTS
The code above will pull the top twenty SQL statements as ranked by
disk reads per execution. The
rownum filter at the end can be changed to show more or all
SQL that has executed in a database. WHERE predicates can be added
that only show the SQL for one or more of the previously identified
top sessions.
In Oracle9i, the cpu_time
and elapsed_time columns
have been added, which provide more data that can be used to
determine the overall efficiency of an SQL statement. Figure 15.16
shows a sample output of this query:
Figure 15.16 –
The Ion tool for high resource
SQL
We highly recommend the Ion tool to quickly find
and tune high-resource SQL.
The new Oracle9i v$sql_plan view can also help with
identification of problem SQL. For example, a DBA may want to know
how many total SQL statements are causing Cartesian joins on a
system. The following query can answer that question:
select
count(distinct hash_value) carteisan_statements,
count(*) total_cartesian_joins
from
sys.v_$sql_plan
where
options = 'CARTESIAN'
and
operation like '%JOIN%'
SEE CODE DEPOT FOR FULL SCRIPTS
Output from this query will resemble the following, noting that it
is possible for a single SQL statement to contain more than one
Cartesian join:
CARTESIAN_STATEMENTS TOTAL_CARTESIAN_JOINS
----------------------- ---------------------
3 3
A DBA can then view the actual SQL statements containing the
Cartesian joins, along with their performance metrics by using a
query like the following:
select *
from
sys.v_$sql
where
hash_value in
(select hash_value
from
sys.v_$sql_plan
where
options = 'CARTESIAN'
and
operation LIKE '%JOIN%' )
order by hash_value;
SEE CODE DEPOT FOR FULL SCRIPTS
Another area of interest for DBAs is table scan activity. Most DBAs
don’t worry about small-table scans because Oracle can many times
access small tables more efficiency through a full scan than through
index access. Large table scans, however, are another matter. Most
DBAs prefer to avoid those where possible through smart index
placement or intelligent partitioning.
Using the v$sql_plan
view, a DBA can quickly
identify any SQL statement that contains one or more large table
scans. The following query shows any SQL statement containing a
large table scan, defined as a table over 1 MB, along with a count
of how many large scans it causes for each execution, the total
number of times the statement has been executed, and the sum total
of all scans it has caused on the system:
select
sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
from
(select
sql_text,
count(*) total_large_scans,
executions
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where
a.object_owner (+) = b.owner
and
a.object_name (+) = b.segment_name
and
b.segment_type IN ('TABLE', 'TABLE PARTITION')
and
a.operation LIKE '%TABLE%'
and
a.options = 'FULL'
and
c.hash_value = a.hash_value
and
b.bytes / 1024 > 1024
group by
sql_text, executions)
order by
4 desc
;
SEE CODE DEPOT FOR FULL SCRIPTS
This query produces output like that shown in Figure 15.17. Should a
DBA worry more about a SQL statement that causes only one large
table scan but has been executed 1000 times, or should they care
more about a SQL statement that has ten large scans in it but has
only been executed a handful of times?
Figure 15.17 – Ion tool for SQL queries
Each DBA will likely have an opinion on this, but regardless, it is
apparent how such a query can assist in identifying SQL statements
that have the potential to cause system slowdowns.
Now that a way to find suboptimal SQL execution has been introduced,
it would be useful to have a way to change the SQL execution plans
with special optimizer directives called hints. After the tuning
hints have been introduced, readers will be able to appreciate how
SQL Profiles improve the SQL tuning process.
SEE CODE DEPOT FOR FULL SCRIPTS
and include the URL for the page.
