简单判断sql解析类型

我们知道sql解析可以分为软解析和硬解析，（我去，这都不知道百度去），由于硬解析和会消耗大小的性能，特别是对于oltp系统来说。

怎么判断解析类型呢，一般想到10046事件，但是不要完全信10046事件，原因是是10046sql_trace 在trace session的时候，默认session执行的每个SQL都将从新进行hard parse,产生一个新的child cursor,自然，这个被traced的cursor和原本应用的cursor就有可能因为dbms_auto_invalidations等原因使用不同的plan.

详细类容请查看这位大哥的博客 http://yumianfeilong.com/html/2008/12/29/309.html

那么要怎么做呢，两步搞定

首先我们查看系统里面解析比较多的sql

SQL> select sql_id ,a.SQL_TEXT,a.PARSE_CALLS,a.EXECUTIONS from v$sql a where rownum<=10 order by a.PARSE_CALLS desc ;

结果如下

SQL> select sql_id ,a.SQL_TEXT,a.PARSE_CALLS,a.EXECUTIONS from v$sql a where rownum<=10 order by a.PARSE_CALLS desc ;
 
SQL_ID        SQL_TEXT                                                                         PARSE_CALLS EXECUTIONS
------------- -------------------------------------------------------------------------------- ----------- ----------
bjk8auuwpn00u select product0_.PRODUCT_ID as PRODUCT_ID, product0_.type as type, product0_.shi          10         10
0q15uyf0rn006 select product0_.PRODUCT_ID as PRODUCT_ID, product0_.type as type, product0_.shi          10         10
67tqvgvch8077 select count(*) as x0_0_ from product product0_ where (product0_.isconfig!=1 )an           7          7
8663bbvz7408d select product0_.PRODUCT_ID as PRODUCT_ID, product0_.type as type, product0_.shi           6          6
96s233k75c08q select product0_.PRODUCT_ID as PRODUCT_ID, product0_.type as type, product0_.shi           5          5
2w48bkaawn03m select productcat0_.CATEGORY_ID as CATEGORY1_, productcat0_.opensub as opensub,            2          2
9r50tgqs2h053 select productcat0_.CATEGORY_ID as CATEGORY1_, productcat0_.opensub as opensub,            2          2
4a7wj92bbn04b select * from message                                                                      1          1
50ph8shy0408h select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1+kglobhs2+kglobhs3+k           1          0
c6gnc2fq1w078 select product0_.PRODUCT_ID as PRODUCT_ID, product0_.type as type, product0_.shi           1          1
 
10 rows selected

第二部，根据sql_id，查看是否被共享

我们以最多的这个sql(sql_id=bjk8auuwpn00u )来看

select * from v$sql_shared_cursor where SQL_ID='bjk8auuwpn00u';

SQL> select * from v$sql_shared_cursor where SQL_ID='bjk8auuwpn00u';
 
SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER UNBOUND_CURSOR SQL_TYPE_MISMATCH OPTIMIZER_MISMATCH OUTLINE_MISMATCH STATS_ROW_MISMATCH LITERAL_MISMATCH SEC_DEPTH_MISMATCH EXPLAIN_PLAN_CURSOR BUFFERED_DML_MISMATCH PDML_ENV_MISMATCH INST_DRTLD_MISMATCH SLAVE_QC_MISMATCH TYPECHECK_MISMATCH AUTH_CHECK_MISMATCH BIND_MISMATCH DESCRIBE_MISMATCH LANGUAGE_MISMATCH TRANSLATION_MISMATCH ROW_LEVEL_SEC_MISMATCH INSUFF_PRIVS INSUFF_PRIVS_REM REMOTE_TRANS_MISMATCH LOGMINER_SESSION_MISMATCH INCOMP_LTRL_MISMATCH OVERLAP_TIME_MISMATCH SQL_REDIRECT_MISMATCH MV_QUERY_GEN_MISMATCH USER_BIND_PEEK_MISMATCH TYPCHK_DEP_MISMATCH NO_TRIGGER_MISMATCH FLASHBACK_CURSOR ANYDATA_TRANSFORMATION INCOMPLETE_CURSOR TOP_LEVEL_RPI_CURSOR DIFFERENT_LONG_LENGTH LOGICAL_STANDBY_APPLY DIFF_CALL_DURN BIND_UACS_DIFF PLSQL_CMP_SWITCHS_DIFF CURSOR_PARTS_MISMATCH STB_OBJECT_MISMATCH ROW_SHIP_MISMATCH PQ_SLAVE_MISMATCH TOP_LEVEL_DDL_MISMATCH MULTI_PX_MISMATCH BIND_PEEKED_PQ_MISMATCH MV_REWRITE_MISMATCH ROLL_INVALID_MISMATCH OPTIMIZER_MODE_MISMATCH PX_MISMATCH MV_STALEOBJ_MISMATCH FLASHBACK_TABLE_MISMATCH LITREP_COMP_MISMATCH
------------- ---------------- ---------------- ------------ -------------- ----------------- ------------------ ---------------- ------------------ ---------------- ------------------ ------------------- --------------------- ----------------- ------------------- ----------------- ------------------ ------------------- ------------- ----------------- ----------------- -------------------- ---------------------- ------------ ---------------- --------------------- ------------------------- -------------------- --------------------- --------------------- --------------------- ----------------------- ------------------- ------------------- ---------------- ---------------------- ----------------- -------------------- --------------------- --------------------- -------------- -------------- ---------------------- --------------------- ------------------- ----------------- ----------------- ---------------------- ----------------- ----------------------- ------------------- --------------------- ----------------------- ----------- -------------------- ------------------------ --------------------
bjk8auuwpn00u 000000009E56DBC0 00000000B37D0BE8            0 N              N                 N                  N                N                  N                N                  N                   N                     N                 N                   N                 N                  N                   N             N                 N                 N                    N                      N            N                N                     N                         N                    N                     N                     N                     N                       N                   N                   N                N                      N                 N                    N                     N                     N              N              N                      N                     N                   N                 N                 N                      N                 N                       N                   N                     N                       N           N                    N                        N

我们看到v$sql_shared_cursor视图里面关键字段都是N，说明是可以共享的。v$sql_shared_cursor这个视图你不知道，我去，看我的博客

http://blog.csdn.net/rulev5/article/details/7083334，呵呵 ，收工了