Skip navigation.

Oracle in Action

Syndicate content
Let's do it simply...
Updated: 12 hours 57 min ago

CRS-4995: The command ‘Modify resource’ is invalid in crsctl. Use srvctl for this command.

Mon, 2015-06-15 09:40

RSS content

Today, in my 12.1.0.2 cluster,  I encountered above error message when I was trying to modify ACL of an ASM cluster file system created on volume VOL1 in DATA diskgroup as follows:

[root@host01 ~]# crsctl modify resource ora.data.vol1.acfs -attr "ACL='owner:root:rwx,pgrp:dba:rwx,other::r--'"

CRS-4995: The command 'Modify resource' is invalid in crsctl. Use srvctl for this command.

I resolved the above problem by using the unsupported flag as follows:

[root@host01 ~]# crsctl modify resource ora.data.vol1.acfs -attr "ACL='owner:root:rwx,pgrp:dba:rwx,other::r--'" -unsupported

 

Hope it helps!!

References:
Oracle Issue running 12.1.0.2 clusterware with 11.2.0.2 database

Oracle Issue running 12.1.0.2 clusterware with 11.2.0.2 database

——————————————————————————————————————-

 Related Links :

Home

12c RAC Index

 



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [CRS-4995: The command 'Modify resource' is invalid in crsctl. Use srvctl for this command.], All Right Reserved. 2015.

The post CRS-4995: The command ‘Modify resource’ is invalid in crsctl. Use srvctl for this command. appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

INDEX FULL SCAN (MIN/MAX) Not Used – How To Resolve

Sat, 2015-06-06 01:06

RSS content

If you want to find out  the minimum or the maximum of a column value and the column is indexed, Oracle can very quickly determine the minimum or maximum value of the column by navigating to the first (left-most) or last (right-most) leaf blocks in the index structure to get the  Min or Max values respectively.  This access path known as  Index Full Scan (Min/Max) is extremely cost effective as instead of scanning the entire index / table, only first or last entries in the index need to be read.

In case the Select clause includes another column with a function applied to it, optimizer employs Full table Scan instead. In this post, I will demonstrate this scenario and also the solution to the same.

In my test setup, I have a table HR.EMP having index on SALARY column.

— Let’s first query the MIN(SALARY) and SYSDATE from HR.EMP. It can be seen that optimizer employs INDEX FULL SCAN (MIN/MAX) as desired.

SQL>select min(salary),  sysdate from hr.emp ;
select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 7c3q3s8g2ucxx, child number 0
-------------------------------------
select min(salary), sysdate from hr.emp

Plan hash value: 3077585419
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_SAL | 107 | 428 | |
----------------------------------------------------------------------

— Now if I try to find out MIN(SALARY)  with function applied to SYSDATE, the optimizer chooses  costly TABLE ACCESS FULL instead of  INDEX FULL SCAN (MIN/MAX) .

SQL>select min(salary), to_char(sysdate, 'dd/mm/yy') from hr.emp ;
select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 3dthda93cgm6v, child number 0
-------------------------------------
select min(salary), to_char(sysdate, 'dd/mm/yy') from hr.emp

Plan hash value: 2083865914
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| EMP | 107 | 428 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

As a workaround , we can  restructure our query as shown so that  it uses an inline view to get the MIN(SALARY)   so that optimizer chooses   INDEX FULL SCAN (MIN/MAX) and  function to SYSDATE  is applied in the  main SELECT clause.

SQL>select min_salary, to_char(sysdt, 'dd/mm/yy') from
(select min(salary) min_salary, sysdate sysdt from hr.emp) ;
select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 5rzz6x8wzkh2k, child number 0
-------------------------------------
select min_salary, to_char(sysdt, 'dd/mm/yy') from (select
min(salary) min_salary, sysdate sysdt from hr.emp)

Plan hash value: 2631972856
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | VIEW | | 1 | 19 | 3 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 4 | | |
| 3 | INDEX FULL SCAN (MIN/MAX)| EMP_SAL | 107 | 428 | | |
-------------------------------------------------------------------------

Hope it helps!

References:

AIOUG -North India Chapter- Performance Tuning By Vijay Sehgal – 30th May 2015
Index Full Scan (MIN/MAX) and Partitioned Table

——————————————————————————————————————-

Related links:

Home
Tuning Index



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [INDEX FULL SCAN (MIN/MAX) Not Used - How To Resolve], All Right Reserved. 2015.

The post INDEX FULL SCAN (MIN/MAX) Not Used – How To Resolve appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Troubleshooting ASM Proxy instance startup

Wed, 2015-05-20 08:53

RSS content

Recently, I had trouble starting ASM proxy instance on one of the nodes in my  2 node flex cluster having nodes host01 and host02. As a result I could not access the volume I created on an ASM  diskgroup.  This post explains  how I resolved it.

While connected to host01, I created a volume VOL1 on DATA diskgroup with corresponding volume device /dev/asm/vol1-106 .

[grid@host01 root]$ asmcmd volcreate -G DATA -s 300m VOL1

[grid@host01 root]$ asmcmd volinfo -G DATA VOL1

Diskgroup Name: DATA

Volume Name: VOL1
Volume Device: /dev/asm/vol1-106
State: ENABLED
Size (MB): 320
Resize Unit (MB): 32
Redundancy: MIRROR
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath:

I created  ACFS file system on the newly created volume

[root@host01 ~]# mkfs -t acfs /dev/asm/vol1-106

I also created corresponding mount point /mnt/acfsmounts/acfs1 on both the nodes in the cluster.

root@host01 ~]# mkdir -p /mnt/acfsmounts/acfs1

root@host02 ~]# mkdir -p /mnt/acfsmounts/acfs1

When I tried to mount the volume device, I could mount the volume device on host01 but not on host02 .

[root@host01 ~]#mount -t acfs /dev/asm/vol1-106 /mnt/acfsmounts/acfs1

[root@host01 ~]# mount | grep vol1

/dev/asm/vol1-106 on /mnt/acfsmounts/acfs1 type acfs (rw)

[root@host02 ~]# mount -t acfs /dev/asm/vol1-106 /mnt/acfsmounts/acfs1

mount.acfs: CLSU-00100: Operating System function: open64 failed with error data: 2
mount.acfs: CLSU-00101: Operating System error message: No such file or directory
mount.acfs: CLSU-00103: error location: OOF_1
mount.acfs: CLSU-00104: additional error information: open64 (/dev/asm/vol1-106)
mount.acfs: ACFS-02017: Failed to open volume /dev/asm/vol1-106. Verify the volume exists.

The corresponding volume device was visible on host01 but not on host02

[root@host01 ~]# cd /dev/asm
[root@host01 asm]# ls
vol1-106

[root@host02 ~]# cd /dev/asm
[root@host02 asm]# ls

Since ADVM / ACFS utilize an ASM Proxy instance in a flex cluster to access metadata from a local /  remote  ASM instance ,  I checked whether ASM Proxy instance was running on both the nodes and realized that whereas ASM Proxy instance was running on host01, it  was not running on host02

[root@host01 ~]# ps -elf | grep pmon | grep APX

0 S grid 27782 1 0 78 0 – 350502 – 10:09 ? 00:00:00 apx_pmon_+APX1

[root@host02 asm]# ps -elf | grep pmon | grep APX

[root@host01 ~]# srvctl status asm -proxy

ADVM proxy is running on node host01

[root@host01 ~]# crsctl stat res ora.proxy_advm -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.proxy_advm
ONLINE ONLINE host01 STABLE
ONLINE OFFLINE host02 STABLE

I tried to start ASM  proxy instance manually on host02

[grid@host02 ~]$ . oraenv
ORACLE_SID = [grid] ? +APX2
The Oracle base has been set to /u01/app/grid

[grid@host02 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Sat May 2 10:31:45 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORA-00099: warning: no parameter file specified for ASMPROXY instance
ORA-00443: background process "VUBG" did not start

SQL> ho oerr ORA 00443

00443, 00000, "background process \"%s\" did not start"
// *Cause: The specified process did not start.
// *Action: Ensure that the executable image is in the correct place with
// the correct protections, and that there is enough memory.

I checked the memory allocated to VM for host02 – It was 1.5 GB as against 2.5 GB assigned to VM for host01. I  increased the memory of host02 to 2.5 GB and ASM proxy instance started automatically.

[root@host01 ~]# crsctl stat res ora.proxy_advm -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.proxy_advm
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE

Hope it helps!

References: 

Oracle documentation

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

Related Links :

Home

12c RAC Index

12c RAC: ORA-15477: cannot communicate with the volume driver



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [Troubleshooting ASM Proxy instance startup], All Right Reserved. 2015.

The post Troubleshooting ASM Proxy instance startup appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Book Review: Oracle Database 12c New Features by Robert Freeman

Sun, 2015-04-19 03:04

RSS content

This book touches a myriad of new features of oracle database 12c relevant to DBA’s, developers and architects. It starts with new features as well as step by step detailed instructions of  installation along with relevant screenshots followed by an  introduction to EM Express. Next chapter covers new features related to upgrading to Oracle Database 12c and various methods to perform the upgrade. All the subsequent chapters explore  a whole lot of  new features from which as a DBA,  I was more interested in multitenant architecture, Flex Clusters, Flex ASM, ACFS, RMAN-Related New Features, Oracle Data Guard New Features, auditing , statistics and Optimizer-related new features. For  every feature,  first the need to introduce the feature has been explained followed by the demonstration of  basic functionality of the  feature with simple and easy to reproduce scripts.  There is a lot left to be learnt and explored for which you need to refer to documentation and practice. In short, this book a launching point to start your journey  for understanding oracle database 12c.

Thanks to the author Robert Freeman, contributors Scott Black,  Tom Kyte and Eric Yen for putting together such a great book. A must have for Oracle enthusiasts.

 

 



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [Book Review: Oracle Database 12c New Features by Robert Freeman], All Right Reserved. 2015.

The post Book Review: Oracle Database 12c New Features by Robert Freeman appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Find Out Values Of Bind Variables Across Multiple Executions Of A SQL

Tue, 2015-04-14 00:48

RSS content

The execution of the same SQL statement might result in different execution plans due to various reasons. In my last post,  I had demonstrated how the value of parameter OPTIMIZER_MODE can cause change in execution plan.  In this post, I will demonstrate that in case of skewed data distribution,  change in the value of a bind variables can also result in differing execution plans for the same statement. I will also explore how we can find out the values of bind variables used during various executions of the same statement when

  • the cursors are still available in shared pool
  • the cursors have been flushed to AWR

As part of the setup, I have created a table  TEST_BIND with skewed data distribution in column ID which is indexed. There are 1000 records for ID = 1 and only 1 record with ID = 2.

SQL>select id, count(*) from hr.test_bind group by id;

ID COUNT(*)
---------- ----------
1 1000
2 1

Subsequently, I have executed the following statement with values 1 and 2 assigned to bind variable VID multiple no. of times so that Adaptive Cursor Sharing kicks in and the statement executes with Full Table Scan when VID = 1 and with Index Range Scan when VID = 2 .

SQL>select * from hr.test_bind where id = :vid;

–  Now I will execute the statement with different values of the bind variable

SQL>variable vid number;
exec :vID := 1;
select * from hr.test_bind where id = :vid;

variable vid number;
exec :vID := 2;
select * from hr.test_bind where id = :vid;

– Since the statement is still in shared pool, we can find out sql_id for the statement from V$SQL
– It can be seen two child cursors have been created indicating that the two executions used different execution plans

SQL>select sql_id, child_number, sql_text, is_bind_sensitive bind_sensitive,
is_bind_aware bind_aware , is_shareable shareable
fom v$sql
where sql_text like '%select * from hr.test_bind%'
and is_shareable = 'Y'
and sql_text not like '%sql_text%';

SQL_ID CHILD_NUMBER SQL_TEXT BIND_SENSITIVE BIND_AWARE SHAREABLE
------------- ------------ ----------------------------------- ----------
7a73kdgy0q1c2 1 select * from hr.test_bind whe Y Y Y
re id = :vid

7a73kdgy0q1c2 2 select * from hr.test_bind whe Y Y Y
re id = :vid

–  It can be verified that different plans are being used by the same statement during two executions due to different values of bind variable VID. The respective values of the bind variable are visible in Peeked Binds section of the execution plan

SQL>select * from table (dbms_xplan.display_cursor('7a73kdgy0q1c2', 1, format => 'TYPICAL +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 7a73kdgy0q1c2, child number 1
-------------------------------------
select * from hr.test_bind where id = :vid

Plan hash value: 3519963602
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_BIND | 1000 | 7000 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
1 - :VID (NUMBER): 1

SQL>select * from table (dbms_xplan.display_cursor('7a73kdgy0q1c2', 2, format => 'TYPICAL +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID 7a73kdgy0q1c2, child number 2
-------------------------------------
select * from hr.test_bind where id = :vid

Plan hash value: 2825156252
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIND | 1 | 7 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
1 - :VID (NUMBER): 2

– We can look at v$sql_bind_capture also  to find out  the values of bind variable across various executions of the SQL

SQL>SELECT NAME, child_number, DATATYPE_STRING,VALUE_STRING
FROM v$sql_bind_capture
WHERE sql_id='7a73kdgy0q1c2' order by child_number;

NAME CHILD_NUMBER DATATYPE_STRING VALUE_STRING
--------------- ------------ -------------------- ---------------
:VID 0 NUMBER 2
:VID 1 NUMBER 1
:VID 2 NUMBER 2

Now I will demonstrate how we can find out different execution plans and corresponding values of bind variables after the statement has been flushed to AWR.

– To flush the statement to AWR, let’s  take a snapshot

SQL>exec dbms_workload_repository.create_snapshot ('ALL');

– We can verify that that the  statement is indeed in AWR

SQL> select sql_id, sql_text
from dba_hist_sqltext
where sql_text like '%select * from hr.test_bind%'
and  sql_text not like '%sql_text%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
7a73kdgy0q1c2 select * from hr.test_bind where id = :vid

– Find out various execution plans andcorresponding values of bind variables  used by the statement

SQL>select * from table(dbms_xplan.display_awr ('7a73kdgy0q1c2', format => 'TYPICAL +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 7a73kdgy0q1c2
--------------------
select * from hr.test_bind where id = :vid

Plan hash value: 2825156252
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIND | 1 | 7 | 2 (0)| 00:00:01 |
| 2 | INDEX RANGE SCAN | TEST_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
1 - :VID (NUMBER): 2

SQL_ID 7a73kdgy0q1c2
--------------------
select * from hr.test_bind where id = :vid

Plan hash value: 3519963602
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| TEST_BIND | 1000 | 7000 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
1 - :VID (NUMBER): 1

– We can also use dba_hist_sqlbind to check the values of bind variables during 2 executions

SQL>SELECT sql_id, NAME,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND WHERE SQL_ID='7a73kdgy0q1c2' ;

SQL_ID NAME DATATYPE_STRING VALUE_STRING
------------- --------------- -------------------- ---------------
7a73kdgy0q1c2 :VID NUMBER 1
7a73kdgy0q1c2 :VID NUMBER 2

 Summary:
In case of skewed data distribution, different values of bind variables can result in different execution plans. Various execution plans and corresponding values of bind variables can be determined both when the statement is still shared pool and when it has been flushed to AWR.

I hope this post was useful.  Your comments and suggestions are always welcome.
Keep visiting my blog …

References:
http://shaharear.blogspot.in/2009/02/find-bind-variable-value.html

https://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&gid=2342993&discussionID=5993686363038375940&trk=eml-group_discussion_new_comment-respond-btn&midToken=AQE9SYOdN_UFjg&fromEmail=fromEmail&ut=0ZAtL1rWAWtCI1

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

Related links:

Home
Tuning Index

Identify difference in CBO parameters across two executions of a SQL



Tags:  

Del.icio.us
Digg

Comments:  2 comments on this item
You might be interested in this:  
Copyright © ORACLE IN ACTION [Find Out Values Of Bind Variables Across Multiple Executions Of A SQL], All Right Reserved. 2015.

The post Find Out Values Of Bind Variables Across Multiple Executions Of A SQL appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Identify difference in CBO parameters across two executions of a SQL

Fri, 2015-04-10 01:16

RSS content

Various initialization parameters  influence the choice of execution plan by the optimizer. Multiple executions of the same SQL with  different optimizer parameters  may employ different execution plans and hence result in  difference in performance . In this post, I will demonstrate how we can identify the CBO parameters which had  different values during two executions of the same SQL.

I will explore two scenarios :

  • the cursors are still available in shared pool
  • the cursors have been flushed to AWR

Overview:

  •  Flush shared pool
  •  Execute the same statement in two sessions with different values of OPTIMIZER_MODE
  •  Verify that the cursors for the statement are still in the shared pool
  •  Find out the execution plans employed by two cursors
  •  Find out the optimizer parameters which had different values during two executions of the SQL
  •  Take snapshot so that cursors are flushed to AWR
  •  Verify that the cursors for the statement are in AWR
  •  Find out the execution plans employed by two cursors
  •  Find out the value of parameter OPTIMIZER_MODE during two executions of the SQL

Implementation

- Flush shared pool

SQL>conn / as sysdba

alter system flush shared_pool;

- Execute the same statement in two sessions with different values of OPTIMIZER_MODE

– Note that there is difference in the time elapsed during two executions of the same statement

– Session – I –

SQL>alter session set optimizer_mode = 'ALL_ROWS';

set timing on
select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s,
sh.products p where s.prod_id = p.prod_id;
set timing off

918843 rows selected.

Elapsed: 00:01:19.57

-- Session - II  --
SQL>alter session set optimizer_mode = 'FIRST_ROWS_1';

set timing on
select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s,
sh.products p where s.prod_id = p.prod_id;
set timing off

918843 rows selected.

Elapsed: 00:01:16.63

 – Verify that the cursors for the statement are still in the shared pool and find out SQL_ID of the statement

SQL>set pagesize 200
col sql_text for a50

select sql_id, child_number, sql_text
from v$sql
where sql_text like '%select  s.quantity_sold, s.amount_sold%';

SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------------
394uuwwbyjdnh 0 select sql_id, child_number, sql_text from v$sql w
here sql_text like '%select s.quantity_sold, s.am
ount_sold%'

6y2xaw3asr6xv 0 select s.quantity_sold, s.amount_sold, p.prod_nam
e from sh.sales s, sh.products p where s.prod_id =
p.prod_id

6y2xaw3asr6xv 1 select s.quantity_sold, s.amount_sold, p.prod_nam
e from sh.sales s, sh.products p where s.prod_id =
p.prod_id

 – Find out the execution plans employed by two cursors

Note that

  • child 0 uses hash join whereas child 1 employs nested loops join
  • Outline Data shows that optimizer_mode was ALL_ROWS during first execution and FIRST_ROWS(1) during second execution.
SQL>select * from table (dbms_xplan.display_cursor('6y2xaw3asr6xv', 0, format => 'TYPICAL +OUTLINE'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 6y2xaw3asr6xv, child number 0
-------------------------------------
select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s,
sh.products p where s.prod_id = p.prod_id

Plan hash value: 1019954709
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 495 (100)| || |
|* 1 | HASH JOIN | | 918K| 36M| 495 (3)| 00:00:06 | | |
| 2 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE ALL| | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 |
| 4 | TABLE ACCESS FULL | SALES | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 |
-------------------------------------------------------------------------

Outline Data
-------------
 /*+
 BEGIN_OUTLINE_DATA
 IGNORE_OPTIM_EMBEDDED_HINTS
 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
 DB_VERSION('11.2.0.1')
 ALL_ROWS
 OUTLINE_LEAF(@"SEL$1")
 FULL(@"SEL$1" "P"@"SEL$1")
 FULL(@"SEL$1" "S"@"SEL$1")
 LEADING(@"SEL$1" "P"@"SEL$1" "S"@"SEL$1")
 USE_HASH(@"SEL$1" "S"@"SEL$1")
 END_OUTLINE_DATA
 */

Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("S"."PROD_ID"="P"."PROD_ID")
select * from table (dbms_xplan.display_cursor('6y2xaw3asr6xv', 1, format => 'TYPICAL +OUTLINE'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 6y2xaw3asr6xv, child number 1
-------------------------------------
select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s,
sh.products p where s.prod_id = p.prod_id

Plan hash value: 3603960078
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 42 | 3 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE ALL | | 1 | 12 | 2 (0)| 00:00:01 | 1 | 28 |
| 4 | TABLE ACCESS FULL | SALES | 1 | 12 | 2 (0)| 00:00:01 | 1 | 28 |
|* 5 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | | 0 (0)| | | |
| 6 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------

Outline Data
-------------
 /*+
 BEGIN_OUTLINE_DATA
 IGNORE_OPTIM_EMBEDDED_HINTS
 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
 DB_VERSION('11.2.0.1')
 FIRST_ROWS(1)
 OUTLINE_LEAF(@"SEL$1")
 FULL(@"SEL$1" "S"@"SEL$1")
 INDEX(@"SEL$1" "P"@"SEL$1" ("PRODUCTS"."PROD_ID"))
 LEADING(@"SEL$1" "S"@"SEL$1" "P"@"SEL$1")
 USE_NL(@"SEL$1" "P"@"SEL$1")
 NLJ_BATCHING(@"SEL$1" "P"@"SEL$1")
 END_OUTLINE_DATA
 */

Predicate Information (identified by operation id):
---------------------------------------------------
 5 - access("S"."PROD_ID"="P"."PROD_ID")

- It can be verified from v$sql_shared_cursor also that there was optimizer mode mismatch between two executions of the SQL

SQL>select SQL_ID, child_number, OPTIMIZER_MODE_MISMATCH
from v$sql_shared_cursor
where sql_id = ‘6y2xaw3asr6xv';

SQL_ID CHILD_NUMBER O
————- ———— –
6y2xaw3asr6xv 0 N
6y2xaw3asr6xv 1 Y

 – We can also employ v$sql_optimizer_env to find out optimizer parameters which have different values during 2 executions

SQL>set line 500
col name for a15

select child1.name, child1.value child1_value, child2.value child2_value
from v$sql_optimizer_env child1, v$sql_optimizer_env child2
where child1.sql_id = '6y2xaw3asr6xv' and child1.name like 'optimizer%'
and child2.sql_id = '6y2xaw3asr6xv' and child2.name like 'optimizer%'
and child1.name = child2.name
and child1.value <> child2.value;

NAME CHILD1_VALUE CHILD2_VALUE
--------------- ------------------------- -------------------------
optimizer_mode first_rows_1 all_rows
optimizer_mode all_rows first_rows_1

 – Take snapshot so that cursors are flushed to AWR

SQL>exec dbms_workload_repository.create_snapshot ('ALL');

 – Verify that the statement is  in AWR

SQL> set pagesize 200
SQL> col sql_text for a50
SQL>  select sql_id, sql_text from dba_hist_sqltext where sql_text like '%select s.quantity_sold, s.amount_sold%';
SQL_ID SQL_TEXT

------------- --------------------------------------------------
6y2xaw3asr6xv select s.quantity_sold, s.amount_sold, p.prod_nam
e from sh.sales s,
sh.products

9fr7ycjcfqydb select sql_id, sql_text from v$sql where sql_text
like '%select s.quantity_sold

 – Find out the execution plans that were  employed by various  cursors of the same statement

Note that

  • one cursor  uses hash join whereas other cursor employs nested loops join
  • The value of parameter OPIMIZER_MODE was ALL_ROWS during one execution and FIRST_ROWS(1) during second execution
SQL>select * from table(dbms_xplan.display_awr ('6y2xaw3asr6xv', format => 'TYPICAL +OUTLINE'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 6y2xaw3asr6xv
--------------------
select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s,
sh.products p where s.prod_id = p.prod_id

Plan hash value: 1019954709
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 495 (100)| || |
| 1 | HASH JOIN | | 918K| 36M| 495 (3)| 00:00:06 | | |
| 2 | TABLE ACCESS FULL | PRODUCTS | 72 | 2160 | 3 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE ALL| | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 |
| 4 | TABLE ACCESS FULL | SALES | 918K| 10M| 489 (2)| 00:00:06 | 1 | 28 |
-------------------------------------------------------------------------

Outline Data
-------------
 /*+
 BEGIN_OUTLINE_DATA
 IGNORE_OPTIM_EMBEDDED_HINTS
 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
 DB_VERSION('11.2.0.1')
 ALL_ROWS
 OUTLINE_LEAF(@"SEL$1")
 FULL(@"SEL$1" "P"@"SEL$1")
 FULL(@"SEL$1" "S"@"SEL$1")
 LEADING(@"SEL$1" "P"@"SEL$1" "S"@"SEL$1")
 USE_HASH(@"SEL$1" "S"@"SEL$1")
 END_OUTLINE_DATA
 */

SQL_ID 6y2xaw3asr6xv
--------------------
select s.quantity_sold, s.amount_sold, p.prod_name from sh.sales s,
sh.products p where s.prod_id = p.prod_id

Plan hash value: 3603960078
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 42 | 3 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE ALL | | 1 | 12 | 2 (0)| 00:00:01 | 1 | 28 |
| 4 | TABLE ACCESS FULL | SALES | 1 | 12 | 2 (0)| 00:00:01 | 1 | 28 |
| 5 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | | 0 (0)| | | |
| 6 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 1 | 30 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------

Outline Data
-------------
 /*+
 BEGIN_OUTLINE_DATA
 IGNORE_OPTIM_EMBEDDED_HINTS
 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
 DB_VERSION('11.2.0.1')
 FIRST_ROWS(1)
 OUTLINE_LEAF(@"SEL$1")
 FULL(@"SEL$1" "S"@"SEL$1")
 INDEX(@"SEL$1" "P"@"SEL$1" ("PRODUCTS"."PROD_ID"))
 LEADING(@"SEL$1" "S"@"SEL$1" "P"@"SEL$1")
 USE_NL(@"SEL$1" "P"@"SEL$1")
 NLJ_BATCHING(@"SEL$1" "P"@"SEL$1")
 END_OUTLINE_DATA
 */

 — We can also use dba_hist_sqlstat to check the optimizer mode during two executions

SQL>select sql_text, optimizer_mode   
   from dba_hist_sqlstat h, dba_hist_sqltext t
     where h.sql_id = '6y2xaw3asr6xv'
       and t.sql_id = '6y2xaw3asr6xv';

SQL_TEXT OPTIMIZER_
-------------------------------------------------- ----------
select s.quantity_sold, s.amount_sold, p.prod_nam ALL_ROWS
e from sh.sales s,
sh.products

select s.quantity_sold, s.amount_sold, p.prod_nam FIRST_ROWS
e from sh.sales s,
sh.products

Summary:

The difference in values of various CBO parameters during various executions of the same statement can be found out while the statement is in shared pool or has been flushed to disk .

————————————————————————————————————————-

Related links:

Home
Tuning Index



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [Identify difference in CBO parameters across two executions of a SQL], All Right Reserved. 2015.

The post Identify difference in CBO parameters across two executions of a SQL appeared first on ORACLE IN ACTION.

Categories: DBA Blogs