 |
|
Oracle Tips by Burleson |
Dynamic Oracle Parameters
Dynamic Oracle parameters have been around for
years, but it was only with the advent of Oracle 10g that all Oracle
parameters could be changed with “alter system” commands.
As we have noted, it is imperative that you
understand the techniques for viewing the configuration parameters so
you can see how your system has been configured. There are two ways
to see Oracle parameters, querying the v$parameter view and using the
“show parameter” command. Let’s take a look at each method.
Using the v$parameter view
At database startup time, Oracle maintains an
internal view to hold the current instance parameters. Internally,
the v$ views are not really table, but are in-memory C structures that
Oracle has made to look like a table.
If we describe the v$parameter view, we see
all sorts of information about each parameter:
SQL> desc
v$parameter
Name Null? Type
----------------------------------------- --------
-------------------
NUM NUMBER
NAME VARCHAR2(64)
TYPE NUMBER
VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
DESCRIPTION VARCHAR2(64)
UPDATE_COMMENT VARCHAR2(255)
For example, to see all parameter that have
been changed from their default value, we could issue this query.
This is an important query because it is important to know all changed
that have been made to the Oracle parameters:
Here is the query. Try it on your Oracle
database:
select
name,
value,
description
from
v$parameter
where
isdefault = 'FALSE'
order by
name;
Here is a typical output:
NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------DESCRIPTION
----------------------------------------------------------------
background_dump_dest
C:\oracle\admin\diogenes\bdump
Detached process dump directory
compatible
9.0.0
Database will be completely compatible with
this software versio
control_files
C:\oracle\oradata\diogenes\CONTROL01.CTL,
C:\oracle\oradata\diogenes\CONTROL02.C
TL,
C:\oracle\oradata\diogenes\CONTROL03.CTL
control file names
list
core_dump_dest
C:\oracle\admin\diogenes\cdump
Core dump
directory
db_16k_cache_size
16777216
Size of cache for 16K
buffers
db_block_size
4096
Size of
database block in
bytes
db_cache_size
33554432
Size of DEFAULT buffer pool for standard block size
buffers
db_domain
directory
part of global database name stored with CREATE DATABA
db_name
diogenes
database name specified in CREATE
DATABASE
dispatchers
(PROTOCOL=TCP)(SER=MODOSE),
(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer),
(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
specifications of
dispatchers
fast_start_mttr_target
300
MTTR target of forward crash recovery in
seconds
instance_name
diogenes
instance name supported by the
instance
java_pool_size
33554432
size in bytes of the Java pool
large_pool_size
1048576
size in bytes of the large allocation
pool
open_cursors
300
max #
cursors per session
processes
150
user
processes
remote_login_passwordfile
EXCLUSIVE
password file usage parameter
sga_max_size
143421172
max total SGA
size
shared_pool_size
50331648
size in bytes of shared
pool
sort_area_size
524288
size of in-memory sort work
area
timed_statistics
TRUE
maintain internal timing
statistics
undo_management
AUTO
instance runs in SMU mode if TRUE, else in RBU
mode
undo_tablespace
UNDOTBS
use/switch undo
tablespace
user_dump_dest
C:\oracle\admin\diogenes\udump
User process dump
directory
We can also use the SQL “like” operator to
filer our output from v$parameter. In this example, we only display
those parameters whose name end with the string “cache_size:
select
name,
value
from
V$PARAMETER
where
name like '%cache_size';
Here is our output:
NAME VALUE
--------------------- -----------
db_keep_cache_size 0
db_recycle_cache_size 0
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_cache_size 242371520
While the v$parameter view is great for
showing details about parameters, Oracle has a shortcut command to
quickly display instance parameter values. Let’s take a look.
Using the show parameter command
The “show parameter” command is a great way to
quickly see the names and values of specified parameters. Best of
all, the show parameter command has a built-in filer that only
displays parameters that match your argument.
For example, if we want to see all parameters
having to do with buffers, we could do this:
SQL> show
parameter buffer
NAME TYPE VALUE
------------------------------------ ----------- -------------------
buffer_pool_keep string
buffer_pool_recycle
string
db_block_buffers integer
0
log_buffer integer
524288
use_indirect_data_buffers boolean FALSE
Note that this command shows all parameters
with the string “buffer” regardless of the position of the string in
the parameter name. Essentially, the show parameter command is
equivalent to this SQL:
select
name,
type,
value
from
v$parameter
where
name like ‘%buffer%’;
In practice, the show parameter command is
frequently used by Oracle database administrators who want to see the
internal settings of instance parameters.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
