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)
Below is the snippet of the O/P, which displays the expensive SQL's seen so far.
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
---------- ------------- -------------------------------------------------------------------- ----------- ------------------------ ---------------------------------------------------------------------------------------------
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? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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.
INST_ID ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
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.
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.
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
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!
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
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!
No comments:
Post a Comment
Thanks for you valuable comments !