[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
- 如何給esxi打補丁
- [20210929]sql打補丁使用rule提示問題.txtSQL
- [202021127]sql打補丁問題.txtSQL
- 【PSU】怎麼給RAC打PSU補丁
- [20220329]19c sql語句打補丁.txtSQL
- [20220330]編寫sql打補丁的指令碼.txtSQL指令碼
- 【UP_ORACLE】如何給Oracle DG打補丁（二）備庫安裝補丁步驟Oracle
- 【UP_ORACLE】如何給Oracle DG打補丁（三）主庫安裝補丁步驟Oracle
- Linux檔案打補丁Linux
- oracle打補丁回顧Oracle
- 【UP_ORACLE】如何給Oracle DG打補丁（一）整體思路Oracle
- Git 打補丁-- patch 和 diff 的使用（詳細）Git
- Git 打補丁– patch 和 diff 的使用（詳細）Git
- weblogic的版本及打補丁Web
- DG環境下打補丁
- 打補丁時重建Inventory目錄
- ORACLE打補丁的方法和案例Oracle
- Oracle RAC 19.3打19.5.1 RU補丁Oracle
- windows oracle 11201打補丁報錯WindowsOracle
- Oracle Goldengate 12c打pus補丁OracleGo
- 打補丁打出新的BUG來了
- 19c 自動打RU補丁
- [20220119]超長sql語句補充3.txtSQL
- SAP打補丁時需要注意的地方
- 19c rac自動打補丁步驟
- weblogic10.3.6軟體打補丁很慢問題Web
- Oracle 11g RAC自動打GI PSU補丁Oracle
- oracle 19c rac打補丁常見錯誤Oracle
- 伺服器批次打補丁 批次vps用什麼伺服器
- 伺服器打補丁需要注意的幾點伺服器
- [20200212]使用DBMS_SHARED_POOL.MARKHOT與sql語句3.txtSQL
- IE漏洞致數百萬使用者中招快用瑞星卡卡打補丁
- 12. Oracle版本、補丁及升級——12.2. 補丁及補丁集Oracle
- SharePoint or Office 打補丁或產品安裝遇到問題
- Oracle 19c RAC打補丁過程避坑指南Oracle
- ORACLE RAC的全自動 打補丁標準化文件Oracle
- 12C RAC 打31720486補丁 後報錯處理