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

Identify difference in CBO parameters across two executions of a SQL

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

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

Conditions Based On Inequalities Can’t Use Indexes – How To Resolve?

Conditions based on inequalities (!=, <>) cannot make use of index(es). I will illustrate this limitation and show you how to optimize SQL statements hitting it.

For the demonstration, I have  a table  students table having a column named result that  can contain the values – ‘Pass’, ‘Fail’, ‘To be evaluated’. The column is characterized by a very non-uniform distribution having most of the rows  set to value Passed (P). Here’s the example:

SQL>drop table students purge;
    create table students (id , result )
    as
    select rownum, decode (mod(rownum, 30), 0, 'F', 1, 'T',  'P')
    from  all_tables;

    create index students_idx on students (result);
    exec dbms_stats.gather_table_stats (USER, 'STUDENTS', cascade => TRUE);

     SELECT result , count(*)
     FROM students
     GROUP BY result;
RESULT COUNT(*)
---------- ----------
P              100
T                4
F                3

Let’s execute the  query to select all students who have not passed (result = ‘T’ or ‘F’). Even though the query has a very strong selectivity and the result column is indexed, the query optimizer chooses a full table scan for reading 7 rows as the predicate involves inequality.

SQL>select * from students where result <> 'P';
    select * from table(dbms_xplan.display_cursor);

ID RESULT
---------- ----------
1 T
30 F

....

7 rows selected.

PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID f2wkxqy3b6b5h, child number 0
-------------------------------------
select * from students where result <> 'P'

Plan hash value: 4078133427
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| STUDENTS | 71 | 355 | 3 (0)| 00:00:01 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RESULT"<>'P')

In a case like this, where the inequality condition has a strong selectivity, we can advantage of an index using following four techniques :

First, the inequality condition can be rewritten into an IN condition. This is an option only when the number of values to be selected is known and the number is limited. For example, if the query is modified as shown, index range scan is employed.

SQL>select * from students where result in ('F', 'T');
select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------
SQL_ID 672mnj9pggkq7, child number 0
-------------------------------------
select * from students where result in ('F', 'T')

Plan hash value: 2871222462
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 71 | 355 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | STUDENTS_IDX | 71 | | 1 (0)| 00:00:01 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("RESULT"='F' OR "RESULT"='T'))

Second,   manually rewrite the query to make sure that both component queries can take advantage of an index range scan. This technique  can be applied if the values are unknown or the number of values to be specified is too high.   Hence, if  the query is rewritten as shown, it will be able to to take advantage of the or expansion query transformation:

SQL>select * from students where result < 'P'
    union all
    select * from students where result > 'P' ;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------- 
SQL_ID gqrp063y9c5a5, child number 0
-------------------------------------
select * from students where result < 'P' union all select * from
students where result > 'P'

Plan hash value: 2171568329
--------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------- 
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 76 | 380 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | STUDENTS_IDX | 76 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 36 | 180 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | STUDENTS_IDX | 36 | | 1 (0)| 00:00:01 |
--------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RESULT"<'P')
5 - access("RESULT">'P')

– Third technique: We can create function based index using decode / case and restructure our query to use decode / case 

In this technique, we will create a function based index using decode or case which will return two distinct values when the the condition is satisfied / not satisfied. Subsequently, we will restructure or query to use decode / case.

Using decode

SQL>create index students_decode_idx  
    on students (decode (result, 'P', 'Passed', 'Not Passed'));
    select * from students 
    where decode (result, 'P', 'Passed', 'Not Passed') = 'Not Passed';

select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 2ddr5p1t2q7ax, child number 0
-------------------------------------
select * from students where decode (result, 'P', 'Passed', 'Not
Passed') = 'Not Passed'

Plan hash value: 3076565877
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | STUDENTS_DECODE_IDX  | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("STUDENTS"."SYS_NC00004$"='Not Passed')

Using Case

SQL>create index students_case_IDX 
      on students (CASE result
                    WHEN 'P' THEN 'Passed'
                    ELSE 'Not Passed' END);

select * from students
where (CASE result
         WHEN 'P' THEN 'Passed'
          ELSE 'Not Passed' END) = 'Not Passed';

select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------SQL_ID 6pqgppzp47tzb, child number 0
-------------------------------------
select * from students where (CASE result WHEN 'P' THEN
'Passed' ELSE 'Not Passed' END) = 'Not Passed'

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

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STUDENTS"."SYS_NC00004$"='Not Passed')

The fourth technique simply forces an index full scan with, for example, the index hint. From a performance point of view, it’s not optimal,as, for a query with very strong selectivity, full index has to be scanned.

SQL>SELECT /*+ index(students) */ * FROM students where result != 'P';
select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------- 
SQL_ID 2hyrf6n7kb8pr, child number 0
-------------------------------------
SELECT /*+ index(students) */ * FROM students where result != 'P'

Plan hash value: 635752001
---------------------------------------------------------  
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------- 
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| STUDENTS | 71 | 355 | 2 (0)| 00:00:01|
|* 2 | INDEX FULL SCAN | STUDENTS_IDX | 71 | | 1 (0)| 00:00:01 |
--------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RESULT"<>'P')

Conclusion:

In cases where the inequality condition having a strong selectivity is notable to make use of an index, we can advantage of an index using following three techniques : 

  • First, the inequality condition can be rewritten into an IN condition. This is an option only when the number of values to be selected is known and the number is limited.
  • Second,   manually rewrite the query to make sure that both component queries can take advantage of an index range scan. This technique  can be applied if the values are unknown or the number of values to be specified is too high.
  • Third ,  create function based index using decode / case and restructure the query to use decode / case 
  • The fourth technique simply forces an index full scan with, for example, the index hint. From a performance point of view, it’s not optimal,as, for a query with very strong selectivity, full index has to be scanned.

References:

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

Related links:

Home
Tuning Index

————————-

 

Not NULL Constraint Influences Access Path

The optimizer can make use of explicitly defined Not NULL constraints to take advantage
of an index in order to avoid a full table scan since a B-tree index stores only not NULL values .
When  count (constant) or count(*)  is queried,  we want to count no. of rows in the table. Hence , if there is a column which is defined as not NULL and has an index on it, the number of index entries  in the index are bound to be same as the number of rows. The query optimizer uses the index to count no. of rows in the table.

Similarly, when  a count (not-nullable-column) is queried,  we want to count the no. of rows having not null values in the column. Since the column  has a not NULL constraint on it, every row in the table will have a not null value in it and count(not-nullable-column) is  same as count(*). As a result, the query optimizer can use  the index on the column to process the query.
In fact, in both the cases above, any B-tree containing at least a not-nullable column can serve the purpose.

When a count (nullable-column) is queried, we want to count the no. of rows having not null values in the column. If we have an index on the column, the index will store only not NULL values and hence can be effectively used by  the query optimizer to give the result.
In fact, the optimizer can use any index containing the nullable column for this purpose.

To demonstrate the above functionality, I have created a  table HR.TEST with two columns – NOTNULL having not NULL constraint
NULLABLE
. having same data as column NOTNULL but has not been declared not NULL
. has a B-tree index on it

SQL>drop table hr.test purge;
    create table hr.test (notnull number not null, nullable number);
    insert into hr.test select rownum, rownum from all_tables;
    create index hr.test_idx on hr.test(nullable);
    exec dbms_stats.gather_table_stats ('HR','TEST', cascade => true);

Now I will query count for various arguments and check if optimizer can use the index on NULLABLE column.

Note that to process count(*),  count(1) and   count(notnull), the query optimizer uses Full Table Scan. Although the column NULLABLE has non-null values in all the rows but since it has not been explicitly declared not null , the  optimizer does not know that no. of entries in index reflect the count correctly and hence does not use the index .

SQL>select count(*) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 1mat065c25crk, child number 0
-------------------------------------
select count(*) from hr.test

Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 108 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

SQL>select count(1) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID gzpsn7ff3ncmc, child number 0
-------------------------------------
select count(1) from hr.test

Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 108 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

SQL>select count(notnull) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 6kxdzxbac62b4, child number 0
-------------------------------------
select count(notnull) from hr.test

Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 108 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

To process count(nullable), the optimizer uses index on column NULLABLE because we want to count not null values in column nullable and Btree index stores only not null values.

SQL> select count(nullable) from hr.test;
     select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID bz8rxw5rmmv8g, child number 0
-------------------------------------
select count(nullable) from hr.test

Plan hash value: 2983066704
-------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FAST FULL SCAN| TEST_IDX | 2784 | 11136 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

Now I will declare not NULL constraint on  column NULLABLE.

SQL> alter table hr.test modify (nullable not null);

Now if query count(*), count(1), count(notnull) and count(nullable), the optimizer is able to avoid Full Table Index by making  use of the index  on NULLABLE column in all the cases . Since the column NULLABLE having index has been declared not null and optimizer knows that entries in the index represent all the rows of the table.

SQL>select count(*) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 1mat065c25crk, child number 0
-------------------------------------
select count(*) from hr.test

Plan hash value: 2983066704
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| TEST_IDX | 2784 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------
SQL>select count(1) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------- 
SQL_ID gzpsn7ff3ncmc, child number 0
-------------------------------------
select count(1) from hr.test

Plan hash value: 2983066704
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| TEST_IDX | 2784 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

SQL>select count(notnull) from hr.test;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------- 
SQL_ID 6kxdzxbac62b4, child number 0
-------------------------------------
select count(notnull) from hr.test

Plan hash value: 2983066704
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| TEST_IDX | 2784 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

SQL> select count(nullable) from hr.test;
     select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------- 
SQL_ID bz8rxw5rmmv8g, child number 0
-------------------------------------
select count(nullable) from hr.test

Plan hash value: 2983066704
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| TEST_IDX | 2784 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------

A naughty trick

Since null values are not stored in the single column indexes,  a single column index cannot be used to search NULL values. To demonstrate this, I will remove non NULL constraint from column NULLABLE .

SQL>alter table hr.test modify (nullable null);

Now when I  query the rows having NULL values in column NULLABLE, we can see that Full table scan is used as the index on column NULLABLE does not contain NULL values .

SQL>select * from hr.test where nullable is null;
    select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 4w1kz5f6uh43x, child number 0
-------------------------------------
select * from hr.test where nullable is null

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

Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter("NULLABLE" IS NULL)

Now I will use the fact that  even NULL values are  stored in a composite index when at least one of the columns isn’t nullable. I am creating a multi column index on column NULLABLE and a dummy second column containing a not null value,  so that the index will store even NULL values in column NULLABLE. To keep the size of the index  small, I will assign  the value zero to the dummy column.

SQL>drop index hr.test_idx;
    create index hr.test_idx on hr.test(nullable, 0);

Now when I  query the rows having NULL values in column NULLABLE, we can see that  the multi column index is used as it stores NULL values  in column   NULLABLE.

SQL>select * from hr.test where nullable is null;
    select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 4w1kz5f6uh43x, child number 0
-------------------------------------
select * from hr.test where nullable is null

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

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("NULLABLE" IS NULL)

Conclusion:
To enable the optimizer to choose index access in relevant cases,
–  declare NOT NULL constraint on relevant columns or
– create a multi column index on  nullable column  and a dummy second column

References:
Troubleshooting Oracle Performance (second edition ) by Christian Antognini
—————————————————————————————————————

Related links:

Home
Tuning Index

————————-