Error Cannot Fetch Last Explain Plan From Plan_table 10g
resolution for the EXPLAIN PLAN command in comparison to everything else I'm aware of. Wth the error cannot fetch plan for statement_id autotrace usual disclaimer of: I might have missed something… EXPLAIN PLAN is oracle create plan table 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 | -------------------------------------------------------------------------------------------------------- |-------------------------------------- NOT THE QUERY I JUST RAN --------------------------------------| -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- <------------ TEXT REMOVED ------------> Note: cpu costing is off Remember the logon trigger changes my current schema to APP_SCHEMA. MARTINNASH@bi20p> alter s
首页 | 博文目录 | 关于我 pingshx 博客访问: 111980 博文数量: 45 用 户 组: 普通用户 注册时间: 1970-01-01 08:00 个人简介 鏆傛棤浠嬬粛 文章分类 全部博文(45) 一般分类(4) https://oraganism.wordpress.com/2010/01/08/explain-plan-for-anomaly/ Oracle(35) Perl(3) OS(Unix,Linux)(1) DB2(2) 未分配的博文(0) 文章存档 2010年(12) 2010年05月(1) 2010年04月(4) 2010年03月(2) 2010年02月(1) 2010年01月(4) 2009年(33) 2009年12月(2) 2009年11月(3) 2009年10月(1) 2009年09月(4) 2009年08月(5) 2009年07月(4) http://blog.itpub.net/45188/viewspace-1022784/ 2009年06月(5) 2009年05月(9) 我的朋友 最近访客 junyangt zhangxd_ urlgaoji invt_dav sohuyaho jianlian 20061805 tntpro Allen_21 微信关注 IT168企业级官微 微信号:IT168qiye 系统架构师大会 微信号:SACC2013 订阅 推荐博文 ·Tuxedo服务无法启动的问题解... ·ORA-28002触发对概要文件的理... ·巧用闪回数据库来查看历史数... ·Alert Log中回收站对象操作告... ·MySQL中的事务和锁简单测试... 热词专题 Error: cannot fetch last explain plan from plan_table 2009-06-04 13:59:13 分类: Oracle
环境:OS: Solaris 10DB: 10.2.0.3今天碰到这样的怪事,用dbms_xplan.display显示一条SQL执行计划时,报错"Error: cannot fetch last explain plan from plan_table". 一开始就觉得Plan_table的版本是不是过时了,也没仔细看,就重新生成plan_table,再跑刚条SQL,还是报一样的错误,这样看来,就不是Plan_table版本的问题了,那会不会是SQL本身的问题呢?换一条SQL:select sysdate from dual,通过dbms_xplan.display正确显示结果,再跑一下here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about http://stackoverflow.com/questions/25613444/how-to-create-explain-plan-table-on-amazon-rds-database Stack Overflow the company Business Learn more about hiring developers or posting ads with https://docs.google.com/document/d/1oWPOf9tJ86kpWu-1y1Y_eO2m3ikb-5fja1v5-zSriMY/preview us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to create EXPLAIN_PLAN table on Amazon RDS database? up vote 0 down vote error cannot favorite How to create EXPLAIN_PLAN table having no access to console? Is there an option to tick somewhere? oracle amazon-rds share|improve this question asked Sep 1 '14 at 22:10 gumol 497 are you able to connect to your rds from local computer? –zaratustra Sep 1 '14 at 22:26 yes, using python+cx_Oracle and sqlplus –gumol Sep 1 '14 at 22:33 Have you tried set autotrace error cannot fetch traceonly? –zaratustra Sep 1 '14 at 22:35 I don't think it works outside sql*plus –gumol Sep 2 '14 at 1:06 But you told me that you could connect to your database through the SQL*Plus tool. Next time express yourself clearly. –zaratustra Sep 2 '14 at 8:09 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote accepted I believe you are referring to PLAN_TABLE used in "explain plan" commands. In Oracle 11.2 it's already created by default as a temporary table in the SYS schema (PLAN_TABLE$) with public synonym and privileges to all users. So "explain plan" should work right out of the box and then you can read the results from the table: explain plan for select * from dual; select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- Plan hash value: 272002086 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- It's a temporary table, so the contents aren't saved between sessions. If for any reason you think you still