Pythian Blog: Technical Track

Oracle Live SQL: explain plan

by Gleb Otochkin on Apr 7, 2016 12:00:00 AM

We've all encountered a situation when you want to check a simple query or syntax for your SQL and don't have a database around. Of course, most of us have at least a virtual machine for that, but it takes time to fire it up, and if you work from battery, it can leave you without power pretty quickly. Some time ago, Oracle began to offer a new service called "Oracle Live SQL" . It provides you with the ability to test a sql query, procedure or function, and have a code library containing a lot of examples and scripts. Additionally, you can store your own private scripts to re-execute them later. It's a really great online tool, but it lacks some features. I've tried to check the execution plan for my query but, unfortunately, it didn't work: [code lang="SQL"] explain plan for select * from test_tab_1 where pk_id<10; ORA-02402: PLAN_TABLE not found [/code] So, what could we do to make it work? The workaround is not perfect, but it works and can be used in some cases. We need to create our own plan table using script from an installed Oracle database home $ORACLE_HOME/rdbms/admin/utlxplan.sql. We can open the file and copy the statement to create plan table to SQL worksheet in the Live SQL. And you can save the script in Live SQL code library, and make it private to reuse it later because you will need to recreate the table every time when you login to your environment again. So far so good. Is it enough? Let's check. [code lang="SQL"] explain plan for select * from test_tab_1 where pk_id<10; Statement processed. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ERROR: an uncaught error in function display has happened; please contact Oracle support Please provide also a DMP file of the used plan table PLAN_TABLE ORA-00904: DBMS_XPLAN_TYPE_TABLE: invalid identifier [/code] Ok, the package doesn't work. I tried to create the types in my schema but it didn't work. So far the dbms_xplan is not going to work for us and we have to request the information directly from our plan table. It is maybe not so convenient, but it give us enough and, don't forget, you can save your script and just reuse it later. You don't need to memorize the queries. Here is a simple example of how to get information about your last executed query from the plan table: [code lang="SQL"] SELECT parent_id,id, operation,plan_id,operation,options,object_name,object_type,cardinality,cost from plan_table where plan_id in (select max(plan_id) from plan_table) order by 2; PARENT_ID ID OPERATION PLAN_ID OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE CARDINALITY COST - 0 SELECT STATEMENT 268 SELECT STATEMENT - - - 9 49 0 1 TABLE ACCESS 268 TABLE ACCESS FULL TEST_TAB_1 TABLE 9 49 [/lang] I tried a hierarchical query but didn't find it too useful in the Live SQL environment. Also you may want to put unique identifier for your query to more easily find it in the plan_table. [code lang="SQL"] explain plan set statement_id='123qwerty' into plan_table for select * from test_tab_1 where pk_id<10; SELECT parent_id,id, operation,plan_id,operation,options,object_name,object_type,cardinality,cost from plan_table where statement_id='123qwerty' order by id; PARENT_ID ID OPERATION PLAN_ID OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE CARDINALITY COST - 0 SELECT STATEMENT 272 SELECT STATEMENT - - - 9 3 0 1 TABLE ACCESS 272 TABLE ACCESS BY INDEX ROWID BATCHED TEST_TAB_1 TABLE 9 3 1 2 INDEX 272 INDEX RANGE SCAN TEST_TAB_1_PK INDEX 9 2 [/code] Now I have my plan_table script and query saved in the Live SQL and reuse them when I want to check the plan for my query. I posted the feedback about the ability to use dbms_xplan and Oracle representative replied to me promptly and assured they are already working implementing dbms_xplan feature and many others including ability to run only selected SQL statement in the SQL worksheet (like we do it in SQLdeveloper). It sounds really good and promising and is going to make the service even better. Stay tuned.
Topics: Oracle Technical Track

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.