Adaptive Cursor Sharing

Adaptive cursor sharing was introduced by Oracle in 11g release 1 as a way to generate best plan for a SQL in all situation. Prior to adaptive cursor sharing, optimizer used to generate a single plan for a SQL statement and that plan is used by all cursors of that SQL_ID. So if we have data skewness in a column and that column is being using in “where” clause of the SQL statement, single plan of that SQL will not be optimum for all values of that column, which is passed to the bind variable.

Lets take a simple example. We will create a table and add random data to it.

SQL> create table T1
 2 (emp_id number,
 3 dept_id number,
 4 salary number,
 5 gender char(1),
 6 dob date,
 7 address varchar2(60));

Table created.

Let me add 10K records to this table

SQL> insert into T1
     SELECT LEVEL,
            (MOD (ROWNUM, 2)+1)*10,
            TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2),
            DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F'),
            TO_DATE ( ROUND (DBMS_RANDOM.VALUE (1, 28))
                     || '-'
                     || ROUND (DBMS_RANDOM.VALUE (1, 12))
                     || '-'
                     || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
                        'DD-MM-YYYY'
                     ),
            DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50))
     FROM DUAL
     CONNECT BY LEVEL < 10000;

9999 rows created.

We can make one of the column – DEPT_ID as skewed by updating the records as below

SQL> update T1 set dept_id = 10 where rownum <= 9950;

9950 rows updated.

SQL> update T1 set dept_id = 20 where dept_id != 10;

49 rows updated.

SQL> select dept_id, count(1) from T1 group by dept_id;

   DEPT_ID COUNT(1)
---------- ----------
        20 49
        10 9950

So we have skewness for column DEPT_ID now.

Lets gather statistics on this table with cascade option so that it will gather statistics on index as well. Since the data is skew in index, it will also generate histogram.


SQL> exec dbms_stats.gather_table_stats(OWNNAME=>user, TABNAME=>'T1',CASCADE => TRUE , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL> select * from dba_histograms where TABLE_NAME = 'T1';

OWNER                          TABLE_NAME                     COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ ------------------------------ --------------- -------------- --------------------
ADVAITD_DBA                    T1                             DEPT_ID                                   9950             10
ADVAITD_DBA                    T1                             DEPT_ID                                   9999             20

SQL> select table_name, column_name, histogram from user_tab_cols where table_name = 'T1' and histogram != 'NONE';

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM
------------------------------ ------------------------------ ---------------
T1                             DEPT_ID                        FREQUENCY

We can see it has created frequency histogram on the index column – DEPT_ID

Adaptive cursor sharing is affected by multiple factors. Following are some of the important factors which affects adaptive cursor sharing.

  • Adaptive cursor sharing is enabled or not. This is the first basic factor which determines if adaptive cursor sharing is enabled or not
  • CURSOR_SHARING parameter and what value we are setting for this parameter
  • SQL plan baseline. If we have baselines enabled and what all SQL baselines are available for a SQL in ENABLED=YES and ACCEPTED=YES
  • OPTIMIZER_INDEX_COST_ADJ – What is the value of this parameter set in database.

You can check if adaptive cursor sharing is enabled by checking the parameter _optimizer_adaptive_cursor_sharing

SQL>@param
Enter Parameter name: _adaptive_cursor_sharing

Parameter                            |Description                                                 |Session Va|Instance V
-------------------------------------|------------------------------------------------------------|----------|----------
_optimizer_adaptive_cursor_sharing   |optimizer adaptive cursor sharing                           |TRUE      |TRUE

Now that we have adaptive cursor sharing enabled, lets see how it works and the effect of other factors on adaptive cursor sharing

How adaptive cursor sharing works?

So we have a table T1 with 10K records and one of the column DEPT_ID is having skewed data as seen above. We have 9950 records with DEPT_ID = 10 and 49 records with DEPT_ID = 20.

Lets try to run some queries and check how optimizer behaves

Running SQL for DEPT_NO = 20


SQL>exec :dept_no :=20;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;

  COUNT(1)|MAX(DOB)
----------|----------------
        49|2002-09-06:00:00

SQL>select sql_id, child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';

SQL_ID       |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g|           0|     3776485839|         1|         58|Y|N|Y

Pay attention to the columns IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE. Column IS_BIND_SENSITIVE tells whether output of the query is sensitive to values of bind variable. Meaning if we change the value of bind variable, will the output change significantly?

In above case, optimizer is showing IS_BIND_SENSITIVE=Y based on the histogram that is present in the column used in “where” clause.

As per Oracle definition, IS_BIND_SENSITIVE VARCHAR2(1) Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan.

The plan its using currently is index range scan

select /*+ GATHER_PLAN_STATISTICS */ count(:"SYS_B_0"), max(dob) from
t1 where dept_id = :dept_no

Plan hash value: 3776485839

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE              |              |     1 |    12 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |    49 |   588 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_T1_DEPT_ID |    49 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT_ID"=:DEPT_NO)

Lets run the query again but this time we will use the bind value of 10, which has 9950 records

Running SQL for DEPT_NO = 10


SQL>exec :dept_no := 10;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;

  COUNT(1)|MAX(DOB)
----------|----------------
      9950|2010-12-27:00:00

SQL>select sql_id, child_number, plan_hash_value , executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';

SQL_ID       |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g|           0|     3776485839|         2|        169|Y|N|Y

You can see now, we have 2 executions but same cursor is being used. During the second run, we used different bind value and optimizer becomes aware that using different bind value is actually fetching different number of records and plan is expensive.

So even though it didn’t generate any new cursor for this and used same plan as it had earlier, it will generate a new cursor when we run this again for DEPT_NO = 10 and hopefully it will also use a new plan (probably full table scan).

Lets run this query again for same DEPT_NO = 10

Running SQL for DEPT_NO = 10


SQL>exec :dept_no := 10;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;

  COUNT(1)|MAX(DOB)
----------|----------------
      9950|2010-12-27:00:00

SQL>select sql_id, child_number, plan_hash_value , executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';

SQL_ID       |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g|           0|     3776485839|         2|        169|Y|N|N
btj8b1hx3cf0g|           1|     3724264953|         1|         96|Y|Y|Y

Now, you see that it has created a new cursor along with a new plan and also is_bind_aware becomes YES for new cursor. At the same time, cursor # 0 becomes non-shareable (check IS_SHAREABLE column is showing ‘N’). So since optimizer knows that this query is bind sensitive, it will create new cursors which are bind aware and doesn’t use the cursor it created initially, which was not bind aware.

As per Oracle definition, IS_BIND_AWARE VARCHAR2(1) Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive.

Sometimes optimizer just creates a new cursor with same plan and it might go for index range scan even for selecting majority of rows from the table. This happened with me when I was trying this example. In such cases, cost of plan is determined by optimizer_index_cost_adj parameter. In my case, value of this parameter is set to lowest value of 1 so it will going for index range scan in all cases. Lower value of this variable represents lower cost for index access and it favors use of index. I set the value of this parameter to 50 and after that it was going for full table scan.

So if you check the plan for cursor 1, it will show full table scan as we are selecting 99% of rows from the table (DEPT_NO = 10 will fetch 9950 rows)

SQL>select * from table(dbms_xplan.display_cursor('btj8b1hx3cf0g',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  btj8b1hx3cf0g, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(:"SYS_B_0"), max(dob) from
t1 where dept_id = :dept_no

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    27 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    12 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  9950 |   116K|    27   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPT_ID"=:DEPT_NO)

At this point if we go for another execution with DEPT_NO = 20, it will create another cursor, which will be bind aware

Running SQL for DEPT_NO = 20

SQL>exec :dept_no := 20;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;

  COUNT(1)|MAX(DOB)
----------|----------------
        49|2002-09-06:00:00

SQL>select sql_id, child_number, plan_hash_value , executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';

SQL_ID       |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g|           0|     3776485839|         2|        169|Y|N|N
btj8b1hx3cf0g|           1|     3724264953|         1|         96|Y|Y|Y
btj8b1hx3cf0g|           2|     3776485839|         1|          3|Y|Y|Y

As we can see it created cursor 2 which is bind aware. So now cursor 0 is not shareable and will be removed from shared pool in sometime but cursor 1 and 2 are shareable and will be used by further executions.

Effect of bind peeking on adaptive cursor sharing

One of the important thing for using adaptive cursor sharing is that you need to enable bind peeking in database. Without enabling bind peeking, adaptive cursor sharing will not work.

Initially, in my environment, bind peeking was not enabled and because of that optimizer doesn’t have visibility on what values are being passed to the bind variables. So in that case optimizer will not generate new cursors even when adaptive cursor sharing is enabled. Optimizer does NOT even mark the cursor as bind sensitive as it doesn’t have any visibility in bind values

As you can see below, it was using same cursor for multiple executions


SQL>select sql_id, child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like '%test_sql%';

SQL_ID       |CHILD_NUMBER|EXECUTIONS|BUFFER_GETS|I|I
-------------|------------|----------|-----------|-|-
2bxcmkhms19jn|           0|        12|        220|N|N

If we check the bind peeking, it was turned off

SQL>@param
Enter Parameter name: peek

Parameter                            |Description                                                 |Session Va|Instance V
-------------------------------------|------------------------------------------------------------|----------|----------
_optim_peek_user_binds               |enable peeking of user binds                                |FALSE     |FALSE

Effect of CURSOR_SHARING parameter on adaptive cursor sharing

Cursor sharing plays an important role when we are using adaptive cursor sharing. CURSOR_SHARING parameters determines if a cursor can be shared or a new cursor should be created. This was discussed very well on https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force

But will give a brief about how CURSOR_SHARING plays an important role.
Until 11g R2, we had 3 values for CURSOR_SHARING parameter

  • EXACT: By setting this value, optimizer will not replace the literals used in the statement with system generated bind variables. So if you are using literals in the statement, each statement is considered as different depending on the literal values you have used.
  • SIMILAR: Using this value, optimizer will replace the literals with system generated bind variable only if you don’t have histogram on the column for which you have used literal. If you have histogram, optimizer will NOT replace the literal with bind variable
  • FORCE: This value will replace all the literals with system generated bind variables irrespective of whether a histogram is present on the column or not.

Example: we have following statement with literal

select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = 10;

This is the same statement that we used above and optimzer knows that DEPT_ID column is skewed and has histogram defined on it. Becuase it has histogram defined, optimizer knows that data is skewed and replacing the literal value with system generated bind variable can lead to suboptimal plan.

So if we use CURSOR_SHARING = SIMILAR and we have statements like above where we are using literal values on columns with histogram, optimizer will not replace the literal with bind variables.

In such cases adaptive cursor sharing will not kick in as no bind variables present in the query.

For adaptive cursor sharing to work in such situation, we need to drop histograms on the columns where we are using literal values. This has its own problems as data skewness cannot be determined by optimizer.

This is one of the main reason why a value of SIMILAR is deprecated in oracle 12c for CURSOR_SHARING parameter. Oracle encourages to use only FORCE and EXACT going forward.

Effect of baseline on adaptive cursor sharing

This is one of the thing that is covered in good detail in https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing. I am just covering this topic briefly for the sake of completion.

So baseline has a different purpose than adaptive cursor sharing. While adaptive cursor sharing deals with using the right cursor and plan for a SQL statement depending on the bind vales we are passing and skewness of data, purpose of baseline is to provide the stability and avoid using any new unverified plan.

Adaptive cursor sharing kicks in before SQL management baseline. So when the statement is executed, adaptive cursor sharing will peak for bind values and will check the skewness of data. Based on these inputs, it will check if one of the existing cursor and plan can be used or it can derive a new better plan. Adaptive cursor sharing can tells optimizer that a new plan or a cursor needs to be created and after that control moves on to the next part of optimizer. So adaptive cursor sharing only suggests that a new cursor or plan is better and required to be created and provides all the required data.

Now, if we have SQL plan baseline already enabled on this SQL, it will prevent optimizer to use a new plan. However, optimizer can create a new plan and save that in the history of SQL management base. This new plan will be enabled but NOT ACCEPTED. EIther DBA has to manually accept the plan or have to wait for “sql tuning advisor” auto task to kick in and accept the baseline if its verified as good. Only after accepting the new plan, it will be used for the SQL.

In my case, I had a baseline created for the SQL initially.


SQL>exec :dept_no :=20;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;

  COUNT(1)|MAX(DOB)
----------|----------------
        49|2002-09-06:00:00


If you check the explain plan for this SQL

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  btj8b1hx3cf0g, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(:"SYS_B_0"), max(dob) from
t1 where dept_id = :dept_no

Plan hash value: 3776485839

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE              |              |     1 |    12 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |    49 |   588 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_T1_DEPT_ID |    49 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT_ID"=:DEPT_NO)

Note
-----
   - SQL plan baseline SQL_PLAN_gwfyvvw5h1w96e68c0740 used for this statement

We can see baseline is getting used in the Note section. We can also see the details of baseline in DBA_SQL_PLAN_BASELINES table


SQL>select sql_handle, plan_name from dba_sql_plan_baselines where signature = (select exact_matching_signature from v$sql where sql_id = 'btj8b1hx3cf0g');

SQL_HANDLE                    |PLAN_NAME
------------------------------|------------------------------
SQL_fe3bdbdf0b00f126          |SQL_PLAN_gwfyvvw5h1w96e68c0740

Even after running the SQL multiple times for different bind values, we are not getting any new cursor. Also, the cursor is not marked as bind sensitive as shown in below output


SQL>select sql_id, child_number, plan_hash_value , executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';

SQL_ID       |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g|           0|     3776485839|         5|        339|N|N|Y

So if we have SQL plan baselines enabled for a SQL and we have only 1 baseline accepted, we will not see the advantages of adaptive cursor sharing unless we have multiple baselines ACCEPTED so that optimizer can use different plan as suggested by adaptive cursor sharing.

This covers most of the situation for using adaptive cursor sharing.

Hope this helps !!

Reference

https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing

https://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing

https://docs.oracle.com/database/121/REFRN/GUID-455358F8-D657-49A2-B32B-13A1DC53E7D2.htm#REFRN10025

https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force

Advertisements

Fixing SQL plans on ADG using SQL Profiles

With Active Dataguard setup, many of the read only applications runs on ADG and sometime the SQLs that are used by these applications runs into bad plans. ADG being a read only database, its not possible to create a baseline or a profile on ADG.

One of the way to have good plan for SQLs running on ADG is to make them run on primary first and fix the plan by creating profile or baseline and then transfer these SQLs to ADG. Practically I have seen many times that Dev teams are not very flexible in changing the configuration to run the queries on primary. Either there configuration setup is complex and takes more time and efforts to change them or most of the time in bigger enterprises, they have central configuration file which when changed, changes the configuration for entire region. In that case complete software techstack will run on primary for entire region and not just 1 database.

Another way to deal make queries run on primary is to point ADG CNAME to primary. That way all the applications which are supposed to run on ADG will start running on primary. Down side for this is the increase in load on primary as all the applications on ADG will connect to primary. Its too much of a change to fix 1 SQL on ADG.

This article is about fixing plans on ADG using profile, without making any other change. We don’t have to run the query on primary to fix the plan.

Fixing SQL plan on ADG

In one of my old article “Fixing SQL Plans: The hard way – Part 1“, I mentioned about DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure. This procedure takes internal SQL hints that optimizer understands as input and create a profile to fix the plan.

In that method, I am providing SQL text as input, but the way I am providing SQL text as input is by selecting “SQL_TEXT” column from either V$SQL_TEXT view or from DBA_HIST_SQLTEXT view.

Following is the partial code of the PL/SQL block I used to create profile

for sql_rec in (
 select t.sql_id, t.sql_text
 from dba_hist_sqltext t, dba_hist_sql_plan p
 where t.sql_id = p.sql_id
 and p.sql_id = '&SQL_ID_TO_FIX'
 and p.plan_hash_value = &BAD_PLAN_HASH_VALUE
 and p.parent_id is null
)
loop
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => sql_rec.sql_text,
 profile => ar_profile_hints,
 name => 'PROFILE_33fndgzsas09k');

end loop;

Why we cannot use this method for ADG?

We cannot use this method for ADG. You will find the required SQL text in V$SQL_TEXT view in ADG, but you cannot create SQL profile on ADG as its a read-only database.
Also, you cannot create a profile on primary because you will not find SQL text on primary.

So, How do we fix this?

Method 1:

One of the method I thought of for fixing the SQL is by creating a database link between primary and ADG (say adg_db_link) and use that DB link to get SQL text from standby. So above PL/SQL code will look like below. Note that I have change DBA_HIS* table with V$ views because ADG data will be present in only V$ views. DBA_HIST* tables will have data from primary database only as part of AWR snapshot.

for sql_rec in (
 select t.sql_id, t.sql_text
 from v$sqltext@adg_db_link t, v$sql_plan@adg_db_link p
 where t.sql_id = p.sql_id
 and p.sql_id = '&SQL_ID_TO_FIX'
 and p.plan_hash_value = &BAD_PLAN_HASH_VALUE
 and p.parent_id is null
)
loop
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => sql_rec.sql_text,
 profile => ar_profile_hints,
 name => 'PROFILE_33fndgzsas09k');

end loop;

You can run the complete PL/SQL block on primary and using the DB link will fetch the required SQL text from ADG and you can create SQL profile on primary.

Once this SQL profile is created, it should be picked up by SQL query on ADG. Note that you need to purge the SQL from shared pool of ADG so that SQL will go for hard parse and pick the profile.

Method 2:

Another method we can use, is to pass SQL text as input to DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure. This is the method I implemented and it worked for me.

Let’s take a example.

Following is my SQL text that I want to fix. I was having a completely different SQL in my environment, but I cannot provide actual text for that SQL. My SQL was very big and complex, So I simplified the same and changed table name and column name to understand easily.

select * from ( 
          select this_.col1 as col1,
                 this_.col2 as col2, 
                 this_.col3 as col3, 
                 this_.col4 as col4
          from TAB1 this_ 
          inner join TAB2 compaddres3_ on this_.col2 = compaddres3_.col2 
          inner join TAB3 compshipme1_ on this_.col1=compshipme1_.col1
          where this_.col1 in (:1) 
          and   this_.col2 in (:2 , :3) 
          and   compshipme1_.col1 in (:4 )
          and   this_.col8 = 'OPEN'
          and   this_.col5>=:5 
          and   this_.col5<=:6 
          order by this_.col1 asc ) 
where rownum <= :7

Assuming that existing plan in ADG database is bad, we need to find a good plan of this SQL and also create a profile so that new good plan will be picked in ADG.
We are going to do that without running this SQL on primary or without pointing ADG CNAME to primary. Also, we are not going to do any code changes to add any hints.

Following are the steps to fix the plan on ADG

1) Get a good plan for this SQL

You can use the hints in SQL text and do an explain plan on primary to check if the plan looks good. Alternatively, if you want to be sure, you can run the hinted SQL in ADG to make sure that SQL runs good and efficiently.

I used few index hints in my SQL to make sure correct index is getting picked. I did “explain plan” for the SQL to check the explain plan.

explain plan for
select * from ( 
          select /*+ index(tab1 I_UNIQ_COL2) */ this_.col1 as col1,
                 this_.col2 as col2, 
                 this_.col3 as col3, 
                 this_.col4 as col4
          from TAB1 this_ 
          inner join TAB2 compaddres3_ on this_.col2 = compaddres3_.col2 
          inner join TAB3 compshipme1_ on this_.col1=compshipme1_.col1
          where this_.col1 in (:1) 
          and   this_.col2 in (:2 , :3) 
          and   compshipme1_.col1 in (:4 )
          and   this_.col8 = 'OPEN'
          and   this_.col5>=:5 
          and   this_.col5<=:6 
          order by this_.col1 asc ) 
where rownum <= :7

I got following plan after using required hints

Plan hash value: 2524091007

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name         | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0   | SELECT STATEMENT                          |              | 134  | 1524K | 4134   (3) | 00:00:50 |       |       |
|* 1  |  COUNT STOPKEY                            |              |      |       |            |          |       |       |
| 2   |   VIEW                                    |              | 134  | 1524K | 4134   (3) | 00:00:50 |       |       |
|* 3  |    SORT ORDER BY STOPKEY                  |              | 134  | 71154 | 4134   (3) | 00:00:50 |       |       |
|* 4  |     FILTER                                |              |      |       |            |          |       |       |
| 5   |      NESTED LOOPS                         |              | 134  | 71154 | 4133   (3) | 00:00:50 |       |       |
| 6   |       NESTED LOOPS                        |              | 134  | 43550 | 3731   (3) | 00:00:45 |       |       |
| 7   |        TABLE ACCESS BY INDEX ROWID        | TAB1         | 1    | 22    | 1      (0) | 00:00:01 |       |       |
|* 8  |         INDEX UNIQUE SCAN                 | I_UNIQ_COL2  | 1    |       | 0      (0) | 00:00:01 |       |       |
| 9   |        TABLE ACCESS BY GLOBAL INDEX ROWID | TAB2         | 134  | 40602 | 3730   (3) | 00:00:45 | ROWID | ROWID |
|* 10 |         INDEX RANGE SCAN                  | I_TAB2_COL2  | 242  |       | 3507   (3) | 00:00:43 |       |       |
| 11  |        TABLE ACCESS BY GLOBAL INDEX ROWID | TAB3         | 1    | 206   | 3      (0) | 00:00:01 | ROWID | ROWID |
|* 12 |         INDEX RANGE SCAN                  | PK_TAB3_COL1 | 1    |       | 2      (0) | 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------

2) Obtain outline hints for SQL

Once you perform explain plan for the SQL, you can use FORMAT=>’ADVANCED’ to get the neccessary outline hints

select * from table(dbms_xplan.display(format=>’advanced’));

/*+
 BEGIN_OUTLINE_DATA
 USE_NL(@"SEL$54D64B3C" "COMPADDRES3_"@"SEL$2")
 USE_NL(@"SEL$54D64B3C" "THIS_"@"SEL$2")
 LEADING(@"SEL$54D64B3C" "COMPSHIPME1_"@"SEL$3" "THIS_"@"SEL$2" "COMPADDRES3_"@"SEL$2")
 INDEX_RS_ASC(@"SEL$54D64B3C" "COMPADDRES3_"@"SEL$2" ("TAB2"."COL2"))
 INDEX_RS_ASC(@"SEL$54D64B3C" "THIS_"@"SEL$2" ("TAB1"."COL1" "TAB1"."COL2" "TAB1"."COL3" "TAB1"."COL4"))
 INDEX_RS_ASC(@"SEL$54D64B3C" "COMPSHIPME1_"@"SEL$3" ("TAB3"."COL1"))
 NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
 OUTLINE(@"SEL$2")
 OUTLINE(@"SEL$3")
 MERGE(@"SEL$2")
 OUTLINE(@"SEL$64EAE176")
 OUTLINE(@"SEL$4")
 OUTLINE_LEAF(@"SEL$1")
 MERGE(@"SEL$64EAE176")
 OUTLINE_LEAF(@"SEL$54D64B3C")
 ALL_ROWS
 OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
 DB_VERSION('11.2.0.4')
 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
 IGNORE_OPTIM_EMBEDDED_HINTS
 END_OUTLINE_DATA
 */

Step 3) Above outline data is useful for creating profile for query running on ADG

SQL Profile have to created on primary as we cannot create SQL profile on ADG (read only database).
Previous method described in”Fixing SQL Plans: The hard way – Part 1” uses the SQL ID as input and queries V$SQLTEXT or DBA_HIST* tables to get the required SQL text. This SQL text is then supplied to DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure.

But since we don’t have SQL text in primary, we can directly supply SQL text to DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure as shown below.

Note – If you have single quotes in your script, you need to change that to 2 single quotes. 1st single quote works as escape character

In my SQL text, I have a single quote literal. So I have to enclose that to 2 single quotes as shown below

select * from ( 
       select this_.col1 as col1,
              this_.col2 as col2, 
              this_.col3 as col3, 
              this_.col4 as col4
       from TAB1 this_ 
       inner join TAB2 compaddres3_ on this_.col2 = compaddres3_.col2 
       inner join TAB3 compshipme1_ on this_.col1=compshipme1_.col1
       where this_.col1 in (:1) 
       and   this_.col2 in (:2 , :3) 
       and   compshipme1_.col1 in (:4 )
       and   this_.col8 = ''OPEN''
       and   this_.col5>=:5 
       and   this_.col5<=:6 
       order by this_.col1 asc ) 
where rownum <= :7

Above SQL will be given as input to DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure.
Similarly, if we have single quotation in outline hints that we extracted, we need to change that to 2 single quotes as shown below.

Also, we need to mark every outline hint in single quotation.

'BEGIN_OUTLINE_DATA',
'USE_NL(@"SEL$54D64B3C" "COMPADDRES3_"@"SEL$2")',
'USE_NL(@"SEL$54D64B3C" "THIS_"@"SEL$2")',
'LEADING(@"SEL$54D64B3C" "COMPSHIPME1_"@"SEL$3" "THIS_"@"SEL$2" "COMPADDRES3_"@"SEL$2")',
'INDEX_RS_ASC(@"SEL$54D64B3C" "COMPADDRES3_"@"SEL$2" ("TAB2"."COL2"))',
'INDEX_RS_ASC(@"SEL$54D64B3C" "THIS_"@"SEL$2" ("TAB1"."COL1" "TAB1"."COL2" "TAB1"."COL3" "TAB1"."COL4"))',
'INDEX_RS_ASC(@"SEL$54D64B3C" "COMPSHIPME1_"@"SEL$3" ("TAB3"."COL1"))',
'NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")',
'OUTLINE(@"SEL$2")',
'OUTLINE(@"SEL$3")',
'MERGE(@"SEL$2")',
'OUTLINE(@"SEL$64EAE176")',
'OUTLINE(@"SEL$4")',
'OUTLINE_LEAF(@"SEL$1")',
'MERGE(@"SEL$64EAE176")',
'OUTLINE_LEAF(@"SEL$54D64B3C")',
'ALL_ROWS',
'OPT_PARAM(''_optimizer_extended_cursor_sharing_rel'' ''none'')',
'DB_VERSION(''11.2.0.4'')',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'END_OUTLINE_DATA'

Note that we have enclosed the hints in single quotes and also put a comma at the end of every hint except the last hint.

So we have required outline hints and SQL text that should be provided as input to DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure

Step 4) Creating required profile on primary

Below shows the code for creating SQL profile on primary after providing required inputs.

declare
ar_profile_hints sys.sqlprof_attr;
begin
  ar_profile_hints := sys.sqlprof_attr(
     'BEGIN_OUTLINE_DATA',
     'USE_NL(@"SEL$54D64B3C" "COMPADDRES3_"@"SEL$2")',
     'USE_NL(@"SEL$54D64B3C" "THIS_"@"SEL$2")',
     'LEADING(@"SEL$54D64B3C" "COMPSHIPME1_"@"SEL$3" "THIS_"@"SEL$2" "COMPADDRES3_"@"SEL$2")',
     'INDEX_RS_ASC(@"SEL$54D64B3C" "COMPADDRES3_"@"SEL$2" ("TAB2"."COL2"))',
     'INDEX_RS_ASC(@"SEL$54D64B3C" "THIS_"@"SEL$2" ("TAB1"."COL1" "TAB1"."COL2" "TAB1"."COL3" "TAB1"."COL4"))',
     'INDEX_RS_ASC(@"SEL$54D64B3C" "COMPSHIPME1_"@"SEL$3" ("TAB3"."COL1"))',
     'NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")',
     'OUTLINE(@"SEL$2")',
     'OUTLINE(@"SEL$3")',
     'MERGE(@"SEL$2")',
     'OUTLINE(@"SEL$64EAE176")',
     'OUTLINE(@"SEL$4")',
     'OUTLINE_LEAF(@"SEL$1")',
     'MERGE(@"SEL$64EAE176")',
     'OUTLINE_LEAF(@"SEL$54D64B3C")',
     'ALL_ROWS',
     'OPT_PARAM(''_optimizer_extended_cursor_sharing_rel'' ''none'')',
     'DB_VERSION(''11.2.0.4'')',
     'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',
     'IGNORE_OPTIM_EMBEDDED_HINTS',
     'END_OUTLINE_DATA'
 );
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => 'select * from ( 
                   select /*+ index(tab1 I_TAB1_IDX1) */ this_.col1 as col1,
                          this_.col2 as col2, 
                          this_.col3 as col3, 
                          this_.col4 as col4
                   from TAB1 this_ 
                   inner join TAB2 compaddres3_ on this_.col2 = compaddres3_.col2 
                   inner join TAB3 compshipme1_ on this_.col1=compshipme1_.col1
                   where this_.col1 in (:1) 
                   and   this_.col2 in (:2 , :3) 
                   and   compshipme1_.col1 in (:4 )
                   and   this_.col8 = ''OPEN''
                   and   this_.col5>=:5 
                   and   this_.col5<=:6 
                   order by this_.col1 asc ) 
             where rownum <= :7',
 profile => ar_profile_hints,name => 'PROFILE_d7w7fruzwyh2s'
 );
end;
/

You can run above code on primary to create required profile.
Once you create profile, purge the SQL on ADG using sys.dbms_shared_pool.purge procedure.

When the SQL runs next time on ADG, it will automatically pick the above created profile and plan will be the one we tested before by including hints.

I was able to see following in the NOTE section when I did DBMS_XPLAN.DISPLAY_CURSOR on ADG

Note
-----
   - SQL profile PROFILE_d7w7fruzwyh2s used for this statement

Hope this helps !!

Reference

https://avdeo.com/2012/06/20/fixing-sql-plans-the-hard-way-part-1/

https://avdeo.com/2012/07/04/fixing-sql-plans-the-hard-way-part-2/

https://avdeo.com/2012/07/13/fixing-sql-plans-the-hard-way-part-3/

 

Library Cache: Mutex X – Bug 20879889 – Fixed in 11.2.0.4

I recently encountered a bug related to MView log causing very high library cache: mutex x wait events.

I will brief about the debugging steps I tried and fix for the same.

Few things to note before I proceed:-

  • We observed huge wait events for library cache: mutex X whenever we performed flip to standby or when DB was bounced. I am implying that library cache was cold and didn’t had required cursor information and object handles.
  • Load on the database was very high. This includes very high number of sessions connected to database and high number of DMLs performed on same table by many sessions.
  • Table on which DMLs are performed is having MLOG created on that based on primary key. This is required as these changes and data needs to flow to downstream databases via MView (fast refresh). So around 10 downstream sites were registered on this MLOG.

Root Cause Analysis:

After looking at the wait events, we immediately started digging into the root cause. We wanted to understand the bottleneck which is causing these wait events and remove whatever it takes to reduce these wait events.

I started with checking top 10 wait events in last 20 mins from v$active_session_history


SQL>select * from (select event, count(1) from v$active_session_history
2 where sample_time > (sysdate - 20/1440) group by event order by 2 desc) where rownum < 10;

EVENT                                    COUNT(1)
---------------------------------------- ----------
library cache: mutex X                        50943
checkpoint completed                          15170
read by other session                          5487
row cache lock                                 4205
log file sync                                  3137
flashback buf free by RVWR                     1815
db file sequential read                        1675
log file switch completion                     1611
cursor: pin S wait on X                        1516

9 rows selected.

Clearly library cache: mutex X was way higher.

We can check who is causing library cache: mutex X by checking P1 and P2 of that wait event


SQL>select event, p1, count(1) from v$active_session_history where sample_time > (sysdate - 20/1440) and event = 'library cache: mutex X' group by event, p1 order by 3;

EVENT                                    P1         COUNT(1)
---------------------------------------- ---------- ----------
library cache: mutex X                    421399181          1
library cache: mutex X                   3842104349          1
library cache: mutex X                   3477606468          1
library cache: mutex X                   2432877226          1
library cache: mutex X                    955484820          2
library cache: mutex X                        37005         25
library cache: mutex X                   1412465886        297
library cache: mutex X                   2417922189      50615

8 rows selected.

As you can see max wait events are caused by P1 = 2417922189

P1 is idn – can be used for finding the cursor related to mutex

For library cache: mutex X

  1. P1 represents library cache hash bucket number (if idn <= 131072)
  2. P1 represents hash value of the library cache object under protection (if idn > 131072)

In our case hash value was 2417922189. So it represents library cache object.

We can check if this hash value belongs to a cursor (SQL) using v$sql


SQL>select sql_id from v$sql where hash_value = 2417922189;

SQL_ID
-------------
3b7aa6f81x44d

SQL>@sql
Enter SQL_ID:- 3b7aa6f81x44d
old 3: where sql_id = '&SQL_ID'
new 3: where sql_id = '3b7aa6f81x44d'

INSERT /*+ IDX(0) */ INTO "BOOKER"."MLOG$_FULFILLMENT_DEMAND_I"
(dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$$,"WAREHOUSE
_ID","CO_WAREHOUSE_ITEM_ID") VALUES (:d,:o,to_date('4000-01-01:0
0:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1,:2)

As you can see this is DML on MLOG table. So clearly MLOG seems to be the bottleneck.

Immediate Action:

Wait events were making every thing stand still and nothing was getting processed. Since database availability was critical and we couldn’t afford to loose any more time because of wait events, our immediate action was to drop MLOG.

But since DB was having thousands of sessions and all stuck in library cache: mutex X, we cannot even get a lock to drop MLOG (not even with ddl_lock_timeout). Killing a session was not helping as they were keep coming back in no time.

So we decided to take quick bounce of DB (by keeping listeners down), dropped this MLOG and started listeners again.

This has fixed the issue and there were no wait events.

On down side, all downstream sites had to do complete refresh of MView followed by fast refresh as they got deregistered from MLOG. But it was OK as size of master table was not very big.

Patch Fix:

We also tried to investigate why an MLOG can cause this issue ? But were not able to get any insight.

MLOG is a table where primary key of changed records in main table will get inserted and when downstream table refreshes the data, these records will get deleted. So we don’t expect much higher size of MLOG. Unfortunately this is case it was 7.7GB (bigger than master table size).

Because the size of MLOG was too high, whenever a downstream database is doing fast refresh it has to update SNAPTIME$$ column in MLOG table which tells MLOG which rows are refreshed by downstream DB at what time. This update might be taking more time and holding enqueue locks. But inserts should not be blocked because of this update as it inserts a new record and doesn’t collied at enqueue level. Also, we were seeing these library cache wait events just after bounce or failover.

This is identified as a bug and Oracle has provided following patch as a fix for this issue

Patch 20879889: INSERT INTO MV LOG LEAVING TOO MANY OPEN CURSORS AFTER UPGR TO 11.2.0.4

Make sure you have this patch applied if you are using MLOG in your database.

Hope this helps !!

Reference:

https://support.oracle.com/epmos/faces/PatchDetail?requestId=19179589&_afrLoop=14055698364071&patchId=20879889&_afrWindowMode=0&_adf.ctrl-state=xqyu7jtft_77

 

 

Mutex: What do we know ?

In my previous article on Latches, I mentioned various things related to latches including different types and their behavior.

In this article I will describe similar things about mutexes.

Mutex are low level serialization / locking mechanism to protect memory structure inside library cache.

Why there was a change from Latches ? 

Mutex was introduced from 10.2 onwards and have been proved very efficient in managing library cache operations.

Mutex takes less memory then latches. Typically latch structure takes around 110 bytes on 32 bit system whereas mutex takes only 30 bytes. This variation is because of number of instructions it takes to acquire latch vs mutex. Latch takes around 150-200 instructions where as mutex takes 30-35 instructions. But the down side is that mutex gives less information about the waits and blockers compared to latches.

As we know the basics of library cache architecture, it consists of hash buckets and each bucket will contain linked list of library cache object handles. Whenever an access to library cache object happens, it is hashed to a bucket and corresponding latch should be obtained to traverse the chain in that bucket. Latch will be released when corresponding object is found or not found.

But there are only 67 library cache latches available to protect 131,072 buckets created by default in library cache. So single latch covers multiple buckets. This creates a false contention, meaning if 2 process are trying to access 2 different buckets protected by same latch, one of them have to wait until other completes traversing its bucket. So even though they are not colliding on same bucket they still will be blocked on each other because latch mechanism.

Further, if required object is found in library cache, process needs to pin that object while it is being used. Pin will basically protect object so that no other process can modify that object or that object will not be discarded from memory while you are accessing. When you are done, you need to take a latch again to unpin the object. These latches to pin and unpin needs memory allocation and deallocation to happen, which is expensive.

Compared to above process, mutex has many benefits

As against 67 latches to protect 131,072 buckets, Oracle introduced 131,072 mutexes to protect each bucket. So false contention is reduced drastically. False contention can still occur if 2 process want to access to 2 different objects which belongs to same bucket but its very rare.

After a mutex is obtained process will traverse the chain until it finds the required object. Once an object is found, a process needs to pin the object to access the same. But in case of mutex, there is no need for “latch: library cache pin”, instead mutex itself will act as a pin. Mutex acts as serialization mechanism to traverse a linked list as well as cursor pin structure. A mutex pin can be referenced by multiple sessions, providing that all sessions reference the mutex in shared mode only. The total number of sessions referencing a mutex in shared (S) mode is called reference count (ref count). The ref count for a mutex is stored in the mutex itself. Whenever a session wants to pin an object in shared pool, it increments this ref count of the mutex associated with that object. This is much faster and no need for any memory allocation or deallocation. Once process is done with that object, it will reduce the ref count. An object is not discarded from memory until ref count is zero.

Why not to increase number of latches ?

a. latches needs more memory and they are allocated upfront. As against Mutexes which are dynamic and are created when requested, latches are created when instance starts and its memory locations are externalized in view v$latch, v$latch_children

b. more latches you have, the more work you may have to do in some form of maintenance, reporting, or garbage collection. This will increase processing demand on server.

Now that we know why Oracle introduced mutex, lets move further in understanding more.

Shared, Exclusive and Examine mode:

Shared mode: Multiple sessions can access the memory structure protected by mutex in shared mode. Meaning every session has read access to structure protected by mutex. Every time a session access or acquire mutex in shared mode, ref count of mutex needs to be updated in mutex structure. Number of sessions accessing a mutex can be seen in lower bytes of P3 value of mutex wait event (cursor: pin S).

Exclusive mode: This mode is incompatible with all modes. Only 1 session can hold the lock in exclusive mode. In this mode, upper bytes of P3 value of mutex wait events represents the SID of holding session.

Examine mode: This mode indicates that mutex is in transition phase from shared mode to exclusive mode. During this time no other session can access or modify the mutex. In this mode, upper bytes of P3 value of mutex wait events represents the SID of holding session and lower bytes represents number of session holding mutex in shared mode.

Mutex Acquisition:

So how mutex acquisition works ?

We have seen in the past article on latches about how latch acquisition has worked. Mutex acquisition is similar to latch acquisition in that it tries for immediate gets and if its not able to acquire, it will spin followed by sleep (but sleep depends on oracle version).

Over past years with different releases of Oracle, mutex acquisition algorithm has changed drastically and I would say it got more stabilized with new releases. In Oracle 10.2, mutex was used for only pinning the object in library cache (only when _kks_use_mutex_pin=true) and there was no sleep.

Below algorithm applies to other mutexes which were introduced in 11g.

Oracle 10.2 – 11.1

Following is the mutex acquisition algorithm in 10.2 – 11.2

- Immediate gets mutex
      - spin gets mutex
            - yield()

Session will try to acquire mutex in immediate mode, if mutex is not available, it will spin. If still mutex is not available it will yield CPU, meaning, that process will be placed at the end of run queue but it will still be “ON CPU”. Because of this, CPU consumption was very high. Also because there was no sleep, wait interface was not able to record actual time waited for mutex acquisition. In AWR report, in top 5 timed events, we could see high number of waits, but total time waited used to be very low and CPU time always used to be close to DB time.

If CPU resources are not available OS will de-schedule such process (which is spinning and yielding CPU) and at that time that actual wait event for cursor: pin S gets recorded. But if a system has lot of CPU (like in many systems), that process will not even get de-scheduled, and Oracle thinks it is not waiting at all.

Therefore, in Oracle 10.2-11.1 the “cursor: pin S” wait time is the pure wait for CPU

For systems which do not have enough spare CPU, “cursor: pin S” used to be top wait events because it used to get de-scheduled from run queue and also whenever it used to spin and yield CPU consumption used to sky rocket. These are the systems where mutex issue was coming into light.

Because of these issue, Oracle released patch 6904068: High CPU usage when there are “cursor: pin S” waits.

This patch was released for 11.1 and later back ported to 10.2. With this patch, Oracle introduced underscore parameter – _first_spare_parameter. Default value of this parameter was 1 (centisec). This parameter provides a sleep time for mutex if its not acquired after first spin. So mutex behavior became similar to latch behavior but wait timeout of 10 ms was too high.

On better side, CPU consumption on system decreased and because of sleep time, this wait event (cursor: pin S) was shown up correctly on top 5 timed event (because total time waited was quiet accurate because of sleep). Parameter _first_spare_parameter was dynamic and if set to 0, mutex acquisition will behave without sleep (aggregated CPU usage).

But will all above discussion, question arises – if cursor: pin S is a shared mutex which can be acquired by multiple sessions concurrently why there would be blocking or wait events ?

Well couple of scenario when we can see this wait event

  1. If a mutex is in transition state “E” as mentioned above
  2. Everytime a session acquires mutex in shared mode, it has to update ref count of that mutex. That can cause contention.

What happened in 11.2 ?

Version 11.2.0.1

Behavior of mutex in 11.2.0.1 is same as 10.2. It was aggressive with no sleep. But it was visible as top event in-spite of no sleep calls.

This happened because Oracle counted wait time as time between first spin and successful mutex acquisition. So all the yield happening after the first spin was considered in waits. So total time waited was shown as high in AWR report and it was shown as one of the top timed events (even without sleep).

Version 11.2.0.2.2 PST

This version had a drastic change in behavior of mutex. Oracle came up with a exhaustive structure for controlling mutex called – mutex waits schemes.

Oracle introduced several underscore parameter to control behavior of mutex spin, yield and sleep.

Following underscore parameters were introduced starting this version

_mutex_wait_scheme – this parameter was introduced with 2 different values for 3 different type of mutex wait configurations.

0 - Always yield
1 - Always sleep for _mutex_wait_time
2 - Exponential back off with max sleep time of _mutex_wait_time

_mutex_spin_count – Number of times the process should spin. Default 255
_mutex_wait_time – amount of time process should sleep. Default 1ms

Along with this parameter, Oracle introduced following parameter to control yield and sleep of mutex

_wait_yield_mode – this defines if the process should yield first or sleep first. possible values are “yield” (default) or “sleep”

_wait_yield_hp_mode – defines high priority processes> default is SMON and VKTM

_wait_yield_sleep_time_msecs – defines how much a process should sleep before it yields again (in millisec). Default 1

_wait_yield_sleep_freq – number of yield cycles to do before it goes to sleep. default 100

_wait_yield_yield_freq – number of sleep cycles to do before it yields. default 20

_mutex_wait_scheme=0

I think in this scheme _mutex_wait_time is not applicable. Becuase sleep time depends on _wait_yield_sleep_freq. But I am not very sure.

With _mutex_wait_scheme=0 and default values for above _wait_yield* parameters above, mutex acquisition will work following way

- Immediate mutex gets
      - spin - _mutex_spin_count times
            - yield() - 100 times
                  - sleep - 20 times (20 cycles of 1 ms each)
            - yield() - 100 times
                  - sleep - 20 times (20 cycles of 1 ms each)

Using above underscore parameter we can vary the behaviour. Example if we change “_wait_yield_mode” to “sleep” instead of “yield”, oracle process will first sleep

- Immediate mutex gets
      - spin 
            - sleep - 20 times (20 cycles of 1 ms each)
      - yield() - 100 times
            - sleep - 20 times (20 cycles of 1 ms each)
      - yield() - 100 times

_mutex_wait_scheme=1

In this scheme _mutex_wait_time comes into picture. This is “always sleep” mode. So process goes to sleep after first spin, but wakes up after timeout of every _mutex_wait_time

So behavior will look like following

- Immediate mutex gets
      - Spin for _mutex_spin_count
            - sleep for _mutex_wait_time
            - sleep for _mutex_wait_time
            - sleep for _mutex_wait_time
            - sleep for _mutex_wait_time
            ...
            ...

_mutex_wait_scheme=2

In this scheme _mutex_wait_time defines the max time a process should wait. This is “exponential backoff” scheme. Wait time increases exponentially until it reaches a max value set by _mutex_wait_time.

Other than this behavior differs in initial spin and yield cycle. It spins and yield 2 times initially before the sleep begins

So behavior will look like following

- Immediate mutex gets
      - Spin for _mutex_spin_count - 2 times
            - yield() - 2 times
                  - sleep - with exponential increase of timeouts

Starting from 11.2.0.2.2, _mutex_wait_scheme=2 is the default behavior

Hope this helps !!

References:

http://blog.tanelpoder.com/2008/08/03/library-cache-latches-gone-in-oracle-11g/

http://oracleinaction.com/latche-lock-pin-mutex/

https://andreynikolaev.wordpress.com

Latches: What do we know ?

Latches are low level serialization mechanism which protects memory areas inside SGA. They are light wait and less sophesticated than enqueues and can be acquired and released very quickly.

Latch acquisition does not involve any complex algorithm and is based on test-and-set atomic instruction of a computer processor.

Latch Classification:

Latch can be classified in multiple ways:-

Shared latch and exclusive latch:

Shared latch is the one which can be shared by multiple processes/sessions.

Example if a session wants to read a block in memory and at the same time other session also wants to read the same block in memory, they can acquire shared latch. Example of shared latch is “latch: cache buffer chain”. This latch was exclusive in older version and Oracle changed this to shared latch from Oracle 9i onwards.

Exclusive latch is the one which is acquired when a session wants to make modification to block or memory area. Exclusive latch can be held by only 1 session/process at a time and is not compatible with any other latch.

Both shared latch and exclusive latch are not compatible with each other. Process holding a shared latch will block other process which needs exclusive latch but will allow other process which needs shared latch on same memory area. Similarly process holding exclusive latch will not allow any other process which needs either shared latch or exclusive latch.

Example of exclusive latch is “library cache latches” in previous version. These were taken in exclusive mode even for traversing a hash bucket. These latches are no more present in 11g and they are replaced by mutex.

As per Andrey Nikolaev 460 out of 551 latches are exclusive in Oracle 11.2.0.2. In each new version, Oracle tries to make latches more sharable in order to reduce contention.

Another classification of latches is immedaite latch and willing to wait latch

Immedaite latch and willing to wait latch:

Immediate latches are the one that session try to acquire immediately without waits. If the latch is not available, session will not wait and may get terminated or check for another latch. Example of immediate latch is redo copy latch. This is immediate latch because Oracle only wants to know if anyone else is currently copying redo data to log buffer, but not who exactly is copying and where, as this does not matter to LGWR.

Willing to wait latches are the one which will wait if the latch is not available. These latches have little complex behavior then immediate latches. Most of the latches in oracle are willing to wait latches

Why do we see wait events on shared latches ?

After reading about shared latches and exclusive latches, one question comes to mind regarding shared latches.

If “latch: cache buffer chain” latch is a shared latch (past 9i version) and they dont block other shared latches, why do we see “latch: cache buffer chain” latch wait events even in latest version of Oracle ?

The answer to this is explained by Andrey Nikolaev. Andrey has given a very practical example of why this happens. When multiple processes are trying to acquire shared latch (example CBC latch), we should not see any wait events or process blocking each other. The moment another session try to acquire exclusive lock on the resource (where other sessions were having shared latch), exclusive latches are given higher preference than shared latch. So session with exclusive latch will get access to resource and it will block all other sessions having shared latch. This will form a chain of sessions willing to acquire shared latch. Strange part of this algorithm is that even after exclusive latch has been released by the processing holding it, other processes cannot acquire shared latch concurrently. They still continue to be in latch queue and they get access to shared latch one by one. First process in queue will get shared latch and once its done with the access, it will release the latch and post the next process that latch is available. This is one of the major reason why we see wait events on shared latches.

Process flow for latch acquisition

Following is the process flow for acquiring a latch

 - Immediate latch gets 
       - spin latch gets
             - Add process to queue of latch waits
                   - Sleep until posted

The last step in process flow (sleep until posted) is a changed behavior. Initially until 9i, Oracle used to wake up periodically to check if latch has been freed or not and if latch is still not avaialble go back to sleep. So process flow in 9i used to be following

 - Immediate latch gets 
       - spin latch gets
             - Add process to queue of latch waits
                   - Sleep for fixed time and wake up
             - immediate latch gets
                   - Sleep for fixed time and wake up
             - immediate latch gets
                   - Sleep for fixed time and wake up

This behavior has been change from 10g onwards and holding process will wakeup the waiter process after the latch becomes free. This has been explain well by Tannel Podder. Also behavior is explained in more details by ALEX FATKULIN.

Some times, holding process was not able to wake up the process waiting for latch because of bugs or lower kernel version. So older version of Oracle used to have some default timeout available so that in case holding process miss the wakeup call, it will not wait indefinitely and will wakeup after timeout. Oracle has introduced a parameter _enable_reliable_latch_waits={true|false}, which alters this behavior. If this is set to true then no timeout is added and holding process continue to sleep until it gets posted by holding process. False represents the behavior otherwise.

Only latch which is exception to above process flow is “process allocation” latch. This latch does not depends on holding process to post when latch is free. It wakes up preriodically to acquire latch.

Latch Behavior

Oracle has introduced few parameters to control behavior of latch. We will discuss brief about those parameters and how they affect shared latch and exclusive latches

When process try to acquire latch, it attempts to acquire latch in immediate mode. If latch is not available in immediate mode, it will spin for defined number of times and try again. If still latch is not free, process will go to sleep.

Process Spin:

Spinning is a process of consuming/burning CPU so that process will stay “ON CPU” and not to get descheduled from CPU cycles. During spinning, process will burn CPU for few micro seconds with the hope that after passing that much time, latch will be available for it to acquire. This does increase CPU consumption, but it saves time as it may avoid the needing for the process to sleep (which is expensive as it involves context switches).

Number of times a process spins to acquire latch depends on type of latch.

Exclusive latch:

For exclusive latch, this “can be” controlled by _spin_count but needs database bounce. I said “can be” because exclusive latch spins ar actually decided by “spin” column in x$ksllclass table.


SQL>select indx,spin from x$ksllclass;

INDX SPIN
---------- ----------
0 20000
1 20000
2 20000
3 20000
4 20000
5 20000
6 20000
7 20000

8 rows selected.

There are 8 classes of latches (as indicated by indx column) that we will discuss later in this article. By default all latches belongs to class 0.
If we want to change spin count for exclusive latches, we need to change value of SPIN column for class 0. This can be done by changing _spin_count and bouncing the instance (but that will change spin count for all classes), or by setting _latch_class_0 parameter (which will change spin count for only class 0). We have similar parameter to change spin count for other classes (_latch_class_[0-7]).

So changing _spin_count is not a good idea. Instead we can move a specific latch for which we want to change the spin count to another class (1-7) and change specific underscore parameter (_latch_class_[1-7]).

By details _spin_count parameter is not applicable to exclusive latches as default value of _spin_count is 2000 where as exclusive latch spin for 20000 times as mentioned in above table x$ksllclass.
But changing _spin_count will make it applicable for exclusive latch as well.

Shared Latch:

For shared latch, number of times process spins is _spin_count * 2. This has been proved by Andrey Nikolaev. Also, _spin_count parameter is applicable to shared latches by default. So since default value of _spin_count is 2000, shared latches spins 4000 times (2000 * 2).

Diagnosing latch contention:

I am not going to mention much here because Tannel Podder has already written great script – latchprof.sql and latchprofx.sql which can be used to analyze latch wait events.

Tannel has also written great article on how to diagnose latch wait events – http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

In next article, I will try to cover mutex.

Hope this helps !!

Reference:

https://andreynikolaev.wordpress.com

http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

Affect of object Statistics on SQL Execution statistics

This is a small article to demonstrate why correct statistics are important and how they affect execution statistics of same plan.
In the past we learned that changing table statistics or index statistics (or rebuilding index) can causes plan for a SQL to change. Because when statistics changes, optimizer will try to generate new plan based on changed statistics.
With 11g, oracle provided baseline to ensure stability in SQL plans. So if you have single baseline enabled for a SQL, you essentially have single plan for that SQL and that plan will not change unless auto tuning job evolve another plan or you manually evolves another plan.

Does it mean that object statistics has no role to play if your plan is fixed ?

Lets have a quick demo. In my below exercise, I will not be changing the plan (as I am using baseline). But we will see that execution statistics such as buffer_gets and disk reads changes as you change object statistics.

I faced this issue on one of our production database where stats were completely inaccurate and when we gathered stats on tables and indexes, execution stats for SQLs changed with same plan.

Setup

I am performing these tests on 11.2.0.4.
Lets create 2 tables T1 and T2. I will use one query on single table to go for FTS and other query joining T1 and T2 and use index.


SQL>create table T1 as select * from dba_tables;

Table created.

SQL>insert into T1 select * from T1;

2804 rows created.

SQL>insert into T1 select * from T1;

5608 rows created.
...
...

SQL>commit;

Commit complete.

SQL>--create index on this table on TABLE_NAME column and gather stats

SQL>create index I_T1_TABLE_NAME on T1(TABLE_NAME);

Index created.

SQL>exec dbms_stats.gather_table_stats(null,'T1');

PL/SQL procedure successfully completed.

Create 2nd table from some other view, lets say dba_tab_statistics


DEO>create table T2 as select * from dba_tab_statistics;

Table created.

DEO>insert into T2 select * from T2;

16289 rows created.

...
...

DEO>commit;

Commit complete.

DEO>--create index on TABLE_NAME column in T2 table

DEO>create index I_T2_TABLE_NAME on T2(TABLE_NAME);

Index created.

DEO>exec dbms_stats.gather_table_stats(null,'T2');

PL/SQL procedure successfully completed.

Following are the table and index level stats values for T1 and T2 and corresponding indexes


DEO>select table_name, num_rows, blocks, avg_row_len from dba_tables where table_name in ('T1','T2');

TABLE_NAME			           NUM_ROWS   BLOCKS     AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T1				               34783	  3214	     247
T2				               298096	  4351	     99

DEO>select INDEX_NAME, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, NUM_ROWS from dba_indexes where table_name in ('T1','T2');

INDEX_NAME		               LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
------------------------------ ----------- ------------- ----------------- ----------
I_T1_TABLE_NAME 		       360	       1414	         34675	           34783
I_T2_TABLE_NAME 		       1813	       2364	         74610             298096

So when object statistics are accurate and current, I see following execution statistics for following 2 queries

Query 1:

select a.table_name, sum(b.num_rows) from T1 a, T2 b where a.table_name= b.table_name and a.owner = b.owner and a.table_name = :b1 group by a.table_name;


DEO>var b1 varchar2(40);
DEO>exec :b1 := 'ABC';

PL/SQL procedure successfully completed.

DEO>select a.table_name, sum(b.num_rows) from T1 a, T2 b where a.table_name= b.table_name and a.owner = b.owner and a.table_name = :b1 group by a.table_name;

TABLE_NAME		               SUM(B.NUM_ROWS)
------------------------------ ---------------
ABC				               240678640


Execution Plan
----------------------------------------------------------
Plan hash value: 1483037242

--------------------------------------------------------------------------------------------------------
| Id  | Operation			                 | Name 	       | Rows  | Bytes | Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		             |		           |	 1 |	61 |	 4  (50)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT		         |		           |	 1 |	61 |	 4  (50)| 00:00:01 |
|   2 |   MERGE JOIN			             |		           |	47 |  2867 |	 4  (50)| 00:00:01 |
|   3 |    SORT JOIN			             |		           |	11 |   396 |	 2  (50)| 00:00:01 |
|   4 |     VIEW			                 | VW_GBC_5	       |	11 |   396 |	 2  (50)| 00:00:01 |
|   5 |      HASH GROUP BY		             |		           |	11 |   297 |	 2  (50)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID    | T2		       |   126 |  3402 |	 1   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN 	         | I_T2_TABLE_NAME |   126 |	   |	 1   (0)| 00:00:01 |
|*  8 |    SORT JOIN			             |		           |	25 |   625 |	 2  (50)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID      | T1		       |	25 |   625 |	 1   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN		         | I_T1_TABLE_NAME |	25 |	   |	 1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("B"."TABLE_NAME"=:B1)
   8 - access("A"."TABLE_NAME"="ITEM_2" AND "A"."OWNER"="ITEM_1")
       filter("A"."OWNER"="ITEM_1" AND "A"."TABLE_NAME"="ITEM_2")
  10 - access("A"."TABLE_NAME"=:B1)

Note
-----
   - SQL plan baseline "SQL_PLAN_gt8npsxnncgm2abc84fa9" used for this statement


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	157  consistent gets
	  3  physical reads
	  0  redo size
	598  bytes sent via SQL*Net to client
	453  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

Above query has done 157 consistent gets and 3 physical reads.

Query 2:

select count(1) from T2 where owner = :b2;


DEO>var b2 varchar2(40);
DEO>exec :b2 := 'SYS';

PL/SQL procedure successfully completed.

DEO>select count(1) from T2 where owner = :b2;

  COUNT(1)
----------
    251088


Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation	       | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1     |	5     |   625   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE    |	  |	1     |	5     |	           |	      |
|*  2 |   TABLE ACCESS FULL| T2   | 19873 | 99365 |   625   (2)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"=:B2)

Note
-----
   - SQL plan baseline "SQL_PLAN_9wq67xmrafzda1c6cf506" used for this statement


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
   4350  consistent gets
	  0  physical reads
	  0  redo size
	517  bytes sent via SQL*Net to client
	453  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

Above query has done 4350 consistent gets and no physical reads.

Lets fake statistics to random high value and see the affect. Note that both queries above are using baseline, so plan will not change with further executions even after changing object stats

I am going to set table stats and index stats as follows


DEO>exec dbms_stats.SET_TABLE_STATS(null,'T1',NUMROWS=>3221020,NUMBLKS=>202590, AVGRLEN=>150)

PL/SQL procedure successfully completed.

DEO>exec dbms_stats.SET_INDEX_STATS(null,'I_T1_TABLE_NAME',NUMROWS=>3153430,NUMLBLKS=>124232,NUMDIST=>6,AVGLBLK=>2990,AVGDBLK=>19002,CLSTFCT=>76010)

PL/SQL procedure successfully completed.

DEO>exec dbms_stats.SET_TABLE_STATS(null,'T2',NUMROWS=>140000000,NUMBLKS=>13540202, AVGRLEN=>120)

PL/SQL procedure successfully completed.

DEO>exec dbms_stats.SET_INDEX_STATS(null,'I_T2_TABLE_NAME',NUMROWS=>13345304,NUMLBLKS=>1242022,NUMDIST=>12,AVGLBLK=>324,AVGDBLK=>1342,CLSTFCT=>260123)

PL/SQL procedure successfully completed.

DEO>select table_name, num_rows, blocks, avg_row_len from dba_tables where table_name in ('T1','T2');

TABLE_NAME			           NUM_ROWS   BLOCKS     AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
T1				               3221020    202590	 150
T2				               140000000  13540202	 120

DEO>select INDEX_NAME, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, NUM_ROWS from dba_indexes where table_name in ('T1','T2');

INDEX_NAME		               LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
------------------------------ ----------- ------------- ----------------- ----------
I_T1_TABLE_NAME 		       124232	   6	         76010             3153430
I_T2_TABLE_NAME 		       1242022	   12	         260123            13345304


I will purge those queries from shared_pool and run those same queries again


DEO>!cat purgesql.sql
accept sql_id prompt 'Enter SQL_ID:- '
DECLARE
 name varchar2(50);
BEGIN
 select distinct address||','||hash_value into name
 from v$sqlarea
 where sql_id like '&sql_id';

 sys.dbms_shared_pool.purge(name,'C',65);

END;
/

DEO>@purgesql
Enter SQL_ID:- 46j56265bmw7u

PL/SQL procedure successfully completed.

DEO>@purgesql
Enter SQL_ID:- gkbtfpmvxw4hn

PL/SQL procedure successfully completed.

Lets run the queries again after changing the stats

Query 1:

select a.table_name, sum(b.num_rows) from T1 a, T2 b where a.table_name= b.table_name and a.owner = b.owner and a.table_name = :b1 group by a.table_name;


DEO>set autotrace on
DEO>select a.table_name, sum(b.num_rows) from T1 a, T2 b where a.table_name= b.table_name and a.owner = b.owner and a.table_name = :b1 group by a.table_name;

TABLE_NAME		               SUM(B.NUM_ROWS)
------------------------------ ---------------
ABC				               240678640


Execution Plan
----------------------------------------------------------
Plan hash value: 1483037242

----------------------------------------------------------------------------------------------------
| Id  | Operation			              | Name 	        | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		          |		            |	 1  |	61  |	74   (9)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT		      |		            |	 1  |	61  |	74   (9)| 00:00:01 |
|   2 |   MERGE JOIN			          |		            |  4464 |   265K|	74   (9)| 00:00:01 |
|   3 |    SORT JOIN			          |		            |	11  |   396 |	71   (6)| 00:00:01 |
|   4 |     VIEW			              | VW_GBC_5	    |	11  |   396 |	71   (6)| 00:00:01 |
|   5 |      HASH GROUP BY		          |		            |	11  |   297 |	71   (6)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID | T2		        | 59222 |  1561K|	67   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN 	      | I_T2_TABLE_NAME | 59222 |	    |	55   (0)| 00:00:01 |
|*  8 |    SORT JOIN			          |		            |  2278 | 56950 |	 3  (67)| 00:00:01 |
|   9 |     TABLE ACCESS BY INDEX ROWID   | T1		        |  2278 | 56950 |	 1   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN		      | I_T1_TABLE_NAME |  2278 |	    |	 1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("B"."TABLE_NAME"=:B1)
   8 - access("A"."TABLE_NAME"="ITEM_2" AND "A"."OWNER"="ITEM_1")
       filter("A"."OWNER"="ITEM_1" AND "A"."TABLE_NAME"="ITEM_2")
  10 - access("A"."TABLE_NAME"=:B1)

Note
-----
   - SQL plan baseline "SQL_PLAN_gt8npsxnncgm2abc84fa9" used for this statement


Statistics
----------------------------------------------------------
	 26  recursive calls
	 62  db block gets
	231  consistent gets
	  4  physical reads
  14528  redo size
	598  bytes sent via SQL*Net to client
	453  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

Note that plan didn’t change, but we see that consistent gets (buffer_gets/exec) has increased from previous value of 157 to 231.
Physical reads are more or less same (just increase of 1).

Lets check 2nd query doing FTS

Query 2:

select count(1) from T2 where owner = :b2;


DEO>set  autotrace on
DEO>select count(1) from T2 where owner = :b2;

  COUNT(1)
----------
      9600

Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation	       | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1     |	5     |  1926K  (1)| 02:10:54 |
|   1 |  SORT AGGREGATE    |	  |	1     |	5     |	           |	      |
|*  2 |   TABLE ACCESS FULL| T2   |  9333K|    44M|  1926K  (1)| 02:10:54 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"=:B2)

Note
-----
   - SQL plan baseline "SQL_PLAN_9wq67xmrafzda1c6cf506" used for this statement


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
   8195  consistent gets
   4342  physical reads
	  0  redo size
	515  bytes sent via SQL*Net to client
	453  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed


Huge increase in consistent gets compared to previous run. Previous run showed consistent gets as 4350 when stats were accurate. With increased stats, consistent gets became 8195
Also, there was no disk reads previously may be because all blocks were in buffer. But with changed object level stats, we are seeing 4342 disk reads.

So always make sure you have latest accurate statistics available for your tables and indexes in your database. You may have right plans, but having bad object stats can cause Oracle to work more.

Hope this helps !!

Tracing Single SQL in Oracle

Many times, while doing SQL tuning, we want to trace (event 10046) single SQL in database. Instead of going for module level tracing or session level using DBMS_MONITOR, we can simply use below alter system command to trace specific SQL

Example: I have a table T1 and index T_I_TABLE_NAME on that table.
I am running following SQL and I want to trace on this SQL

select * from T1 where table_name = 'SINGLE_PRODUCT_GROUPS';

I can just find out the SQL ID of above SQL

select sql_id, sql_text from v$sql where sql_text like '%SINGLE_PRODUCT_GROUPS%'

SQL_ID	      SQL_TEXT
------------- --------------------------------------------------------------------------------
8kybysnu4nn34 select * from T1 where table_name = 'SINGLE_PRODUCT_GROUPS'

Once I have the SQL ID, I can use below alter system to trace this SQL


alter system set events 'sql_trace[SQL:8kybysnu4nn34] plan_stat=all_executions,wait=true,bind=true';

Note that even though this is alter system, other SQL IDs run will not have any effect on tracing because we are tracing only specific SQL ID. So unless SQL ID 8kybysnu4nn34 is run, it will not generate any trace file.

Once SQL ID is run and trace is generated, you can turn off tracing using following statement


alter system set events 'sql_trace[SQL:8kybysnu4nn34] off';

This might generate multiple trace files as multiple sessions might run same SQL ID (depending on the application).

Hope this helps !!!