标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,606)
- DB2 (22)
- MySQL (71)
- Oracle (1,470)
- Data Guard (50)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (534)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- rm -rf误删Oracle数据库恢复
- 分布式存储故障导致数据库无法启动故障处理
- read_me_recover_tn勒索恢复
- WINDOWS 下用dg broker搭建ADG(单机to单机)
- 存储故障后oracle报—ORA-01122/ORA-01207故障处理
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
月归档:四月 2019
sql plan baseline简单介绍
Oracle 11g开始,提供了一种新的固定执行计划的方法,即SQL plan baseline,中文名SQL执行计划基线(简称基线),可以认为是OUTLINE(大纲)或者SQL PROFILE的改进版本,基本上它的主要作用可以归纳为如下两个:
1、稳定给定SQL语句的执行计划,防止执行环境或对象统计信息等因子的改变对SQL语句的执行计划产生影响
2、减少数据库中出现SQL语句性能退化的概率,理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上(可以通过OPTIMIZER_USE_SQL_PLAN_BASELINE实现)
3、sql baseline对于sql 大小写,sql空格可以生效,但是对于非绑定变量sql,如果使用不同变量无法生效(无force_matching功能)
确认当前无sql baseline启用
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; no rows selected
创建场景
模拟一个表有index,如果再不强制的情况下,查询直接使用index,但是我这边要通过sql baseline模拟使用走全表扫描,实现不修改sql的情况下直接修改执行计划
SQL> create table t_xifenfei tablespace users as select * from dba_objects; Table created. SQL> create index i_xifenfei on t_xifenfei(object_id) tablespace users; Index created. SQL> execute dbms_stats.gather_table_stats('SYS','T_XIFENFEI',CASCADE=>TRUE); PL/SQL procedure successfully completed. SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100; OBJECT_NAME -------------------------------------------------------------------------------------------------------------- ORA$BASE SQL> SELECT * FROM table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID b9hj14ntjgmtr, child number 0 ------------------------------------- select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 Plan hash value: 1926396081 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=100) 19 rows selected. SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100; OBJECT_NAME -------------------------------------------------------------------------------------------------------------- ORA$BASE SQL> SELECT * FROM table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID aqgv7stwu6w5t, child number 0 ------------------------------------- select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 349 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=100) 19 rows selected.
从shared pool->library cache中直接加载sql plan baseline
SQL> set serveroutput on SQL> DECLARE 2 ret PLS_INTEGER; 3 BEGIN 4 ret := dbms_spm.load_plans_from_cursor_cache(sql_id => 'b9hj14ntjgmtr', 5 plan_hash_value => null); 6 dbms_output.put_line(ret || ' SQL plan baseline(s) created'); 7 END; 8 / 1 SQL plan baseline(s) created PL/SQL procedure successfully completed. SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ------------------------------ ------------------------------ SQL_TEXT -------------------------------------------------------------------------------------------------------------- ACC --- SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxd04acd9ab select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 YES SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','','')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID b9hj14ntjgmtr, child number 1 ------------------------------------- select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 Plan hash value: 1926396081 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=100) Note ----- - SQL plan baseline SQL_PLAN_fuuvsrqvvchxd04acd9ab used for this statement 23 rows selected.
利用第一个baseline的sql_handle创建新执行计划的baseline
SQL> set serveroutput on SQL> DECLARE 2 ret pls_integer; 3 begin 4 ret := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 5 sql_id=>'aqgv7stwu6w5t', 6 plan_hash_value=>548923532,sql_handle=>'SQL_ed6b78bdb7b643ad' 7 ); 8 dbms_output.put_line(ret || ' SQL plan baseline(s) created'); 9 end; 10 / 1 SQL plan baseline(s) created PL/SQL procedure successfully completed. SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ------------------------------ ------------------------------ SQL_TEXT -------------------------------------------------------------------------------------------------------------- ACC --- SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxd04acd9ab select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 YES SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxdf0c521d1 select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 YES
删除第一个baseline
SQL> set serveroutput on SQL> DECLARE 2 ret pls_integer; 3 begin 4 ret := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SQL_ed6b78bdb7b643ad' ,plan_name=>'SQL_PLAN_fuuvsrqvvchxd04acd9ab'); 5 dbms_output.put_line(ret || ' SQL plan baseline(s) created'); 6 end; 7 / 1 SQL plan baseline(s) created PL/SQL procedure successfully completed. SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ------------------------------ ------------------------------ SQL_TEXT -------------------------------------------------------------------------------------------------------------- ACC --- SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxdf0c521d1 select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 YES
验证baseline生效,实现sql语句执行计划的改变
SQL> alter system flush shared_pool; System altered. SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100; OBJECT_NAME -------------------------------------------------------------------------------------------------------------- ORA$BASE SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','','')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID b9hj14ntjgmtr, child number 1 ------------------------------------- select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 349 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=100) Note ----- - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement 22 rows selected.
利用coe脚本利用baseline快速绑定sql执行计划
SQL> set serveroutput on SQL> DECLARE 2 ret pls_integer; 3 begin 4 ret := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SQL_ed6b78bdb7b643ad', plan_name=>'SQL_PLAN_fuuvsrqvvchxdf0c521d1'); 5 dbms_output.put_line(ret || ' SQL plan baseline(s) created'); 6 end; 7 / 1 SQL plan baseline(s) created PL/SQL procedure successfully completed. SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; no rows selected SQL> alter system flush shared_pool; System altered. SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100; OBJECT_NAME -------------------------------------------------------------------------------------------------------------- ORA$BASE SQL> select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100; OBJECT_NAME -------------------------------------------------------------------------------------------------------------- ORA$BASE SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%'; SQL_ID ------------- SQL_TEXT -------------------------------------------------------------------------------------------------------------- aqgv7stwu6w5t select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 7jdqvvnpxb9z5 select sql_id,sql_text from v$sql where sql_text like '%from T_XIFENFEI where OBJECT_ID=100%' b9hj14ntjgmtr select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 SQL> select sql_id,PLAN_HASH_VALUE from v$sql where sql_id in('b9hj14ntjgmtr','aqgv7stwu6w5t'); SQL_ID PLAN_HASH_VALUE ------------- --------------- aqgv7stwu6w5t 548923532 b9hj14ntjgmtr 1926396081 SQL> select * from table(dbms_xplan.display_cursor('aqgv7stwu6w5t','','')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID aqgv7stwu6w5t, child number 0 ------------------------------------- select /*+FULL(T_XIFENFEI)*/OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 349 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=100) 19 rows selected. SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','','')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- SQL_ID b9hj14ntjgmtr, child number 0 ------------------------------------- select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 Plan hash value: 1926396081 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=100) 19 rows selected. SQL> @coe_load_sql_baseline.sql Parameter 1: ORIGINAL_SQL_ID (required) Enter value for 1: b9hj14ntjgmtr Parameter 2: MODIFIED_SQL_ID (required) Enter value for 2: aqgv7stwu6w5t PLAN_HASH_VALUE AVG_ET_SECS -------------------- -------------------- 548923532 .003 Parameter 3: PLAN_HASH_VALUE (required) Enter value for 3: 548923532 SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ------------------------------ ------------------------------ SQL_TEXT -------------------------------------------------------------------------------- ACC --- SQL_ed6b78bdb7b643ad SQL_PLAN_fuuvsrqvvchxdf0c521d1 select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 YES SQL> alter system flush shared_pool ; System altered. SQL> select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100; OBJECT_NAME -------------------------------------------------------------------------------- ORA$BASE SQL> select * from table(dbms_xplan.display_cursor('b9hj14ntjgmtr','','')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID b9hj14ntjgmtr, child number 1 ------------------------------------- select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 349 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=100) Note ----- - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement 22 rows selected.
sql空格和大小写改变不影响baseline效果
SQL> select * from table(dbms_xplan.display_cursor('dwfxd7x6kwx6u','','')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID dwfxd7x6kwx6u, child number 1 ------------------------------------- select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=100 Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 349 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=100) Note ----- - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement 22 rows selected. SQL> select OBJECT_NAME from T_xifenfei where OBJECT_ID=100; OBJECT_NAME -------------------------------------------------------------------------------- ORA$BASE SQL> select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OBJECT_ID=100%'; SQL_ID ------------- SQL_TEXT -------------------------------------------------------------------------------- 5spn2x6ac44af select sql_id,sql_text from v$sql where sql_text like '%from T_xifenfei where OB JECT_ID=100%' 8tytmh8r6w80n select OBJECT_NAME from T_xifenfei where OBJECT_ID=100 SQL> select * from table(dbms_xplan.display_cursor('8tytmh8r6w80n','','')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 8tytmh8r6w80n, child number 1 ------------------------------------- select OBJECT_NAME from T_xifenfei where OBJECT_ID=100 Plan hash value: 548923532 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 349 (100)| | |* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 30 | 349 (1)| 00:00:05 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=100) Note ----- - SQL plan baseline SQL_PLAN_fuuvsrqvvchxdf0c521d1 used for this statement 22 rows selected.
但是sql变量不一样导致baseline失效
SQL> select * from table(dbms_xplan.display_cursor('fp9u8wkp5cuw1','','')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID fp9u8wkp5cuw1, child number 0 ------------------------------------- select OBJECT_NAME from T_XIFENFEI where OBJECT_ID=101 Plan hash value: 1926396081 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 30 | 2 (0)|00:00:01 | |* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=101) 19 rows selected.
发表在 Oracle性能优化
评论关闭