Sessions and Processes Parameters – Oracle 11g

Oracle has changed the way it derives sessions and processes parameters in database.

In 10g, oracle used to derive sessions parameter from processes parameter using following formula

(1.1 * PROCESSES) + 5

In 11g R1 onwards it changed to

(1.5 * PROCESSES) + 22

It has another rule though:

If we set lower value of sessions parameters than derived value, Oracle will automatically bump it to above derived value.
If we set higher value of sessions parameters than derived value, Oracle will consider our set value

So it always takes which ever is higher

SQL>select name, value from v$spparameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               5000

SQL>select name, value from v$parameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               6776

We can see derived value (4500*1.5)+22=6776 is greater than set value of 5000 in spfile. So its taking derived value for this parameter

Lets change the value in spfile to 7000

SQL> alter system set sessions = 7000 scope=spfile;

System altered.

<< Bounce DB >>
SQL>select name, value from v$spparameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               7000

SQL>select name, value from v$parameter where name in ('sessions','processes');

NAME                   VALUE
------------------------------ ----------
processes               4500
sessions               7000

Now you can see its taking the set value, because set value of more than derived value of 6776.

Hope this helps !!

References:

Sessions parameter in 10.2 – http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams230.htm#REFRN10197

Sessions parameter in 11.1 – http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams191.htm#i1133629

Sessions parameter in 11.2 – http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams230.htm#i1133629

Auto-Capture Baseline Behaviour

This article is about demonstrating Auto-Capture baseline behavior, which I was surprised that many people are not aware.

Auto-Capture of baseline behaves in following way

Case 1) optimizer_capture_sql_plan_baselines parameter is TRUE

If you have optimizer_capture_sql_plan_baselines parameter set to TRUE, baselines for the SQLs will get captured automatically if SQL query runs 2 or more times. I believe all of us know about this feature

Case 2) optimizer_capture_sql_plan_baselines parameter is FALSE

If optimizer_capture_sql_plan_baselines parameter is set to FALSE, many people think that no new baseline will be captured in the database. This is WRONG. Even when this parameter is set to FALSE, baselines will be captured for following case

*** If a query has one or more baselines with ENABLED=YES ***

Lets take an example

SQL>create table T as select * from dba_objects;

Table created.

SQL>explain plan for 
  2  select count(1) from T where data_object_id between 1000 and 2000;

Explained.

SQL>@display

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2966233522

-----------------------------------
| Id  | Operation       | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|*  2 |   TABLE ACCESS FULL| T      |
-----------------------------------

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

   2 - filter("DATA_OBJECT_ID"<=2000 AND "DATA_OBJECT_ID">=1000)

Note
-----
   - rule based optimizer used (consider using cbo)

18 rows selected.

SQL>select count(1) from T where data_object_id between 1000 and 2000;

  COUNT(1)
----------
     0

SQL>/

  COUNT(1)
----------
     0
         
SQL>select sql_id from v$sql where sql_text like 'select count(1) from T where data_object_id between 1000 and%';

SQL_ID
-------------
7fbxxz894w0mg

SQL>@baseline
Enter sql ID:- 7fbxxz894w0mg

SQL_TEXT
----------------------------------------------------------------------------------------------------
select count(1) from T where data_object_id between 1000 and 2000


SQL_HANDLE               PLAN_NAME              CREATOR          ORIGIN         LAST_MODIFIED            ENA ACC FIX
------------------------------ ------------------------------ --------------- -------------- ------------------------------ --- --- ---
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc3315573fdbb376  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.30.08.000000 AM   YES YES NO

SQL_HANDLE            PLAN_NAME                  PLAN_HASH_VALUE     ENABLED    ACCEPTED     FIXED
-------------------------   ----------------------------      --------------     -------    -------     -------
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc3315573fdbb376     2966233522      YES         YES       NO

PL/SQL procedure successfully completed.

SQL>show parameters  capture

optimizer_capture_sql_plan_baselines    boolean         TRUE

SQL>alter system set optimizer_capture_sql_plan_baselines=false;

System altered.

SQL>create index T_IDX on T(data_object_id);

Index created.

SQL>select count(1) from T where data_object_id between 1000 and 2000;

  COUNT(1)
----------
     0

SQL>/

  COUNT(1)
----------
     0

         
SQL>@baseline
Enter sql ID:- 7fbxxz894w0mg

SQL_TEXT
----------------------------------------------------------------------------------------------------
select count(1) from T where data_object_id between 1000 and 2000


SQL_HANDLE               PLAN_NAME              CREATOR          ORIGIN         LAST_MODIFIED            ENA ACC FIX
------------------------------ ------------------------------ --------------- -------------- ------------------------------ --- --- ---
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc3315573fdbb376  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.30.08.000000 AM   YES YES NO
SYS_SQL_46f1e9bfbc331557       SYS_SQL_PLAN_bc331557ded8ae2f  ORACLE_DBA      AUTO-CAPTURE   26-JUN-12 11.31.20.000000 AM   YES NO  NO

SQL_HANDLE            PLAN_NAME                  PLAN_HASH_VALUE     ENABLED    ACCEPTED     FIXED
-------------------------   ----------------------------      --------------     -------    -------     -------
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc3315573fdbb376     2966233522      YES         YES       NO
SYS_SQL_46f1e9bfbc331557    SYS_SQL_PLAN_bc331557ded8ae2f     293504097      YES        NO         NO

PL/SQL procedure successfully completed.

SQL>

This proves that even when optimizer_capture_sql_plan_baselines parameter is set to false we can have baselines captured for our old SQL.

If we think about this feature, this is perfect.

Imagine you are rolling out a new module and new queries are going to run against new tables.

At first, initially you will not be having enough data in new tables and your queries will go with FTS (Full Table Scan) and will not use index, even if you create them. This is because based on value of optimizer_index_cost_adj parameter optimizer might make a decision to get data from table rather than going to index and than table.

If you have optimizer_capture_sql_plan_baselines set to TRUE, this is going to create baselines on those queries (which will be automatically accepted as first baselines are automatically accepted).

At later point when data grows and you really want the indexes to get used, your query will still be doing FTS (because of the auto-capture baseline in ACCEPTED state). So even if new baselines are getting captured, they are not getting used.

So you have to manually (or automatically) evolve those baselines to correct the plans.

Instead, my approach would be to enable optimizer_capture_sql_plan_baselines parameter when your database is really baked and you have enough data available. This way it will create baselines which will be first time right.

If your database is still not baked enough and you are looking for plan stability, I would suggest getting baselines from other similar prod database which is baked for long time.

Once you have baselines in place for almost all queries turn off optimizer_capture_sql_plan_baselines parameter. This will prevent baseline capture for new queries, at the same time this doesn’t stop new baselines from getting captured for old SQLs.

Hope this helps !!

Fixing SQL Plans: The hard way – Part 1

This article is about fixing a plan for SQL statement in a hard way.

Till now we have seen that profiles can be created using DBMS_SQLTUNE package where

  1. we create a SQL tuning set
  2. Pack sql_ids into SQL tuning sets
  3. we create tuning task and provide SQL tuning set as inputs
  4. Finally after running tuning task we accept the profiles created.

Profiles consists of hints that a SQL can use it to arrive at a plan.

We can use same DBMS_SQLTUNE package and IMPORT_SQL_PROFILE procedure to import the required hints and apply to the SQL where we have to fix the plan.

Where are these hints stored ?

If you are taking about a sql, check V$SQL_PLAN, you will see a column OTHER_XML (CLOB). This has all the hints stored in XML form.

So logically if we have a good plan in any other database, we can just get the hints from that plan and use  it in our database so that same plan comes in our database (provided we have all the objects used by that plan exists in our database).

This scenario happens many times. We have several similar production databases and sometimes plan is good in some DBs where as in few DBs plan is entirely different (may be because of different stats or different environment or some parameter difference).

Making changes to stats or parameters or DB environment has a very big impact as it affects other SQLs. So if you have 1 or 2 SQLs which are having bad plans, its better to gett a better plan from some other DB and just apply to the affected DBs.

Here is another way to create a profile.

If we have a better plan in some other DB, we need to parse the OTHER_XML column for the SQL and extract the hints from that

This can be done using following SQL

select  CHR(9)||''''
        ||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
        || ''','
from    xmltable('/*/outline_data/hint'
                passing (select     xmltype(other_xml) as xmlval
                        from        v$sql_plan
                        where       sql_id = '&GOOD_SQL_ID'
                        and         CHILD_NUMBER = &CHILD_NO
                        and         other_xml is not null)) d;

In my case sql_id = 33fndgzsas09k and child_no = 0

I got following output

            'IGNORE_OPTIM_EMBEDDED_HINTS',
            'OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')',
            'DB_VERSION(''11.2.0.2'')',
            'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
            'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
            'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
            'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
            'OPT_PARAM(''optimizer_index_caching'' 80)',
            'OUTLINE_LEAF(@"SEL$1")',
            'INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
            'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
            'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
            'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
            'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
            'USE_NL(@"SEL$1" "SR"@"SEL$1")',
            'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',

Above are the hints that used in the SQL. Note that these are not the sql hints that user supply. CBO internally creates a tree of hints (which we call a plan) and uses them to get the required data.

How do we make use of these hints ?

We will define a variable of type sys.sqlprof_attr (This is a VARRAY defined already in database) and put all the hints into this VARRAY

Once we get this VARRAY with all hints, we can apply the same to all SQLs that matches our criteria

Following PLSQL code will do that

declare
	ar_profile_hints sys.sqlprof_attr;
begin
	ar_profile_hints := sys.sqlprof_attr(
	'BEGIN_OUTLINE_DATA',
        'IGNORE_OPTIM_EMBEDDED_HINTS',
        'OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')',
        'DB_VERSION(''11.2.0.2'')',
        'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
        'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
        'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
        'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
        'OPT_PARAM(''optimizer_index_caching'' 80)',
        'OUTLINE_LEAF(@"SEL$1")',
        'INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
        'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
        'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
        'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
        'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
        'USE_NL(@"SEL$1" "SR"@"SEL$1")',
        'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',
        'END_OUTLINE_DATA');
	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;
end;
/

You can run above PLSQL code in a database where plan is bad and it should create a profile for that SQL. Once profile is created you can purge that SQL from shared_pool so that existing plan will be wiped off and run the SQL again.

This will create a new plan (good plan) same as the one we copied from other DB.

Above PLSQL code can generated directly using following SQL

Run the following SQL in database where plan is good and provide required inputs. This will output PLSQL code same as above (with all values in place). You can just copy and paste that PLSQL code on affected DB where plan is bad.

*** NOTE :- You need to run below SQL code in DB where plan is good.

accept HINTED_SQL_ID prompt 'Enter good SQL ID:- '
accept CHILD_NO prompt 'Enter child number of good SQL:- '
accept BAD_SQL_ID prompt 'Enter bad SQL ID to be fixed:- '
accept PLAN_HASH_VALUE prompt 'Enter bad SQL plan_hash_value:- '
set pagesize 0
set line 9999
set verify off;
set heading off;
set feedback off;
set echo off;
set pagesize 0
prompt '======================= OUTPUT ======================='
select CHR(10) from dual;
select  'declare'
        ||CHR(10)||CHR(9)
        ||'ar_profile_hints sys.sqlprof_attr;'
        ||CHR(10)
        ||'begin'||CHR(10)||CHR(9)
        ||'ar_profile_hints := sys.sqlprof_attr('||CHR(10)||CHR(9)
        ||'''BEGIN_OUTLINE_DATA'','
from    dual;
select  CHR(9)||''''
        ||regexp_replace(extractvalue(value(d),'/hint'),'''','''''')
        || ''','
from    xmltable('/*/outline_data/hint'
                passing (select     xmltype(other_xml) as xmlval
                        from        v$sql_plan
                        where       sql_id = '33fndgzsas09k'
                        and         CHILD_NUMBER = 0
                        and         other_xml is not null)) d;
select  CHR(9)
        ||'''END_OUTLINE_DATA'');'||CHR(10)||CHR(9)
        ||'for sql_rec in ('||CHR(10)||CHR(9)
        ||'select t.sql_id, t.sql_text'||CHR(10)||CHR(9)
        ||'from dba_hist_sqltext t, dba_hist_sql_plan p'||CHR(10)||CHR(9)
        ||'where t.sql_id = p.sql_id'||CHR(10)||CHR(9)
        ||'and p.sql_id = '''||'&BAD_SQL_ID'||'''' ||CHR(10)||CHR(9)
        ||'and p.plan_hash_value = '||&PLAN_HASH_VALUE ||CHR(10)||CHR(9)
        ||'and p.parent_id is null'||CHR(10)||') loop' ||CHR(10)
        ||'DBMS_SQLTUNE.IMPORT_SQL_PROFILE(' ||CHR(10)||CHR(9)
        ||'sql_text    => sql_rec.sql_text,'||CHR(10)||CHR(9)
        ||'profile     => ar_profile_hints,' ||CHR(10)||CHR(9)
        ||'name        => ''PROFILE_'||'&BAD_SQL_ID'||'''); '||CHR(10)||CHR(9)
        || 'end loop;'||CHR(10)|| 'end;'|| CHR(10)||'/'
from    dual;
select CHR(10) from dual;
prompt '======================= OUTPUT ======================='

Enter good SQL ID:- <This should be the SQL_ID with good plan. In our case its the same SQL ID but in different DB>

Enter child number of hinted SQL:- <This is the child number of the SQL in DB where plan is good>

Enter bad SQL ID to be fixed:- <This is same SQL ID as input 1 since we are checking plan for same SQL ID in other DB>

Enter bad SQL plan_hash_value:- <This is the plan hash value of SQL ID where plan is bad>

Example Run:

SQL>@create_profile_code.sql
Enter good SQL ID:- 33fndgzsas09k
Enter child number of good SQL:- 0
Enter bad SQL ID to be fixed:- 33fndgzsas09k
Enter bad SQL plan_hash_value:- 3225275398
'======================= OUTPUT ======================='
declare
	ar_profile_hints sys.sqlprof_attr;
begin
	ar_profile_hints := sys.sqlprof_attr(
	'BEGIN_OUTLINE_DATA',

        'IGNORE_OPTIM_EMBEDDED_HINTS',
        'OPTIMIZER_FEATURES_ENABLE(''11.2.0.2'')',
        'DB_VERSION(''11.2.0.2'')',
        'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
        'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
        'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
        'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
        'OPT_PARAM(''optimizer_index_caching'' 80)',
        'OUTLINE_LEAF(@"SEL$1")',
        'INDEX_RS_ASC(@"SEL$1" "BI"@"SEL$1" ("TEST_ITEMS"."ISBN" "TEST_ITEMS"."TEST_ORDER_ID" "TEST_ITEMS"."OWNER"))',
        'INDEX_RS_ASC(@"SEL$1" "PCS"@"SEL$1" ("TEST_CUSTS"."TEST_WORK_ID"))',
        'INDEX(@"SEL$1" "SR"@"SEL$1" ("CUST_REQUESTS"."TEST_WORK_ID"))',
        'LEADING(@"SEL$1" "BI"@"SEL$1" "PCS"@"SEL$1" "SR"@"SEL$1")',
        'USE_NL(@"SEL$1" "PCS"@"SEL$1")',
        'USE_NL(@"SEL$1" "SR"@"SEL$1")',
        'NLJ_BATCHING(@"SEL$1" "SR"@"SEL$1")',
	'END_OUTLINE_DATA');
	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 = '33fndgzsas09k'
	and p.plan_hash_value = 3225275398
	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;
end;
/

'======================= OUTPUT ======================='

Hope this helps !!