Oracle Live SQL: explain plan

We've all encountered a situation where 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 features a code library containing a vast array of examples and scripts. Additionally, you can store your own private scripts to re-execute them later. It's a fantastic online tool, but it does lack some specific diagnostic features.

The Challenge: Execution Plans in Live SQL

I recently tried to check the execution plan for a query in Live SQL but, unfortunately, it didn't work out of the box:

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

So, what can we do to make it work? The workaround is not perfect, but it is functional and can be used in most testing scenarios.

1. Creating a Custom PLAN_TABLE

We need to create our own plan table using the script found in a standard Oracle database home: $ORACLE_HOME/rdbms/admin/utlxplan.sql.

You can open that file on a local machine, copy the statement to create the plan table, and paste it into the SQL worksheet in Live SQL. You should save this script in your Live SQL code library and mark it as private so you can reuse it later—you will need to recreate this table every time you log in to a new session.

Testing the Manual Plan

With the table created, let's see if we can generate and view a plan: 

explain plan for select * from test_tab_1 where pk_id < 10; -- Statement processed.  select * from table(dbms_xplan.display);  -- Result: -- ERROR: an uncaught error in function display has happened;  -- ORA-00904: DBMS_XPLAN_TYPE_TABLE: invalid identifier 

Unfortunately, the standard DBMS_XPLAN package does not work in this environment, and attempts to create the required types in a user schema are typically restricted.

2. Querying the PLAN_TABLE Directly

Since DBMS_XPLAN is off the table, we have to request the information directly from our manual PLAN_TABLE. It is perhaps not as convenient as a formatted package output, but it provides sufficient data for tuning. Plus, since you can save the script, you don't need to memorize the query.

Basic Plan Selection

Here is a simple example of how to retrieve information about your last executed query from the plan table: 

SELECT parent_id, id, operation, plan_id, options, object_name, object_type, cardinality, cost  FROM plan_table  WHERE plan_id IN (SELECT MAX(plan_id) FROM plan_table)  ORDER BY id; 

Output Example:

Using Statement IDs for Precision

To make finding your specific query easier—especially if you are running multiple tests—you should use a unique statement_id.

explain plan set statement_id='123qwerty' into plan_table  for select * from test_tab_1 where pk_id < 10;  SELECT parent_id, id, operation, options, object_name, object_type, cardinality, cost  FROM plan_table  WHERE statement_id='123qwerty'  ORDER BY id; 

Detailed Output Example:

Conclusion and Future Updates

Now I have my PLAN_TABLE setup script and diagnostic queries saved in my Live SQL library, ready to be reused whenever I need to check an execution plan.

I reached out to Oracle regarding the DBMS_XPLAN limitation, and a representative informed me that they are already working on implementing the package as a native feature, along with other improvements like the ability to run only selected SQL statements in the worksheet (similar to SQL Developer). It sounds very promising and will make an already great service even better.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?