Martins Blog

Trying to explain complex things in simple terms

How to resolve the text behind v$views?

Posted by Martin Bach on January 21, 2015

This is a common problem I have and I never write it down (except now). For example, today I wanted to know what the valid parameters for _serial_direct_read were:

SQL> select * from v$parameter_valid_values where name ='_serial_direct_read';

no rows selected

OK so if Oracle doesn’t tell me then maybe I can work it out? Getting the view_text has worked in the past:

SQL> select view_name, text_vc from dba_views where view_name = '%PARAMETER_VALID_VALUES'

VIEW_NAME                          TEXT_VC
---------------------------------- ----------------------------------------------------------------------------------------------------
V_$PARAMETER_VALID_VALUES          select "NUM","NAME","ORDINAL","VALUE","ISDEFAULT","CON_ID"
                                   from v$parameter_valid_values
GV_$PARAMETER_VALID_VALUES         select "INST_ID","NUM","NAME","ORDINAL","VALUE","ISDEFAULT","CON_ID"
                                   from gv$parameter_valid_values

I’m sure I did the step wrong, but I couldn’t find what the lower case thingie was.

SQL> desc "v$parameter_valid_values"
ERROR:
ORA-04043: object "v$parameter_valid_values" does not exist

SQL> sho user
USER is "SYS"
SQL> desc "gv$parameter_valid_values"
ERROR:
ORA-04043: object "gv$parameter_valid_values" does not exist

SQL> select * from dba_views where view_name = '"gv$parameter_valid_values"';

no rows selected

SQL> select * from dba_objects where object_name = '"gv$parameter_valid_values"';

no rows selected

Yes, I’m pretty sure I got something wrong along the way.

Solutions

One possibility is to use dbms_xplan.display_cursor() – easy!

SQL> select * from v$parameter_valid_values where name = '_serial_direct_read';

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  9hkygnf02nd8y, child number 0
-------------------------------------
select * from v$parameter_valid_values where name =
'_serial_direct_read'

Plan hash value: 1012408093

-------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |       |       |     1 (100)|
|*  1 |  FIXED TABLE FULL| X$KSPVLD_VALUES |     1 |    49 |     0   (0)|
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("NAME_KSPVLD_VALUES"='_serial_direct_read' AND
              TRANSLATE("NAME_KSPVLD_VALUES",'_','#') NOT LIKE '#%' AND
              "INST_ID"=USERENV('INSTANCE')))

Now if I translate this I can write a query that shows me what I need. It also demonstrates that – just like v$parameter – underscore parameters aren’t shown in this view.

SQL> @desc X$KSPVLD_VALUES
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ADDR                                     RAW(8)
    2      INDX                                     NUMBER
    3      INST_ID                                  NUMBER
    4      CON_ID                                   NUMBER
    5      PARNO_KSPVLD_VALUES                      NUMBER
    6      NAME_KSPVLD_VALUES                       VARCHAR2(64)
    7      ORDINAL_KSPVLD_VALUES                    NUMBER
    8      VALUE_KSPVLD_VALUES                      VARCHAR2(255)
    9      ISDEFAULT_KSPVLD_VALUES                  VARCHAR2(64)

SQL> select PARNO_KSPVLD_VALUES, NAME_KSPVLD_VALUES, ORDINAL_KSPVLD_VALUES, VALUE_KSPVLD_VALUES
  2  from X$KSPVLD_VALUES where NAME_KSPVLD_VALUES ='_serial_direct_read';

PARNO_KSPVLD_VALUES NAME_KSPVLD_VALUES             ORDINAL_KSPVLD_VALUES VALUE_KSPVLD_VALUES
------------------- ------------------------------ --------------------- ------------------------------
               2873 _serial_direct_read                                1 ALWAYS
               2873 _serial_direct_read                                2 AUTO
               2873 _serial_direct_read                                3 NEVER
               2873 _serial_direct_read                                4 TRUE
               2873 _serial_direct_read                                5 FALSE

There you go!

Another way is to use the 12c functionality in DBMS_UTILITY.EXPAND_SQL_TEXT. Reusing the example by Tom Kyte:

SQL> var x clob.

SQL> exec dbms_utility.expand_sql_text( -
  2   input_sql_text => 'select * from V$PARAMETER_VALID_VALUES', -
  3   output_sql_text => :x)

print :x

X
--------------------------------------------------------------------------------------------------------
SELECT "A1"."NUM" "NUM","A1"."NAME" "NAME","A1"."ORDINAL" "ORDINAL","A1"."VALUE" "VALUE",
"A1"."ISDEFAULT" "ISDEFAULT","A1"."CON_ID" "CON_ID" FROM  (SELECT "A2"."NUM" "NUM","A2"."NAME"
"NAME","A2"."ORDINAL" "ORDINAL","A2"."VALUE" "VALUE","A2"."ISDEFAULT" "ISDEFAULT","A2"."CON_ID" "CON_ID"
FROM  (SELECT "A3"."INST_ID" "INST_ID","A3"."PARNO_KSPVLD_VALUES" "NUM","A3"."NAME_KSPVLD_VALUES"
"NAME","A3"."ORDINAL_KSPVLD_VALUES" "ORDINAL","A3"."VALUE_KSPVLD_VALUES"
"VALUE","A3"."ISDEFAULT_KSPVLD_VALUES" "ISDEFAULT","A3"."CON_ID" "CON_ID" FROM SYS."X$KSPVLD_VALUES" "A3"
WHERE TRANSLATE("A3"."NAME_KSPVLD_VALUES",'_','#') NOT LIKE '#%') "A2" WHERE
"A2"."INST_ID"=USERENV('INSTANCE')) "A1"

This seems to have worked in earlier versions too, one example is on Jonathan Lewis’ blog.

Update: the most obvious solution to this was to use v$fixed_view_definition! The view must have dropped at the cold end of my brain’s LRU list. As others have pointed out (thanks everyone for your comments!), this would be the way to query the object:

SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name like 'GV$PARAMETER_VALID_VALUES';

VIEW_DEFINITION
-------------------------------------------------------------------------------------------------------
SELECT INST_ID, PARNO_KSPVLD_VALUES, NAME_KSPVLD_VALUES, ORDINAL_KSPVLD_VALUES, VALUE_KSPVLD_VALUES,
 ISDEFAULT_KSPVLD_VALUES, CON_ID
 FROM X$KSPVLD_VALUES WHERE TRANSLATE(NAME_KSPVLD_VALUES,'_','#') NOT LIKE '#%'

Summary

It’s probably not what Oracle intended but DBMS_UTILITY.EXPAND_SQL_TEXT() worked really well. I came across the DBMS_XPLAN.DISPLAY_CURSOR() output by chance when I ran my diagnostic script at the wrong time but it, too, does the job.

Or, I could have used Tanel Poder’s script I didn’t know about until now:


SQL> @pvalid _serial_direct_read
Display valid values for multioption parameters matching "_serial_direct_read"...

  PAR# PARAMETER                                                 ORD VALUE                          DEFAULT
------ -------------------------------------------------- ---------- ------------------------------ -------
  2873 _serial_direct_read                                         1 ALWAYS
       _serial_direct_read                                         2 AUTO
       _serial_direct_read                                         3 NEVER
       _serial_direct_read                                         4 TRUE
       _serial_direct_read                                         5 FALSE

5 Responses to “How to resolve the text behind v$views?”

  1. mwidlake said

    Hi Martin,

    I think this is a surprisingly common problem – how to see what is inside the dynamic GV$ (V$) Views and *how to remember how to*. Like yourself, I had to write a blog for my own use, to remind me how to do it – https://mwidlake.wordpress.com/2009/08/05/looking-inside-v-performance-views/. I had a couple of comments (both on the blog and direct) from people saying they kept forgetting where to look. I don;t use your methods (nice though they are) but v$fixed_view_objects. I’ve not verified it on 12c yet though.

    • Martin Bach said

      See – glad I posted!

      select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name like ‘GV$PARAMETER_VALID_VALUES’;

      would have worked too :)

    • Fun, pieces come back from the good-ol-days when Anjo Kolk gave still internal courses at Oracle. Starting points from those days/lessons which were hidden in my mind would be: v$fixed_table | v$fixed_view_definition

    • Hi,
      The view V$FIXED_VIEW_DEFINITION give s only the first 4000 character for the view definition; for views having definition on more than 4000 characters I have used ‘ dbms_sql2.expand_sql_text’ (introduced on 11.0.2.3)

      Thanks,
      Cherif

      • Nice Query, i have enhanced it a little bit: All possible values for each parameter where shown in one line:
        SELECT NAME_KSPVLD_VALUES AS parameter,
        listagg(VALUE_KSPVLD_VALUES
        ||'(‘
        ||ORDINAL_KSPVLD_VALUES
        ||’)’,’,’) within GROUP (
        ORDER BY PARNO_KSPVLD_VALUES) AS Possible_values
        FROM X$KSPVLD_VALUES
        GROUP BY NAME_KSPVLD_VALUES;

        Kind regards
        Marco Patzwahl

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

 
%d bloggers like this: