[20181007]12cR2 Using SQL Patch.txt
[20181007]12cR2 Using SQL Patch.txt
--//12cR2 已經把sql打補丁整合進入dbms_sqldiag,不是11g的 DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH .做一個記錄.
--//以前的連結:http://blog.itpub.net/267265/viewspace-751900/=>[20121231]給sql打補丁.txt
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> @ desc_proc sys dbms_sqldiag create_sql_patch
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED
---------- -------------------- ---------------- -------- -------------------- -------------------- --------- -------------------- ----------
SYS DBMS_SQLDIAG CREATE_SQL_PATCH 1 VARCHAR2 OUT VARCHAR2 N
2 SQL_ID VARCHAR2 IN VARCHAR2 N
3 HINT_TEXT CLOB IN CLOB N
4 NAME VARCHAR2 IN VARCHAR2 Y
5 DESCRIPTION VARCHAR2 IN VARCHAR2 Y
6 CATEGORY VARCHAR2 IN VARCHAR2 Y
7 VALIDATE PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y
1 VARCHAR2 OUT VARCHAR2 N
2 SQL_TEXT CLOB IN CLOB N
3 HINT_TEXT CLOB IN CLOB N
4 NAME VARCHAR2 IN VARCHAR2 Y
5 DESCRIPTION VARCHAR2 IN VARCHAR2 Y
6 CATEGORY VARCHAR2 IN VARCHAR2 Y
7 VALIDATE PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y
14 rows selected.
2.測試:
SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10;
DEPTNO DNAME LOC
----------------- -------------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g0qybdz1796cn, child number 0
-------------------------------------
select /*+ full(dept) */ * from dept where deptno=10
Plan hash value: 3383998547
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
--//sql_id=g0qybdz1796cn,實際上走索引更佳.注意多執行幾次保留在共享池.
SCOTT@test01p> variable patch_name varchar2(2000);
SCOTT@test01p> exec :patch_name := dbms_sqldiag.create_sql_patch(sql_id=>'g0qybdz1796cn',hint_text=>'index(dept pk_dept)');
PL/SQL procedure successfully completed.
--//現在居然scott使用者就可以執行,我記憶裡以前不行,必須sys使用者執行.
SCOTT@test01p> print :patch_name
PATCH_NAME
------------------------------------------
SYS_SQLPTCH_01664e9a59810003
--//相關資訊記錄在檢視DBA_SQL_PATCHES.
SCOTT@test01p> select NAME c30,SQL_TEXT from DBA_SQL_PATCHES;
C30 SQL_TEXT
------------------------------ ------------------------------------------------------------
SYS_SQLPTCH_01664e9a59810003 select /*+ full(dept) */ * from dept where deptno=10
SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10;
DEPTNO DNAME LOC
----------------- -------------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g0qybdz1796cn, child number 0
-------------------------------------
select /*+ full(dept) */ * from dept where deptno=10
Plan hash value: 3383998547
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
Note
-----
- SQL patch "SYS_SQLPTCH_01664e9a59810003" used for this statement
--//沒有起作用.實際上不能使用這樣的提示.執行如下:
select * from dept where deptno=10;
--//再看執行計劃提示:
SCOTT@test01p> @ dpc '' outline
...
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
END_OUTLINE_DATA
*/
--//使用提示INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO")).才行.
SCOTT@test01p> exec dbms_sqldiag.drop_sql_patch(name=>'SYS_SQLPTCH_01664e9a59810003');
PL/SQL procedure successfully completed.
SCOTT@test01p> exec :patch_name := dbms_sqldiag.create_sql_patch(sql_id=>'g0qybdz1796cn',hint_text=>'INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))');
PL/SQL procedure successfully completed.
SCOTT@test01p> print :patch_name
PATCH_NAME
------------------------------------
SYS_SQLPTCH_01664ea1bc190004
SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10;
DEPTNO DNAME LOC
----------------- -------------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g0qybdz1796cn, child number 0
-------------------------------------
select /*+ full(dept) */ * from dept where deptno=10
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
2 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
Note
-----
- SQL patch "SYS_SQLPTCH_01664ea1bc190004" 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
--//OK,現在起作用了.
3.我個人認為sql打補丁最佳方式是加BIND_AWARE或者result_cache提示,其它情況我很少使用.
--//我記憶裡11g下不能加result_cache,再加這個提示看看.
SCOTT@test01p> exec dbms_sqldiag.drop_sql_patch(name=>'SYS_SQLPTCH_01664ea1bc190004');
PL/SQL procedure successfully completed.
SCOTT@test01p> exec :patch_name := dbms_sqldiag.create_sql_patch(sql_id=>'g0qybdz1796cn',hint_text=>'result_cache INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))');
PL/SQL procedure successfully completed.
SCOTT@test01p> Select /*+ full(dept) */ * from dept where deptno=10;
DEPTNO DNAME LOC
----------------- -------------------- -------------
10 ACCOUNTING NEW YORK
--//我修改select=>Select.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4sg4rbwu9r59q, child number 0
-------------------------------------
Select /*+ full(dept) */ * from dept where deptno=10
Plan hash value: 2852011669
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | RESULT CACHE | 364dg0urjj61xc7was3s7u5hcj | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
Result Cache Information (identified by operation id):
------------------------------------------------------
1 -
Note
-----
- SQL patch "SYS_SQLPTCH_01664eab2c7a0006" 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
--//OK,12c已經修復這個問題.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2215508/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20181007]12cR2 Using SQL Patch 2.txtSQL
- Oracle 12Cr2 Using CloneDB to clone a databaseOracleDatabase
- sql之27 using sql*plusSQL
- sql之26 using sql*plusSQL
- 12CR2 using create database command for create non-CDB rac databaseDatabase
- Recipe 5.6. Using SQL to Generate SQLSQL
- Using XML Parser for PL/SQLXMLSQL
- Using SQL Script Get Information about ASMSQLORMASM
- Using Excel to generate Inserts for SQL ServerExcelSQLServer
- [20181225]12CR2 SQL Plan Directives.txtSQL
- 動態 SQL、EXECUTE IMMEDIATE、using、into、returningSQL
- 【SQL】Using Oracle's Parallel Execution FeaturesSQLOracleParallel
- [20210621]Driving site patch.txt
- How to Quickly Create a Copy of a Table using Transact-SQLUISQL
- Send Email with Attachments from PL/SQL using Java Stored ProceduresAISQLJava
- Running Workload Repository Reports Using SQL ScriptsSQL
- How to See Supplier Contact Information Using SQL in R12ORMSQL
- java.sql.SQLException: Access denied for user ‘root‘@‘localhost‘ (using password: YES)JavaSQLExceptionlocalhost
- Guide to Database Migration from Microsoft SQL Server using MySQL WorkbenchGUIIDEDatabaseROSServerMySql
- Oracle 12CR2 dbca -silent -createDatabaseOracleDatabase
- Using index condition Using indexIndex
- Oracle 12CR2 Install the Sample SchemasOracle
- [20181007]Scalable sequences oracle database 12c.txtOracleDatabase
- Example of SQL Linux Windows Authentication configuration using Managed Service AccountsSQLLinuxWindows
- Paging Records Using SQL Server 2005 Database - ROW_NUMBER FunctionSQLServerDatabaseFunction
- mysql point in time recovery using sql_thread SQL_Thread增量恢復binlog 要點MySqlthread
- RHEL6.4安裝Oracle 12cR2Oracle
- MySQL 索引優化 Using where, Using filesortMySql索引優化
- Connect SQL Server from Linux Client using Windows Authentication and troubleshoot stepsSQLServerLinuxclientWindows
- Failed to run 'create login' or 'sp_addsrvrolemeber' in sql Linux using windows authentcationAIVRSQLLinuxWindows
- using indexIndex
- MySQL explain結果Extra中"Using Index"與"Using where; Using index"區別MySqlAIIndex
- Oracle Data Guard Feature 12cR2系列(二)Oracle
- Oracle VM上實施Oracle 12cR2 RACOracle
- Oracle Data Guard Feature 12cR2系列(一)Oracle
- Using hints for PostgresqlSQL
- MySQL 之 USINGMySql
- Using the WITH CHECK OPTION