I really thought I’d blogged about this before, but it appears not!
First of all, this isn’t something I’ve worked out, I’m just recycling something that John Beresniewicz (who seems to be better known as JB) shared during one of his presentations at the UKOUG Conference 2011. This wasn’t a big part of the presentation, but it jumped out at me as a gem of knowledge that I was unlikely to get from anywhere else. In fact, as I recall, John described how he only became aware of the functionality as a result of discussing what he was working on, and the problem he had, to a member of the database server development team at Oracle.
That nicely brings me to the usual, and very important, point about Oracle functionality that isn’t documented: It is not supported (unless someone from Oracle tells you otherwise).
So, did you know that as well as the GV$ views in an Oracle database there is a GV$ function?
Keeping it really simple to start with…
Selecting INSTANCE_NAME for all instances via GV$INSTANCE
SQL> select instance_name from gv$instance; INSTANCE_NAME ---------------- ORCL1 ORCL2 SQL>
Selecting INSTANCE_NAME for all instances via V$INSTANCE with the GV$ Function
SQL> select instance_name from TABLE(GV$(CURSOR(select instance_name from v$instance))); INSTANCE_NAME ---------------- ORCL1 ORCL2 SQL>
So, apart from being cool because it’s undocumented, what good is the GV$ function? Well, in the above example it is no use at all apart from allowing you to type more characters and confuse anyone that is looking over your shoulder!
However, what it is doing is executing the statement in CURSOR( ) on each instance in the cluster database and returning the results to the Query Coordinator (QC) session.
I don’t remember the specifics of what John was working on, but as I recall the issue was that, on the basis of performance, he didn’t want to retrieve all the relevant rows from two GV$ views in order to join them locally. I’ll try to demonstrate the point below using a join between [G]V$SQL and [G]V$SESSION
Preparation
The first step was to execute the same statement a number of times in each of my two instances.
Having obtained the SQL_ID for the statement the next step was to query the GV$ views in order to get some output – Note this is an artificially simple statement compared with where you would get a major benefit from this approach.
Querying GV$ Views
I’ve used autotrace as it’s a nice way to get the statement, results and execution plan without having to type/run too many commands…
SQL> select se.inst_id
2 , se.sql_id
3 , sq.executions
4 from gv$sql sq
5 , gv$session se
6 where se.sql_id = sq.sql_id
7 and se.inst_id = sq.inst_id
8 and se.sql_id = 'cw1knhbvzvdbf'
9 /
INST_ID SQL_ID EXECUTIONS
---------- ------------- ----------
1 cw1knhbvzvdbf 15
2 cw1knhbvzvdbf 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3376983457
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 1 (100)| 00:00:01 | | | |
|* 1 | HASH JOIN | | 1 | 55 | 1 (100)| 00:00:01 | | | |
| 2 | PX COORDINATOR | | 1 | 34 | 0 (0)| 00:00:01 | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
|* 4 | VIEW | GV$SQL | | | | | Q1,00 | PCWP | |
|* 5 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | PX COORDINATOR | | 1 | 21 | 0 (0)| 00:00:01 | | | |
| 7 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 99 | 0 (0)| 00:00:01 | Q2,00 | P->S | QC (RAND) |
|* 8 | VIEW | GV$SESSION | | | | | Q2,00 | PCWP | |
| 9 | NESTED LOOPS | | 1 | 99 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
| 10 | NESTED LOOPS | | 1 | 86 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
|* 11 | FIXED TABLE FULL | X$KSUSE | 1 | 60 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
|* 12 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 26 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
|* 13 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 | Q2,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SQL_ID"="SQL_ID" AND "INST_ID"="INST_ID")
4 - filter("SQL_ID"='cw1knhbvzvdbf')
5 - filter("KGLOBT03"='cw1knhbvzvdbf')
8 - filter("SQL_ID"='cw1knhbvzvdbf')
11 - filter("S"."KSUSESQI"='cw1knhbvzvdbf' AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
12 - filter("S"."INDX"="W"."KSLWTSID")
13 - filter("W"."KSLWTEVT"="E"."INDX")
Note
-----
- statement not queuable: gv$ statement
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
The next part was to use the GV$ funtion.
Querying V$ Views with GV$ Function
Running effectively the same statement with the GV$ function.
SQL> select * from TABLE(GV$(CURSOR(select i.instance_number inst_id
2 , se.sql_id
3 , sq.executions
4 from v$sql sq
5 , v$session se
6 , v$instance i
7 where se.sql_id = sq.sql_id
8 and se.sql_id = 'cw1knhbvzvdbf')))
9 /
INST_ID SQL_ID EXECUTIONS
---------- ------------- ----------
1 cw1knhbvzvdbf 15
2 cw1knhbvzvdbf 10
Execution Plan
----------------------------------------------------------
Plan hash value: 1284594253
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3400 | 1 (100)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | 100 | 3400 | 1 (100)| 00:00:01 | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 4200 | 1 (100)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | VIEW | | | | | | Q1,00 | PCWP | |
| 4 | MERGE JOIN CARTESIAN | | 100 | 4200 | 1 (100)| 00:00:01 | Q1,00 | PCWP | |
|* 5 | HASH JOIN | | 1 | 29 | 1 (100)| 00:00:01 | Q1,00 | PCWP | |
| 6 | VIEW | V_$SQL | 1 | 21 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 7 | VIEW | V$SQL | 1 | 21 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | VIEW | GV$SQL | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 9 | FIXED TABLE FIXED INDEX | X$KGLCURSOR_CHILD (ind:2) | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | VIEW | V_$SESSION | 1 | 8 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 11 | VIEW | V$SESSION | 1 | 8 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 12 | VIEW | GV$SESSION | 1 | 21 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 13 | NESTED LOOPS | | 1 | 99 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 14 | NESTED LOOPS | | 1 | 86 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 15 | FIXED TABLE FULL | X$KSUSE | 1 | 60 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 16 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 26 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 17 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 18 | BUFFER SORT | | 100 | 1300 | 1 (100)| 00:00:01 | Q1,00 | PCWP | |
| 19 | VIEW | V_$INSTANCE | 100 | 1300 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 20 | VIEW | V$INSTANCE | 100 | 1300 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 21 | VIEW | GV$INSTANCE | 100 | 2600 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 22 | MERGE JOIN CARTESIAN | | 100 | 6000 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 23 | MERGE JOIN CARTESIAN | | 1 | 60 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 24 | FIXED TABLE FULL | X$KSUXSINST | 1 | 26 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 25 | BUFFER SORT | | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 26 | FIXED TABLE FULL | X$KVIT | 1 | 34 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 27 | BUFFER SORT | | 100 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 28 | FIXED TABLE FULL | X$QUIESCE | 100 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SE"."SQL_ID"="SQ"."SQL_ID")
9 - filter("KGLOBT03"='cw1knhbvzvdbf' AND "INST_ID"=USERENV('INSTANCE'))
15 - filter("S"."KSUSESQI"='cw1knhbvzvdbf' AND "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
16 - filter("S"."INDX"="W"."KSLWTSID")
17 - filter("W"."KSLWTEVT"="E"."INDX")
24 - filter("KS"."INST_ID"=USERENV('INSTANCE'))
26 - filter("KVITTAG"='kcbwst')
Note
-----
- statement not queuable: gv$ statement
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
As you can see from the executions plans above:
- The GV$ view statement involves the rows from both GV$SQL and GV$SESSION being sent to the QC (line IDs 3 and 7) with the join between the two views being performed by the QC (line ID 1).
- The GV$ function statement involves all joins happening before any rows are sent to the QC (line ID 2).
I, for one, think that’s pretty cool 
Note – In both cases the statements had previously been executed so the execution statistics don’t include the initial parse recursive calls… Just in case you were wondering.
Yeah, definitely unsupported. I’m pretty sure JB wasn’t keen I blogged about it actually
Oh, and the specifics of what he was working on was a RAC-aware version of the ASH outliers script which exists, but not in the wild… Single-instance version here ….
http://oracledoug.com/serendipity/index.php?/archives/1669-UKOUG-2011-Ash-Outliers.html