[20201126]使用cursor_sharing_exact與給sql打補丁3.txt
[20201126]使用cursor_sharing_exact與給sql打補丁3.txt
--//以前我記憶裡給sql語句打補丁，好像在11g下打上cursor_sharing_exact提示無效的，看連結
--//https://hourim.wordpress.com/2020/10/24/function-based-indexes-and-cursor-sharing/
--//感覺我視乎做錯一些步驟，自己重複測試：
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table empx as select * from emp;
Table created.
SCOTT@book> create index if_empx_hiredate on empx(to_char(hiredate,'yyyymmdd'));
Index created.
--//分析略。
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201126';
no rows selected
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID du47xmcwj8j83, child number 0
-------------------------------------
select * from empx where to_char(hiredate,'yyyymmdd')='20201126'
Plan hash value: 3554333430
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPX | 1 | 93 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IF_EMPX_HIREDATE | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPX@SEL$1
2 - SEL$1 / EMPX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPX"."SYS_NC00009$"='20201126')
--//發現可以使用索引，但是如果設定cursor_sharing=force，因為裡面引數被替換，會導致建立的函式索引無法使用。
SCOTT@book> alter session set cursor_sharing=force;
Session altered.
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201126';
no rows selected
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID ff2sbvc2b3pyb, child number 0
-------------------------------------
select * from empx where to_char(hiredate,:"SYS_B_0")=:"SYS_B_1"
Plan hash value: 722738080
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMPX | 1 | 87 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :SYS_B_0 (VARCHAR2(30), CSID=852): 'yyyymmdd'
2 - :SYS_B_1 (VARCHAR2(30), CSID=852): '20201126'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),:SYS_B_0)=:SYS_B_1)
2.給sql語句打補丁看看。
SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch');
PL/SQL procedure successfully completed.
declare
v_sql CLOB;
begin
select distinct sql_text into v_sql from v$sql where sql_id='&sql_id';
sys.dbms_sqldiag_internal.i_create_patch(
sql_text => v_sql,
hint_text => 'cursor_sharing_exact)',
name => 'user_extents_patch');
end;
/
--//輸入sql_id=ff2sbvc2b3pyb
SYS@book> select name, status, created, sql_text from dba_sql_patches where name='user_extents_patch'
2 @ prxx
==============================
NAME : user_extents_patch
STATUS : ENABLED
CREATED : 2020-11-26 15:31:55.000000
SQL_TEXT : select * from empx where to_char(hiredate,:"SYS_B_0")=:"SYS_B_1"
PL/SQL procedure successfully completed.
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> alter system flush shared_pool;
System altered.
--//退出會話重新登入：
SCOTT@book> alter session set cursor_sharing=force;
Session altered.
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201126';
no rows selected
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID du47xmcwj8j83, child number 0
-------------------------------------
select * from empx where to_char(hiredate,'yyyymmdd')='20201126'
Plan hash value: 3554333430
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPX | 1 | 47 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IF_EMPX_HIREDATE | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPX@SEL$1
2 - SEL$1 / EMPX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPX"."SYS_NC00009$"='20201126')
Note
-----
- SQL patch "user_extents_patch" used for this statement
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
32 rows selected.
--//OK,函式索引可以使用。修改語句看看：
SCOTT@book> select * from empx where to_char(hiredate,'yyyy')='2020';
no rows selected
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9sqnzyt0mjkzs, child number 0
-------------------------------------
select * from empx where to_char(hiredate,'yyyy')='2020'
Plan hash value: 722738080
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMPX | 1 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMPX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIREDATE"),'yyyy')='2020')
Note
-----
- SQL patch "user_extents_patch" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
30 rows selected.
--//一樣ok，但是注意函式索引不滿足需求，走的是全表掃描。
--//有了這樣的方式就可以解決生產系統遇到的垃圾問題，像這樣的垃圾語句連結：
--// http://blog.itpub.net/267265/viewspace-1771727/
--//建議開發不要在寫這樣垃圾的拼接sql語句，豆腐渣豆腐渣.
--//當然缺點就是無法共享相同游標了，消耗大量共享池資源，每次可能都需要硬解析。
--//session 1:
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201128';
no rows selected
--//session 2:
SYS@book> @ viewsessx parse 325
NAME STATISTIC# VALUE SID
---------------------- ---------- ---------- ----------
parse time cpu 622 34 325
parse time elapsed 623 35 325
parse count (total) 624 1007 325
parse count (hard) 625 224 325
parse count (failures) 626 12 325
--//session 1:
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201128';
no rows selected
--//session 2:
SYS@book> @ viewsessx parse 325
NAME STATISTIC# VALUE SID
---------------------- ---------- ---------- ----------
parse time cpu 622 34 325
parse time elapsed 623 36 325
parse count (total) 624 1009 325
parse count (hard) 625 225 325
parse count (failures) 626 13 325
--//session 1:
SCOTT@book> select * from empx where to_char(hiredate,'yyyymmdd')='20201130';
no rows selected
--//session 2:
SYS@book> @ viewsessx parse 325
NAME STATISTIC# VALUE SID
---------------------- ---------- ---------- ----------
parse time cpu 622 35 325
parse time elapsed 623 37 325
parse count (total) 624 1011 325
parse count (hard) 625 227 325
parse count (failures) 626 14 325
--//注意看parse count (failures),parse count (hard)計數。如果前面執行過，parse count (hard)增加1。224->225.
--//如果從來沒有執行過，parse count (hard)增加2.225->227. parse count (failures)從增加1次。
--//收尾：
SYS@book> exec sys.dbms_sqldiag.drop_sql_patch('user_extents_patch');
PL/SQL procedure successfully completed.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2737353/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20201126]使用cursor_sharing_exact與給sql打補丁2.txtSQL
- 使用OPatch給Oracle打補丁Oracle
- [20121231]給sql打補丁.txtSQL
- 安裝oraInventory和使用opatch給Oracle打補丁AIOracle
- zt_使用opatch給oracle打補丁patch_bugOracle
- 給Oracle資料庫打補丁(轉)Oracle資料庫
- 給oracle打Patch 9352237補丁Oracle
- 給HP-UX作業系統打補丁UX作業系統
- opatch 工具打補丁使用說明
- 【UP_ORACLE】如何給Oracle DG打補丁（二）備庫安裝補丁步驟Oracle
- 【UP_ORACLE】如何給Oracle DG打補丁（三）主庫安裝補丁步驟Oracle
- Torvalds給Linux核心打補丁抵禦病毒(轉)Linux
- 網管必讀教會使用者自己給系統打補丁(轉)
- oracle打補丁回顧Oracle
- Oracle EBS中打補丁Oracle
- 聊聊兩種給Grid Infrastructure打補丁的方法（上）ASTStruct
- 聊聊兩種給Grid Infrastructure打補丁的方法（下）ASTStruct
- [20210929]sql打補丁使用rule提示問題.txtSQL
- 打Oracle最新CPU patch與打臨時補丁的區別Oracle
- 【UP_ORACLE】如何給Oracle DG打補丁（一）整體思路Oracle
- Linux檔案打補丁Linux
- SAP打補丁(Tcode:SPAM)
- Git 打補丁– patch 和 diff 的使用（詳細）Git
- Git 打補丁-- patch 和 diff 的使用（詳細）Git
- weblogic的版本及打補丁Web
- ORACLE打補丁的方法和案例Oracle
- ORACLE11G DG打補丁Oracle
- Oracle資料庫打補丁方法Oracle資料庫
- oracle 小補丁能全部打嗎？Oracle
- 批處理打補丁的方法
- 給oracle 10.2.0.4.0 打em補丁8350262執行步驟Oracle
- 到底打還是不打補丁：安裝第三方補丁(轉)
- windows 打補丁後網路異常Windows
- Windows 10打個補丁 Office歇菜了Windows
- 【opatch】Oracle打補丁工具opatch簡介Oracle
- 在windows上打Oracle的CPU補丁WindowsOracle
- [202021127]sql打補丁問題.txtSQL
- [20220329]19c sql語句打補丁.txtSQL