Oracle Live SQL: explain plan

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:

explain plan for
select * from test_tab_1 where pk_id<10;
ORA-02402: PLAN_TABLE not found

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.

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

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:

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.

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

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.