父游标:保存HASH值,SQL文本--相同SQL语句,就只有一个父游标
oracle内部是将SQL文本转化为ASCII值(大小写ASCII不同)并进行hash函数的运算
父游标里主要包含两种信息:sql文本以及优化目标。父游标在第一次打开时被锁定,直到其他所有的session都关闭该游标后才被解锁。当父游标被锁定的时候是不能被交换出librarycache的,只有在解锁以后才能被交换出library cache。父游标被交换出内存时父游标对应的所有子游标也被交换出library cache。
我们来执行两条sql语句
select * from t5 where empno=7900;
select * FROM t5 WHERE empno=7900;
然后我们进行查询
23:47:02 sys@orcl> select sql_id,sql_text,EXECUTIONS,VERSION_COUNT from v$sqlarea where sql_text like '%empno=7900';
SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ------------- -------------
c2b7t2mu9xfub select * from t5 where empno=7900 1 1
8ujbmb6j5xpc6 select * FROM t5 WHERE empno=7900 1 1
可以看到虽然只是有大小写不同 但实际上生成了两个sql_id,所以sql_id是父游标的辨别标志
另外一方面,我们也可以知道,一次父游标就代表了一次硬解析,而硬解析是我们应该完全避免的方式。为此,我们可以cursor_sharing参数与绑定变量的方式来减少父游标(即硬解析)的产生
先看当前系统的cursor_sharing
00:01:27 sys@orcl> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
1)、EXACT:通常来说,exact值是Oracle推荐的,也是默认的,它要求SQL语句在完全相同时才会重用,否则会被重新执行硬解析操作。
2)、SIMILAR:similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时,才会被重新分析,否则将重用SQL。
3)、FORCE:force是在任何情况下,无条件重用SQL。
此时我们执行
select * from t5 where empno=7788;
select * from t5 where empno=7900;
我们进行查看
00:00:40 sys@orcl> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS,LOADS from v$sqlarea where sql_text like 'select * from t5%';
SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS LOADS
------------- -------------------------------------------------- ------------- ------------- -------------
85v4jvh5yj733 select * from t5 where empno=7788 1 1 1
c2b7t2mu9xfub select * from t5 where empno=7900 1 1 1
这里我们解释下这些的含义
字段解释:
PARSE_CALLS 解析的次数
LOADS 硬解析的次数
EXECUTIONS 执行的次数
现在我们改变系统参数再来进行测试
00:15:25 sys@orcl> alter system set cursor_sharing =force;
System altered.
Elapsed: 00:00:00.05
00:17:13 sys@orcl> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE
00:17:30 sys@orcl> /
SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS LOADS
------------- -------------------------------------------------- ------------- ------------- -------------
5sfmgma8jwcna select * from t5 where empno=:"SYS_B_0" 1 1 1
此时oracle会自动的把当前的sql语句改写为带绑定变量的sql语句
多进行几次其他查询
select * from t5 where empno=7782;
select * from t5 where empno=7499;
00:18:57 sys@orcl> /
SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS LOADS
------------- -------------------------------------------------- ------------- ------------- -------------
5sfmgma8jwcna select * from t5 where empno=:"SYS_B_0" 3 3 1
可以看到硬解析不会变化
同样的我们也可以使用绑定变量进行测试
00:24:25 scott@orcl> var x number;
00:24:32 scott@orcl> exec :x:=7900;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
00:24:55 scott@orcl> select * from emp where empno=:x;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
Elapsed: 00:00:00.00
00:25:14 scott@orcl> exec :x:=7788;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
00:25:56 scott@orcl> select * from emp where empno=:x;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
00:22:13 sys@orcl> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS,LOADS from v$sql where sql_text like 'select * from emp where%';
SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS LOADS
------------- -------------------------------------------------- ------------- ------------- -------------
6r15aucqqz440 select * from emp where empno=:x 1 1 1
Elapsed: 00:00:00.08
00:25:34 sys@orcl> /
SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS LOADS
------------- -------------------------------------------------- ------------- ------------- -------------
6r15aucqqz440 select * from emp where empno=:x 2 2 1
可以看到通过使用绑定变量,可以有效的减少硬解析
子游标
当发生硬解析时,在产生父游标的同时,则跟随父游标会产生相应的子游标,此时V$SQL.CHILD_NUMBER的值为0。
如果存在父游标,由于不同的运行环境,此时同样会产生新的子游标,新子游标的CHILD_NUMBER在已有子游标基础上以1为单位累计。
子游标包括游标所有相关信息,如具体的执行计划、绑定变量,OBJECT和权限,优化器设置等。子游标随时可以被LRU算法置换出
library cache,当子游标被置换出library cache时,oracle可以利用父游标的信息重新构建出一个子游标来,这个过程叫reload。
v$sql中中 的每一行表示了一个child cursor,根据hash value和address与parent cursor 关联。
child cursor有自己的address,即v$sql.child_address。
我们也来看看可能生成子游标的两种情况
00:17:16 sys@orcl> alter system flush shared_pool;
System altered.
Elapsed: 00:00:02.19
00:30:47 sys@orcl> alter system flush buffer_cache;
System altered.
假设在不同的schema下面有相同的表,我们在不同的schema下面分别执行如下的sql语句
00:38:38 kiwi@orcl> select * from t6 where empno=7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
00:38:53 scott@orcl> select * from t6 where empno=7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
00:37:25 sys@orcl> select sql_id,sql_text,EXECUTIONS,VERSION_COUNT from v$sqlarea where sql_text like 'select * from t6%';
SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ------------- -------------
7b2sgq6rbgvpa select * from t6 where empno=7369 2 2
此时我们看到同一个sql_id存在两个版本的,我们再来看v$sql中的内容
00:43:36 sys@orcl> /
SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS LOADS HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ------------- ------------- ------------- ------------- -------------
7b2sgq6rbgvpa select * from t6 where empno=7369 1 1 1 2931289770 0
7b2sgq6rbgvpa select * from t6 where empno=7369 1 1 1 2931289770 1
可以看到存在了两个子游标
现在我们再来看另外一种情况
00:48:32 scott@orcl> select * from t6 where empno=7654;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
Elapsed: 00:00:00.07
00:48:57 scott@orcl> alter session set optimizer_mode=first_rows;
Session altered.
Elapsed: 00:00:00.05
00:49:44 scott@orcl> select * from t6 where empno=7654;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
00:43:37 sys@orcl> select sql_id,sql_text,EXECUTIONS,VERSION_COUNT from v$sqlarea where sql_text like 'select * from t6%';
SQL_ID SQL_TEXT EXECUTIONS VERSION_COUNT
------------- ---------------------------------------- ------------- -------------
2hx8th12xzgw5 select * from t6 where empno=7654 2 2
Elapsed: 00:00:00.07
00:50:02 sys@orcl> select sql_id,sql_text,EXECUTIONS,PARSE_CALLS,LOADS,HASH_VALUE,CHILD_NUMBER from v$sql where sql_text like 'select * from t6%';
SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS LOADS HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ------------- ------------- ------------- ------------- -------------
2hx8th12xzgw5 select * from t6 where empno=7654 1 1 1 1172291461 0
2hx8th12xzgw5 select * from t6 where empno=7654 1 1 1 1172291461 1
我们可以看到,在改变系统环境改变的情况下,oracle一样也可能生成新的子游标