Explanation of Oracle parameters and parameter files spfile/pfile

Spfilepfile for Oracle parameters and parameter files. The glogin. SQL script automatically calls $ ORACLE_HOMEsqlplusadminglo when sqlplus is started.

For details about Oracle parameters and parameter files, spfile/pfile, glogin. SQL script automatically calls $ ORACLE_HOME/sqlplus/admin/glo when sqlplus is started.

1. parameter file v $ parameter
SQL> desc v $ parameter
Name Null? Type
-----------------------------------------------------------------------------
NUM NUMBER
NAME VARCHAR2 (80)
TYPE NUMBER
VALUE VARCHAR2 (512)
DISPLAY_VALUE VARCHAR2 (512)
ISDEFAULT VARCHAR2 (9)
ISSES_MODIFIABLE VARCHAR2 (5)
ISSYS_MODIFIABLE VARCHAR2 (9)
ISINSTANCE_MODIFIABLE VARCHAR2 (5)
ISMODIFIED VARCHAR2 (10)
ISADJUSTED VARCHAR2 (5)
ISDEPRECATED VARCHAR2 (5)
DESCRIPTION VARCHAR2 (255)
UPDATE_COMMENT VARCHAR2 (255)
HASH NUMBER
Its structure is as follows:
The following results show that the v $ parameter structure is created by GV $ PARAMETER, while GV $ PARAMETER is created by X $, from the following we can see that GV $ parameter comes from x $ ksppi, x $ ksppcv
SQL> select view_definition from v $ fixed_view_definition a where a. view_name = 'v $ PARAMETER ';
VIEW_DEFINITION
Bytes ------------------------------------------------------------------------------------------------------------------------
Select NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIAB
LE, ISMODIFIED, ISADJUSTED, ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH from GV $ PARAMETER where inst_id = USERENV
('Instance ')
Execution Plan
----------------------------------------------------------
Plan hash value: 1020564687
Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 2058 | 0 (0) | 00:00:01 |
| 1 | nested loops | 1 | 2058 | 0 (0) | 00:00:01 |
| * 2 | fixed table full | X $ k1_vi | 1 | 43 | 0 (0) | 00:00:01 |
| * 3 | fixed table fixed index | X $ k1_vt (ind: 2) | 1 | 2015 | 0 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------------
SQL> select view_definition from v $ fixed_view_definition a where a. view_name = 'gv $ PARAMETER ';
VIEW_DEFINITION
Bytes ------------------------------------------------------------------------------------------------------------------------
Select x. inst_id, x. indx + 1, ksppinm, ksppity, ksppstvl, ksppstdvl, ksppstdf, decode (bitand (ksppiflg/256, 1), 1, 'true', 'false'
), Decode (bitand (ksppiflg/65536,3), 1, 'immediate', 2, 'referred', 3, 'immediate', 'false '),
Decode (bitand (ksppiflg, 4), 4, 'false', decode (bitand (ksppiflg/65536,3), 0, 'false ',
'True'), decode (bitand (ksppstvf, 7), 1, 'modified', 4, 'System _ mod', 'false'), decode (bitand (ksppstvf, 2), 2, 'true', 'false
'), Decode (bitand (ksppilrmflg/64, 1), 1, 'true', 'false'), ksppdesc, ksppstcmnt, ksppihash from x $ ksppi x, x $ ksppcv y
Where (x. indx = y. indx) and (translate (ksppinm, '_', '#') not like '# %') and (translate (ksppinm ,'_','#') not like
'# %') Or (ksppstdf = 'false') or (bitand (ksppstvf, 5)> 0 )))
Execution Plan
----------------------------------------------------------
Plan hash value: 1020564687
Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 2058 | 0 (0) | 00:00:01 |
| 1 | nested loops | 1 | 2058 | 0 (0) | 00:00:01 |
| * 2 | fixed table full | X $ k1_vi | 1 | 43 | 0 (0) | 00:00:01 |
| * 3 | fixed table fixed index | X $ k1_vt (ind: 2) | 1 | 2015 | 0 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------------
When the processes parameter is started, it first allocates a memory address space for the processes and registers it with the shared pool. By default, each process allocates a 4-Bytes registration space in the shared pool;
As follows:
SQL> select name, value from v $ parameter where name in ('processs', 'session ');
NAME VALUE
----------------------------------------
Processes 150
Sessions 170
SQL> select * from v $ sgastat where;
POOL NAME BYTES
------------------------------------------------
Shared pool processes 600
If the processes size is changed, for example, to 200, it should be the registration space of bytes in the shared pool ;;
SQL> select * from v $ sgastat where;
POOL NAME BYTES
------------------------------------------------
Shared pool processes 800
SQL> select name, value from v $ parameter where name in ('processs ');
NAME VALUE
----------------------------------------
Processes 200
2. initialization parameter tracking
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/Oracle/admin/source/udump/source_ora_19471.trc
SQL> alter session set SQL _trace = true;
Session altered.
SQL> show parameter sga;
NAME TYPE VALUE
-----------------------------------------------------------------------------
Lock_sga boolean FALSE
Pre_page_sga boolean FALSE
Sga_max_size big integer 260 M
Sga_target big integer 260 M
SQL> alter session set SQL _trace = false;
Let's take a look at what the show parameter sga background mainly performs, and find the following statement in the trace file:
Select name NAME_COL_PLUS_SHOW_PARAM,
DECODE (TYPE,
1,
'Boolean ',
2,
'String ',
3,
'Integer ',
4,
'File ',
5,
'Number ',
6,
'Big integer ',
'Unknown ') TYPE,
DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
From v $ PARAMETER
Where upper (NAME) like upper ('% sga % ')
Order by NAME_COL_PLUS_SHOW_PARAM, ROWNUM
UPPER is case-insensitive.