Oracle Live SQL: explain plan

Posted in: Oracle, Technical Track

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.

email

Interested in working with Gleb? Schedule a tech call.

About the Author

Regarded by his peers as an Oracle guru, Gleb is known for being able to resolve any problem related to Oracle. He loves the satisfaction of troubleshooting, and his colleagues even say that seeking Gleb’s advice regarding an issue is more efficient than looking it up. Gleb enjoys the variety of challenges he faces while working at Pythian, rather than working on the same thing every day. His areas of speciality include Oracle RAC, Exadata, RMAN, SQL tuning, high availability, storage, performance tuning, and many more. When he’s not working, running, or cycling, Gleb can be found reading.

1 Comment. Leave new

Oracle Online Training
June 16, 2016 4:17 am

The article has given very much information about ORACLE with very clear explanation. Thank you very much.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *