从oracle 10g开始,oracle推出了存储提纲用来固定sql语句的执行计划,在oracle 11g后存储提纲被sql计划基线(sql plan baseline)取而代之,sql plan baseline也和存储提纲一样被用来提供稳定的执行计划,简单来讲也是固化sql语句执行计划的,而同样固化执行
从oracle 10g开始,oracle推出了存储提纲用来固定sql语句的执行计划,在oracle 11g后存储提纲被sql计划基线(sql plan baseline)取而代之,sql plan baseline也和存储提纲一样被用来提供稳定的执行计划,简单来讲也是固化sql语句执行计划的,而同样固化执行计划的还有hint、sql profile、存储提纲等,下面小鱼简单来对存储提纲和sql计划基线做一定的介绍。
Oracle stored outline(存储提纲)和sql计划基线最主要的作用就是用来保持固定sql语句的执行计划,让sql语句的执行计划在数据库环境变更(统计信息、优化器参数、表结果变更等)后还能保持原来的执行计划。
比如我们升降级数据库会由于优化器版本的不同造成执行计划差异、统计信息不准确导致执行计划可能出现波动、优化器的bug等造成执行计划不合理等,我们都可以采取oracle的存储提纲和sql计划基线来固化sql的执行计划。
下面来看看oracle的stored outline是如何工作的:
存储提纲是一个和sql语句相关的对象,存储在数据字典里的,优化器正是根据存储提纲关联的执行计划来固化sql语句执行计划。
首先对sql语句进行标准化,移除空白并转换非字段值的部分为大写,为标准化的sql语句计算一个签名。注意该签名是一个hash value,然后当发现相同签名的存储提纲时,就会去检查当前执行的sql语句和存储提纲记录的sql语句是否相同,如果相同则使用存储提纲内的执行计划。
关于创建存储提纲有两种方法,分别是自动创建和手动创建,自动创建需要结合初始化参数create_stored_outlines,手动创建可以用create outline或者dbms_outln.create_outline来创建。
这里小鱼主要来演示下手动创建存储提纲。
SQL> create table t_out01 as select * from dba_objects;
Table created.
SQL> create or replace outline outline01 for category outline on select object_name from t_out01 where object_id=1099;
Outline created.
SQL> select name,category,used,enabled,sql_text from user_outlines;
NAME CATEGORY USED ENABLED
------------------------------ ------------------------------ ------ --------
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE01 OUTLINE UNUSED ENABLED
select object_name from t_out01 where object_id=1099
SQL> select hint from user_outline_hints where name='OUTLINE01';
HINT
--------------------------------------------------------------------------------
FULL(@"SEL$1" "T_OUT01"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_push_join_union_view' 'false')
OPT_PARAM('_push_join_predicate' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
7 rows selected.
SQL> alter system set use_stored_outlines=OUTLINE;
System altered.
SQL> select object_name from t_out01 where object_id=1099;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
V$PARAMETER
SQL> set linesize 140
SQL> select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a5fdfsf61dk3f, child number 0
-------------------------------------
select object_name from t_out01 where object_id=1099
Plan hash value: 43222384
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 234 (100)| |
|* 1 | TABLE ACCESS FULL| T_OUT01 | 1 | 26 | 234 (1)| 00:00:03 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1099)
18 rows selected.
SQL> create index ind_obj_id on t_out01(object_id);
Index created.
SQL> select object_name from t_out01 where object_id=1099;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
V$PARAMETER
Execution Plan
----------------------------------------------------------
Plan hash value: 2483087502
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OUT01 | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJ_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1099)
这个数据库版本是linux的10.2.0.4.0的版本,又尝试了windows的一个10.2.0.4.0版本的数据库,结果还是如此,就是创建的outline优化器并不去使用它,刚开始小鱼觉得可能是否和bug有关的,于是换了一个11.2.0.1的库还是如此,群里的一个热情的朋友测试存储提纲也没问题,人品真的很有点差啊,不过对于这类问题我们可不能就直接扔到一边去,小鱼一直坚信能遇见就是幸运的。
对于上述测试小鱼均都是使用的sys用户测试的,我们换一个用户来试试看
SQL> show user;
USER is "XIAOYU"
SQL> create table t_out01 as select * from dba_objects;
Table created.
SQL> create or replace outline outline1 for category cate on select object_name
from t_out01 where object_id=1009;
Outline created.
SQL> set autotrace on exp
SQL> set linesize 140;
SQL> select object_name from t_out01 where object_id=1009;
OBJECT_NAME
--------------------------------------------------------------------------------
------------------------------------------------
V$LOG
Execution Plan
----------------------------------------------------------
Plan hash value: 43222384
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 158 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_OUT01 | 8 | 632 | 158 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1009)
Note
-----
- dynamic sampling used for this statement
SQL> set autotrace off
SQL> select name,category,used,sql_text from user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE1 CATE UNUSED
select object_name from t_out01 where object_id=1009
SQL> alter session set use_stored_outlines=cate;
Session altered.
SQL> set autotrace on exp
SQL> select object_name from t_out01 where object_id=1009;
OBJECT_NAME
--------------------------------------------------------------------------------
------------------------------------------------
V$LOG
Execution Plan
----------------------------------------------------------
Plan hash value: 43222384
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 581 | 45899 | 158 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_OUT01 | 581 | 45899 | 158 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1009)
Note
-----
- outline "OUTLINE1" used for this statement
看出这里的note表示已经使用了outline OUTLINE1,下面我们建立索引
SQL> create index indid on t_out01(object_id);
Index created.
SQL> select object_name from t_out01 where object_id=1009;
OBJECT_NAME
--------------------------------------------------------------------------------
------------------------------------------------
V$LOG
Execution Plan
----------------------------------------------------------
Plan hash value: 1679505401
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 581 | 45899 | 4 (0)| 00:
00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OUT01 | 581 | 45899 | 4 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | INDID | 232 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1009)
Note
-----
- outline "OUTLINE1" used for this statement
但是建议索引后,执行计划好像还是变化了,从之前的全表扫描变为了索引扫描。
SQL> set autotrace off
SQL> select name,category,used,sql_text from user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE1 CATE USED
select object_name from t_out01 where object_id=1009
SQL> select hint from dba_outline_hints where name='OUTLINE1';
HINT
--------------------------------------------------------------------------------
FULL(@"SEL$1" "T_OUT01"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
防止set autotrace的执行计划不准,我们用dbms_xplan.display_cursor看看真实的执行计划
SQL> select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 7k0h3uvq0xufh, child number 0
-------------------------------------
select object_name from t_out01 where object_id=1009
Plan hash value: 1679505401
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | | | 4 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| T_OUT01 | 581 | 45899 | 4 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | INDID | 232 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1009)
Note
-----
- outline "OUTLINE1" used for this statement
Note是显示使用了outline outline1,但是执行计划依然还是索引扫描,而我们查看outline outline1对应的hint中还是原来的全表扫描的执行计划,对于这个问题问了一些朋友,都没有合适的答案。
首先这里我们确定了stored outline对于sys用户无效,这个还没有找到合适的资料和文档来说明,第二个是10.2.0.4版本的数据库,虽然执行计划提示使用了存储提纲,但是执行计划确没有使用stored outline的执行计划,找了半天在mos上发现了一篇关于stored outline的bug ,有兴趣的朋友可以试试打patch看看!
Bug 6455659 - Stored outlines do not work in multibyte DB
而在oracle性能诊断艺术那本书中也提到了即使是使用了存储提纲,执行计划依然可能变化,也正是这个原因,存储提纲很少在生产环境中大批量的使用。
小鱼再找来一个11.2.0.1的数据库来进行存储提纲的测试:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table t as select * from dba_objects;
Table created.
SQL> create or replace outline outline01 for category cate on select object_name from t where object_id=1090;
Outline created.
SQL> show user;
USER is "TEST"
SQL> set autotrace on exp
SQL> set linesize 140
SQL> select object_name from t where object_id=1090;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000001087C00012$$
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 948 | 299 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 948 | 299 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1090)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> create index ind_id on t(object_id);
Index created.
SQL> select object_name from t where object_id=1090;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000001087C00012$$
Execution Plan
----------------------------------------------------------
Plan hash value: 4043158466
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1090)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> alter system set use_stored_outlines=cate;
System altered.
SQL> select object_name from t where object_id=1090;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000001087C00012$$
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 895 | 70705 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1090)
Note
-----
- outline "OUTLINE01" used for this statement
SQL> alter session set use_stored_outlines=false;
Session altered.
SQL> select object_name from t where object_id=1090;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000001087C00012$$
Execution Plan
----------------------------------------------------------
Plan hash value: 4043158466
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1090)
Note
-----
- dynamic sampling used for this statement (level=2)
此时我们发现同样的测试在11.2.0.1上面则优化器使用了outline记录的hint 全表扫描,小鱼又找了几个10.2.0.4的库都是上述的情况,而这个测试更加给我们一个清晰的认识,就是一般而言我们不要在生产环境中大量使用存储提纲,即使设置了优化器也可能稀里糊涂的不去用outline的执行计划。
还有一种手动创建存储提纲的办法就是利用dbms_outln.create_outline 来创建存储提纲,dbms_outln包还有很多关于outline的procedure和function,有兴趣的同学可以倒腾下
SQL> desc dbms_outln;
PROCEDURE CREATE_OUTLINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HASH_VALUE NUMBER IN
CHILD_NUMBER NUMBER IN
CATEGORY VARCHAR2 IN DEFAULT
SQL> drop table t;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> select object_name,object_type from t where object_id=1099;
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
LOGMNR_SESSION_PK
INDEX
SQL> select hash_value from v$sql where sql_text like 'select object_name,object_type from t where object_id=1099%';
HASH_VALUE
----------
300252238
SQL> declare
2 begin
3 dbms_outln.create_outline(
4 hash_value=>300252238,
5 child_number=>0);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-16953: Type of SQL statement not supported.
ORA-06512: at "SYS.OUTLN_PKG", line 324
ORA-06512: at "SYS.OUTLN_PKG", line 368
ORA-06512: at line 3
这里又报出错误了,查了半天资料没有发现一个合理的说法,现在测试的版本是11.2.0.1,而在别的版本中dbms_outln.create_outline是可以创建cache cursor的stored outline的,小鱼个人推断这个又可能是个bug,测试这个运气确实有点背
上面介绍了手动收集,下面简单来讲下自动收集,自动收集outline需要一个很重要的参数就是create_stored_outlines
SQL> create table t_auto01 as select * from dba_objects;
Table created.
SQL> alter session set create_stored_outlines=true;
Session altered.
SQL> select object_name from t_auto01 where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXPACT$
SQL> select object_name from t_auto01 where object_id=1001;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
NOEXP$
SQL> alter session set create_stored_outlines=false;
Session altered.
SQL> create index ind_auto_id on t_auto01(object_id);
Index created.
SQL> select name,category,used,sql_text from user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_14061917391589506 DEFAULT UNUSED
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB
SYS_OUTLINE_14061917391589305 DEFAULT UNUSED
select object_name from t_auto01 where object_id=1000
SYS_OUTLINE_14061917393895607 DEFAULT UNUSED
select object_name from t_auto01 where object_id=1001
这里设置create_stored_outlines=true后,默认这个session就开始收集sql语句并创建对应的outlines
SQL> set autotrace on exp
SQL> select object_name from t_auto01 where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXPACT$
Execution Plan
----------------------------------------------------------
Plan hash value: 2314754062
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_AUTO_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> select object_name from t_auto01 where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXPACT$
Execution Plan
----------------------------------------------------------
Plan hash value: 795571617
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 895 | 70705 | 300 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1000)
Note
-----
- outline "SYS_OUTLINE_14061917391589305" used for this statement
SQL> select object_name from t_auto01 where object_id=1001;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
NOEXP$
Execution Plan
----------------------------------------------------------
Plan hash value: 795571617
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 895 | 70705 | 300 (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1001)
Note
-----
- outline "SYS_OUTLINE_14061917393895607" used for this statement
SQL> select object_name from t_auto01 where object_id=1002;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXPPKGOBJ$
Execution Plan
----------------------------------------------------------
Plan hash value: 2314754062
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_AUTO_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1002)
Note
-----
- dynamic sampling used for this statement (level=2)
这里最后一个sql语句由于sql_text不相同,所以没有配对的outline hint可供使用。
而如果是针对一类sql语句,我们不太可能每个去创建outline,可以针对该sql改成bind value然后直接对bind后的sql创建outline
SQL> variable v_id number;
SQL> exec :v_id:=10000;
PL/SQL procedure successfully completed.
SQL> create outline outline02 on select object_name,object_type from t where object_id=:v_id;
Outline created.
SQL> select name,category,used,sql_text from user_outlines where name='OUTLINE02';
NAME CATEGORY USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE02 DEFAULT UNUSED
select object_name,object_type from t where object_id=:v_id
下面创建了object_id上的索引
SQL> create index ind_id on t(object_id);
Index created.
SQL> alter session set use_stored_outlines=false;
Session altered.
SQL> select object_name,object_type from t where object_id=:v_id;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
KU$_EQNTABLE_BYTES_ALLOC_VIEW
VIEW
Execution Plan
----------------------------------------------------------
Plan hash value: 4043158466
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 855 | 76950 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 855 | 76950 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID | 342 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:V_ID))
Note
-----
- dynamic sampling used for this statement (level=2)
当设置use_stored_outlines=true后,优化器采取的outline中的执行计划。
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> select object_name,object_type from t where object_id=:v_id;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
KU$_EQNTABLE_BYTES_ALLOC_VIEW
VIEW
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 80550 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 895 | 80550 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=TO_NUMBER(:V_ID))
Note
-----
- outline "OUTLINE02" used for this statement
原文地址:oracle的存储提纲简介, 感谢原作者分享。

oracle asm指的是“自动存储管理”,是一种卷管理器,可自动管理磁盘组并提供有效的数据冗余功能;它是做为单独的Oracle实例实施和部署。asm的优势:1、配置简单、可最大化推动数据库合并的存储资源利用;2、支持BIGFILE文件等。

方法:1、利用“select*from user_indexes where table_name=表名”语句查询表中索引;2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

在Oracle中,可利用lsnrctl命令查询端口号,该命令是Oracle的监听命令;在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态,语法为“lsnrctl status”,结果PORT后的内容就是端口号。

在oracle中,可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角;“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符,只有当数据库字符集同时包含多字节和单字节字符的时候有效。

在oracle中,可以利用“drop sequence sequence名”来删除sequence;sequence是自动增加数字序列的意思,也就是序列号,序列号自动增加不能重置,因此需要利用drop sequence语句来删除序列。

在oracle中,可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

方法:1、利用“LOWER(字段值)”将字段转为小写,或者利用“UPPER(字段值)”将字段转为大写;2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”,当参数设置为“i”时,说明进行匹配不区分大小写。

方法:1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数;2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

Dreamweaver CS6
視覺化網頁開發工具

SecLists
SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合,這些清單在安全評估過程中經常使用,而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表,幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上,他就可以存取所需的每種類型的清單。

Safe Exam Browser
Safe Exam Browser是一個安全的瀏覽器環境,安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問,並防止學生使用未經授權的資源。

EditPlus 中文破解版
體積小,語法高亮,不支援程式碼提示功能

mPDF
mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),