Error Cannot Fetch Last Explain Plan From Plan_table Dbms_xplan
resolution for the EXPLAIN PLAN command in comparison to error cannot fetch plan for statement_id autotrace everything else I'm aware of. Wth the usual disclaimer oracle create plan table of: I might have missed something…聽EXPLAIN PLAN is not affected by the "ALTER SESSION SET CURRENT_SCHEMA" command. This little investigation all started with a report of "explain plan" not working in PL/SQL Developer. On digging into it we discovered 3 PLAN_TABLE tables in the database plus a couple of synonyms. The names have been changed to protect the innocent, so to speak. Anyway it looked something like this: SQL> select owner, object_name, object_type from dba_objects where object_name = 'PLAN_TABLE'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ----------- ------------------- PUBLIC PLAN_TABLE SYNONYM APP_SCHEMA PLAN_TABLE TABLE USER01 PLAN_TABLE SYNONYM SYSTEM PLAN_TABLE TABLE SYS PLAN_TABLE TABLE With the PUBLIC synonym pointing to SYSTEM.PLAN_TABLE and the synonym owned by USER01 pointing to APP_SCHEMA.PLAN_TABLE. Just to spice things up, a logon trigger used ALTER SESSION SET CURRENT_SCHEMA to APP_SCHEMA for pretty much all database users. After a quick test I confirmed that things were definitely not working as expected via SQL*Plus as shown below: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining op JServer Release 9.2.0.8.0 - Production MARTINNASH@orcl> explain plan for 2 select * from dual; Explained. MARTINNASH@orcl> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | -----
the case of partition pruning. If you are using partitions in your application there is fair chance that your database is quite big and performance demanding. If you are not working in classic Data Warehousing manner you have to care about DML statement performance. One of the basic rules of DML performance is less indexes you have and less index columns in existing indexes then higher is the DML performance. Thus one of the architectural DBA targets is to reduce the number of indexed columns by just most critical. Another axiom statement is that smaller indexes is easier to support and manage then big one, thus local indexes usually more preferable. The conclusion from two https://oraganism.wordpress.com/2010/01/08/explain-plan-for-anomaly/ previous axiom is that probably most indexes on partitioned tables in your application is non-prefixed local indexes. Now we came to the topic of this post how oracle optimizer deal with such indexes and what we can do with it. Lets create list base partitioned table transactions Column Name Comment SERNO Unique identifier PARTITIONKEY Partition Key SGENERAL Some indexed Field And create two indexes TRANSACTIONS_PK (SERNO, PARTITIONKEY) and local partitioned non-prefixed index TRANSACTIONSI (SGENERAL). Let鈥檚 start https://oraganism.wordpress.com/tag/explain-plan/ from the most common statement EXPLAIN PLAN FOR SELECT SGENERAL, count(1) FROM transactions WHERE PARTITIONKEY='JUL2012' AND SGENERAL is not null GROUP BY SGENERAL; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 2338610280 Id Operation Name Rows Bytes Cost Time Pstart Pstop 0 SELECT STATEMENT 5 55 69978 (1) 00:14:00 1 HASH GROUP BY 5 55 69978 (1) 00:14:00 2 PARTITION LIST SINGLE 5340K 56M 69727 (1) 00:13:57 KEY KEY *3 TABLE ACCESS FULL TRANSACTIONS 5340K 56M 69727 (1) 00:13:57 19 19 Predicate Information (identified by operation id): ---------------------------------- 3 - filter("STGENERAL" IS NOT NULL) Does it look like non-optimal? Optimizer has chosen to do FULL SCAN even if it has nice looking index TRANSACTIONSI. The problem is that partition pruning works only for prefixed indexes, that means you have to pay for support extra column in your index even if you do not need it because you clearly can get the partition name based from the identical rules on a table. But luckily we have extended syntaxes. Uses it we can manually define the partition that should be used. In our case the partition name is equal to the partition key. Getting the partition name is very simple for list partitions but can be received in all other cases too. EXPLAIN PLAN FOR SELECT SGENERAL, count(1) FROM TRANSACTIONS PARTITION ("JUL2012") WHERE SGENERAL is not null GROUP B
比特币 小额贷款 贷款 crm ORACLE 项目管理系统 thread error cannot android python java 文章帖子用户 抱歉,指定的主题不存在或已被删除或正在被审核 TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 error cannot fetch 文本模式 帮助 ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客 ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统 CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明 京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号 返回顶部
a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views. See Also: For more information on the EXPLAIN PLAN command, the AWR, and SQL tuning set, see Oracle Database Performance Tuning Guide. For more information on the V$SQL_PLAN and V$SQL_PLAN_STATISTICS fixed views, see Oracle Database Reference. This chapter contains the following topics: Using DBMS_XPLAN Overview Security Model Examples Summary of DBMS_XPLAN Subprograms Using DBMS_XPLAN Overview Security Model Examples Overview The DBMS_XPLAN package supplies four table functions: DISPLAY - to format and display the contents of a plan table. DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor. DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR. DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set. Security Model This package runs with the privileges of the calling user, not the package owner (SYS). The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL. Using the DISPLAY_AWR function requires the user to have SELECT privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE. To use the DISPLAY_SQLSET functionality, the calling user must have SELECT privilege on ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS. All these privileges are automatically granted as part of the SELECT_CATALOG role. Examples Displaying a Plan Table Using DBMS_XPLAN.DISPLAY Execute an explain plan command on a SELECT statement: EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename='benoit'; Display the plan using the DBMS_XPLAN.DISPLAY table function SET LINESIZE 130 SET PAGESIZE 0 SELECT * FROM table(DBMS_XPLAN.DISPLAY); This query produces the following output: Plan hash value: 3693697075 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 57 | 6 (34)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 57 | 6 (34)| 00:00:01 | |* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (34)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (34)| 00:00:01 | ----------------------------------------------------