OTNYathra 2015

The Oracle ACE directors and Java champions will be organizing an evangelist event called ‘OTNYathra 2015’  during February 2015. during which a series of 7 conferences will be held across 7 major cities of India  in a time period of 2 weeks.  This event will bring the Oracle community together, spread the knowledge and increase the networking opportunities in the region. The detailed information about the event can be viewed at http://www.otnyathra.com.

I will be presenting a session on Adaptive Query Optimization on 13th Feb 2015 at FMDI, Sector 17B, IFFCO Chowk , Gurgaon.

Thanks to Sir Murali Vallath  and his team for organizing it and giving me an opportunity to present.

Hope to see you there!!

 

Map OS Groups To Administrative Privileges After Installation

During installing database software, user is prompted to enter names of various operating system groups mapping to various administrative privileges (SYSDBA, SYSOPER, SYSBACKUP, SYSKM, SYSDG). One might map one operating system group to multiple administrative privileges if role separation is not desired.  In case the need for role separation arises later, the mapping can be specified by updating  $ORACLE_HOME/rdbms/lib/config.c file and then relinking it. This post explains the various steps.

While installing database 12.1.0.2 software on linux, I had not  created OS groups corresponding to administrative privileges SYSBACKUP, SYSKM, SYSDG.  Now I want OS groups dgdba, backupdba and kmdba to map to SYSDG, SYSBACKUP and SYSKM administrative privileges respectively.

-- Check that groups dgdba, backupdba and kmdba do not exist

[root@host01 etc]# cat /etc/group | grep dba
dba:x:501:oracle

– Create groups dgdba, backupdba and kmdba

#groupadd -g 54321 dgdba
groupadd -g 54322 backupdba
groupadd -g 54323 kmdba

– Check that groups dgdba, backupdba and kmdba have been created

[root@host01 etc]# cat /etc/group | grep dba
dba:x:501:oracle
dgdba:x:54321:
 backupdba:x:54322:
 kmdba:x:54323:

– Create a user test which is a member of dgdba group

[root@host01 /]# useradd test -g oinstall -G dgdba

[root@host01 /]# passwd test
Changing password for user test.
New UNIX password:

– Login as test user

[root@host01 /]# su - test

[test@host01 ~]$ . oraenv
ORACLE_SID = [test] ? orcl

– As test user try to connect as sysdg – fails as dgdba group
has not been mapped to SYSDG administrative privilege

[test@host01 ~]$ dgmgrl

DGMGRL> connect sysdg/xx
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

– Verify in configuration file that currently OS group dba corresponds to administrative priviliges SYSDBA, SYSKM, SYSDG and SYSBACKUP

[oracle@host01 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c |grep define
/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
#define SS_ASM_GRP ""
#define SS_BKP_GRP "dba"
#define SS_DGD_GRP "dba"
#define SS_KMT_GRP "dba"

– Edit configuration file so that OS groups dgdba, backupdba and kmdba to map to SYSDG, SYSBACKUP and SYSKM administrative privileges respectively.

[oracle@host01 ~]$ vi $ORACLE_HOME/rdbms/lib/config.c
#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
#define SS_ASM_GRP ""
#define SS_BKP_GRP "backupdba"
 #define SS_DGD_GRP "dgdba"
 #define SS_KMT_GRP "kmdba"

– To relink oracle binaries, Shut down all Oracle processes of all instances

a. Shut down the listener.

$ lsnrctl stop

b. Shut down all instances.

$ ps -ef |grep pmon |grep -v grep
oracle 11832 1 0 15:21 ? 00:00:00 ora_pmon_orcl

ORCL> shutdown immediate

— Relink binaries

[oracle@host01 ~]$ cd $ORACLE_HOME/bin; relink all

writing relink log to: /u01/app/oracle/product/12.1.0.2/dbhome_1/install/relink.log

– Now as test user connect as sysdg – succeeds

[test@host01 bin]$ dgmgrl

DGMGRL> connect sysdg/xx
Connected as SYSDG.

– Optionally modify existing OS user oracle to become part of new groups

#usermod -a -G dgdba,backupdba,kmdba oracle

[root@host01 /]# su - oracle

[oracle@host01 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin),54321(dgdba),54322(backupdba),54323(kmdba)

Hope it helps!

Your comments and suggestions are always welcome.

References:

https://community.oracle.com/message/12806120?et=watches.email.thread#12806120

https://www.linkedin.com/groups/Map-OS-Groups-Administrative-Privileges-3698383.S.5964260145260216320?view=&item=5964260145260216320&type=member&gid=3698383&trk=eml-b2_anet_digest-hero-1-hero-disc-disc-0&midToken=AQE9SYOdN_UFjg&fromEmail=fromEmail&ut=1fAfQMlI9DO6A1
==============================================================

Related Links:

Home

Oracle 12c Index

Oracle Ace Associate

It gives me immense pleasure to share with you the news that
I am an Oracle Ace Associate“.

Thanks to the “Oracle ACE Program” for accepting  me  to receive the Oracle ACE Associate award.

My heart is full of gratitude for Sir Murali Vallath who nominated me for this.

Thanks to AIOUG for giving me an opportunity to speak during SANGAM 14 and publishing my white paper on ‘Histograms – Pre-12c and now” in  Oracle Connect Issue Dec 2014.

I want to  thank  my husband  for encouraging me, and readers of my blog for their time, comments and suggestions.

Thank you so much!

Oracleinaction.com in 2014 : A review

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 320,000 times in 2014 with an average of 879 page views per day. If it were an exhibit at the Louvre Museum, it would take about 14 days for that many people to see it.

The busiest day of the year was December 1st with 1,656 views. The most popular post that day was ORACLE CHECKPOINTS.

These are the posts that got the most views on ORACLE IN ACTION in 2014.

The blog was visited by readers from 194 countries in all!
Most visitors came from India. The United States & U.K. were not far behind.

Thanks to all the visitors.

Keep visiting and giving your valuable feedback.

Wish you all a Very Happy New Year 2015  !!!!!

Create Histograms On Columns That Already Have One

The default value of METHOD_OPT from  10g onwards is ‘FOR ALL COLUMNS SIZE AUTO’.

The definition of AUTO as per Oracle documentation is  :
AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

This basically implies that Oracle will automatically  create histograms on those  columns which have skewed data distribution and there are  SQL statements  referencing those columns.

However, this gives rise to the problem is that Oracle generates too many  unnecessary histograms .

Let’s demonstrate:

– Create a table with skewed data distribution in two columns

SQL>drop table hr.skewed purge;

create table hr.skewed
( empno number,
job_id varchar2(10),
salary number);

insert into hr.skewed select employee_id, job_id, salary
from hr.employees;

– On gathering statistics for the table using default options, it can be seen that histogram is not gathered on any column although data
distribution in columns JOB_ID and SALARY is skewed

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED');

col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID NONE
SKEWED EMPNO NONE

– Let’s now issue some queries querying the table based on  the  three columns in the table followed by statistics gathering to verify that histograms get automatically created only on columns with skewed data distribution.

– No histogram gets created if column EMPNO is queried which
has data distributed uniformly

SQL>select * from hr.skewed where empno = 100;
exec dbms_stats.gather_table_stats('HR','SKEWED');

col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID NONE
SKEWED EMPNO NONE

– A histogram gets created on JOB_ID column as soon as we search  for records with a JOB_ID as data distribution is non-uniform in JOB_ID column

SQL>select * from hr.skewed where job_id = 'CLERK';
exec dbms_stats.gather_table_stats('HR','SKEWED');

col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

– A histogram gets created on SALARY column when search is made for  employees drawing salary more than 10000 as data distribution is non-uniform in SALARY column.

SQL>select * from hr.skewed where salary < 10000;
exec dbms_stats.gather_table_stats('HR','SKEWED');

col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY FREQUENCY
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

Thus gathering statistics using default options, manually or as part of the automatic maintenance task,  might lead to creation of histograms  on all such columns  which have  skewed data distribution and  had been  part of the search clause even once. That is, Oracle  makes even the histograms you didn’t ask for.  Some of the histograms might not be needed by the application and hence are undesirable as computing histograms is a resource intensive operation and moreover they might  degrade the performance as a result of their interaction with bind peeking.

Solution
Employ FOR ALL COLUMNS SIZE REPEAT option of METHOD_OPT parameter  which prevents deletion of existing histograms and collects histograms only on the columns that already have histograms.

First step is to eliminate unwanted histograms and have histograms only on the desired columns.

Well, there are two options:

OPTION-I: Delete histograms from unwanted columns and use REPEAT option henceforth which Collects histograms only on the columns that already have histograms.

– Delete unwanted histogram for SALARY column

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED', -
METHOD_OPT => 'for columns salary size 1');

-- Verify that histogram for salary column has been deleted

col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

– Issue a SQL with  salary column in where clause and verify that gathering  stats using repeat  option retains histogram on JOB_ID column and does not cause histogram to be created on salary column.

SQL>select * from hr.skewed where salary < 10000;

exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns salary size REPEAT');

col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
 SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

OPTION-II:   Wipe out all histograms and manually add only the desired ones. Use REPEAT option henceforth which Collects histograms only on the columns that already have one.

– Delete histograms on all columns 

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for all columns size 1');

– Verify that histograms on all columns have been dropped

SQL>col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
 SKEWED JOB_ID NONE
 SKEWED EMPNO NONE

– Create histogram only on the desired JOB_ID column

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns JOB_ID size AUTO');

– Verify that histogram has been created on JOB_ID

SQL>col table_name for a10
col column_name for a10

select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

- Verify that gathering  stats using repeat  option creates histogram only on JOB_ID column on which it already exists

SQL>exec dbms_stats.gather_table_stats('HR','SKEWED',-
METHOD_OPT => 'for columns salary size REPEAT');

SQL>col table_name for a10
col column_name for a10
select TABLE_NAME,COLUMN_NAME,HISTOGRAM
from dba_tab_columns where table_name = 'SKEWED';

TABLE_NAME COLUMN_NAM HISTOGRAM
---------- ---------- ---------------
SKEWED SALARY NONE
SKEWED JOB_ID FREQUENCY
SKEWED EMPNO NONE

That is, now Oracle will no longer make histograms you didn’t ask for.

– Finally, change the preference for METHOD_OPT parameter of automatic stats gathering job from default value of AUTO to REPEAT so that it will gather histograms only for the columns already having one.

–  Get Current value –

SQL> select dbms_stats.get_prefs ('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
-----------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

– Set preference to REPEAT–

SQL> exec dbms_stats.set_global_prefs ('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

– Verify –

SQL> select dbms_stats.get_prefs ('METHOD_OPT') from dual;

DBMS_STATS.GET_PREFS('METHOD_OPT')
-----------------------------------------------------------------------
FOR ALL COLUMNS SIZE REPEAT

From  now onwards, gathering  of statistics, manually or automatically will not create any new histograms while retaining  all the existing ones.

I hope this post is useful.

Happy reading….

References:

https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt
http://www.pythian.com/blog/stabilize-oracle-10gs-bind-peeking-behaviour/
https://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/

———————————————————————————————–

Related Links:

Home

Database Index
Tuning Index

———————————————————————————————

Influence execution plan without adding hints

We often encounter situations when a SQL runs optimally when it is hinted but  sub-optimally otherwise. We can use hints to get the desired plan but it is not desirable to use hints in production code as the use of hints involves extra code that must be managed, checked, and controlled with every Oracle patch or upgrade. Moreover, hints freeze the execution plan so that you will not be able to benefit from a possibly better plan in future.

So how can we make such queries use optimal plan until a provably better plan comes along without adding hints?

Well, the answer is to use SQL Plan Management which ensures that you get the desirable plan which will evolve over time as optimizer discovers better ones.

To demonstrate the procedure, I have created two tables CUSTOMER and PRODUCT having CUST_ID and PROD_ID respectively as primary keys. PROD_ID column in CUSTOMER table is the foreign key and is indexed.

SQL>onn hr/hr

drop table customer purge;
drop table product purge;

create table product(prod_id number primary key, prod_name char(100));
create table customer(cust_id number primary key, cust_name char(100), prod_id number references product(prod_id));
create index cust_idx on customer(prod_id);

insert into product select rownum, 'prod'||rownum from all_objects;
insert into customer select rownum, 'cust'||rownum, prod_id from product;
update customer set prod_id = 1000 where prod_id > 1000;

exec dbms_stats.gather_table_stats (USER, 'customer', cascade=> true);
exec dbms_stats.gather_table_stats (USER, 'product', cascade=> true);

– First, let’s have a look at the undesirable plan which does not use the index on PROD_ID column of CUSTOMER table.

SQL>conn / as sysdba
    alter system flush shared_pool;

    conn hr/hr

    variable prod_id number
    exec :prod_id := 1000

    select cust_name, prod_name
    from customer c, product p
    where c.prod_id = p.prod_id
    and c.prod_id = :prod_id;

    select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------SQL_ID  b257apghf1a8h, child number 0
-------------------------------------
select cust_name, prod_name from customer c, product p where c.prod_id
= p.prod_id and c.prod_id = :prod_id

Plan hash value: 3134146364

----------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |       |       |   412 (100)|          |
|   1 |  NESTED LOOPS                |              | 88734 |    17M|   412   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PRODUCT      |     1 |   106 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0010600 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | CUSTOMER     | 88734 |  9098K|   410   (1)| 00:00:01 |
----------------------------------------------------------------------

– Load undesirable plan into baseline  to establish a SQL plan baseline for this query into which the desired plan will be loaded later

SQL>variable cnt number
    exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'b257apghf1a8h');

    col sql_text for a35 word_wrapped
    col enabled for a15

    select  sql_text, sql_handle, plan_name, enabled 
    from     dba_sql_plan_baselines
    where sql_text like   'select cust_name, prod_name%';

SQL_TEXT                            SQL_HANDLE                                      PLAN_NAME                                                                        ENABLED
----------------------------------- ----------------------------------------------------------------------
select cust_name, prod_name         SQL_7d3369334b24a117                            SQL_PLAN_7ucv96d5k988rfe19664b                                                   YES

– Disable undesirable plan so that this plan will not be used

SQL>variable cnt number
    exec :cnt := dbms_spm.alter_sql_plan_baseline (-
    SQL_HANDLE => 'SQL_7d3369334b24a117',-
    PLAN_NAME => 'SQL_PLAN_7ucv96d5k988rfe19664b',-
    ATTRIBUTE_NAME => 'enabled',-
    ATTRIBUTE_VALUE => 'NO');

    col sql_text for a35 word_wrapped
    col enabled for a15

    select  sql_text, sql_handle, plan_name, enabled 
    from   dba_sql_plan_baselines
     where sql_text like   'select cust_name, prod_name%';

SQL_TEXT                            SQL_HANDLE                                      PLAN_NAME                                                                        ENABLED
----------------------------------------------------------------------select cust_name, prod_name         SQL_7d3369334b24a117                            SQL_PLAN_7ucv96d5k988rfe19664b                                                   NO

– Now we use hint in the above SQL to generate the optimal plan which uses index on PROD_ID column of CUSTOMER table

SQL>conn hr/hr

variable prod_id number
exec :prod_id := 1000

select /*+ index(c)*/ cust_name, prod_name
from customer c, product p
where c.prod_id = p.prod_id
and c.prod_id = :prod_id;

select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5x2y12dzacv7w, child number 0
-------------------------------------
select /*+ index(c)*/ cust_name, prod_name from customer c, product p
where c.prod_id = p.prod_id and c.prod_id = :prod_id

Plan hash value: 4263155932

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |       |       |  1618 (100)|          |
|   1 |  NESTED LOOPS                        |              | 88734 |    17M|  1618   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT      |     1 |   106 |    2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | SYS_C0010600 |     1 |       |    1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER     | 88734 |  9098K|  1616   (1)| 00:00:01 |
|   5 |    INDEX FULL SCAN                   | SYS_C0010601 | 89769 |       |  169   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

– Now we will load the hinted plan  into baseline –
– Note that we have SQL_ID and PLAN_HASH_VALUE of the hinted statement and SQL_HANDLE for the unhinted statement i.e. we are associating hinted plan with unhinted statement.

SQL>variable cnt number
exec :cnt := dbms_spm.load_plans_from_cursor_cache(-
sql_id => '5x2y12dzacv7w',  -
plan_hash_value => 4263155932, -
sql_handle => 'SQL_7d3369334b24a117');

– Verify that there are now two plans loaded for that SQL statement:

  •  Unhinted sub-optimal plan is disabled
  •  Hinted optimal plan which even though is for a  “different query,”  can work with earlier unhinted query (SQL_HANDLE is same)  is enabled.
SQL>col sql_text for a35 word_wrapped
col enabled for a15

select  sql_text, sql_handle, plan_name, enabled from dba_sql_plan_baselines
where sql_text like   'select cust_name, prod_name%';

SQL_TEXT                            SQL_HANDLE                                      PLAN_NAME                                                                        ENABLED
----------------------------------------------------------------------
select cust_name, prod_name         SQL_7d3369334b24a117                            SQL_PLAN_7ucv96d5k988rea320380                                                   YES

select cust_name, prod_name         SQL_7d3369334b24a117                            SQL_PLAN_7ucv96d5k988rfe19664b                                                   NO

– Verify that hinted plan is used even though we do not use hint in the query  –
– The note confirms that baseline has been used for this statement

SQL>variable prod_id number
exec :prod_id := 1000

select cust_name, prod_name
from customer c, product p
where c.prod_id = p.prod_id
and c.prod_id = :prod_id;

select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b257apghf1a8h, child number 0
-------------------------------------
select cust_name, prod_name from customer c, product p where c.prod_id
= p.prod_id and c.prod_id = :prod_id

Plan hash value: 4263155932

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |       |       |  1618 (100)|          |
|   1 |  NESTED LOOPS                        |              | 88734 |    17M|  1618   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | PRODUCT      |     1 |   106 |    2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | SYS_C0010600 |     1 |       |    1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER     | 88734 |  9098K|  1616   (1)| 00:00:01 |
|   5 |    INDEX FULL SCAN                   | SYS_C0010601 | 89769 |       |  169   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

3 - access("P"."PROD_ID"=:PROD_ID)
4 - filter("C"."PROD_ID"=:PROD_ID)

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

With this baseline solution, you need not employ permanent hints the production code and hence no upgrade issues. Moreover, the plan will evolve with time as optimizer discovers better ones.

Note:  Using this method, you can swap  the plan for only a query which is fundamentally same i.e. you should get the desirable plan by adding hints, modifying  an optimizer setting, playing around with statistics etc. and then associate sub-optimally performing statement with the optimal plan.

I hope this post was useful.

Your comments and suggestions are always welcome!

References:
http://www.oracle.com/technetwork/issue-archive/2014/14-jul/o44asktom-2196080.html

—————————————————————————————————————————————–

Related links:

HOME
Tuning Index

Change Parameter Value In Another Session

The values of initialization parameters in another session can be changed  using procedures SET_BOOL_PARAM_IN_SESSION and SET_INT_PARAM_IN_SESSION provided in DBMS_SYSTEM package.

Let’s demonstrate:

SQL>conn / as sysdba

SYS> grant dba to hr;

– Currently parameter HASH_AREA_SIZE is set to 131073 in HR session

HR>> sho parameter hash_area_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hash_area_size                       integer     131073

– Find out SID, SERIAL# for HR session

SYS> select sid, serial#, username from v$session where username=’HR';

SID    SERIAL# USERNAME
———- ———- ——————————
54        313 HR

– Set value of parameter HASH_AREA_SIZE to 131072 in HR session

SYS> exec dbms_system.SET_INT_PARAM_IN_SESSION(54, 313, ‘HASH_AREA_SIZE’,131072);

– Verify that the value of parameter HASH_AREA_SIZE has been changed in HR session

HR> sho parameter hash_area_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hash_area_size                       integer     131072

Similary Boolean initialization parameters can be modified using  dbms_system.SET_BOOL_PARAM_IN_SESSION.

– Let’s find out the value of parameter SKIP_UNUSABLE_INDEXES in HR session

HR> sho parameter skip_unusable indexes

NAME TYPE VALUE
———————————— ———– ——————————
skip_unusable_indexes boolean TRUE

– Modify the  value of parameter SKIP_UNUSABLE_INDEXES to FALSE in HR session

SYS> exec dbms_system.SET_BOOL_PARAM_IN_SESSION(54, 313, ‘skip_unusable_indexes’,FALSE);

– Verify that the value of parameter SKIP_UNUSABLE_INDEXES  has been changed to FALSE in HR session

HR> sho parameter skip_unusable indexes

NAME TYPE VALUE
———————————— ———– ——————————
skip_unusable_indexes boolean FALSE

References:

http://dbaspot.com/oracle-server/143210-how-query-sessions-active-initialization-parameters-other-session.html

————————————————————————————————–

Related links:

Home

Database Index 

Find Values Of Another Session’s Parameters

 

—————————-