Cursor_Sharing Practice
We will be doing practice session for Cursor_Sharing and other related cursor operations.
Parent and Child cursor details
We will fetch details for Parent and Child cursor details as per below examples.
Login to sh schema and execute below Query.
[oracle@ace2oracledb ~]$ sqlplus sh/sh
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> SELECT COUNT(*) FROM customers;
COUNT(*)
----------
55500
SQL> exit
Login to oe schema and execute below Query.
[oracle@ace2oracledb ~]$ sqlplus oe/oe
SQL> SELECT COUNT(*) FROM customers;
COUNT(*)
----------
319
SQL> exit
As both statement are textually same ,hence There will be 1 parent cursor and 2 Child cursor for the same (Child 0 and Child 1).
To get Details Child
SQL> COL SQL_TEXT FORMAT a30
COL CHILD# FORMAT 99999
COL EXEC FORMAT 9999
COL SCHEMA FORMAT a6
SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT,
CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL
WHERE SQL_TEXT LIKE '%ustom%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID;SQL> SQL> SQL> SQL> 2 3
SQL_ID SCHEMA SQL_TEXT CHILD# EXEC
------------- ------ ------------------------------ ------ -----
8h916vv2yw400 SH SELECT COUNT(*) FROM customers 1 1
8h916vv2yw400 OE SELECT COUNT(*) FROM customers 0 1
To get Details of Parent
COL SQL_TEXT FORMAT a30
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM V$SQLAREA
WHERE SQL_TEXT LIKE '%ustom%'
AND SQL_TEXT NOT LIKE '%SQL_TEXT%';SQL> SQL> 2 3 4
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
------------------------------ ------------- ------------- ----------
SELECT COUNT(*) FROM customers 8h916vv2yw400 2 3320713216
SQL>
Here VERSION_COUNT 2 means there are 2 child exists for 8h916vv2yw400.
Check Why Childs are Not shared if there are multiple children for a parent Cursor
SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string EXACT If a parent cursor has multiple children, then the V$SQL_SHARED_CURSOR view provides information about why the cursor was not shared. 1 SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS, 2 C.TRANSLATION_MISMATCH 3 FROM V$SQL S, V$SQL_SHARED_CURSOR C 4 WHERE SQL_TEXT LIKE '%ustom%' 5 AND SQL_TEXT NOT LIKE '%SQL_TEXT%' 6* AND S.CHILD_ADDRESS = C.CHILD_ADDRESS SQL> / SQL_TEXT CHILD_NUMBER CHILD_ADDRESS TRANSLATION_MISMATCH ------------------------------ ------------ ---------------- ---------------------------------------- SELECT COUNT(*) FROM customers 0 0000000071104810 N SELECT COUNT(*) FROM customers 1 0000000074BF9138 Y As we can see above children are not shared due to TRANSLATION_MISMATCH. now let we have another example of OPTIMIZER_MODE_MISMATCH as below => Login from hr schema and execute below statement. [oracle@ace2oracledb ~]$ sqlplus hr/hr SQL> ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS; SELECT salary FROM employees WHERE employee_id < 900; Session altered. SQL> SALARY ---------- 24000 17000 .. ... SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string EXACT SQL > exit => Login again from hr schema and execute below statement with changed value of OPTIMIZER_MODE as below. [oracle@ace2oracledb ~]$ sqlplus hr/hr SQL> ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS; SELECT salary FROM employees WHERE employee_id < 900; Session altered. SQL> SALARY ---------- 24000 17000 ... ... SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string EXACT SQL > exit => Now let we verify details and check if children are sharing same Child cursor. Details of child SQL> COL SQL_TEXT FORMAT a30 COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL SCHEMA FORMAT a6 SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL WHERE SQL_TEXT LIKE '%employee_id%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID; SQL> SQL> SQL> SQL> 2 3 SQL_ID SCHEMA SQL_TEXT CHILD# EXEC ------------- ------ ------------------------------ ------ ----- gsyjx69s0f7xn HR SELECT salary FROM employees W 1 1 HERE employee_id < 900 gsyjx69s0f7xn HR SELECT salary FROM employees W 0 1 HERE employee_id < 900 Details of Parent SQL> COL SQL_TEXT FORMAT a30 SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%employee_id%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL> 2 3 4 SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE ------------------------------ ------------- ------------- ---------- SELECT salary FROM employees W gsyjx69s0f7xn 2 1879515060 HERE employee_id < 900 Reason Why child cursor not shared. SELECT S.SQL_TEXT, S.CHILD_NUMBER, s.CHILD_ADDRESS, C.TRANSLATION_MISMATCH,C.OPTIMIZER_MODE_MISMATCH FROM V$SQL S, V$SQL_SHARED_CURSOR C WHERE SQL_TEXT LIKE '%employee_id%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' AND S.CHILD_ADDRESS = C.CHILD_ADDRESS; SQL> SQL_TEXT CHILD_NUMBER CHILD_ADDRESS TRANSLATION_MISMATCH OPTIMIZER_MODE_MISMATCH ------------------------------ ------------ ---------------- ---------------------------------------- ---------------------------------------- SELECT salary FROM employees W 0 00000000614834E0 N N HERE employee_id < 900 SELECT salary FROM employees W 1 00000000614892F8 N Y HERE employee_id < 900 SQL> As we can see above children are not shared due to OPTIMIZER_MODE_MISMATCH.
Advantages of Bind Variables Over Literals
Using Bind variables in SQL Query can reduce child cursor generation and hence reduce overhead on Library cache and data dictionary , resulting reduction in Latches.
Bind variables are essential to cursor sharing in Oracle database applications.
Examples using Literals
=> Login using HR schema and execute below Queries.
SQL> SELECT /* test_emp */ SUM(salary) FROM employees WHERE employee_id < 101;
SUM(SALARY)
-----------
24000
SQL> SELECT /* test_emp */ SUM(salary) FROM employees WHERE employee_id < 120;
SUM(SALARY)
-----------
163308
SQL> SELECT /* test_emp */ SUM(salary) FROM employees WHERE employee_id < 165;
SUM(SALARY)
-----------
445608
SQL>
=> Let we check CHILDS for this SQL
COL CHILD# FORMAT 99999
COL EXEC FORMAT 9999
COL SCHEMA FORMAT a6
SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT,
CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL
WHERE SQL_TEXT LIKE '%test_emp%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID;
SQL> SQL> SQL> SQL> 2 3
SQL_ID SCHEMA SQL_TEXT CHILD# EXEC
------------- ------ ------------------------------ ------ -----
cn9dw030ynx80 HR SELECT /* test_emp */ SUM(sala 0 1
ry) FROM employees WHERE emplo
yee_id < 120
g00cmpyjfyrqt HR SELECT /* test_emp */ SUM(sala 0 1
ry) FROM employees WHERE emplo
yee_id < 101
g3dr5y0y7u9cn HR SELECT /* test_emp */ SUM(sala 0 1
ry) FROM employees WHERE emplo
yee_id < 165
SQL_ID SCHEMA SQL_TEXT CHILD# EXEC
------------- ------ ------------------------------ ------ -----
=> Let we check Parent for this SQL
SQL> SQL>
SQL> COL SQL_TEXT FORMAT a30
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM V$SQLAREA
WHERE SQL_TEXT LIKE '%test_emp%'
AND SQL_TEXT NOT LIKE '%SQL_TEXT%';
SQL> 2 3 4
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
------------------------------ ------------- ------------- ----------
SELECT /* test_emp */ SUM(sala cn9dw030ynx80 1 3253368064
ry) FROM employees WHERE emplo
yee_id < 120
SELECT /* test_emp */ SUM(sala g3dr5y0y7u9cn 1 1014834580
ry) FROM employees WHERE emplo
yee_id < 165
SELECT /* test_emp */ SUM(sala g00cmpyjfyrqt 1 2733596377
ry) FROM employees WHERE emplo
yee_id < 101
SQL>
As we can see using Different Literal values will cause to creation of different Parent cursor ,Even complete SQL TEXT is same execept literal values.
Let we use same example using Bind variable as below
[oracle@ace2oracledb ~]$ sqlplus hr/hr
SQL> VARIABLE emp_id NUMBER
SQL> EXEC :emp_id := 101;
SQL>SELECT /*test_bind*/ SUM(salary) FROM employees WHERE employee_id < :emp_id;
PL/SQL procedure successfully completed.
SQL>
SUM(SALARY)
-----------
24000
SQL>EXEC :emp_id := 120;
SQL>SELECT /*test_bind*/ SUM(salary) FROM employees WHERE employee_id < :emp_id;
PL/SQL procedure successfully completed.
SUM(SALARY)
-----------
163308
SQL>EXEC :emp_id := 165;
SQL>SELECT /*test_bind*/ SUM(salary) FROM employees WHERE employee_id < :emp_id;
PL/SQL procedure successfully completed.
SUM(SALARY)
-----------
445608
=> Let we check CHILDS for this SQL
SQL> COL SQL_TEXT FORMAT a30
COL CHILD# FORMAT 99999
COL EXEC FORMAT 9999
COL SCHEMA FORMAT a6
SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT,
CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL
WHERE SQL_TEXT LIKE '%test_bind%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID;
SQL>
SQL_ID SCHEMA SQL_TEXT CHILD# EXEC
------------- ------ ------------------------------ ------ -----
070f5av1w2gqh HR SELECT /*test_bind*/ SUM(salar 0 3
y) FROM employees WHERE employ
ee_id < :emp_id
As we can see in above example there are 3 Execution happend and only 1 child (As Child number 0) created and reused for all 2 remaining childs.
=> Let we check Parent for this SQL
SQL>
SQL>
SQL> COL SQL_TEXT FORMAT a30
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM V$SQLAREA
WHERE SQL_TEXT LIKE '%test_bind%'
AND SQL_TEXT NOT LIKE '%SQL_TEXT%';
SQL> 2 3 4
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
------------------------------ ------------- ------------- ----------
SELECT /*test_bind*/ SUM(salar 070f5av1w2gqh 1 3284221648
y) FROM employees WHERE employ
ee_id < :emp_id
As per above result only 1 parent has been created and 1 child also created only.
So with above example we can see Bind variables can significantly reduce creation of New Child cursor and results in reduced Latches.
Disadvantages and Issues with Bind varaibles.
As we have seen that Bind variables can significantly improve concurrency due to reuse of child cursor. But there is issue with Bind variable , and we will discuss the same as per below examples. Using literals in SQL Query . => Let we execute below query using Literal and check what execution plan is getting generated for that. SQL> SET LINESIZE 167 SET PAGESIZE 0 SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());SQL> SQL> 24000 SQL> SQL_ID cn5250y0nqpym, child number 0 ------------------------------------- SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101 Plan hash value: 2410354593 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 8 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMPLOYEE_ID"<101) 20 rows selected. SQL> SELECT SUM(salary) FROM hr.employees WHERE employee_id < 165; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); 445608 SQL> SQL_ID b1tvfcc5qnczb, child number 0 ------------------------------------- SELECT SUM(salary) FROM hr.employees WHERE employee_id < 165 Plan hash value: 2410354593 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 66 | 528 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 66 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMPLOYEE_ID"<165) 20 rows selected. SQL> SELECT SUM(salary) FROM hr.employees WHERE employee_id < 90000; 691416 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); SQL_ID 8yqzyru1nzrvj, child number 0 ------------------------------------- SELECT SUM(salary) FROM hr.employees WHERE employee_id < 90000 Plan hash value: 1756381138 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 856 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("EMPLOYEE_ID"<90000) 19 rows selected. SQL> So as we can see using Literals employee_id<90000 generate FULL TABLE SCAN as per above example. Using Bind Variable in SQL Query . SQL> set lines 500 VARIABLE emp_id NUMBER EXEC :emp_id := 101; SELECT /*bind_issue*/ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); SQL> SQL> SQL> PL/SQL procedure successfully completed. SQL> 24000 SQL> SQL_ID 0mth4gr3mxuaz, child number 0 ------------------------------------- SELECT /*bind_issue*/ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id Plan hash value: 2410354593 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 8 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMPLOYEE_ID"<:EMP_ID) 21 rows selected. SQL> EXEC :emp_id := 165; SELECT /*bind_issue*/ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());SQL> PL/SQL procedure successfully completed. SQL> 445608 SQL> SQL_ID 0mth4gr3mxuaz, child number 0 ------------------------------------- SELECT /*bind_issue*/ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id Plan hash value: 2410354593 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 8 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMPLOYEE_ID"<:EMP_ID) 21 rows selected. SQL> EXEC :emp_id := 90000; SELECT /*bind_issue*/ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR()); PL/SQL procedure successfully completed. SQL> 691416 SQL> SQL_ID 0mth4gr3mxuaz, child number 0 ------------------------------------- SELECT /*bind_issue*/ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id Plan hash value: 2410354593 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 8 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMPLOYEE_ID"<:EMP_ID) 21 rows selected. So as we can see when we use Bind variable due to "Bind Peeked as first Parsed time" nature all subsequent cursor usages same execution plan as of first BInd varaiable values. So in our case employee_id<90000 using bind variable still usage INDEX RANGE SCAN whether it must use FULL TABLE SCAN. Bind Variable Peeking In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement. The optimizer does not look at the bind variable values before every parse. Rather, the optimizer peeks only when the optimizer is first invoked, which is during the hard parse. Because the optimizer only peeks at the bind value during the hard parse, the plan may not be optimal for all possible bind values. SO this problem addressed and resolved using ADAPTIVE CURSOR SHARING method.
Adaptive Cursor Sharing
As discussed above to resolve Bind varaiable issue Adaptive Cursor sharing is intorduced in 11g and we will be covering the same here . =>Desc v$sql view and you will find below 3 columns -> is_bind_sensitive- if value is "Y" Indicates not only bind peeking is used for generating execution plan but also Adaptive Cursor sharing may used. -> is_bind_aware- if "Y" Adaptive Cursor Sharing is used. -> is_shareable- if "Y" Indicates Cursor Can be Shared. DEMO for ACS => Login to user and create below table with mentioned table data. [oracle@ace2oracledb Desktop]$ sqlplus ABHI_TEST/Oracle_4U SQL> CREATE TABLE acs_lab(tid NUMBER, details VARCHAR(50)); Table created. SQL> DECLARE i NUMBER; entryupto NUMBER; BEGIN i:=1; entryupto:=1000; LOOP EXIT WHEN i>entryupto; IF (i=1) THEN INSERT INTO acs_lab VALUES(1,RPAD('B',10,'B')); ELSE INSERT INTO acs_lab VALUES(entryupto,RPAD('B',10,'B')); END IF; i:=i+1; END LOOP; COMMIT; END; / PL/SQL procedure successfully completed. SQL> CREATE INDEX acs_lab_idx_id ON acs_lab(tid); Index created. SQL> select count(*) as total_count,count(distinct tid) as dist_val from acs_lab; TOTAL_COUNT DIST_VAL ----------- ---------- 1000 2 => as we can see there is data skew for TID column. SQL> select tid,count(*) from acs_lab group by tid; TID COUNT(*) ---------- ---------- 1 1 1000 999 => Check for Histogram on table which we created. SQL> SET LINES 500 SET PAGES 100 COL COLUMN_NAME FOR A40 select column_name, histogram, num_distinct from user_tab_cols where table_name ='ACS_LAB';SQL> SQL> SQL> 2 3 COLUMN_NAME HISTOGRAM NUM_DISTINCT ---------------------------------------- --------------- ------------ TID NONE DETAILS NONE SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ABHI_TEST', tabname=>'acs_lab', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 2', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); PL/SQL procedure successfully completed. => Check for Histogram on table which we created. SQL> SET LINES 500 SET PAGES 100 COL COLUMN_NAME FOR A40 select column_name, histogram, num_distinct from user_tab_cols where table_name ='ACS_LAB';SQL> SQL> SQL> 2 3 COLUMN_NAME HISTOGRAM NUM_DISTINCT ---------------------------------------- --------------- ------------ TID FREQUENCY 2 DETAILS NONE => Let we test TID column for low cardinality and it must use INDEX . SQL> variable tid NUMBER; EXEC :tid:=1; SELECT /*acs_demo*/ * FROM acs_lab WHERE tid=:tid; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());SQL> SQL> PL/SQL procedure successfully completed. TID DETAILS ---------- -------------------------------------------------- 1 BBBBBBBBBB SQL> PLAN_TABLE_OUTPUT --------------------------------- SQL_ID 8hygrf4wdq4ba, child number 0 ------------------------------------- SELECT /*acs_demo*/ * FROM acs_lab WHERE tid=:tid Plan hash value: 4188866867 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ACS_LAB | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | ACS_LAB_IDX_ID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TID"=:TID) 19 rows selected. => check if Bind_sensitive and Bind_Aware value updated . SQL> COL BIND_AWARE FORMAT a10 COL SQL_TEXT FORMAT a22 COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL BUFF_GETS FORMAT 999999999 COL BIND_SENS FORMAT a9 COL SHARABLE FORMAT a9 SELECT SQL_ID,SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '%acs_demo%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 SQL_ID SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ------------- ---------------------- ------ ----- ---------- --------- ---------- --------- 8hygrf4wdq4ba SELECT /*acs_demo*/ * 0 1 4 Y N Y FROM acs_lab WHERE ti d=:tid run again the above query and recheck for Bind_aware. SQL> COL BIND_AWARE FORMAT a10 COL SQL_TEXT FORMAT a22 COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL BUFF_GETS FORMAT 999999999 COL BIND_SENS FORMAT a9 COL SHARABLE FORMAT a9 SELECT SQL_ID,SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '%acs_demo%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 SQL_ID SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ------------- ---------------------- ------ ----- ---------- --------- ---------- --------- 8hygrf4wdq4ba SELECT /*acs_demo*/ * 0 2 8 Y N Y FROM acs_lab WHERE ti d=:tid => Now Let we test TID column for high cardinality and it must NOT use INDEX as it should use FULL TABLE SCAN. SQL> variable tid NUMBER; EXEC :tid:=1000; SELECT /*acs_demo*/ * FROM acs_lab WHERE tid=:tid; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());SQL> PL/SQL procedure successfully completed. SQL> TID DETAILS ---------- -------------------------------------------------- 1000 BBBBBBBBBB .. ... 999 rows selected. SQL> PLAN_TABLE_OUTPUT -------------------------- SQL_ID 8hygrf4wdq4ba, child number 0 ------------------------------------- SELECT /*acs_demo*/ * FROM acs_lab WHERE tid=:tid Plan hash value: 4188866867 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ACS_LAB | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | ACS_LAB_IDX_ID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TID"=:TID) 19 rows selected. As we can see optimizer still using INDEX SCAN where it should use FULL TABLE SCAN. Let we execute same query 2 more times same query and check for outcomes as below SQL> COL BIND_AWARE FORMAT a10 COL SQL_TEXT FORMAT a22 COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL BUFF_GETS FORMAT 999999999 COL BIND_SENS FORMAT a9 COL SHARABLE FORMAT a9 SELECT SQL_ID,SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '%acs_demo%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 SQL_ID SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ------------- ---------------------- ------ ----- ---------- --------- ---------- --------- 8hygrf4wdq4ba SELECT /*acs_demo*/ * 0 4 286 Y N Y FROM acs_lab WHERE ti d=:tid SQL> variable tid NUMBER; EXEC :tid:=1000; SELECT /*acs_demo*/ * FROM acs_lab WHERE tid=:tid; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());SQL> PL/SQL procedure successfully completed. SQL> TID DETAILS ---------- -------------------------------------------------- 1000 BBBBBBBBBB ... ... 1000 BBBBBBBBBB 999 rows selected. SQL> PLAN_TABLE_OUTPUT ------------------------ SQL_ID 8hygrf4wdq4ba, child number 1 ------------------------------------- SELECT /*acs_demo*/ * FROM acs_lab WHERE tid=:tid Plan hash value: 1352053510 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| ACS_LAB | 999 | 13986 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TID"=:TID) 18 rows selected. => check for Bind_aware and Bind_sensitive SQL> COL BIND_AWARE FORMAT a10 COL SQL_TEXT FORMAT a22 COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL BUFF_GETS FORMAT 999999999 COL BIND_SENS FORMAT a9 COL SHARABLE FORMAT a9 SELECT SQL_ID,SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '%acs_demo%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 SQL_ID SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ------------- ---------------------- ------ ----- ---------- --------- ---------- --------- 8hygrf4wdq4ba SELECT /*acs_demo*/ * 0 4 286 Y N N FROM acs_lab WHERE ti d=:tid 8hygrf4wdq4ba SELECT /*acs_demo*/ * 1 1 81 Y Y Y FROM acs_lab WHERE ti d=:tid As we can see once cursor is in adaptive mode it created new cursor and utilize new plan for bind variable. => Now let we execute 1 more time tid=1 and see as below SQL> variable tid NUMBER; EXEC :tid:=1; SELECT /*acs_demo*/ * FROM acs_lab WHERE tid=:tid; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());SQL> PL/SQL procedure successfully completed. SQL> TID DETAILS ---------- -------------------------------------------------- 1 BBBBBBBBBB SQL> PLAN_TABLE_OUTPUT --------------------- SQL_ID 8hygrf4wdq4ba, child number 2 ------------------------------------- SELECT /*acs_demo*/ * FROM acs_lab WHERE tid=:tid Plan hash value: 4188866867 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ACS_LAB | 1 | 14 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | ACS_LAB_IDX_ID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TID"=:TID) 19 rows selected. SQL> COL BIND_AWARE FORMAT a10 COL SQL_TEXT FORMAT a22 COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL BUFF_GETS FORMAT 999999999 COL BIND_SENS FORMAT a9 COL SHARABLE FORMAT a9 SELECT SQL_ID,SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC, BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS, IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE FROM V$SQL WHERE SQL_TEXT LIKE '%acs_demo%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 SQL_ID SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ------------- ---------------------- ------ ----- ---------- --------- ---------- --------- 8hygrf4wdq4ba SELECT /*acs_demo*/ * 0 4 286 Y N N FROM acs_lab WHERE ti d=:tid 8hygrf4wdq4ba SELECT /*acs_demo*/ * 1 1 81 Y Y Y FROM acs_lab WHERE ti d=:tid 8hygrf4wdq4ba SELECT /*acs_demo*/ * 2 1 4 Y Y Y FROM acs_lab WHERE ti d=:tid SQL> As we can see once cursor is Bind-aware it will discard old cursor for same bind value as to use new cursor
CURSOR_SHARING parameter
The CURSOR_SHARING initialization parameter controls how the database processes statements with bind variables. The parameter supports the following values: • EXACT This is the default value. The database enables only textually identical statements to share a cursor. The database does not attempt to replace literal values with system-generated bind variables. In this case, the optimizer generates a plan for each statement based on the literal value. • FORCE The database replaces all literals with system-generated bind variables. For statements that are identical after the bind variables replace the literals, the optimizer uses the same plan. Note: The SIMILAR value for CURSOR_SHARING is deprecated. DEMO of CURSOR_SHARING =EXACT [oracle@ace2oracledb ~]$ sqlplus ABHI_TEST/Oracle_4U SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- -- cursor_sharing string EXACT => Using Literals SQL> SELECT /* cur_sharing */ SUM(salary) FROM hr.employees WHERE employee_id < 101; SUM(SALARY) ----------- 24000 SQL> SELECT /* cur_sharing */ SUM(salary) FROM hr.employees WHERE employee_id < 150; SUM(SALARY) ----------- 316408 SQL> SELECT /* cur_sharing */ SUM(salary) FROM hr.employees WHERE employee_id < 200; SUM(SALARY) ----------- 631208 No of Parents => as we can see below each query has gone through hard parse and generate 3 distinct Parents SQL> COL SQL_TEXT FORMAT a30 SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%cur_sharing%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL> 2 3 4 SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE ------------------------------ ------------- ------------- ---------- SELECT /* cur_sharing */ SUM(s 7vy2x6f3rd6yp 1 2272697301 alary) FROM hr.employees WHERE employee_id < 150 SELECT /* cur_sharing */ SUM(s g3009yjr4fkzs 1 1850166264 alary) FROM hr.employees WHERE employee_id < 200 SELECT /* cur_sharing */ SUM(s 2pa15xkgb7ddy 1 2662577598 alary) FROM hr.employees WHERE employee_id < 101 No of Childs => as we can see below each query has gone through hard parse and generate 3 distinct Childs consisting 1 parent. SQL> COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL SCHEMA FORMAT a6 SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL WHERE SQL_TEXT LIKE '%cur_sharing%' AND SQL_TEXT NOT LIKE '%SQL_TEXTSQL> SQL> SQL> 2 3 %' ORDER BY SQL_ID; SQL_ID SCHEMA SQL_TEXT CHILD# EXEC ------------- ------ ------------------------------ ------ ----- 2pa15xkgb7ddy ABHI_T SELECT /* cur_sharing */ SUM(s 0 1 EST alary) FROM hr.employees WHERE employee_id < 101 7vy2x6f3rd6yp ABHI_T SELECT /* cur_sharing */ SUM(s 0 1 EST alary) FROM hr.employees WHERE employee_id < 150 g3009yjr4fkzs ABHI_T SELECT /* cur_sharing */ SUM(s 0 1 EST alary) FROM hr.employees WHERE employee_id < 200 => Using Bind Variable SQL> VARIABLE emp_id NUMBER SQL> EXEC :emp_id := 101; SQL> SELECT /* cur_sharing_bind */ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;SQL> SQL> PL/SQL procedure successfully completed. SQL> SUM(SALARY) ----------- 24000 SQL> EXEC :emp_id := 150; SELECT /* cur_sharing_bind */ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;SQL> PL/SQL procedure successfully completed. SQL> SUM(SALARY) ----------- 316408 SQL> EXEC :emp_id := 200; SELECT /* cur_sharing_bind */ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id; PL/SQL procedure successfully completed. SQL> SUM(SALARY) ----------- 631208 SQL> No of Parents => as we can see below query has gone through soft parse and generate 1 Parent only due to Bind variable. SQL> COL SQL_TEXT FORMAT a30 SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%cur_sharing_bind%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';SQL> 2 3 4 SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE ------------------------------ ------------- ------------- ---------- SELECT /* cur_sharing_bind */ fg2xxcd1wurj1 1 1137532449 SUM(salary) FROM hr.employees WHERE employee_id < :emp_id No of Childs => as we can see below each query has used same Child with 3 executions and gone through soft parse. COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL SCHEMA FORMAT a6 SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL WHERE SQL_TEXT LIKE '%cur_sharing_bind%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID; SQL> SQL> SQL> SQL> 2 3 SQL_ID SCHEMA SQL_TEXT CHILD# EXEC ------------- ------ ------------------------------ ------ ----- fg2xxcd1wurj1 ABHI_T SELECT /* cur_sharing_bind */ 0 3 EST SUM(salary) FROM hr.employees WHERE employee_id < :emp_id DEMO of CURSOR_SHARING =FORCE [oracle@ace2oracledb ~]$ sqlplus ABHI_TEST/Oracle_4U => Using Literals SQL> alter session set cursor_sharing=FORCE; Session altered. SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- - cursor_sharing string FORCE SQL> SQL> SELECT /* force_sharing_N */ SUM(salary) FROM hr.employees WHERE employee_id < 101; SUM(SALARY) ----------- 24000 SQL> SELECT /* force_sharing_N */ SUM(salary) FROM hr.employees WHERE employee_id < 150; SUM(SALARY) ----------- 316408 SQL> SELECT /* force_sharing_N */ SUM(salary) FROM hr.employees WHERE employee_id < 200; SUM(SALARY) ----------- 631208 No of Childs => as we can see below each query has gone through soft parse and generate 1 Child consisting 1 parent. Literal Got replaced by SYSTEM GENERATED Bind variable . SQL> COL SQL_TEXT FORMAT a30 SQL> set pages 50 SQL> COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL SCHEMA FORMAT a6 SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL WHERE SQL_TEXT LIKE '%force_sharing_N%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID;SQL> SQL> SQL> 2 3 SQL_ID SCHEMA SQL_TEXT CHILD# EXEC ------------- ------ ------------------------------ ------ ----- 7ct11na101qan ABHI_T SELECT /* force_sharing_N */ S 0 3 EST UM(salary) FROM hr.employees W HERE employee_id < :"SYS_B_0" No of Parents => as we can see below query has gone through soft parse and generate 1 Parent only due to Literal Got replaced by SYSTEM GENERATED Bind variable and hence reduce space in shared pool and reduction in latches. SQL> COL SQL_TEXT FORMAT a30 SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%force_sharing_N%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL> 2 3 4 SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE ------------------------------ ------------- ------------- ---------- SELECT /* force_sharing_N */ S 7ct11na101qan 1 2181093716 UM(salary) FROM hr.employees W HERE employee_id < :"SYS_B_0" => Using Bind Variable SQL> SQL> VARIABLE emp_id NUMBER EXEC :emp_id := 101; SELECT /* force_sharing_N_bind */ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id;SQL> PL/SQL procedure successfully completed. SQL> SUM(SALARY) ----------- 24000 SQL> EXEC :emp_id := 150; SELECT /* force_sharing_N_bind */ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id; PL/SQL procedure successfully completed. SQL> SUM(SALARY) ----------- 316408 SQL> SQL> EXEC :emp_id := 200; SELECT /* force_sharing_N_bind */ SUM(salary) FROM hr.employees WHERE employee_id < :emp_id; PL/SQL procedure successfully completed. SQL> SUM(SALARY) ----------- 631208 No of Parents => as we can see below query has generate 1 Parent only due to Literal Got replaced by USER DEFINED Bind variable . SQL> COL SQL_TEXT FORMAT a30 SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%force_sharing_N_bind%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%'; SQL> 2 3 4 SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE ------------------------------ ------------- ------------- ---------- SELECT /* force_sharing_N_bind f8h0azdjtcry4 1 1670799300 */ SUM(salary) FROM hr.employ ees WHERE employee_id < :emp_id No of Childs => as we can see below each query has gone through soft parse and generate 1 Child consisting 1 parent. Literal Got replaced by USER DEFINED Bind variable . SQL> COL CHILD# FORMAT 99999 COL EXEC FORMAT 9999 COL SCHEMA FORMAT a6 SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL WHERE SQL_TEXT LIKE '%force_sharing_N_bind%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%' ORDER BY SQL_ID;SQL> SQL> SQL> 2 3 SQL_ID SCHEMA SQL_TEXT CHILD# EXEC ------------- ------ ------------------------------ ------ ----- f8h0azdjtcry4 ABHI_T SELECT /* force_sharing_N_bind 0 3 EST */ SUM(salary) FROM hr.employ ees WHERE employee_id < :emp_id SQL> Only Literal Value replacement SQL> alter session set cursor_sharing=FORCE; Session altered. SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing string FORCE SQL> SQL> SELECT /* force_sharing_t */ SUM(salary) FROM hr.employees WHERE employee_id < 101; SUM(SALARY) ----------- 24000 SQL> SELECT /* force_sharing_t */ SUM(salary) FROM hr.employees WHERE Employee_id < 150; SUM(SALARY) ----------- 316408 SQL> SELECT /* force_sharing_t */ SUM(salary) FROM hr.employees WHERE employee_id < 200; -> extra space b/w WHERE employee_id SUM(SALARY) ----------- 631208 As we can see ONLY Literal value got replaced with Bind value and all 3 statements which are NOT IDENTICAL ,Don't share same plan event FORCE is used. SQL> COL SQL_TEXT FORMAT a30 SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE '%force_sharing_t%' AND SQL_TEXT NOT LIKE '%SQL_TEXT%';SQL> 2 3 4 SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE ------------------------------ ------------- ------------- ---------- SELECT /* force_sharing_t */ S 3jpfrjwpmtak6 1 725396038 UM(salary) FROM hr.employees W HERE employee_id < :"SYS_B_0" SELECT /* force_sharing_t */ S 18k7s3315z6nw 1 3261045404 UM(salary) FROM hr.employees W HERE Employee_id < :"SYS_B_0" SELECT /* force_sharing_t */ S 01xqthqj6v7y7 1 2725093319 UM(salary) FROM hr.employees W HERE employee_id < :"SYS_B_0" SQL>
Disabling ACS
Disable the Adaptive cursor sharing in Oracle alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both; --Disable bind variable or other parameter alter system set "cursor_sharing"=exact scope=both; alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both; alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444