做网站销售药品,wordpress 后门,烟台酒店网站建设,在线网页视频抓取工具coe_load_sql_profile.sql 是Oracle数据库环境下用于迁移或固定SQL执行计划的一个脚本#xff0c;它可以帮助DBA将特定SQL语句的高效执行计划转化为SQL Profile#xff0c;并将其应用到目标数据库中。 SQL Profile是一种Oracle数据库中用来指导优化器选择特定执行计划的方法。… coe_load_sql_profile.sql 是Oracle数据库环境下用于迁移或固定SQL执行计划的一个脚本它可以帮助DBA将特定SQL语句的高效执行计划转化为SQL Profile并将其应用到目标数据库中。 SQL Profile是一种Oracle数据库中用来指导优化器选择特定执行计划的方法。 操作演示使用 coe_load_sql_profile.sql 脚本来固定执行计划
1、准备演示环境
此操作需要以具有足够权限如DBA权限的非SYS用户本操作中的scott用户已经具有了dba权限。
[oracledb11g ~]$ sqlplus scott/scott
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 19 16:56:53 2024Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSCOTTPROD11G
--查看一下emp表
SCOTTPROD11G select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.--给ename列创建一个索引emp_idx1
SCOTTPROD11G create index emp_idx1 on emp(ename);Index created.--运行SQL并查看执行计划
SCOTTPROD11G set autot traceSCOTTPROD11G select ename from emp where enamename;no rows selectedExecution Plan
----------------------------------------------------------
Plan hash value: 654424206-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_IDX1 | 1 | 6 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - access(ENAMEname)Statistics
----------------------------------------------------------5 recursive calls0 db block gets2 consistent gets0 physical reads0 redo size333 bytes sent via SQL*Net to client513 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 rows processedSCOTTPROD11G -- 可以看到执行计划走了索引扫描INDEX RANGE SCAN
-- 下面使用hint强制走全表扫描SCOTTPROD11G select /* FULL (EMP) */ ename from emp where enamename;no rows selectedExecution Plan
----------------------------------------------------------
Plan hash value: 3956160932--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 6 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(ENAMEname)Statistics
----------------------------------------------------------1 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size333 bytes sent via SQL*Net to client513 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 rows processedSCOTTPROD11G -- 可以看到使用hint强制sql走了全表扫描
-- 下面查看sql的sql_id,plan_hash_value,sql_textselect sql_id ,plan_hash_value, sql_text from v$sql where sql_text like select%from emp%ename%;
SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ------------------------------------------------------------------------------------
66q0r9w4df2f5 654424206 select ename from emp where enamename
3w056txhg3vdu 3956160932 select /* FULL (EMP) */ ename from emp where enamename
2、使用 coe_load_sql_profile脚本固定执行计划
下面执行 coe_load_sql_profile.sql 脚本来固定全表扫描的执行计划。
--执行coe_load_sql_profile.sql 脚本SCOTTPROD11G coe_load_sql_profile.sqlParameter 1:
ORIGINAL_SQL_ID (required)Enter value for 1: 66q0r9w4df2f5 --输入源SQL的SQL_IDParameter 2:
MODIFIED_SQL_ID (required)Enter value for 2: 3w056txhg3vdu --输入要固定执行计划的SQL的SQL_IDPLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------3956160932 .002Parameter 3:
PLAN_HASH_VALUE (required)Enter value for 3: 3956160932 --输入要固定执行计划的SQL的PLAN_HASH_VALUEValues passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: 66q0r9w4df2f5
MODIFIED_SQL_ID: 3w056txhg3vdu
PLAN_HASH_VALUE: 3956160932SQLBEGIN2 IF :sql_text IS NULL THEN3 RAISE_APPLICATION_ERROR(-20100, SQL_TEXT for original SQL_ID original_sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).);4 END IF;5 END;6 /
SQLSET TERM OFF;
SQLBEGIN2 IF :other_xml IS NULL THEN3 RAISE_APPLICATION_ERROR(-20101, PLAN for modified SQL_ID modified_sql_id. and PHV plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).);4 END IF;5 END;6 /
SQL
SQLSET ECHO OFF;
0001 BEGIN_OUTLINE_DATA
0002 IGNORE_OPTIM_EMBEDDED_HINTS
0003 OPTIMIZER_FEATURES_ENABLE(11.2.0.4)
0004 DB_VERSION(11.2.0.4)
0005 OPT_PARAM(_table_scan_cost_plus_one false)
0006 ALL_ROWS
0007 OUTLINE_LEAF(SEL$1)
0008 FULL(SEL$1 EMPSEL$1)
0009 END_OUTLINE_DATA
dropping staging table STGTAB_SQLPROF_66Q0R9W4DF2F5
creating staging table STGTAB_SQLPROF_66Q0R9W4DF2F5
packaging new sql profile into staging table STGTAB_SQLPROF_66Q0R9W4DF2F5PROFILE_NAME
------------------------------
66Q0R9W4DF2F5_3956160932
SQLREM
SQLREM SQL Profile
SQLREM ~~~~~~~~~~~
SQLREM
SQLSELECT signature, name, category, type, status2 FROM dba_sql_profiles WHERE name :name;SIGNATURE NAME CATEGORY TYPE STATUS
-------------------- ------------------------------ ------------------------------ ------- --------
10938475214343355302 66Q0R9W4DF2F5_3956160932 DEFAULT MANUAL ENABLED
SQLSELECT description2 FROM dba_sql_profiles WHERE name :name;DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORIGINAL:66Q0R9W4DF2F5 MODIFIED:3W056TXHG3VDU PHV:3956160932 SIGNATURE:10938475214343355302 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQLSET ECHO OFF;****************************************************************************
* Enter SCOTT password to export staging table STGTAB_SQLPROF_66q0r9w4df2f5
****************************************************************************Export: Release 11.2.0.4.0 - Production on Tue Mar 19 17:32:01 2024Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password: ---此处要输入操作用户scott的密码Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exportedAbout to export specified tables via Conventional Path ...
. . exporting table STGTAB_SQLPROF_66Q0R9W4DF2F5 1 rows exported
Export terminated successfully without warnings.If you need to implement this Custom SQL Profile on a similar system,
import and unpack using these commands:imp SCOTT fileSTGTAB_SQLPROF_66q0r9w4df2f5.dmp tablesSTGTAB_SQLPROF_66q0r9w4df2f5 ignoreYBEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name 66Q0R9W4DF2F5_3956160932,
replace TRUE,
staging_table_name STGTAB_SQLPROF_66q0r9w4df2f5,
staging_schema_owner SCOTT );
END;
/updating: coe_load_sql_profile_66q0r9w4df2f5.log (deflated 76%)
updating: STGTAB_SQLPROF_66q0r9w4df2f5.dmp (deflated 89%)adding: coe_load_sql_profile.log (deflated 62%)deleting: coe_load_sql_profile.logcoe_load_sql_profile completed.
SQL--当看到【coe_load_sql_profile completed.】时表示执行计划固定完成。
--下面验证一下直接执行sql然后查看执行计划SCOTTPROD11G select ename from emp where enamename;no rows selectedExecution Plan
----------------------------------------------------------
Plan hash value: 3956160932--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 6 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(ENAMEname)Note
------ SQL profile 66Q0R9W4DF2F5_3956160932 used for this statementStatistics
----------------------------------------------------------7 recursive calls0 db block gets11 consistent gets1 physical reads0 redo size333 bytes sent via SQL*Net to client513 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 rows processedSCOTTPROD11G --可以看到SQL已经走了全表扫描并且可以看到使用了SQL profile【SQL profile 66Q0R9W4DF2F5_3956160932 used for this statement】固定了执行计划
3、查看SQL profile
创建完成后可以查询USER_SQL_PROFILES或DBA_SQL_PROFILES视图来确认SQL Profile是否成功创建和应用到了相应的SQL语句上。
SCOTTPROD11G select * from dba_sql_profiles;NAME CATEGORY SIGNATURE
------------------------------ ------------------------------ ----------
SQL_TEXT
--------------------------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
LAST_MODIFIED
---------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
TYPE STATUS FOR TASK_ID TASK_EXEC_NAME TASK_OBJ_ID
------- -------- --- ---------- ------------------------------ -----------
TASK_FND_ID TASK_REC_ID
----------- -----------
66Q0R9W4DF2F5_3956160932 DEFAULT 1.0938E19
select ename from emp where enamename
19-MAR-24 05.32.00.000000 PM
19-MAR-24 05.32.00.000000 PM
ORIGINAL:66Q0R9W4DF2F5 MODIFIED:3W056TXHG3VDU PHV:3956160932 SIGNATURE:109384752
14343355302 CREATED BY COE_LOAD_SQL_PROFILE.SQL
MANUAL ENABLED NO-- 可以从dba_sql_profiles查询到sql profile 【66Q0R9W4DF2F5_3956160932】
4、总结 通过固定执行计划的方法即使在Oracle优化器可能因为统计信息变化而选择不同执行计划的情况下SQL Profile也能强制数据库按照已知高效的执行计划来执行SQL语句。这有助于保持应用程序的性能稳定性。
