Category Archives: Tuning

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

Tuning, Uncategorized

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

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

Tuning, Uncategorized

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

Tuning, Uncategorized

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

Create Histograms On Columns That Already Have One

Database, Tuning, Uncategorized

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

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

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

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

Let’s demonstrate:

– Create a table with skewed data distribution in two columns

SQL>drop table hr.skewed purge;

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

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

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

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

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

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

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

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

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

col table_name for a10
col column_name for a10

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

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

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

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

col table_name for a10
col column_name for a10

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

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

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

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

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

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

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

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

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

Well, there are two options:

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

– Delete unwanted histogram for SALARY column

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

-- Verify that histogram for salary column has been deleted

col table_name for a10
col column_name for a10

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

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

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

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

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

col table_name for a10
col column_name for a10

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

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

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

– Delete histograms on all columns 

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

– Verify that histograms on all columns have been dropped

SQL>col table_name for a10
col column_name for a10

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

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

– Create histogram only on the desired JOB_ID column

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

– Verify that histogram has been created on JOB_ID

SQL>col table_name for a10
col column_name for a10

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

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

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

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

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

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

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

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

–  Get Current value –

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

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

– Set preference to REPEAT–

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

– Verify –

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

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

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

I hope this post is useful.

Happy reading….

References:

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

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

Related Links:

Home

Database Index
Tuning Index

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

Influence execution plan without adding hints

Tuning, Uncategorized

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

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

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

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

SQL>onn hr/hr

drop table customer purge;
drop table product purge;

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

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

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

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

SQL>conn / as sysdba
    alter system flush shared_pool;

    conn hr/hr

    variable prod_id number
    exec :prod_id := 1000

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

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

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

Plan hash value: 3134146364

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

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

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

    col sql_text for a35 word_wrapped
    col enabled for a15

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

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

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

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

    col sql_text for a35 word_wrapped
    col enabled for a15

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

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

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

SQL>conn hr/hr

variable prod_id number
exec :prod_id := 1000

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

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

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

Plan hash value: 4263155932

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

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

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

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

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

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

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

select cust_name, prod_name         SQL_7d3369334b24a117                            SQL_PLAN_7ucv96d5k988rfe19664b                                                   NO

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

SQL>variable prod_id number
exec :prod_id := 1000

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

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

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

Plan hash value: 4263155932

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

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

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

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

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

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

I hope this post was useful.

Your comments and suggestions are always welcome!

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

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

Related links:

HOME
Tuning Index

PARALLEL_ADAPTIVE_MULTI_USER

Database, Tuning, Uncategorized,

One of the  initialization parameters that influence the number of slave processes assigned to a server process is parallel_adaptive_multi_user.

It accepts two values:

• FALSE (default value in Oracle9i): If the pool is not exhausted, the server process is assigned the requested number of slave processes .

• TRUE (default value from Oracle Database 10g onwards): As the number of  slave processes already in use increases, the requested degree of parallelism is automatically reduced, even if there are still enough servers in the pool to satisfy the required degree of parallelism.

Let’s demonstrate ..

Overview:

– parallel_max_servers = 40

– Set parallel_adaptive_multi_user = false
– Execute a query requesting  DOP of 8 concurrently in 1, 2, 3, 4, 5 and 6 sessions and note down the no. of slave processes allocated in each session for each concurrent execution.
– Verify that
. for no.  of concurrent sessions <= 5, each session gets requested no. of slave processes i.e. 8
. for no.  of concurrent sessions > 5, first 5 sessions get 8 slave processes each but query in any subsequent session is executed serially i.e. it is silently downgraded.

– Set parallel_adaptive_multi_user = true
– Execute a query requesting  DOP of 8 concurrently in 1, 2, 3, and 4 sessions and note down the no. of slave processes allocated in each session for each concurrent
– Verify that
. for no.  of concurrent sessions  <= 2, each session gets requested no. of slave processes i.e. 8
. for no.  of concurrent sessions  =  3,
– first 2 sessions get 8 slave processes each
-Third session  gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
. for no.  of concurrent sessions > 3,
– first 2 sessions get 8 slave processes each
-Third session  gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
– The query in any subsequent session is executed serially i.e. it is silently downgraded.

Implementation :
———— PARALLEL_ADAPTIVE_MULTIUSER = FALSE —————————
– Create test table temp_sales with dictionary DOP = 4

SQL> drop table sh.temp_sales purge;
create table sh.temp_sales as select *    from sh.sales;
alter table sh.temp_sales parallel 4;

– Set parallel_min_percent=0 and restart the database

SQL> alter system set parallel_min_percent=0 scope=spfile;
              shu immediate;              startup SQL> select * from v$px_process_sysstat               where statistic like '%Server%'; STATISTIC                           VALUE  ------------------------------ ----------  Servers In Use                          0  Servers Available                       8  Servers Started                         0  Servers Shutdown                        0  Servers Highwater                       0  Servers Cleaned Up                      0  Server Sessions                         0

– Check that parallel_max_servers = 40

SQL> sho parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
parallel_max_servers                 integer     40

– check that parallel_adaptive_multi_user   =    FALSE

SQL> sho parameter parallel_adaptive

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
parallel_adaptive_multi_user         boolean     FALSE

– Issue the following query requesting DOP = 8 in one session --

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

– Check that the query gets requested no. of slave processes (8)

SQL> select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

-- check the highwater  of parallel processes started = 8–

select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                         0
Servers Shutdown                        0
Servers Highwater                       8
Servers Cleaned Up                      0
Server Sessions                         8

-- Issue the following query requesting DOP = 8 in two sessions –
-- Check that both the sessions get requested no. of slave processes (8)

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– check that
      . 8 more parallel processes have started (Servers Started  =  8)
      . 16 parallel processes are available now (Servers Available = 16)          
      . Servers highwater has been updated to 16

select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      16
Servers Started                         8
Servers Shutdown                        0
Servers Highwater                      16
Servers Cleaned Up                      0
Server Sessions                        24

– Issue the following query requesting DOP = 8 in three sessions –
– Check that all the sessions get requested no. of slave processes (8)

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– check that
      .8 more parallel processes have started (Servers Started  =  16)
      . 24 parallel processes are available now (Servers Available = 24)          
      . Servers highwater has been updated to 24

SQL>select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      24
Servers Started                        16
Servers Shutdown                        0
Servers Highwater                      24
Servers Cleaned Up                      0
Server Sessions                        48

– Issue the following query requesting DOP = 8 in four sessions –
– Check that all the sessions get requested no. of slave processes (8)

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– check that
      .8 more parallel processes have started (Servers Started  =  24)
      . 32 parallel processes are available now (Servers Available = 32)          
      . Servers highwater has been updated to 32

SQL>select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      32
Servers Started                        24
Servers Shutdown                        0
Servers Highwater                      32
Servers Cleaned Up                      0
Server Sessions                        80

– Issue the following query requesting DOP = 8 in five sessions –
– Check that all the sessions get requested no. of slave processes (8)

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– check that
      . 8 more parallel processes have started (Servers Started  =  32)
      . 40 parallel processes are available now (Servers Available = 40)          
      . Servers highwater has been updated to40

SQL>select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      40
Servers Started                        32
Servers Shutdown                        0
Servers Highwater                      40
Servers Cleaned Up                      0
Server Sessions                       120

– Issue the following query requesting DOP = 8 in five sessions –
– In first 5 sessions  Server process gets requested no. of slaves (8):

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

-- In 6th session, the query gets executed serially

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          0

– check that
      . No more parallel processes have started (Servers Started  =  32 as  earlier)
      . 40 parallel processes are available as earlier (Servers Available = 40)          
      . Servers highwater  is at the same value as earlier i.e. 40

SQL>select * from v$px_process_sysstat
    where statistic like '%Server%'; STATISTIC                           VALUE ------------------------------ ---------- 
Servers In Use                          0
Servers Available                      40 
Servers Started                        32 
Servers Shutdown                        0 
Servers Highwater                      40 
Servers Cleaned Up                      0 
Server Sessions                       160

The observations for PARALLEL_DAPTIVE_MULTI_USER = FALSE can be tabulated as follows:

PARALLEL_ADAPTIVE_MULTI_USER = FALSE
|————– No. of slave processes assigned ————–|
No. of sessions        sess1         sess2         sess3      sess4      sess5      sess6
———————-            ———        ——–        ———      ——–      ——–       ——–

          1                                    8                   –                   –                 –                –                 -

          2                                    8                   8                   –                 –                –                 -

          3                                    8                   8                   8                 –                –                 -

          4                                    8                   8                   8                 8                –                 -

          5                                    8                   8                   8                 8                8                 -

          6                                    8                   8                   8                 8                8                 0

  Hence, it can be seen that
. for no.  of concurrent sessions <= 5, each session gets requested no. of slave processes i.e. 8 so that total px servers allocated =  parallel_max_servers = 40
. for no.  of concurrent sessions > 5, first 5 sessions get 8 slave processes each but query in any subsequent session is executed serially i.e. it is silently downgraded as maximum no. of px slaves have already been used.
Hence, when PARALLEL_ADAPTIVE_MULTI_USER = FALSE, as many px slaves are allocated as are requested as long as they are available.

————- PARALLEL_ADAPTIVE_MULTI_USER = TRUE —————————

– Set parallel_adaptive_multi_user = true and restart the database

SQL>Alter system set parallel_adaptive_multi_user = true;
    shu immediate; 
    startup             
    sho parameter parallel_adaptive NAME                                 TYPE        VALUE  -------------------------------  ----------- -----------------  
parallel_adaptive_multi_user         boolean     TRUE

– Issue the following query requesting 8 px slaves in one session –
– Check that the  Server process gets requested no. of slaves (8)
– While the query is executing , go to next step and check that Servers in  use = 8 and servers highwater = 8

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          8

SQL>select * from v$px_process_sysstat
      where statistic like '%Server%';

STATISTIC                           VALUE
 ------------------------------ ----------
 Servers In Use                          8
 Servers Available                       0
 Servers Started                         0
 Servers Shutdown                        0
 Servers Highwater                       8
 Servers Cleaned Up                      0
 Server Sessions                         8

– Issue the following query requesting 8 px slaves in two sessions –
– Check that the  both the sessions get requested no. of slaves (8)

– While the query is executing , go to next step and check that Servers in  use = 16 and servers highwater = 16

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          8

SQL>select * from v$px_process_sysstat
      where statistic like '%Server%';

STATISTIC                           VALUE
 ------------------------------ ----------
 Servers In Use                         16
 Servers Available                       0
 Servers Started                         8
 Servers Shutdown                        0
 Servers Highwater                      16
 Servers Cleaned Up                      0
 Server Sessions                        24

– Issue the following query requesting 8 px slaves in three sessions –
– Check that the first two sessions get requested no. of slaves (8)
                                the third session get 6 slaves only

– While the query is executing , go to next step and check that Servers in  use = 22 and servers highwater = 22 (8 + 8 + 6)

– In first 2 sessions :

SQL> conn sh/sh
      select count(*) from sh.temp_sales s1, sh.temp_sales s2
      where rownum < 10000000;

    select statistic, last_query from v$pq_sesstat
           where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          8

In third session :

SQL> conn sh/sh
      select count(*) from sh.temp_sales s1, sh.temp_sales s2
      where rownum < 10000000;

    select statistic, last_query from v$pq_sesstat
           where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          6

SQL>select * from v$px_process_sysstat
            where statistic like '%Server%';

STATISTIC                           VALUE
 ------------------------------ ----------
 Servers In Use                         22
 Servers Available                       0
 Servers Started                        14
 Servers Shutdown                        0
 Servers Highwater                      22
 Servers Cleaned Up                      0
 Server Sessions                        46

– Issue the following query requesting 8 px slaves in four sessions –
  – Check that the first two sessions get requested no. of slaves (8)
                                the third session get 6 slaves only 
                               the fourth session does not get any slaves

– While the query is executing , go to next step and check that Servers in  use = 22 and servers highwater = 22 (8 + 8 + 6) as earlier
– In first 2 sessions :

SQL> conn sh/sh
select count(*) from sh.temp_sales s1, sh.temp_sales s2
where rownum < 10000000;

select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                          8

– in 3rd session:

SQL> conn sh/sh
      select count(*) from sh.temp_sales s1, sh.temp_sales s2
      where rownum < 10000000;

     select statistic, last_query from v$pq_sesstat
          where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
 Server Threads                          6

– In 4th session:

SQL> conn sh/sh
      select count(*) from sh.temp_sales s1, sh.temp_sales s2
      where rownum < 10000000;

     select statistic, last_query from v$pq_sesstat
          where statistic='Server Threads';

STATISTIC                      LAST_QUERY
 ------------------------------ ----------
Server Threads                          0

SQL>select * from v$px_process_sysstat
      where statistic like '%Server%';

STATISTIC                           VALUE
 ------------------------------ ----------
 Servers In Use                         22
 Servers Available                       0
 Servers Started                        14
 Servers Shutdown                        0
 Servers Highwater                      22
 Servers Cleaned Up                      0
 Server Sessions                        68

The observations for PARALLEL_DAPTIVE_MULTI_USER = FALSE can be tabulated as follows:

PARALLEL_ADAPTIVE_MULTI_USER = FALSE
                                      |—- No. of slave processes assigned –|
No. of sessions        sess1         sess2         sess3      sess4     
———————-            ———        ——–        ———      ——–     

          1                                    8                   –                   –                 –

          2                                    8                   8                   –                 –

          3                                    8                   8                  6                 –

          4                                    8                   8                  6                 0              

  Hence, it can be seen that if same query is issued in all the sessions,
. for no.  of concurrent sessions  <= 2, each session gets requested no. of slave processes i.e. 8
. for no.  of concurrent sessions  =  3,
– first 2 sessions get 8 slave processes each
-Third session  gets 6 slave processes i.e DOP gets silently downgraded even requested no. of slaves can be provided
. for no.  of concurrent sessions > 3,
– first 2 sessions get 8 slave processes each
-Third session  gets 6 slave processes i.e DOP gets silently downgraded even though 34 servers are available
– The query in any subsequent session is executed serially i.e. it is silently downgraded.

 

Hence, when PARALLEL_ADAPTIVE_MULTI_USER = true, DOP of the a SQL statements is downgraded automatically  so that px slaves can be assigned to other SQL statements which might be issued in other sessions.

References:

Troubleshooting Oracle Performance by Christian Antognini

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

Related Links :

Home

Automatic Degree Of Parallelism – Part – I
Automatic Degree Of Parallelism – Part – II

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

PARALLEL_MIN_PERCENT

Database, Tuning, Uncategorized

The Oracle server maintains a “pool” of parallel slave processes available for parallel operations. The Database configuration parameters PARALLEL_MIN_SERVERS and PARALLEL_MAX_SERVERS determine the initial and maximum size of the pool.  The number of slave processes provided to a query coordinator may be less than requested by it, due to system load or other parallel processes which are using the parallel server pool. For example, if the maximum number of slave processes is set to 40 and an  execution plan requires eight slave processes, only 5 concurrent SQL statements (40/8) can be executed with the required degree of parallelism. When the limit is reached, there are two possibilities:

– either the degree of parallelism is downgraded (in other words, reduced) or

– an error (ORA-12827: insufficient parallel query slaves available) is returned to the server process.

To configure which one of these two possibilities is used, you have to set the initialization parameter parallel_min_percent. It can be set to an integer value ranging from 0 to 100. There are three main situations:

• 0: This value (which is the default) specifies that if requested no. of slave processes are not available,  the degree of parallelism of the statement can be silently downgraded.  If less than two slave processes are available, the statement might even execute serially. The error ORA-12827 is never raised .

• 1–99: The values ranging from 1 to 99 define a limit for the downgrade. If no. of available slave processes is less than the specified percentage of the requested slave processes , the error ORA-12827 is raised.
For example, if it is set to 25 and 16 slave processes are requested, at least 4 (16*25/100) must be provided to avoid the error.

• 100: With this value, If no. of available slave processes is less than the no. of  requested slave processes , the error ORA-12827 is raised.

Let’s demonstrate …

– Find out the value of parallel_max_servers

SQL> sho parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
parallel_max_servers                 integer     40

– Let’s  set parallel_min_servers to a value < parallel_max_servers i.e. 8

SQL> alter system set parallel_min_servers=8;

– Restart the database

SQL>shu immediate;
startup

– Initially only 8 px servers (= parallel_min_servers) are started

SQL>  select * from v$px_process_sysstat
           where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                       8
Servers Started                         0
Servers Shutdown                        0
Servers Highwater                       0
Servers Cleaned Up                      0
Server Sessions                         0

– Initially all the statistics related to parallel execution show values
   as 0

SQL>select name, value from v$sysstat
where upper(name) like '%PARALLEL OPERATIONS%'
or upper(name) like '%PARALLELIZED%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
queries parallelized                                                      0
DML statements parallelized                                               0
DDL statements parallelized                                               0
DFO trees parallelized                                                    0
Parallel operations not downgraded                                        0
Parallel operations downgraded to serial                                  0
Parallel operations downgraded 75 to 99 pct                               0
Parallel operations downgraded 50 to 75 pct                               0
Parallel operations downgraded 25 to 50 pct                               0
Parallel operations downgraded 1 to 25 pct                                0

-—————- PARALLEL_MIN_PERCENT = 0 ————————

– Check that currently, parallel_min_percent is set to its default value of 0

SQL> sho parameter parallel_min_p

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent                 integer     0

– Let’s create a test table  –

SQL> drop table sh.temp_sales purge;
create table sh.temp_sales as select *    from sh.sales;

– Let’s modify dictionary DOP of temp_sales to a value > parallel_max_servers (40)

SQL>alter table sh.temp_sales parallel 50;

– Let’s execute a query on temp_sales 

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/

– check that the query uses 40 px processes = parallel_max_servers
   i.e. DOP of the statement has been silently downgraded from requested value
   of 50 to 40 as parallel_min_percent= 0

SQL> select statistic, last_query from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY
------------------------------ ----------
Server Threads                         40

– Note that 1 query has been parallelized so far and its parallelization
   operation has been downgraded (1 to 25) % (50 to 40 i.e. 20%)

SQL>select name, value from v$sysstat
where upper(name) like '%PARALLEL OPERATIONS%'
or upper(name) like '%PARALLELIZED%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
queries parallelized                                                      1
DML statements parallelized                                               0
DDL statements parallelized                                               0
DFO trees parallelized                                                    1
Parallel operations not downgraded                                        0
Parallel operations downgraded to serial                                  0
Parallel operations downgraded 75 to 99 pct                               0
Parallel operations downgraded 50 to 75 pct                               0
Parallel operations downgraded 25 to 50 pct                               0
Parallel operations downgraded 1 to 25 pct                                1

– check that 32 more  px servers were started in addition earlier 8 (servers started has increasesd    from 8 to 32)  and 40 of them are available (servers available = 40).    Servers highwater has been updated to 40

SQL>  select * from v$px_process_sysstat
where statistic like '%Server%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          0
Servers Available                      40
Servers Started                        32
Servers Shutdown                        0
Servers Highwater                      40
Servers Cleaned Up                      0
Server Sessions                        40

----------------- PARALLEL_MIN_PERCENT = 50 ------------------------

– Let’s set parallel_min_percent = 50 

SQL> alter system set parallel_min_percent=50 scope=spfile;

shu immediate;
startup

SQL> sho parameter parallel_min_percent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent                 integer     50

– check that query executes with DOP = 40 as
   requested DOP * (parallel_min_percent/100) = 50 *(50/100) = 25
   and upto maximum of 40 servers  (parallel_max_servers)    are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/

select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

– Let’s change dictionary DOP of temp_sales to 82 

SQL> alter table sh.temp_sales parallel 82;

– Let’s re execute the earlier query
– It can be seen that we get error message ORA-12827 as
   requested DOP * (parallel_min_percent/100) = 82 *(50/100) = 41
   but maximum of only 40 servers  (parallel_max_servers)   are available 

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
begin
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves available
ORA-06512: at line 2

– Let’s change dictionary DOP of temp_sales to 80 

SQL> alter table sh.temp_sales parallel 80;

— Let’s re execute the earlier query
— It can be seen that query executes with DOP of 40  as
requested DOP * (parallel_min_percent/100) = 80 *(50/100) = 40
and maximum of 40 servers  (parallel_max_servers)    are available

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

Thus PARALLEL_MIN_PERCENT = 1 to 99  ensures that
if specified percent of the requested px servers are available
        a SQL statement will be parallelized  
Else,
   ORA-12827 will be returned

-—————- PARALLEL_MIN_PERCENT = 100 ————————
– Let’s set parallel_min_percent to 100 so that query will be parallelized only if available px servers >= requested no. of px servers

SQL>alter system set parallel_min_percent=100 scope=spfile;
shu immediate;
startup;

– Currently  Dictionary DOP = 80, parallel_max_servers = 40

– Let’s execute the same query again

– Since maximum no. of available px servers (40) < requested no. (80), we get ORA-12827

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
begin
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves available
ORA-06512: at line 2

– Let’s change dictionary DOP of temp_sales to 40 (=parallel_max_servers) so that available servers = requested

SQL> alter table sh.temp_sales parallel 40;

– Let’s re execute the earlier query
– It can be seen that the query executes with DOP = 40
   requested DOP * (parallel_min_percent/100) = 40 *(100/100) = 40
   and upto maximum of 40 servers  (parallel_max_servers)    are available 

SQL> begin
execute immediate 'select * from sh.temp_sales';
end;
/
select * from v$pq_sesstat
where statistic='Server Threads';

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Server Threads                         40             0

References:

Troubleshooting Oracle Performance by Christian Antognini

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

Related Links :

Home

Automatic Degree Of Parallelism – Part – I
Automatic Degree Of Parallelism – Part – II

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