Start a new topic

Oracle 11g explain plan

[This topic is migrated from our old forums. The original author name has been removed]

I have 1 schema on ORACLE 10 and if i try to see explain plan - everything is OK. So i have same schema on 11g and if I try "explain plan" i got the error: ... Physical database connection acquired for: Animals new 2 14:15:48 [SELECT - 0 row(s), 0.000 secs] [Error Code: 904, SQL State: 42000] ORA-00904: "ANIMALS"."PLAN_TABLE"."STATEMENT_ID": invalid identifier ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors] Ideas?

[This reply is migrated from our old forums.]

Re: Oracle 11g explain plan
Hi, What exact 11g version are you using and what DbVisualizer version? I just tried with the following which works fine with DbVisualizer 6.5.9: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production It would be great if you can enable debug of DbVisualizer and re-run the explain. Read more in: http://www.dbvis.com/products/dbvis/doc/faq/faq.jsp#4.14 Post the debug output or email it. Regards Roger support@minq.se
[This reply is migrated from our old forums. The original author name has been removed]

Re: Oracle 11g explain plan
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Oracle JDBC driver 11.1.0.7.0 DbVisualizer Personal 6.5.8 Java VM: Java HotSpot(TM) Client VM Java Version: 1.6.0_11 Java Vendor: Sun Microsystems Inc. OS Name: Windows 2003 OS Arch: x86 OS Version: 5.2 15:24:24 [DEBUG AWT-EventQueue-1 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 15:24:24 [DEBUG AWT-EventQueue-1 C.invoke] DefaultEditor-0: T4CConnection.createStatement() 15:24:24 [DEBUG pool-97-thread-1 B.?] DefaultEditor-0: OracleStatementWrapper.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual") 15:24:24 [DEBUG Thread-69 H.execute] Executing... 15:24:24 [DEBUG Thread-69 H.setCurrentCatalog] DbConnection='Animals new 2' Catalog='null' Schema='null' NewCatalog='null' 15:24:24 [DEBUG Thread-69 C.invoke] DefaultEditor-0: T4CConnection.createStatement() 15:24:24 [DEBUG Thread-69 B.?] DefaultEditor-0: OracleStatementWrapper.execute("DECLARE objExists NUMBER; BEGIN SELECT count(0) INTO objExists FROM all_all_tables WHERE table_name = 'PLAN_TABLE' AND owner = 'ANIMALS'; IF objExists = 0 THEN EXECUTE IMMEDIATE 'create table ANIMALS.plan_table (statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(30), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30)) '; END IF; END;") 15:24:24 [DEBUG Thread-69 H.execute] Executing... 15:24:24 [DEBUG Thread-69 H.setCurrentCatalog] DbConnection='Animals new 2' Catalog='null' Schema='null' NewCatalog='null' 15:24:24 [DEBUG Thread-69 C.invoke] DefaultEditor-0: T4CConnection.createStatement() 15:24:24 [DEBUG Thread-69 B.?] DefaultEditor-0: OracleStatementWrapper.execute("explain plan set statement_id='dbvis-1252931064349' into ANIMALS.plan_table for select * from ADM_SYSTEM_CLASSIF where code=10") 15:24:24 [DEBUG Thread-69 H.execute] Executing... 15:24:24 [DEBUG Thread-69 H.setCurrentCatalog] DbConnection='Animals new 2' Catalog='null' Schema='null' NewCatalog='null' 15:24:24 [DEBUG Thread-69 C.invoke] DefaultEditor-0: T4CConnection.createStatement() 15:24:24 [DEBUG Thread-69 B.?] DefaultEditor-0: OracleStatementWrapper.execute("select substr(operation || ' (' || options || ')', 1, 30 ) as Phase, ID, PARENT_ID, OPERATION, OPTIONS, COST, OBJECT_OWNER, OBJECT_NAME, ANIMALS.plan_table.* from ANIMALS.plan_table start with id=0 and statement_id='dbvis-1252931064349' connect by prior id=parent_id and prior statement_id=statement_id") SQLState(42000) vendor code(904) 15:24:24 [DEBUG Thread-69 B.?] DefaultEditor-0: EXCEPTION -> java.sql.SQLException: ORA-00904: "ANIMALS"."PLAN_TABLE"."STATEMENT_ID": invalid identifier 15:24:24 [DEBUG AWT-EventQueue-1 AbstractFacade.getColumn] executing "select sys_context( 'userenv', 'current_schema' ) from dual" 15:24:24 [DEBUG AWT-EventQueue-1 C.invoke] DefaultEditor-0: T4CConnection.createStatement() 15:24:24 [DEBUG pool-101-thread-1 B.?] DefaultEditor-0: OracleStatementWrapper.executeQuery("select sys_context( 'userenv', 'current_schema' ) from dual")
[This reply is migrated from our old forums.]

Re: Oracle 11g explain plan
Hi, Sorry for the delay. We don't currently have Oracle 11g R2 available since it is so new but will install it the next days. I'll get back to you. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Oracle 11g explain plan
Hi Roger, something new about my problem? Regards
[This reply is migrated from our old forums.]

Re: Oracle 11g explain plan
Hi, Sorry for the delay. We're waiting for the Windows edition of 11g2. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Oracle 11g explain plan
Hi there, I have the exact same problem on an 11gR2 Linux Database. Actually the plan table is created, and DBVis also inserts data in the plan table, but then the error mentioned appears. A Fix would be nice... Sebastian Edited by: Sebastian Graf on 09.12.2009 16:51
[This reply is migrated from our old forums.]

Re: Oracle 11g explain plan
Sebastian, Unfortunately we've been unable to try this out. After you execute the Explain Plan feature in DbVisualizer there should be a PLAN_TABLE in the schema. What columns are in it (post the rows listed in the Columns tab for that table). Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Oracle 11g explain plan
Hi, Could you take a look at this again? I'm evaluating DbVis, just installed Oracle 11.2, and got this error on the following query: select * from SCOTT.EMP; It should be easy to replicate (hopefully)...I just enabled the Scott user and ran this query. Query runs fine; the explain plan fails with the error. I looked in the SYS/PLAN_TABLE$ table (not sure if that's the right location.?), but the table is empty. Thanks.
[This reply is migrated from our old forums.]

Re: Oracle 11g explain plan
Dan, This is fixed in the next version planned for release in June. Best Regards Roger
[This reply is migrated from our old forums.]

Re: Oracle 11g explain plan
Hi, This is now fixed in DbVisualizer 7.0.7. Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Oracle 11g explain plan
Hi Roger, this is indeed fixed but IMHO DbVisualizer shouldn't create the PLAN_TABLE at all in 11g. From that release in fact a GLOBAL TEMPORARY TABLE is automatically created and available to all users: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_5127.htm Being a temporary table, it's automatically emptied at the end of the session, so there's no need to delete records from it after the plan generation. It's created in SYS as PLAN_TABLE$ and there's a public synonym PLAN_TABLE pointing to it. You can verify: SELECT * FROM all_tables WHERE owner = 'SYS' AND table_name = 'PLAN_TABLE$'; SELECT * FROM all_synonyms WHERE owner = 'PUBLIC' AND synonym_name = 'PLAN_TABLE'; As that's available, I don't see the point of spreading copies of PLAN_TABLE around on every schema where you run an explain plan. Thank you and kind regards Kamal
[This reply is migrated from our old forums.]

Re: Oracle 11g explain plan
Kamal, You're right. The reason we still stick with the old PLAN_TABLE implementation is that is works with all versions of Oracle that DbVisualizer supports, from 8 to 11g. We have on the todo list to make this version dependent instead. Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Oracle 11g explain plan
Thanks for the quick feedback! :)
[This reply is migrated from our old forums.]

Re: Oracle 11g explain plan
Hi, Just to inform that PLAN_TABLE$ is now supported in the latest DbVisualizer 8.0 version. Regards Roger