Oracle Database parameters for getting started with DBA

In Oracle databases, a series of initialization parameters are used for database constraints and resource restrictions. These parameters are usually stored in a parameter file and read and load when the database instance starts.

Initialization parameters are very important to the database. Many parameters can greatly improve the database performance through reasonable adjustment. Next, we will discuss database parameters. Please pay attention to subsequent articles on Parameter files.

 

Class of initialization parameters

Different initialization parameters can be divided into three types: Derivation parameters, operating system dependency parameters, and variable parameters.

 

1. Derivation Parameters (Derived Parameters)

Derivation parameters are usually calculated based on other parameters. Therefore, such parameters do not need to be modified. If force-modified, the modified value overwrites the derived value.

There are many common such parameters, such as the SESSIONS parameter. In the Oracle document, this parameter is calculated using the following formula:

SESSIONS = (1.1 XPROCESSES) + 5

By default, this parameter is automatically calculated and takes effect when PROCESSES is modified.

 

2. Operating System Dependent Parameters

The valid values or value ranges of some parameters depend on or are limited by the operating system, such as the db_cache_size parameter, which sets the memory size used by Oracle. The maximum value of this parameter is limited by the physical memory. This type of parameter is generally not called the operating system dependency parameter.

 

3. variable parameters

Variable parameters can be adjusted. Some are set with restrictions, such as OPEN_CURSORS, and some are set with capacity, such as DB_CACHE_SIZE. This type of parameter can usually be adjusted by DBA or end users, resulting in restrictions or performance changes, which is critical to Oracle.

There are usually other classification methods for initialization parameters.

According to the modification method, the initialization parameters can be dividedStatic ParametersAndDynamic Parameters.

Static parameters can only be modified in the parameter file and take effect after restart. Dynamic parameters can be dynamically adjusted, which usually take effect immediately after adjustment.

The initialization parameters can be dividedDisplay parametersAndImplicit Parameter.

The display parameters can be obtained through the v $ parameter query. Implicit Parameters usually start with "_" and must be obtained through the query system table.

In short, although the classification method is different, all these parameters are used. We need to know more about the purpose of these parameters.

 

4. Obtain initialization parameters

The initialization parameters of Oracle can be queried through the v $ parameter view. In SQL * plus, the show parameter command is often used to display the setting values of some parameters. For example:

[Oracle @ czjie ~] $ Sqlplus/as sysdba

SQL * Plus: Release 10.2.0.4.0-Production on Tue Nov 22 10:12:21 2011

Copyright (c) 1982,200 7, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 218103808 bytes

Fixed Size 1266680 bytes

Variable Size 113249288 bytes

Database Buffers 100663296 bytes

Redo Buffers 2924544 bytes

Database mounted.

Database opened.

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 208 M

Sga_target big integer 208 M

SQL> select u_dump.value | '/' | db_name.value | '_ ora _' | v $ process. spid | nvl2 (v $ process. traceid, '_' | v $ process. traceid, null) | '. trc '"Trace File" from v $ parameter u_dump cross join v $ parameter db_name cross join v $ process join v $ session on v $ process. addr = v $ session. paddr where u_dump.name = 'user _ dump_dest 'and db_name.name = 'db _ name' and v $ session. audsid = sys_context ('userenv', 'sessionid ');

Trace File

--------------------------------------------------------------------------------

/Opt/ora10g/admin/ORCL/udump/ORCL_ora_3236.trc

[Oracle @ czjie ~] $ Tkprof/opt/ora10g/admin/ORCL/udump/orcl_ora_3236.trc/opt/ora10g/orcl_ora_3236.txt

TKPROF: Release 10.2.0.4.0-Production on Tue Nov 22 10:17:19 2011

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Through SQL _trace tracking, we can find that the essence of this command is obtained through the following SQL query:

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

NAME_COL_PLUS_SHOW_PARAM, ROWNUM

By querying the creation statement of the v $ parameter view, we can see that the v $ parameter view filters out a series of parameters starting:

SQL> select * from v $ fixed_view_definition where view_name = 'v $ PARAMETER ';

VIEW_NAME VIEW_DEFINITION

--------------------------------------------------------------------------------------------------------------

V $ PARAMETER select NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE

Select NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE, ISMODIFIED, ISADJUSTED, ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH from GV $ PARAMETER where inst_id = USERENV ('instance ')

SQL> select * from v $ fixed_view_definition where view_name = 'gv $ PARAMETER ';

VIEW_NAME VIEW_DEFINITION

--------------------------------------------------------------------------------------------------------------

GV $ PARAMETER select x. inst_id, x. indx + 1, ksppinm, ksppity, ksppstvl, ksppstdvl, ksppstdf, decode

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 )))

These initialization parameters starting with "_" are usually called implicit parameters. Oracle generally does not recommend modifying these parameters, but some hidden parameters have this special function, more and more people are familiar with it.

You can obtain these implicit parameters through the following query:

SELECT x. ksppinm NAME,

Y. ksppstvl VALUE,

Y. ksppstdf isdefault,

Decode (bitand (y. ksppstvf, 7), 1, 'modified', 4, 'System _ mod', 'false') ismod,

Transform de (bitand (y. ksppstvf, 2), 'true', 'false') isadj

FROM sys. x $ ksppi x, sys. x $ ksppcv y

WHERE x. inst_id = userenv ('instance ')

AND y. inst_id = userenv ('instance ')

AND x. indx = y. indx

AND x. ksppinm LIKE '% _ & par %'

Order by translate (x. ksppinm ,'_','')

Common implicit parameters include:

NAME VALUE ISDEFAULT ISMOD ISADJ

-------------------------------------------------------------------------------

_ Allow_resetlogs_uption FALSE TRUE FALSE

_ Uploupted_rollback_segments TRUE FALSE

_ Offline_rollback_segments TRUE FALSE

The following sections describe the important usage of these parameters.