When performace issues are reported most
of the DBA's remain in confused state and the following questions keep running
in their minds.
1) From where do I start, investigating?
2) What are the details that can help me
isolate the troublesome part in the database?
3) How do I prove that my findings are
correct and accurate.
Below are few technique's which can help a
DBA to isolate a performance issue and pin point the culprit behind the
database performance issue.
(a) You can use ADDM report, to find out
the most expensive SQL's that are found (The recent SQLwith performance
issue should be in the list)
SQL>@?/rdbms/admin/sqltrpt.sql
Below is the snippet of the O/P, which
displays the expensive SQL's seen so far.
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------ ----------
-------------------------------------------------------
0z318y6g3uagc 2,733.88 declare l_retstat varchar2 ( 3 ) ;
l_msg_data varchar2
8az1s9vjf7zk4 2,135.59 call
SYS.DBMS_AQADM_SYS.aq$_propagation_procedure ( :0,
gqdfvwwknvrgp 1,309.21 select
apps.xmlpr_transform.wf_event2MGW(source.user_da
dfc81r953uw0x 1,082.00 BEGIN xxg_oci_progress_wf_pkg.process_deferred_wf(:errb
6vgrdp67bc6cq 766.20 SELECT "A5"."ITEM_NUM"
"SORT_ORDER","A5"."ATTRIBUTE1" "
g8102j8yynbwz 761.17 begin WF_STANDARD.CONTINUEFLOW (:v1, :v2,
:v3, :v4, :v5
The above SQL's may be of your insterest
now, find more details on their execution plan etc
Further enter the SQL_ID on to the prompts
that can help you identify the the execution plan for the SQL_ID, You can
directly check for any problem in the execution plan, Also find if the SQL is
using dynamic execution plan.
Also, the report gives the rational's and
other task_id created for doing a SQL profiling, If it finds a better execution
plan.
Now that we talked about SQL profiling ,
that does not give a permanent fix to the troublesome SQL, So, we need more
information to find if its really a bad SQL with a poor code.
Below information's can help us identify,
If the SQL keeps changing the SQL plan very frequently, and there by creating
too many cursors in library cache and leading to performance issue.
e.g
INST_ID SQL_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS
---------- ------------- --------------- ------------ ----------
10 8jc25wc5fvuyq 664152792 0 12459
14 8jc25wc5fvuyq 664152792 0 11476
11 8jc25wc5fvuyq 664152792 0 18313
ID SQL_ID BIND_DATA SERVICE MODULE SQL_TEXT
---------- -------------
--------------------------------------------------------------------
----------- ------------------------
---------------------------------------------------------------------------------------------
10 8jc25wc5fvuyq
BEDA0B2003005077C34D000202F41707D010CBD86FD5A4FE3F24E0434D0EBF0AA66F OMEGAP_OCI
jdbc.OCI.OMARDataSource UPDATE XXG.XXG_ORD_STATUS_IN_JMS_QT tab set
tab.user_data.text_lob = :1 where tab.msgid = :2
(b) Check if the SQL is bind sensitive or
Bind aware? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select is_bind_sensitive, is_bind_aware, sql_id, inst_id,
child_number from gv$sql where sql_text like '%UPDATE
XXG.XXG_ORD_STATUS_IN_JMS_QT tab set tab.user_data.text_lob%';
I I SQL_ID INST_ID CHILD_NUMBER
- - ------------- ---------- ------------
N N 5qcyw4ku1ujfb 9 0
N N 1r2pbduccrp3s 9 0
N N 5qcyw4ku1ujfb 16 0
N N 1r2pbduccrp3s 16 0
N N 5qcyw4ku1ujfb 10 0
N N 1r2pbduccrp3s 10 0
Y N 8jc25wc5fvuyq 10 0
N N 5qcyw4ku1ujfb 15 0
N N 1r2pbduccrp3s 15 0
N N 5qcyw4ku1ujfb 14 0
N N 1r2pbduccrp3s 14 0
Y N 8jc25wc5fvuyq 14 0
N N 5qcyw4ku1ujfb 8 0
N N 1r2pbduccrp3s 8 0
N N 5qcyw4ku1ujfb 2 0
N N 1r2pbduccrp3s 2 0
N N 5qcyw4ku1ujfb 7 0
N N 1r2pbduccrp3s 7 0
N N 5qcyw4ku1ujfb 3 0
N N 1r2pbduccrp3s 3 0
N N 5qcyw4ku1ujfb 1 0
N N 1r2pbduccrp3s 1 0
N N 5qcyw4ku1ujfb 11 0
N N 1r2pbduccrp3s 11 0
Y N 8jc25wc5fvuyq 11 0
N N 5qcyw4ku1ujfb 5 0
N N 1r2pbduccrp3s 5 0
N N 5qcyw4ku1ujfb 13 0
N N 1r2pbduccrp3s 13 0
N N 5qcyw4ku1ujfb 12 0
N N 1r2pbduccrp3s 12 0
N N 5qcyw4ku1ujfb 4 0
N N 1r2pbduccrp3s 4 0
N N 5qcyw4ku1ujfb 6 0
N N 1r2pbduccrp3s 6 0
Let's see what the columns mean. Oracle observes the cursors for a while and
sees how the values differ.
If the different values can potentially alter the plan, the cursor is labeled
"Bind-Sensitive" and the column IS_BIND_SENSITIVE shows
"Y".
After a few executions, the database knows more about the cursors and the
values and decides if the cursor should be made to change plans based on the
values. If that is the case, the cursor is called "Bind-Aware" and
the column IS_BIND_AWARE shows "Y".
In summary: Bind-Sensitive cursors are potential candidates for changed plans
and Bind-Aware ones are where the plans actually change.
SQL> select * from gv$sql_cs_histogram where sql_id =
'8jc25wc5fvuyq';
INST_ID ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ---------------- ---------- ------------- ------------
---------- ----------
10 000000056C898240 183364566 8jc25wc5fvuyq 0 0 0
10 000000056C898240 183364566 8jc25wc5fvuyq 0 1 0
10 000000056C898240 183364566 8jc25wc5fvuyq 0 2 0
14 0000000553834DD0 183364566 8jc25wc5fvuyq 0 0 0
14 0000000553834DD0 183364566 8jc25wc5fvuyq 0 1 0
14 0000000553834DD0 183364566 8jc25wc5fvuyq 0 2 0
11 00000005039AE3D0 183364566 8jc25wc5fvuyq 0 0 0
11 00000005039AE3D0 183364566 8jc25wc5fvuyq 0 1 0
11 00000005039AE3D0 183364566 8jc25wc5fvuyq 0 2 0
As the adaptive cursor sharing feature uses the correct plan based on the value
of the bind variable,
the database must be holding that
information somewhere. It exposes that information through another new view
V$SQL_CS_SELECTIVITY that shows the selectivity of the different values passed
to the bind variable.
SQL> select * from gv$sql_cs_selectivity where sql_id =
'8jc25wc5fvuyq';
no rows selected
This view shows a wealth of information. The column PREDICATE shows the various
predicates (the WHERE condition) users have used. The LOW and HIGH values show
the range of values passed.
SQL> select child_number,
bind_set_hash_value,
peeked,
executions,
rows_processed,
buffer_gets,
cpu_time
from gv$sql_cs_statistics
where sql_id = '8jc25wc5fvuyq';
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
CPU_TIME
------------ ------------------- - ---------- --------------
----------- ----------
0 1416895947 Y 1 2 50 0
0 186061784 Y 1 2 78 0
0 3949253548 Y 1 2 108 0
This view shows the statistics about the execution as recorded by the database.
The column EXECUTIONS shows how many times the query was seen to be executed
with different values in the bind variable.
The column PEEKED (shown as "P") in the output shows if the optimizer
peeked into the bind variable to arrive at a good plan.
These views show additional information
that is not necessary for you to understand how this feature works.
Adaptive Cursors are activated and used
automatically.
HAPPY LEARNING!
