oracle file参数,Oracle参数及参数文件spfile/pfile详解

Oracle参数及参数文件spfile/pfile详解，关于glogin.sql脚本的说明在启动sqlplus 时，会自动调用$ORACLE_HOME/sqlplus/admin/glo

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是指可以忽略大小写。

本文原创发布php中文网，转载请注明出处，感谢您的尊重！