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;
pool name bytes
------------ -------------------------- ----------
shared pool processes 600
如果更改processes大小，，如更改为:200,则在共享池中应该为800bytes的注册空间;;
sql> select * from v$sgastat where;
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是指可以忽略大小写。