演员表： A——————boylook B——————hr C——————david SQL> show user USER is "BOYLOOK" SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- USERENV VIEW TEST_REFED TABLE TO_TABLE TABLE FROM_TABLE TABLE TEST TABLE SQL> create table t(x int); Table created. SQL> select * from t; no rows selected SQL> select * from T; no rows selected SQL> create public synonym t for t; Synonym created. SQL> grant select on t to david; Grant succeeded. SQL> conn hr Enter password: Connected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- REGIONS TABLE COUNTRIES TABLE LOCATIONS TABLE DEPARTMENTS TABLE JOBS TABLE EMPLOYEES TABLE JOB_HISTORY TABLE EMP_DETAILS_VIEW VIEW RLS_TEST TABLE 9 rows selected. SQL> create table t(x int); Table created. SQL> select * from t; no rows selected SQL> select * from t; no rows selected SQL> conn david Enter password: Connected. SQL> select * from t; no rows selected SQL> conn boylook Enter password: Connected. 让我们看看现在shared_pool里有几条sql呢： SQL> select address,executions,sql_text from v$sql 2 where upper(sql_text) like 'SELECT * FROM%'; ADDRESS EXECUTIONS -------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 309D0E74 2 ←哪两条共享了这句呢？ select * from t 309D0E74 1 select * from t 308E306C 1 select * from t ADDRESS EXECUTIONS -------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 309D0B00 1 select * from T SQL> show user USER is "BOYLOOK" SQL> show parameter optimizer_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_mode string ALL_ROWS SQL> alter system set optimizer_mode = choose; System altered. SQL> select * from t; no rows selected SQL> select address,executions,sql_text from v$sql 2 where upper(sql_text) like 'SELECT * FROM%'; ADDRESS EXECUTIONS -------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 309D0E74 2 select * from t 309D0E74 1 select * from t 309D0E74 1 select * from t ADDRESS EXECUTIONS -------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 308E306C 1 select * from t 309D0B00 1 select * from T 为了看清楚点，多执行了两次： SQL> select * from t; no rows selected SQL> select * from t; no rows selected OK揭示最终的结果： SQL> select address,executions,sql_text from v$sql 2 where upper(sql_text) like 'SELECT * FROM%'; ADDRESS EXECUTIONS -------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 309D0E74 2 select * from t 309D0E74 1 select * from t 309D0E74 3 select * from t ADDRESS EXECUTIONS -------- ---------- SQL_TEXT -------------------------------------------------------------------------------- 308E306C 1 select * from t 309D0B00 1 select * from T —————————————————————— 非常华丽的分割线 —————————————————————————— 一句sql从打开游标到最终返回结果或者事务结束需要经历一段漫长的旅途。关于共享SQL，主要发生在解析（parse）阶段。那么在解析的时候是什么样的呢？ 首先，检查VPD约束条件（是否有安全策略生成的约束条件，有的话要加到where子句中）。 然后检查语法，语义以及权限：语义指的是引用的对象是否都存在，并且是否用户有权限按照现在的方式访问它们。 这之后，在library cache里面寻找是否存在共享的父游标（将sql散列之后去共享池找相同散列值的sql文本，然后进行比较，以确保sql_text完全一致），如果不存在，就在library cache里分配内存并产生新的父游标。 检查是否存在共享子游标（warning：要获取library cache latch）： 检查语义：如果父游标可共享，第一步检查语义：比如上面的A和B都发出了select * from t；但是其实A访问的是A.t，而B访问的是B.t。这样子游标是无法共享的。第二步检查环境：比如优化器的模式，或者pga等。像上面的optimizer_mode从all_rows——>choose，因此虽然A发出了两条select * from t；却无法共享子游标。只有语义和环境等都统一（具体条件可以查询v$sql_shared_cursor），才能共享子游标，直接用执行计划。 如果无法共享子游标，第一步进行逻辑优化（通过各种等价转换，会生成一些列语义上相同的sql）；第二步物理优化（对于这一系列的sql生成执行计划，并且根据数据字典的统计信息或者dynamic sampling的信息进行成本cost的计划，选出cost最小的执行计划）。理想上是把一个sql彻底的进行优化，把全部的所有的等价的sql都列出来进行成本计算，可是这样并不现实（执行一句多表的select。。。那是多少排列组合），所以在8i,9i有一个参数来控制生成最大的等价sql集合的大小就是——optimizer_max_permutations。在10g里面是隐藏参数。 这样，就生成了一个子游标，再分配内存（warning：要获取share pool latch），然后插入（释放share pool latch并获取library cache latch）。 然后执行游标，继续接下来的漫长旅行。。。。。。 当父游标和子游标都可以共享的时候，我们称发生了一个软解析(soft parse)；否则是硬解析(hard parse)。从性能上来看，硬解析是可怕的。。其实最好是不解析。。这是后话。并且，还有个参数cursor_sharing也影响游标的共享，不过一般都是权宜之计。留给筒子们去思考啦~
本文转自MIKE老毕 51CTO博客，原文链接：http://blog.51cto.com/boylook/1298622，如需转载请自行联系原作者