GV$ Function

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:

  1. 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).
  2. 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.

Advertisements

5 thoughts on “GV$ Function

  1. Reblogged this on Gruñidos sobre … and commented:
    Estaba intentando usar V$DIAG_ALERT_EXT en un sistema RAC, pero sólo contiene información sobre la instancia actual y no existe una vista global. Esta función resuelve el problema 🙂

  2. As Doug says, the use case was one where I wanted to join each instance’s V$ASH to it’s own V$EVENT_HISTOGRAM and return only a small subset of the joined rows (matching a pretty exclusive filter) from each of the instances. This is precisely the problem this function was designed to solve: remotely joining instance-specific V$ views and then returning the results. As you document, normal GV$ view joins would return all the underlying V$ rows to the coordinating instance and do the joins there, which could get very inefficient (like in my case, returning all of V$ASH and V$EVENT_HISTOGRAM from multiple instances and joining/filtering them on the QC node.)

    As far as being unsupported, well probably nobody should build an application that depends on it, but for DBAs to use in diagnostic scripts why not?

    Since you don’t remember the context Martin, I guess it’s safe to say that the “ASH Outliers” technique has not found it’s way into your arsenal. Oh well, it was an interesting exercise anyway.

    JB
    ps – you can call me “John” if you like 😉

  3. Thank you so much! I’ve been searching for months for a solution to the performance problems of joining large GV$ views in a RAC environment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s