Oracle参数及参数文件spfile/pfile详解

1、参数文件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
其结构为：
从以下结果中看到，v$parameter结构是由GV$PARAMETER创建，而GV$PARAMETER则由X$创建，从下面可以看出GV$parameter来源于x$ksppi、x$ksppcv
SQL> select view_definition from v$fixed_view_definition a where a.view_name='V$PARAMETER';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 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$KQFVI | 1 | 43 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KQFVT (ind:2) | 1 | 2015 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select view_definition from v$fixed_view_definition a where a.view_name='GV$PARAMETER';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
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,'DEFERRED', 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
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 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$KQFVI | 1 | 43 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FIXED INDEX| X$KQFVT (ind:2) | 1 | 2015 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
processes参数在启动时，会先为processes分配内存地址空间，并向shared pool注册，默认的每个进程会在共享池中分配4 Bytes大小的注册空间；
如下：
SQL> select name,value from v$parameter where name in('processes','sessions');
NAME VALUE
-------------------- --------------------
processes 150
sessions 170
SQL> select * from v$sgastat where name='processes';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool processes 600
如果更改processes大小，如更改为:200,则在共享池中应该为800Bytes的注册空间;;
SQL> select * from v$sgastat where name='processes';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool processes 800
SQL> select name,value from v$parameter where name in('processes');
NAME VALUE
-------------------- --------------------
processes 200
2、初始化参数的跟踪
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 260M
sga_target big integer 260M
SQL> alter session set sql_trace=false;
我们看看show parameter sga后台主要做了什么操作，在trace文件找到如下语句：
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是指可以忽略大小写。