How to query implicit Oracle Parameters Based on v $ parameter

How to query implicit Oracle Parameters Based on v $ parameter

Oracle implicit parameters are not displayed in Oracle official documents. These parameters are mainly used in Oracle to control certain functions, or, as described by eygle today, oracle is awesome because a feature developed a few years ago was put into the current version, allowing you to "Try it out", but you don't know. If you happen to encounter this bug, oracle will tell you that you can use this parameter to turn it off. After a few years of "trial", it will be relatively stable. Oracle released a new version, saying it has launched a new function, it has been running stably for N years...

How can we know what an implicit parameter means, what is its full name, and what is its default value? There are at least two methods here, which are recorded in the book or class by the eygle and secooler masters for ease of query.

Method 1: Use the vfixedviewdefinition view to get the view definition. We know that regular query parameters can use select? Fromvparameter, how does v $ parameter be defined?

Check that it is from the GV $ PARAMETER view:

We can see that it comes from the query results of XKSPPI and X $ KSPPCV in two internal X tables. I searched for some introductions on the Internet:
Self-built http://blog.csdn.net/cupid1102/article/details/50427308

X $ KSPPCV is used to record the current set value and whether the default value is used.
Addr raw (4)-memory address
Indx number-no., starting from 0
INST_ID NUMBER
KSPPINM VARCHAR2 (64)-Parameter Name
KSPPITY NUMBER
KSPPDESC VARCHAR2 (64)-Description

X $ KSPPI is a basic table used to record simple information such as parameter names and descriptions.
Addr raw (4)-memory address
Indx number-no., starting from 0
INST_ID NUMBER-instance number
KSPPSTVL VARCHAR2 (512)-Current Value
KSPPSTDF VARCHAR2 (9)-default value
Ksppstvf number-flag field, used to describe ('modified' or 'System modified' or
Is_adjusted)
KSPPSTCMNT VARCHAR2 (255)-comment

As needed, you can adjust the preceding SQL statement to retrieve key information such as the implicit parameter name, current value, and description. You can use the like query to precisely find a special implicit parameter, for example, enter the name of the implicit parameter starting with _ partition:

The query result is as follows. There are two implicit parameters starting with _ partition:

Method 2: Use trace to view the execution plan.
First set autot trace, and then execute:

We can see that the bottom layer of this SQL statement uses two X $ internal tables for full table scanning. These two tables are the internal tables described in the preceding method. Looking at the predicate section, there are some filter query conditions. The ksppinm parameter names starting with "_" are filtered using not like, that is, the implicit parameters are filtered. According to the table field definition described in method 1, you can splice the query statements you want to query the implicit and non-implicit parameters.

Some people on the internet can use the show parameter command to display implicit parameters. The principle is basically the same. If you are interested in finding time, you can learn some technical skills.

Summary:
For encapsulation structures such as views, you can find the base table through the Execution Plan, which provides a means to explore its in-depth principles.