前篇(http://space.itpub.net/17203031/viewspace-703624)中我们介绍了SQL父子游标机制和两种SQL不共享的情形。本篇中我们继续介绍由于对象不一致和cursor_sharing等因素带来的SQL不共享的问题。
5、SQL指定对象相同(2)
分别从两个schema下发出SQL,对应相同的对象。我们这里使用公有同义词技术。
SQL> show user
User is "SYS"
SQL> create public synonym m for t;
Synonym created
SQL> select /*+ demo_3 */ count(*) from m;
COUNT(*)
----------
18015
SQL> conn scott/tiger@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> select /*+ demo_3 */ count(*) from m;
COUNT(*)
----------
18015
两句SQL相同,而且指定对象相同。那么游标共享情况如何呢?
SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_3 */%';
SQL_TEXT SQL_ID OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS EXECUTIONS VERSION_COUNT
-------------------------------------------------------------------------------- ------------- -------------- ---------- --------------- -------- ---------- -------------
select /*+ demo_3 */ count(*) from m 9pdna8fx02604 ALL_ROWS 3120633860 2966233522 6CE7C19C 1 1
select /*+ demo_3 */ count(*) from m 8pvzq44h3rnnv ALL_ROWS 540791451 2966233522 695B4930 1 1
SQL> select sql_id, PARSING_SCHEMA_NAME, child_number,executions from v$sql where sql_id='9pdna8fx02604';
SQL_ID PARSING_SCHEMA_NAME CHILD_NUMBER EXECUTIONS
------------- ------------------------------ ------------ ----------
9pdna8fx02604 SYS 0 1
SQL> select sql_id, PARSING_SCHEMA_NAME, child_number,executions from v$sql where sql_id='8pvzq44h3rnnv';
SQL_ID PARSING_SCHEMA_NAME CHILD_NUMBER EXECUTIONS
------------- ------------------------------ ------------ ----------
8pvzq44h3rnnv SCOTT 0 1
结果显而易见,虽然执行SQL相同,对应对象也是相同。但是Oracle在这种情况下,也是会处理为两个父游标。这样的语句不进行共享。
6、cursor_sharing模式
在CBO时代,cursor_sharing可能是我们最常讨论的一个参数。cursor_sharing的设置,主要在于对绑定变量SQL的使用和是否将字面SQL整理为绑定变量SQL上。那么,cursor_sharing不同的设置值,是否会影响到SQL shared_cursor的使用呢?
//当前spfile中定义的参数值为EXACT;
SQL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
//当前session所使用的参数值;
SQL> select name, value from v$parameter where name='cursor_sharing';
NAME VALUE
-------------------- --------------------
cursor_sharing EXACT
此时我们调用Demo的SQL语句。
SQL> select /*+ demo_4 */ count(*) from t;
COUNT(*)
----------
18015
我们修改参数值,之后查看结果。
SQL> alter session set cursor_sharing=FORCE;
Session altered
SQL> select name, value from v$parameter where name='cursor_sharing';
NAME VALUE
-------------------- --------------------
cursor_sharing FORCE
SQL> select /*+ demo_4 */ count(*) from t;
COUNT(*)
----------
18015
此时,我们观察父子游标视图。
SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_4 */%';
SQL_TEXT SQL_ID OPTIMIZER_MODE EXECUTIONS VERSION_COUNT
--------------------------------------- ------------- -------------- ---------- -------------
select /*+ demo_4 */ count(*) from t 5v4v3gmkh8aaf ALL_ROWS 2 1
SQL> select sql_id, PARSING_SCHEMA_NAME, child_number,executions from v$sql where sql_id='5v4v3gmkh8aaf';
SQL_ID PARSING_SCHEMA_NAME CHILD_NUMBER EXECUTIONS
------------- ------------------------------ ------------ ----------
5v4v3gmkh8aaf SYS 0 2
注意,一个child cursor对应两次执行。
结论:在相同的环境下,cursor_sharing参数不会影响到一般SQL的执行情况。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-703757/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-703757/