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

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

 

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

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

Webinar: Adaptive Query Optimization

Oracle Database 12c introduces Adaptive Query optimization framework which is designed to overcome pitfalls of oracle database 11g and help the optimizer to generate an optimal plan when existing statistics are insufficient.

There are two techniques of Adaptive Query Optimization:
• Adaptive / Dynamic plans
• Automatic Re-optimization

I will present a webinar on “Adaptive Query Optimization” on Saturday, June 13 at 10:00 AM – 11:00 AM (IST) organized by All India Oracle User Group – North India Chapter.

OR
Hope to meet you at the webinar!!!

Troubleshooting ASM Proxy instance startup

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

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

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.

 

 

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

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