 |
|
Learn RAC Tuning
Internals!
This is an excerpt from the landmark book
Oracle RAC Performance tuning,
a book that provides real world advice for resolving
the most difficult RAC performance and tuning issues.
Buy it
for 30% off directly from the publisher.
|
So let's see if we can walk through an example to determine
the cause of the problem. To do that, I used the following
query:
select s.inst_id as inst,
s.sid as blocked_sid,
s.username as blocked_user,
sa.sql_id as blocked_sql_id,
trunc(s.p2/4294967296) as blocking_sid,
b.username as blocking_user,
b.sql_id as blocking_sql_id
from gv$session s
join gv$sqlarea sa
on sa.hash_value = s.p1
join gv$session b
on trunc(s.p2/4294967296)=b.sid
and s.inst_id=b.inst_id
join gv$sqlarea sa2
on b.sql_id=sa2.sql_id
where s.event='cursor: pin S wait on X';
Running this in one of my production RAC databases, I get
the following output:
INST BLOCKED_SID BLOCKED_USER BLOCKED_SQL_ID BLOCKING_SID BLOCKING_USER BLOCKING_SQL_ID
---- ----------- ------------ -------------- ------------ ------------- ---------------
4 723 USER12345 cn7m7t6y5h77g 1226 USER12345 cn7m7t6y5h77g
4 723 USER12345 cn7m7t6y5h77g 1226 USER12345 cn7m7t6y5h77g
4 723 USER12345 cn7m7t6y5h77g 1226 USER12345 cn7m7t6y5h77g
4 723 USER12345 cn7m7t6y5h77g 1226 USER12345 cn7m7t6y5h77g
4 1226 USER12345 cn7m7t6y5h77g 1796 USER12345 cn7m7t6y5h77g
4 1226 USER12345 cn7m7t6y5h77g 1796 USER12345 cn7m7t6y5h77g
4 1226 USER12345 cn7m7t6y5h77g 1796 USER12345 cn7m7t6y5h77g
4 1226 USER12345 cn7m7t6y5h77g 1796 USER12345 cn7m7t6y5h77g
The first thing to note is that the mutex is only within
that instance for Oracle RAC databases. For single-instance
databases, the query above will still work. For Oracle RAC,
the output from this query will show which instance is
having the problem.
In the example above, we have session 723 blocked by
session 1226. Session 1226 is further blocked by session
1796. Notice that all three sessions are issuing the same
query with SQL ID cn7m7t6y5h77g.
Now that we know the SQL ID, we can easily query V$SQL to
determine the SQL statement involved in the problem. I used
this query to obtain more information.
select sql_id,loaded_versions,executions,loads,invalidations,parse_calls
from gv$sql
where inst_id=4 and sql_id='cn7m7t6y5h77g';
The output from querying V$SQL is as follows:
SQL_ID LOADED_VERSIONS EXECUTIONS LOADS INVALIDATIONS PARSE_CALLS
------------- --------------- ---------- ---------- ------------- -----------
cn7m7t6y5h77g 1 105 546 308 3513
We can now see that this query has only 1 version in the
SQL Area. So right away, we've eliminated one of the
potential problem areas.
It should be obvious from the above that there is a very
high number of parse calls. The query has only been executed
105 times but has been parsed 3513 times. Yikes! The high
number if invalidation's probably has something to do with
this as well.
In this example, we now have a good idea of what the
problem is. This is an application issue. The application is
over-parsing the query. So we'll send this back to
development and dig into the application code. The usual
reasons for over-parsing need to be examined.
If the number of versions were low and excessive
parsing/invalidations/loads was not an issue, then I would
suspect a bug and file a SR with My Oracle Support Community
(MOSC).
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
