韩都衣舍网站建设方案,建设网站需要展示什么区别,上海建设工程质量监督站网站,网站被降权的原因查看执行计划的方法Explain Plan For SQL不实际执行SQL语句#xff0c;生成的计划未必是真实执行的计划必须要有plan_tableSQLPLUS AUTOTRACE除set autotrace traceonly explain外均实际执行SQL#xff0c;但仍未必是真实计划必须要有plan_tableSQL TRACE需要启用10046戒者SQ…查看执行计划的方法Explain Plan For SQL不实际执行SQL语句生成的计划未必是真实执行的计划必须要有plan_tableSQLPLUS AUTOTRACE除set autotrace traceonly explain外均实际执行SQL但仍未必是真实计划必须要有plan_tableSQL TRACE需要启用10046戒者SQL_TRACE一般用tkprof看的更清楚些当然10046里本身也有执行计划信息V$SQL和V$SQL_PLAN可以查询到多个子游标的计划信息了但是看起来比较费劲Enterprise Manager可以图形化显示执行计划但并非所有环境有EM可用其他第三方工具注意 PL/SQL developer之类工具F5看到的执行计划未必是真实的推荐的方法 DBMS_XPLANselect * from table(dbms_xplan….);dbms_xplan.display()数据来源是Plan Tabledbms_xplan.display_cursor数据来源是Shared pool中的游标缓存FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLEArgument Name Type In/Out Default?------------------------------ ----------------------- ------ --------SQL_ID VARCHAR2 IN DEFAULTCURSOR_CHILD_NO NUMBER(38) IN DEFAULTFORMAT VARCHAR2 IN DEFAULT推荐的使用参数为select * from table(dbms_xplan.display_cursor(sqlId,null,ADVANCED ALLSTATS LAST PEEKED_BINDS));如果sqlId为NULL则显示当前session的执行计划。select * from table(dbms_xplan.display_cursor(null,null,ADVANCED ALLSTATS LAST PEEKED_BINDS));其中format的解释如下IOSTATS: Assuming that basic plan statistics are--- collected when SQL statements are executed (either by--- using the gather_plan_statistics hint or by setting the--- parameter statistics_level to ALL), this format will show--- IO statistics for all (or only for the last as shown below)--- executions of the cursor.------ MEMSTATS: Assuming that PGA memory management is enabled (i.e--- pga_aggregate_target parameter is set to a non 0 value),--- this format allows to display memory management--- statistics (e.g. execution mode of the operator, how--- much memory was used, number of bytes spilled to--- disk, ...). These statistics only apply to memory--- intensive operations like hash-joins, sort or some bitmap--- operators.------ ROWSTATS: Assuming that basic plan statistics are--- collected when SQL statements are executed (either by--- using the gather_plan_statistics hint or by setting the--- parameter statistics_level to ALL), this format will show--- row count statistics for all (or only for the last as--- shown below) executions of the cursor.------ ALLSTATS: A shortcut for IOSTATS MEMSTATS ROWSTATS------ LAST: By default, plan statistics are shown for all executions of--- the cursor. The keyword LAST can be specified to see only--- the statistics for the last execution.------ PEEKED_BINDS显示解析时使用的绑定变量。dbms_xplan.display_awr数据来源是AWR仓库基表WRH$_SQL_PLANFUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLEArgument Name Type In/Out Default?------------------------------ ----------------------- ------ --------SQL_ID VARCHAR2 INPLAN_HASH_VALUE NUMBER(38) IN DEFAULTDB_ID NUMBER(38) IN DEFAULTFORMAT VARCHAR2 IN DEFAULTCON_ID NUMBER(38) IN DEFAULTdbms_xplan.display_sqlset数据来源是SQL Set视图以上内容主要整理自maclean的oracle执行计划教学视频和pptwww.askmaclean.com/archives/read-sql-execution-plan.html
