欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-01-02 11:29:28
...

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

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

上一篇:

下一篇: