By Franck Pachot

.
I’ll present here 3 ways to run a query for each result of another query. Let’s take an exemple: get all executions plan (select from dbms_xplan.display_cursor) for each of my queries (identified from v$sql). The 90’s way was to run the first query, which generates the second queries into a spool file, and execute that file. Here are easier ways, some of them coming from 12c new features lateral join and implicit statement result.

Test case

I’m running 2 queries and will identify them from their module (APPINFO from sqlplus) and user:

SQL> connect scott/tiger
Connected.

SQL> set appinfo DEMO

SQL> select * from DEPT;
SQL> select * from EMP;

I don’t show the result – you know it – but here is how I identify the queries:

SQL> select sql_id,child_number,sql_text from v$sql where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT';

SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------
5aht0fch310ca            0 select * from EMP
18wrqtcj3ksap            0 select * from DEPT

And I want to run dbms_xplan.display_cursor(sql_id,child_number) for each of them. Here are 3 ways to do it

Cursor expression

Do you know cursor expressions? In your SELECT expressions, you can have a query. Your client will retrieve it as a cursor and have to run it. sqlplus and sqldeveloper does that. Here is an example:

SQL> select
  cursor (select * from table(dbms_xplan.display_cursor(sql_id,child_number,format=>'TYPICAL'))) PLAN
  from
  (select * from v$sql where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT')
 /

which gives the following result:

PLAN
--------------------
CURSOR STATEMENT : 1

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  5aht0fch310ca, child number 0
-------------------------------------
select * from EMP

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.

CURSOR STATEMENT : 1

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  18wrqtcj3ksap, child number 0
-------------------------------------
select * from DEPT

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.

This is possible on all versions, and the output can change depending on the tool you use. sqlplus handles it as above, running each cursor.

implicit statement result

You want to do it from pl/sql? That’s possible. But you have to format the output yourself with dbms_output.

Except if you are in 12c and use the ‘implicit statement result’ new feature:

SQL> declare
  r sys_refcursor;
 begin
  for c in
  (select * from v$sql where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT')
  loop
   open r for select * from table(dbms_xplan.display_cursor(c.sql_id,c.child_number));
   dbms_sql.return_result(r);
  end loop;
 end;
 /
PL/SQL procedure successfully completed.

which gives the following result:

ResultSet #1

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  5aht0fch310ca, child number 0
-------------------------------------
select * from EMP

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.

ResultSet #2

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  18wrqtcj3ksap, child number 0
-------------------------------------
select * from DEPT

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.

This is similar to the previous approach in the way that the client has to manage the result.

Lateral inline view

This is my preferred way which is only SQL and do not depend on the client formatting. A lateral inline view is a subquery that you put in the FROM clause, using the LATERAL keyword, and which can use data from the other tables on the left of it in the FROM clause:

SQL> select plan_table_output from
  (select * from v$sql where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT'),
  lateral (select * from table(dbms_xplan.display_cursor(sql_id,child_number)))
 ;

which gives the following result:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  5aht0fch310ca, child number 0
-------------------------------------
select * from EMP

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL_ID  18wrqtcj3ksap, child number 0
-------------------------------------
select * from DEPT

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

This is a 12c new feature. It existed on previous versions but not enabled by default and not documented, but you can set it with the following event:

SQL> host oerr ora 22829

SQL> alter session set events '22829 trace name context forever';

12c came with a lot of nice improvement in SQL and PL/SQL and they are available in all editions.

Update

What is very cool about twitter is that sometimes I have to update my blog post as soon as it is published. In that case it seems that I ignored the simplest way to do that since 9i:

@FranckPachot Why not just “from v$sql, table(dbms_xplan.display_cursor(…))”? Left correlation is official and it works since 9i

— Vladimir Sitnikov (@VladimirSitnikv) December 14, 2014

So here it is:

SQL> select plan_table_output from
  2   v$sql,
  3   table(dbms_xplan.display_cursor(sql_id,child_number))
  4  where plan_hash_value>0 and module='DEMO' and parsing_schema_name='SCOTT'
  5  ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  5aht0fch310ca, child number 0
-------------------------------------
select * from EMP

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL_ID  18wrqtcj3ksap, child number 0
-------------------------------------
select * from DEPT

Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


26 rows selected.

That does not invalidate this blog post because I wanted to show those 3 features. It’s just my example which was badly chosen because dbms_xplan returns a table expression and left correlation can be used for it.

Thanks to Vladimir Sitnikov, I learned a 9i feature while blogging about 12c new feature 🙂

Post Views: 707
Thumbnail [60x60]
by
Oracle Team