我们知道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,呵呵 ,收工了