Oracle 9i New Features
Dynamic SGA
The dynamic SGA infrastructure will allow for the sizing of the Buffer
Cache, Shared Pool and the Large Pool (see note below) without having
to shutdown the database. In this new model, a new unit of allocation
is created called the 'Granule'. A granule is a unit of contiguous
virtual memory allocation. The size of a granule depends on the
estimated total SGA size, whose calculation is based on the value of
the parameter SGA_MAX_SIZE. This would be 4MB if the SGA size is less
than 128MB, else it will be 16MB. The Buffer Cache, Shared Pool and
Large Pool are allowed to grow and shrink based on granule boundaries.
At instance startup the Oracle Server allocates the granule entries,
one for each granule to support SGA_MAX_SIZE bytes of address space.
During the startup each component acquires as many granules as it
requires. The minimum SGA is three granules, as follows:
1. One Granule for Fixed SGA (includes redo buffers)
2. One Granule for Buffer Cache
3. One Granule for Shared Pool
We can alter the granules allocated to components using the 'ALTER
SYSTEM' command. The granules are rounded up to the nearest of the
default graunule size (4MB or 16MB). Example:
alter system set shared_pool_size=64M
You can create an SPFILE from an init.ora file and vice versa with the
CREATE SPFILE and CREATE PFILE commands.
Most parts of the SGA can be dynamically resized and have default
sizes.
Changing init.ora parameters
Before Oracle9i, Oracle instances were always started using a text file
called an init<SID>.ora. This file is by default located in the
"$ORACLE_HOME/dbs" directory. In Oracle9i, Oracle has also introduced
the 'SPFILE', which is a binary file stored on the database Server.
Changes which are applied to the instance parameters may be persistent
accross all startup/shutdown procedures. In Oracle9i, you can startup
the instance using either an spfile or an init.ora file.
The default initialization files must are located as follows:
- on Unix ---> $ORACLE_HOME/dbs
- on WindowsNT/2000 ---> %ORACLE_HOME%\database
1. Specifying neither file:
sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
Oracle first searches if the spfile<SID>.ora exists. If it does
not, Oracle searchs for the spfile.ora parameter file. If neither
spfile<SID>.ora nor spfile.ora exist, Oracle will use the
init<SID>.ora parameter file.
2. Specifying init.ora file:
This behavior is the same as in previous database versions.
SQL> startup pfile=d:\ora901\database\initORA901.ora
3. Specifying spfile:
In this case, you must startup with an init.ora file in which you only
specify the spfile parameter full name:
spfile=d:\ora901\database\spfiletest.ora
SQL> startup pfile=d:\ora901\database\inittest.ora
So in order to modify an init.ora parameter and make it persistant you
should:
1- Backup your init.ora file
2- sqlplus "/ as sysdba"
3- create pfile from spfile;
4- edit your init.ora file created by the previous command and
add/modify your parameter
5- shutdown immediate
6- create spfile from pfile;
7- startup
Startup the DB Remotely
1- Check that the server has a SPFILE
2- Create at the client PC the file pfilename.ora with just one line
with
spfile=/u01/app/oracle/...../spfileSID.ora -->
Location of the spfile on the server
3- Sqlplus /nolog
4- connect user/password@DB as sysdba
5- startup pfile=pfilename.ora
Parameter Scope
The scope of the ALTER SYSTEM SET command can be defined using the
following:
ALTER SYSTEM SET parameter
= value SCOPE=[SPFILE/MEMORY/BOTH];
The actions of the scope parameters are:
A parameter value can be reset to the default using:
ALTER SYSTEM RESET
OPEN_CURSORS SID='*' SCOPE='SPFILE';
In a Real Application Cluster (RAC) environment node-specific
parameters
can be set using the SID parameter:
ALTER SYSTEM SET
OPEN_CURSORS=500 SID='SID1' SCOPE='SPFILE';
Multiple
Block Sizes
Oracle 9i supports tablespaces with different block sizes. This allows
large tables and indexes to have a larger block size than smaller
objects. This is especially useful for indexes on OLTP. The database is
created with a standard block size and up to 5 none-standard block
sizes.
The DB_BLOCK_SIZE is used to
specify the standard block size which is used for the SYSTEM and
TEMPORARY tablespaces. All subsequent tablespaces will also be assigned
this block size unless specified otherwise.
The DB_CACHE_SIZE parameter,
rather than DB_BLOCK_BUFFERS, is used to define the size of the buffer
cache for the standard block size. This can be set to any size, but it
will be rounded to the nearest whole granule. If the total SGA is less
then 128M the granule size is 4M, greater than 128M and it becomes 16M.
The DB_CACHE_SIZE must be at least 1 granule in size and defaults to
48M.
An individual buffer cache must be defined for each non-standard block
size used. These are set up with the following parameters which default
to a size of 0M:
DB_2K_CACHE_SIZE = 0M
DB_4K_CACHE_SIZE = 0M
DB_8K_CACHE_SIZE = 0M
DB_16K_CACHE_SIZE = 0M
DB_32K_CACHE_SIZE = 0M
The instance must be restarted before changes to these parameters take
effect. The DB_nK_CACHE_SIZE parameters can only be set to zero if
there are no online tablespace with a nK block size.
alter system
set DB_2K_CACHE_SIZE = 8M scope=pfile;
alter system
set DB_16K_CACHE_SIZE = 8M scope=pfile;
Once the caches are defined the tablespaces can be defined:
CREATE TABLESPACE TS_2k datafile '.../....' size xxx BLOCKSIZE 2K
extent management local;
CREATE TABLESPACE TS_8k datafile '.../....' size xxx BLOCKSIZE 8K
extent management local;
The KEEP and RECYCLE buffer caches can only be defined for the
standard block size. In 8i these were defined using the
BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE parameters, with their memory
was taken from the total DB_BLOCK_BUFFERS.
In 9i the parameters have been changed to DB_KEEP_CACHE_SIZE and DB_RECYLE_CACHE_SIZE, with their
memory allocations being totally separate to the main buffer cache.
A number of rules apply to the use of variable block sizes:
Automatic
SQL Execution Memory Management and Oracle self-tune PGA memory
The performance of complex long running queries, typical in a DSS
environment, depends to a large extent on the memory available in the
Program Global Area (PGA). In Oracle8i and previous releases of the
database, administrators sized the PGA by carefully adjusting a number
of initialization parameters. Oracle9i completely automates the
management of PGA memory. Administrators merely need to specify the
maximum amount of PGA memory available to an instance using a newly
introduced parameter PGA_AGGREGATE_TARGET. The database server
automatically distributes this memory among various active queries in
an intelligent manner so as to ensure maximum performance benefits and
the most efficient utilization of memory. Furthermore, Oracle9i can
adapt itself to changing workload thus utilizing resources efficiently
regardless of the load on the system. The amount of the PGA memory
available to an instance can be dynamically changed by altering the
value of the PGA_AGGREGATE_TARGET parameter making it possible to add
to and remove PGA memory from an active instance online. In the
automatic mode, administrators no longer have to manually tune the
sizes of the individual work areas using parameters such as
SORT_AREA_SIZE,
HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE.
The automatic SQL execution memory management feature is enabled by
setting the parameter WORK_AREA_SIZE to AUTO. For backward
compatibility reasons, Oracle9i continues to support the manual PGA
management mode. The manual mode can be activated by either setting the
WORK_AREA_SIZE parameter to MANUAL or not specifying a value for
PGA_AGGREGATE_TARGET.
In Summary, you can have Oracle self-tune PGA memory usage
instead of setting sort_area_size, hash_area_size,
bitmap_merge_area_size, and create_bitmap_area_size manually.
- Set PGA_AGGREGATE_TARGET to the total amount of physical memory
available for use by all dedicated server processes.
- Oracle will then self-tune the *_AREA_SIZE parameters for all
dedicated server connections.
- You can still set the *_AREA_SIZE parameters manually and omit
PGA_AGGREGATE_TARGET for manual tuning.
How To Tune PGA_AGGREGATE_TARGET (from ML note
223730.1)
To determine the appropriate setting for PGA_AGGREGATE_TARGET
parameter I recommend to follow the following steps:
1- Make a first estimate
for PGA_AGGREGATE_TARGET based on the
following rule:
PGA_AGGREGATE_TARGET = (Total Physical
Memory * 0.16) - For OLTP systems (16% of
Server Physical Mem)
PGA_AGGREGATE_TARGET = (Total Physical
Memory * 0 .4 ) - For DSS systems
(40% of Server Physical Mem)
So for example if we have an Oracle instance configured on system with
16G of Physical memory, then the suggested PGA_AGGREGATE_TARGET
parameter value we should start with incase we have OLTP system
is (16 G * .16) ~= 2.5G and in case we have DSS system is (16 G * 0.4)
~= 6.5 G.
In the above equation we assume that 20 % of the memory will be used by
the OS, and in OLTP system 20 % of the remaining memory will be
used for PGA_AGGREGATE_TARGET and the remaining memory is going
for Oracle SGA memory and non-oracle processes memory. So make sure
that you have enough memory for your SGA and also for non-oracle
processes
2- Use the view v$sysstat
to check PGA_AGGREGATE_TARGET
values.
The following query gives the total number and the percentage of times
work areas were executed in these three modes since the database
instance was started.
select substr(name,1,32) profile,
cnt count,
decode(total, 0, 0, round(cnt*100/total)) percentage
from (select name, value
cnt, (sum(value) over ()) total
from v$sysstat
where name like 'workarea exec%');
PROFILE
CNT PERCENTAGE
--------------------------------
---------- ----------
workarea executions -
optimal
9378431 99
--> operations performed in memory
workarea executions -
onepass
47 1 -->
operations performed in disk
workarea executions -
multipass
14 0 -->
operations performed in disk
This output of this query is used to tell the DBA when to dynamically
adjust pga_aggregate_target. These executions fall into three
categories:
OPTIMAL: Operations that were performed in memory
ONEPASS: When the operation was too big to be performed in memory, then
part of it spills onto disk.
MULTIPASS: If more that one pass was needed on disk.
Ideally all executions should be in the optimal statistics and the
onepass and multipass should be 0.
3- Monitor
performance using available PGA statistics and see if
PGA_AGGREGATE_TARGET
is under-sized or over-sized. Several dynamic performance views are
available for this purpose:
- V$PGASTAT
This view provides instance-level statistics on the PGA memory usage
and the automatic PGA memory manager. For example:
select substr(name,1,40) name, value, unit from V$PGASTAT;
NAME
VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target
parameter
250609664 bytes (a)
aggregate PGA auto
target
103809024 bytes
global memory
bound
12529664 bytes (b)
total PGA
inuse
135284736 bytes (c)
total PGA
allocated
149349376 bytes (d)
maximum PGA
allocated
250475520 bytes
total freeable PGA
memory
2883584 bytes
PGA memory freed back to
OS
6.0456E+11 bytes
total PGA used for auto
workareas
0 bytes (e)
maximum PGA used for auto
workareas 13918208 bytes
total PGA used for manual
workareas
0 bytes
maximum PGA used for manual
workareas
0 bytes
over allocation
count
0
(f)
bytes
processed
1.2039E+12 bytes
extra bytes
read/written
107754496 bytes
cache hit
percentage
99.99 percent (g)
Main statistics to look at :
(a) aggregate PGA auto target
: This gives the amount of PGA memory
Oracle can use for work areas running in automatic mode. This
part of memory represent the tunable part of PGA memory,i.e. memory
allocated for intensive memory
SQL operators like sorts, hash-join, group-by, bitmap merge and
bitmap index create. This memory part can be shrinked/expanded in
function of the system load. Other parts of PGA memory are known
as untunable, i.e. they require a size that can't be negociated
(e.g. context information for each session, for each open/active
cursor, PL/SQL or Java memory).
So, the aggregate PGA auto target should not be small compared to the
value of PGA_AGGREGATE_TARGET. You must ensure that enough PGA
memory is left for work areas running in automatic mode.
(b) Global memory bound – This
statistic measures the max size of a work area, and Oracle recommends
that whenever this statistics drops below one megabyte, then you should
increase the value of the pga_aggregate_target parameter
(c) total PGA in used: This
gives the total PGA memory in use. The
detail of this value can be found in the PGA_USED_MEM column of the
v$process view. At this time it will be very useful to use the
following query to see allocated, used and maximum memory for all
connections to Oracle. We can see the RAM demands of each of the
background processes and we also have detailed information about
individual connections.Note that it is possible to join the v$process
view with the v$sql_plan table to take a closer look at the RAM memory
demands of specific connections.
select substr(program,1,30) program, pga_used_mem, pga_alloc_mem,
pga_max_mem
from v$process;
(d)Total PGA allocated – This
statistic display the high-water mark of all PGA memory usage on the
database. You should see this value approach the value of
pga_aggregate_target as usage increases.
(e) total PGA used for auto workarea:
This gives the actual tunable PGA
memory used by the system. This statistic monitors RAM consumption or
all connections that are running in automatic memory mode.
Remember, not all internal processes may use the automatic memory
feature. For example, Java and PL/SQL will allocate RAM memory,
and this will not be counted in this statistic. Hence, we can
subtract value to the total PGA allocated to see the amount of memory
used by connections and the RAM memory consumed by Java and PL/SQL
(f) over allocation count:
Over-allocating PGA memory can happen if the
value of PGA_AGGREGATE_TARGET is too small to accommodate the untunable
PGA memory part plus the minimum memory required to execute the work
area workload. When this happens, Oracle cannot honor the
initialization parameter PGA_AGGREGATE_TARGET, and extra PGA memory
needs to be allocated. Over allocation count is the number of time the
system was detected in this state since database startup. This count
should ideally be equal to zero.
(g) cache hit percentage: This
metric is computed by Oracle to reflect
the performance of the PGA memory component. It is cumulative from
instance start-up. A value of 100% means that all work areas executed
by the system since instance start-up have used an optimal amount of
PGA memory. This is, of course, ideal but rarely happens except maybe
for pure OLTP systems. In reality, some work areas run one-pass or even
multi-pass, depending on the overall size of the PGA memory. When a
work area cannot run optimally, one or more extra passes is performed
over the input data. This reduces the cache hit percentage in
proportion to the size of the input data and
the number of extra passes performed. this value if computed from the
"total bytes processed" and "total extra bytes read/written" statistics
available in the same view using the following formula:
total bytes processed * 100
PGA Cache Hit Ratio =
------------------------------------------------------
(total bytes processed + total extra bytes read/written)
- V$SQL_WORKAREA_HISTOGRAM (Oracle92 only)
This view shows the number of work areas executed with optimal memory
size, one- pass memory size, and multi-pass memory size since instance
start-up. Statistics in this view are subdivided into buckets that are
defined by the
optimal memory requirement of the work area. Each bucket is identified
by a range of optimal memory requirements specified by the values
of the columns LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE. The
following query shows statistics for all nonempty buckets:
SELECT
low_optimal_size/1024 AS
low_kb,
(high_optimal_size+1)/1024 AS high_kb,
ROUND(100*optimal_executions/total_executions) AS "Opt_Executions",
ROUND(100*onepass_executions/total_executions) AS "OnePass_Execut",
ROUND(100*multipasses_executions/total_executions) AS "MultiPass_Execut"
FROM
v$sql_workarea_histogram
WHERE
total_executions != 0
ORDER
BY low_kb;
LOW_KB HIGH_KB Opt_Executions
OnePass_Execut MultiPass_Execut
------ ------- ------------------ ------------------
----------------------
8 16
156255
0
0
16 32
150
0
0
32 64
89
0
0
64 128
13
0
0
128 256
60
0
0
256 512
8
0
0
512 1024
657
0
0
1024 2048
551
16
0
2048 4096
538
26
0
4096 8192
243
28
0
8192 16384
137
35
0
16384 32768
45
107
0
32768 65536
0
153
0
65536 131072
0
73
0
131072 262144
0
44
0
262144 524288
0
22
0
The query result shows that, in the 1024 KB to 2048 KB bucket, 551 work
areas used an optimal amount of memory, while 16 ran in one-pass mode
and none ran in multi-pass mode. It also shows that all work areas
under 1 MB were able to run in optimal mode. You can also use
V$SQL_WORKAREA_HISTOGRAM to find the percentage of
times work areas were executed in optimal, one-pass, or multi-pass mode
since start-up. Example :
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count,
round(onepass_count*100/total, 2) onepass_perc,
multipass_count,
round(multipass_count*100/total, 2) multipass_perc
FROM (SELECT
decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM
v$sql_workarea_histogram
WHERE
low_optimal_size > 64*1024); ---- for 64 K optimal
size
3- The Third and last step is
tuning the PGA_AGGREGATE_TARGET. In
Oracle 9i Release 2 we have 2 new views that help us in this task
- V$PGA_TARGET_ADVICE
- V$PGA_TARGET_ADVICE_HISTOGRAM
By examining these two views, you will be able to determine how key PGA
statistics will be impacted if you change the value of
PGA_AGGREGATE_TARGET.
To enable automatic generation of PGA advice performance views, make
sure the following parameters are set:
- PGA_AGGREGATE_TARGET
- STATISTICS_LEVEL. Set this to TYPICAL (the default) or ALL; setting
this parameter to BASIC turns off generation of PGA performance advice
views.
The content of these PGA advice performance views is reset at instance
start-up or when PGA_AGGREGATE_TARGET is altered.
V$PGA_TARGET_ADVICE view predicts how the statistics cache hit
percentage and over allocation count in V$PGASTAT will be
impacted if you change
the value of the initialization parameter PGA_AGGREGATE_TARGET.
The following select statement can be used to find this
information
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE
cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63
23
367
125
24
30
250
30
3
375
39
0 --Lowest value that could be
assigned
500
58
0
600
59
0
700
59
0
800
60
0
900
60
0
1000
61
0
1500
67
0
2000
76
0
3000
83
0
4000
85
0
From the above results we should set the PGA_AGGREGATE_TARGET parameter
to a value where we avoid any over allocation, so the lowest
PGA_AGGREGATE_TARGET value we can set is 375 ( where
ESTD_OVERALLOC_COUNT is 0)
After eliminating over-allocations, the goal is to maximize the PGA
cache hit percentage, based on your response-time requirement and
memory constraints.
V$PGA_TARGET_ADVICE_HISTOGRAM view predicts how the statistics
displayed by the performance view V$SQL_WORKAREA_HISTOGRAM will be
impacted if you
change the value of the initialization parameter PGA_AGGREGATE_TARGET.
You can use that dynamic view to view
detailed information on the predicted number of optimal, one-pass and
multi-pass work area executions for the set of PGA_AGGREGATE_TARGET
values you use for the prediction.
Oracle Advice
Oracle9i r2 now has three predictive utilities:
These advisory utilities are extremely important for the Oracle DBA
who must adjust the sizes of the RAM areas to meet current processing
demands.
Using Data Cache Advice with v$db_cache_advice
You can have Oracle estimate what cache hit ratios would be like if
buffer caches were larger or smaller.
The following query can be used to perform the cache advice
function, once the db_cache_advice has been enabled and the database
has run long enough to give representative results
cache_advice.sql
-- ****************************************************************
-- Display cache advice. Example for 304MB Size
-- ****************************************************************
--# db_cache_advice.sql
col size_for_estimate for 999999 head 'Cache Size (MB)'
col estd_physical_read_factor for 999.90 head 'Estd Phys|Read Factor'
col estd_physical_reads for 999,999,999 head 'Estd Phys| Reads'
SELECT name, block_size, size_for_estimate, estd_physical_read_factor,
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE advice_status = 'ON';
The output from the script is shown below. Note that the values
range from 10 percent of the current size to double the current size of
the db_cache_size.
Estd Phys Estd Phys
NAME
BLOCK_SIZE Cache Size (MB) Read
Factor Reads
-------------------- ---------- --------------- ----------- ------------
DEFAULT
8192
112 4.03
22,276,175 10% SIZE
DEFAULT
8192
224 2.69
14,840,036
DEFAULT
8192
336 1.49
8,241,584
DEFAULT
8192
448 1.37
7,584,065
DEFAULT
8192
560 1.27
7,009,869
DEFAULT
8192
672 1.20
6,644,218
DEFAULT
8192
784 1.11
6,153,303
DEFAULT
8192
896 1.06
5,859,825
DEFAULT
8192
1008 1.03
5,684,688
DEFAULT
8192
1120 1.02
5,628,375
DEFAULT
8192
1200 1.00
5,523,760 CURRENT SIZE
DEFAULT
8192
1232
.99 5,446,959
DEFAULT
8192
1344
.97 5,383,602
DEFAULT
8192
1456
.96 5,314,650
DEFAULT
8192
1568
.95 5,271,983
DEFAULT
8192
1680
.94 5,200,616
DEFAULT
8192
1792
.92 5,082,878
DEFAULT
8192
1904
.90 4,956,648
DEFAULT
8192
2016
.88 4,863,947
DEFAULT
8192
2128
.85 4,668,080
DEFAULT
8192
2240
.68 3,763,293 2X TIMES SIZE
From the above listing we see that increasing the db_cache_size from
1200MB to 1232MB would result in approximately 100,000 less physical
reads.
Using Shared Pool Advice to size your Shared Pool properly
This advisory functionality has been extended in Oracle9i release 2 to
include a new advice called v$shared_pool_advice, and there is talk to
expending the advice facility to all SGA RAM areas in future releases
of Oracle.
The v$shared_pool_advice show
the marginal
difference in SQL parses as the shared pool changes in size from 10% of
the current value to 200% of the current value. The Oracle
documentation contains a complete description for the set-up
and use of shared pool advice, and it is very simple to configure. Once
it is installed, you can run a simple script to query the
v$shared_pool_advice view and
see the marginal changes in SQL parses
for different shared_pool sizes
-- *************************************************************
-- Display shared pool advice. Example for current Size = 96MB
-- *************************************************************
set lines 100
set pages 999
column
c1 heading 'Pool |Size(M)'
column
c2 heading 'Size|Factor'
column
c3 heading 'Est|LC(M) '
column
c4 heading 'Est LC|Mem. Obj.'
column
c5 heading 'Est|Time|Saved|(sec)'
column
c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object
Hits' format 999,999,999
SELECT
shared_pool_size_for_estimate c1,
shared_pool_size_factor c2,
estd_lc_size
c3,
estd_lc_memory_objects
c4,
estd_lc_time_saved
c5,
estd_lc_time_saved_factor c6,
estd_lc_memory_object_hits c7
FROM
v$shared_pool_advice;
Est Est
Time Parse
Pool
Size
Est Est LC
Saved
Saved Est
Size(M)
Factor LC(M) Mem.
Obj. (sec)
Factor Object Hits
---------- ---------- ---------- ---------- ---------- ----------
------------
48
.5
48 20839
1459645 1
135,756,032
64
.6667
63 28140
1459645 1
135,756,101
80
.8333
78 35447
1459645 1
135,756,149
96
1
93 43028
1459645 1
135,756,253
112
1.1667
100 46755
1459646 1
135,756,842
128
1.3333
100 46755
1459646 1
135,756,842
144
1.5
100 46755
1459646 1
135,756,842
160
1.6667
100 46755
1459646 1
135,756,842
176
1.8333
100 46755
1459646 1
135,756,842
192
2
100 46755
1459646 1
135,756,842
Here we see the statistics for the shared pool in a range from 50% of
the current size to 200% of the current size. These statistics can give
you a great idea about the proper size for the shared_pool_size. If you
are automatic the SGA region sizes with automated "alter system"
commands, creating this output and writing a program to interpret the
results is a great way to ensure that the shared pool and library cache
always have enough RAM.
The Oracle Enterprise Manager Shared Pool Sizing Advisor guides you
in finding the optimal size for the shared pool, based on the relative
change in parse time savings. The SGA Advisor is available from
Enterprise Manager by selecting:
Databases > Database Name > Instance > Configuration >
Memory tab > Advice in the SGA section.
Using PGA Advice Utility
The new statistics to help you monitor the performance of the PGA
memory component for a particular value of PGA_AGGREGATE_TARGET are
based on two concepts: work areas groups and a computed PGA cache hit
percentage value. By grouping work areas based on their optimal memory
requirement, statistics can be collected for each group based on the
number of optimal, one-pass, and multi-pass executions for each of
these work area groups. So now you can more accurately predict
how much memory is required across the instance to maximize the number
of optimal executions.
The PGA cache hit percentage summarizes statistics on work area
executions into a single measure of PGA performance for a given value
of PGA_AGGREGATE_TARGET. The PGA cache hit percentage is derived from
the number of work areas, which run optimal, the number of passes for
the non-optimal work areas, and the sizes of the work areas.
The new
view, v$sql_workarea_histogram,
enables you to study the nature of the
work area workload over a chosen time period. The work areas are split
into 33 groups based on their optimal memory requirements. Each group
is defined by the lower bound on its optimal memory requirement,
displayed in the low_optimal_size
column, and its upper bound,
displayed in the high_optimal_size
column. For each group, the view
accumulates the number of executions using optimal, one-pass,
multi-pass memory since instance start up. These are displayed in the
optimal_executions, onepass-executions, and multipasses_executions
columns respectively. The total_executions
column contains the sum of
the other three execution counts. To determine if you have set a good
value for the PGA target size, query the v$sql_workarea_histogram view
using a statement such as the following, which converts the
low_optimal_size and high_optimal_size column values to kilobytes:
SELECT
low_optimal_size/1024 AS
low_kb,
(high_optimal_size+1)/1024 AS high_kb,
ROUND(100*optimal_executions/total_executions) AS "Opt_Executions",
ROUND(100*onepass_executions/total_executions) AS "OnePass_Execut",
ROUND(100*multipasses_executions/total_executions) AS "MultiPass_Execut"
FROM
v$sql_workarea_histogram
WHERE
total_executions != 0
ORDER
BY low_kb;
LOW_KB HIGH_KB Opt_Executions OnePass_Execut
MultiPass_Execut
---------- ---------- -------------- -------------- ----------------
8
16
100
0
0
16
32
100
0
0
32
64
100
0
0
64
128
100
0
0
128
256
100
0
0
256
512
100
0
0
512
1024
100
0
0
1024
2048
100
0
0
2048
4096
100
0
0
4096
8192
100
0
0
8192
16384
100
0
0
16384
32768
100
0
0
32768
65536
17
83
0
65536
131072
0
100
0
131072
262144
0
100
0
262144
524288
0
100
0
524288
1048576
0
100
0
Oracle9i also introduces a new parameter called
WORKAREA_SIZE_POLICY. When this parameter is set to automatic
(default), all
Oracle connections will benefit from the shared PGA memory.
When WORKAREA_SIZE_POLICY is set to manual, connections will allocate
memory according to the values for the sort_area_size parameter.
Although it would
be ideal for all work areas to execute in the optimal size, this goal
is typically achieved by over-allocating memory to the PGA. If you
graph the results of your query as a histogram, you can quickly
identify the location in the graph where work groups begin to use
one-pass, or even multi-pass, sizes
If you monitor the PGA space consumption with the various views
provided for that purpose (v$sql_workarea_histogram,
v$pgastat,
v$sql_workarea, and v$sql_workarea_active), you may decide that
you
need to reset your PGA_AGGREGATE_TARGET initialization parameter value
to make better use of your available memory. To help you determine by
what factor you should
change the parameter value, you can use two new views(v$pga_target_advice and v$pga_target_advice_histogram)
that accumulate advice statistics to help you tune the
PGA_AGGREGATE_TARGET
value.
The views are only populated if PGA_AGGREGATE_TARGET is set to a
non-zero value that enables auto management of the PGA. Also the
initialization parameter STATISTICS_LEVEL value must be set to Typical
or All. . Further, the view contents are refreshed when you shut down
the instance or when you change the value of PGA_AGGREGATE_TARGET
dynamically.
Rows in these two views correspond to setting the PGA_AGGREGATE_TARGET
from
0.125 to 8 times its current value. However, if these values are either
less
than 10MB or more than 256GB, they will not be included in the output.
To begin monitoring and tuning the PGA target size, you should issue
query against the v$pga_target_advice
view similar to:
-- ************************************************
-- Display pga target advice
-- ************************************************
column c1 heading 'Estimated|Target(M)'
column c2 heading 'Estimated|Cache Hit %'
column c3 heading 'Estimated|Over-Alloc'
column c4 heading 'Size|Factor'
SELECT ROUND(pga_target_for_estimate /(1024*1024)) c1,
pga_target_factor c4,
estd_pga_cache_hit_percentage c2,
estd_overalloc_count c3
FROM v$pga_target_advice;
Estimated
Size Estimated Estimated
Target(M) Factor
Cache Hit % Over-Alloc
---------- ------- -----------
-----------
188
.125
93 0
376
.25
95 0
752
.5
96 0
1128
.75
97 0
1504
1
98 0
1805
1.2
8 0
2106
1.4
98 0
2406
1.6
98 0
2707
1.8
98 0
3008
2
98 0
4512
3
98 0
6016
4
98 0
9024
6
98 0
12032
8
98 0
The output from the query contains estimated statistics based on the
multiples of the current
PGA_AGGREGATE_TARGET value. Assuming that your query produced the
output shown above with the PGA_AGGREGATE_TARGET value set to 1500MB,
follow these steps to evaluate the results:
Step 1: Identify the
first row with a value of zero in the "Estimated Over-Alloc" column.
The rows over this one are for PGA_AGGREGATE_TARGET values (shown in
the target_mb column) that are too small for the minimum PGA memory
needs. In this case, this is the fifth row, which contains data for the
current PGA_AGGREGATE_TARGET value, 500MB. Had the target_mb column
value in the first row with a zero estd_overallocation_count
been
larger than the current setting, you should increase the
PGA_AGGREGATE_TARGET parameter to at least this size.
Step 2: Examine the
rows with PGA values larger than the minimum required to find the first
pair of adjacent rows with values in the cache_hit_percent column that
are within a few percentage points of each other. These rows indicate
where, were you to graph the values, you would see an inflection point
(sometimes referred to as a “knee”) in the curve. The optimal value for
the PGA_AGGREGATE_TARGET parameter is at this inflection point,
represented by the target_mb
column value in the first of these two
rows. Based on the above results, you should set the
PGA_AGGREGATE_TARGET to 3000MB if you have sufficient memory. If you
have even more memory available, you should assign it to some other
use, such as one of the SGA components, rather than increasing the PGA
target size.
To refine your analysis, you can look at the contents of the v$pga_target_advice_histogram view. This view is based on the v$sql_workarea_histogram view discussed earlier and contains rows for each of the same 33 work area groups used in that view. However, as with the v$pga_target_advice view, estimated data for each value is created for 14 multiples of the current PGA_AGGREGATE_TARGET setting. Therefore the v$pga_target_advice_histogram view potentially contains 14 different sets of values for each of the 33 work area groups, giving a possible total of 462 rows.
In addition to the columns corresponding to the
work area
lower and upper bounds and execution count columns in
v$sql_workarea_histogram,
v$pga_target_advice_histogram
contains columns showing the size of
PGA_AGGREGATE_TARGET used for the estimates in the row, the factor
(multiple)
of the current PGA_AGGREGATE_TARGET size this represents, whether
advice is
currently off or on (based on the setting of the STATISTICS_LEVEL
parameter),
and the number of work areas not included in the estimates due to space
limitations in the instance. You would typically execute queries
against the v$pga_target_advice_histogram
view in the same way, and over the same
period,
as you do for your queries against v$pga_target_advice.
This way, you
can check
if a new value for PGA_AGGREGATE_TARGET that you identify by the method
just
discussed would result in reasonable optimal, one-pass, and multi-pass
mode
processing. You can select the appropriate rows by querying an
appropriate
value for the pga_target_factor
column.
The following statement would generate output based
on the
value of 3000MB (that is, 6 times the current size) identified in the
previous
step.
SELECT low_optimal_size/1024 AS
low_kb,
(high_optimal_size+1)/1024 AS high_kb,
estd_optimal_executions AS optimal,
estd_onepass_executions AS onepass,
estd_multipasses_executions AS mpass
FROM
v$pga_target_advice_histogram
WHERE pga_target_factor = 6
AND
estd_total_executions != 0
ORDER BY low_kb;
The Power of Cursor
Sharing in 9i
Oracle9i allows existing applications to improve SQL reuse by setting
the CURSOR_SHARING initialization parameter dinamically:
In an ideal world the application should be written to encourage
cursor sharing, but existing applications may gain reduced memory
usage, faster parses, and reduced latch contention when using this
option.
CASE
statements
and expressions
CASE statements and expressions are a shorthand way of representing
IF/THEN choices with multiple alternatives The CASE expression was
first added to SQL in Oracle8i. Oracle9i extends its support to PL/SQL
to allow CASE to be used as an expression or statement:
Value Match CASE Expression
The CASE expression is a more flexible version of the DECODE function.
In its simplest form it is used to return a value when a match is found:
SELECT ename, empno,
(CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END) department
FROM emp
ORDER BY ename;
The value match CASE expression is also supported in PL/SQL:
SET SERVEROUTPUT ON
DECLARE
deptno NUMBER := 20;
dept_desc VARCHAR2(20);
BEGIN
dept_desc := CASE deptno
WHEN 10 THEN 'Accounting'
WHEN 20 THEN 'Research'
WHEN 30 THEN 'Sales'
WHEN 40 THEN 'Operations'
ELSE 'Unknown'
END;
DBMS_OUTPUT.PUT_LINE(dept_desc);
END;
/
Searched CASE Expression
A more complex version is the searched CASE expression where a
comparison expression is used to find a match. In this form the
comparison is not limited to a single column:
SELECT ename, empno,
(CASE
WHEN sal < 1000 THEN 'Low'
WHEN sal BETWEEN 1000 AND 3000 THEN
'Medium'
WHEN sal > 3000 THEN 'High'
ELSE 'N/A'
END) salary
FROM emp
ORDER BY ename;
The searched CASE expression is also supported in PL/SQL:
SET SERVEROUTPUT ON
DECLARE
sal NUMBER := 2000;
sal_desc VARCHAR2(20);
BEGIN
sal_desc := CASE
WHEN sal < 1000 THEN 'Low'
WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
WHEN sal > 3000 THEN 'High'
ELSE 'N/A'
END;
DBMS_OUTPUT.PUT_LINE(sal_desc);
END;
/
New Date/Time Types
The new datatype TIMESTAMP records time values including fractional
seconds. New datatypes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH
LOCAL TIME ZONE allow you to adjust date and time values to account for
time zone differences. You can specify whether the time zone observes
daylight savings time, to account for anomalies when clocks shift
forward or backward. New datatypes INTERVAL DAY TO SECOND and INTERVAL
YEAR TO MONTH represent differences between two date and time values,
simplifying date arithmetic. Examples:
TIMESTAMP (fractional_seconds_precision)
Year, month, and day values of date, as well as hour, minute, and
second values of time, where fractional_seconds_precision is the number
of digits in the fractional part of the SECOND datetime field. Accepted
values of fractional_seconds_precision are 0 to 9. The default is 6.
TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE
All values of TIMESTAMP as well as time zone displacement value, where
fractional_seconds_precision is the number of digits in the fractional
part of the SECOND datetime field. Accepted values are 0 to 9. The
default is 6.
TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE
All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
Data is normalized to the database time zone when it is stored in the
database.
When the data is retrieved, users see the data in the session time zone.
INTERVAL YEAR (year_precision) TO MONTH
Stores a period of time in years and months, where year_precision is
the number of digits in the YEAR datetime field. Accepted values are 0
to 9. The default is 2.
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)
Stores a period of time in days, hours, minutes, and seconds, where
day_precision is the maximum number of digits in the DAY datetime
field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional
part of the SECOND field. Accepted values are 0 to 9. The default is 6.
SELECT SYSDATE,
TO_CHAR(SYSDATE,
'MM/DD/YYYY HH24:MI:SS')
sysdate_prior9i,
TO_CHAR(SYSDATE,
'MM/DD/YYYY
HH24:MI:SS.SSSSSSSSSSSS') sysdate_9i
FROM DUAL;
SYSDATE SYSDATE_PRIOR9I
SYSDATE_9I
--------- ------------------- --------------------------------
21-MAY-03 05/21/2003 12:23:01 05/21/2003 12:23:01.445814458101
Oracle9i DataGuard (previously
called Standby)
DataGuard
maintains one or more synchronized copies of a customers production
data.
An Oracle9i DataGuard configuration consists of a collection of
loosely
connected systems, that combine the primary database and physical
standby
databases into a single, easily managed disaster recovery solution.
Often,
the sites in a DataGuard configuration are dispersed geographically
and
connected by Oracle*Net. As the primary database is modified, the
physical
standby database is sent log information generated by changes made to
the primary database. These changes are applied to the standby
database, which runs in managed recovery mode. While the primary
database is open and active, a standby database is either performing a
recovery or open for reporting access. Should something go wrong with
primary, a standby database can be activated to replace it. A new
background process DMON monitors the primary and standby
databases and can be configured either by OEM using the new DataGuard
Manager tool or through the command line using the new DGMGRL utility.
DMON processes run on both primary and standby servers. There are 2 new
terms of reference to learn:
alter database add standby logfile group 1 '/u01/oradata/SID1/SID1_g1.rdo' size 80M;
Oracle standby server can now run in 4
data protection modes as follows:
| Guaranteed
Protection (no data loss mode) (no data divergence) |
The
strictest mode possible. Primary node's LGWR transmits the redo stream synchronously
to the standby nodes. Standby node must acknowledge, or affirm receipt
of the transaction before it can be committed on Primary. The primary
node will shutdown if LGWR cannot transmit. Performance penalties are
severe.
In addtion, the following command is required to be issued on Primary to differentiate this mode from Instant Protection Mode:
|
| Instant Protection (no data loss mode) |
Behaves
exactly the same as Guaranteed Protection with the exception that data
divergence is permitted. ie. primary operation can continue and will
therefore diverge if the links to the standbys are broken. Performance
penalties are still severe. Switchover to standby is not
permitted until contact is re-established and synchronised.
|
| Rapid Protection (no data loss mode) |
Primary
node's LGWR transmits the redo stream asynchronously to the
standby nodes. No need to wait for acknowledgement from standby node
that the transaction has been received (noaffirm). Performance
penalties are not so severe.
|
| Delayed Protection | ARCH
transmits archived redo logs to standby node(s) in the same way as it
did in Oracle8i. |
The last mode there,
delayed protection is exactly how the old form of standby database
behaved. It cannot guarantee no data loss because ARCH is responsible
for transmitting redo logs that already contain committed transactions.
Database switchover
On Primary (primary becomes new standby): Oracle9i LogMiner
Have you ever wondered who was responsible for changing the salary
table to zero? Would it be useful
to locate the offending SQL statement and be provided with SQL required
to correct it? This is a great tool for undoing a single erroneous
transaction without having to go through a database restore.
Every change made to an Oracle database by default generates undo and
redo information which is accumulated in Oracle redo log files.
Oracle9i LogMiner is an integrated feature of the Oracle9i that
provides DBA's and auditors with the infrastructure required for
relational access to Oracle's redo stream. Some significant
enhancements to LogMiner for Oracle9i generated log
files include:
The online data dictionary can be extracted into the redo log
stream. This enables off-line analysis and provides a snapshot of the
data dictionary that matches the database objects in logs created at
that time. When mining logs in the same database that generated it, the
user
can choose to use the online data dictionary for SQL reconstruction and
internal identifier to name mapping which would otherwise be a manual
process.
The user can group DML statements into transactions with a
COMMITTED_DATA_ONLY option which directs LogMiner to group DML
statements into complete transactions. Only committed transactions will
be returned in the commit SCN order. When the DDL_DICT_TRACKING option
is enabled and LogMiner is run in the context of an open database, it
will automatically apply DDL statements executed in the original redo
stream to its internal dictionary. This enables correct reconstruction
of correct SQL statements on tables whose definition has been altered
or were missing when the original dictionary dump was captured.
LogMiner automatically versions the metadata kept in the database.
New DDL statements have been added to Oracle's vocabulary to allow for
logging of additional column values in case of updates. The extra
information can be used either to identify the updated row logically or
to capture a before-row image. This allows a DBA or auditor to use the
additional column information to construct more complete statements to
undo changes or to create SQL statements for applying to a different
databases.
A powerful new capability allows for queries that can filter out rows
based on actual data values of the columns updated. For instance it is
possible to write a query for a postal database that identifies all
lottery winners who moved to 90210 after making it big in Redwood
Shores.
LogMiner improves data availability by providing a window into all
changes made to a database. It enables auditing of database changes and
reduces the time and effort needed for data recovery.
Oracle9i
Flashback Query
Oracle9i's flashback query provides users with the capability of
viewing data in the past. In the first release of Oracle9i, the only
method available to invoke
flashback query was to execute the system supplied package
DBMS_FLASHBACK. Here is an example that goes back five minutes:
EXECUTE
DBMS_FLASHBACK.ENABLE_AT_TIME (SYSDATE -
(5/(24*60)));
The above statement sends the session back in time for the duration of
that session or until the EXECUTE DBMS_FLASHBACK.DISABLE is executed.
Standard date and time SQL functions are used to determine the time in
the past the data will be retrieved from. The drawback was that data
could not be updated during a flashback
query enabled session. Savvy programmers were able to save historical
data by placing it into a cursor and then dumping the contents of the
cursor into a worktable after the FLASHBACK.DISABLE procedure was
executed.
In Oracle9i Release 2, the AS OF timestamp clause has been added to the
SELECT statement to enable flashback query on a specific table or set
of tables. Developers are able to specify the AS OF clause for a
single-table, multiple-tables (joins) as well as specify different
times for different tables. The AS OF timestamp clause can also be used
inside INSERT or CREATE TABLE AS SELECT statements. Here is an example
of a SELECT statement using the AS OF timestamp clause:
UPDATE
emp.employee_payroll_table SET
emp_salary =
(SELECT emp_salary
FROM emp.employee_payroll_table
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
WHERE emp_last_name = 'FOOT')
WHERE
emp_last_name = 'FOOT';
The statement above uses the SYSTIMESTAMP value with an INTERVAL
function to update the emp.employee_payroll_table with data that is 24
hours old.
The AS OF timestamp clause (and its DBMS_FLASHBACK.ENABLE_AT_TIME
counterpart) maps the timestamp value to a SCN value. Oracle records
the SCN-TIMESTAMP mapping at five-minute intervals. This means that the
time you may actually retrieve the data from could be rounded down by
up to five minutes. For example, you could access a table using a
timestamp
that is a few minutes after the table is created and receive an error
because flashback query used a SCN value that is lower than the actual
time when the table was created.
Using Flashback Query
Before Flashback Query functionality can be used by ordinary users,
some actions are required from the database administrators:
Once these actions have been performed, users can access the old image of data that was modified by a DML statement. Before this data can be accessed, users have to call the package DBMS_FLASHBACK.ENABLE_AT_TIME(param) or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(param) to enable flashback query. Once flashback query is enabled, all queries performed will apply to the state of the database at the time specified by the input parameter to the package. After all the required queries have been performed on the past data, Flashback Query feature is disabled by calling package DBMS_FLASHBACK.DISABLE. From this point on all queries will access the current data in the database.
Flashback Query Applications
The Flashback Query feature can be used for:
These applications of Flashback Query liberate database
administrators from the task of correcting user errors and empower the
users to recover from their errors by giving access to previously
unavailable data through a simple, completely non-intrusive interface.
This not only enhances database
administrator and user productivity but also reduces overall database
administration costs.
Reorganize
Tables and Indexes Online. Rename Columns and Constraints
One of the most exciting new online features in Oracle9i is the online
table redefinition feature using the DBMS_REDEFINITION package.
The new feature allows DBA's to redefine tables online:
Renaming Columns and Constraints
In addition to renaming tables and indexes Oracle9i Release 2 allows
the renaming of columns and constraints on tables. In this example I
have a table and the following PK:
SQL> DESC test1
Name
Null? Type
-------------------- -------- --------------------
COL1
NOT NULL NUMBER(10)
COL2
NOT NULL VARCHAR2(50)
SQL> SELECT constraint_name FROM
user_constraints
3 WHERE table_name
= 'TEST1'
4 AND constraint_type = 'P';
CONSTRAINT_NAME
------------------------------
TEST1_PK
SQL> SELECT index_name, column_name FROM
user_ind_columns
3 WHERE table_name = 'TEST1';
INDEX_NAME
COLUMN_NAME
-------------------- --------------------
TEST1_PK
COL1
Redefine
a Table OnLine
When a table is redefined online, it is accessible by all read and
write operations during the redefinition process. Administrators have
control over when to switch from the original to the newly redefined
table. The switch process is very brief and is independent of the size
of the table or the complexity of the redefinition. The redefinition
process effectively creates a new table and improves its data block
layout efficiency.
The new Oracle9i online table redefinition feature improves data
availability, database performance, response time and disk space
utilization.
Additionally, during an application upgrade, administrators can start
the redefinition process, then periodically synchronize the new image
of the table so that the new image of the table includes the latest
changes to the original table. This reduces the amount of time to
complete the final redefinition step. Administrators can also validate
and use the data in the new image of the table before completing the
redefinition process. This is a very useful feature for administrators,
who must ensure that the upgrade process goes smoothly. Prior to executing
this package, the EXECUTE privilege
must be granted on the DBMS_REDEFINITION package or the
EXECUTE_CATALOG_ROLE
must be granted to the schema.
There are several restrictions that you should recognize before
attempting to redefine a table online:
There are five basic steps to redefine a table:
1. Create a new image of the table with all of the desired attributes.
2. Start the redefinition process.
3. Create any triggers, indexes, grants and constraints on the new
image of the table.
4. Optionally synchronize and validate data in the new image of the
table periodically.
5. Complete the redefinition of the table
Creation Script:
dbmshord.sql
Called By:
catproc.sql script
Located in :
$ORACLE_HOME/rdbms/admin
Directory
Grant Privilege on Package to Schema: EXECUTE
Privilege or EXECUTE_CATALOG_ROLE
DBMS_REDEFINITION Package Example
Create Employee Table
CREATE TABLE s_employee
(employee_id
NUMBER(7) CONSTRAINT
s_employee_id_nn NOT
NULL,
employee_last_name VARCHAR2(25) CONSTRAINT
s_employee_last_name_nn NOT NULL,
employee_first_name VARCHAR2(25),
userid
VARCHAR2(8),
start_date
DATE,
comments
VARCHAR2(255),
manager_id
NUMBER(7),
title
VARCHAR2(25),
department_id NUMBER(7),
salary
NUMBER(11, 2),
commission_pct NUMBER(4, 2),
CONSTRAINT s_employee_id_pk PRIMARY KEY (employee_id),
CONSTRAINT s_employee_userid_uk UNIQUE (userid),
CONSTRAINT s_employee_commission_pct_ck
CHECK (commission_pct IN (10, 12.5, 15, 17.5,
20)));
25 Records Inserted into Employee Table
Assume the Following Desired:
- Remove COMMENTS Column
- Add FULL_NAME Column
- Change DEPARTMENT_ID Column Name to DEPT_ID
- Increase Salary of All Employees by 50%
Step 1: Ensure Table Can be Redefined
Execute CAN_REDEF_TABLE Procedure, If Execution Succeeds without Error,
Then can Redefine
EXECUTE
dbms_redefinition.can_redef_table('plsql_user','s_employee')
Success: s_employee Table Can be Redefined
Illustration of a Table that Cannot Be Redefined
CREATE TABLE temp (temp VARCHAR2(10));
EXECUTE dbms_redefinition.can_redef_table('plsql_user','temp')
begin dbms_redefinition.can_redef_table('plsql_user','temp'); end;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "PLSQL_USER"."TEMP" with no
primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 236
ORA-06512: at line 1
Set of Criteria Checked to Ensure Redefinition Allowed
Step 2: Create Temporary Table with New Structure
CREATE TABLE temp_s_employee
(employee_id
NUMBER(7) CONSTRAINT s_employee_id_nn2 NOT NULL,
employee_last_name VARCHAR2(25) CONSTRAINT
s_employee_last_name_nn2 NOT NULL,
employee_first_name VARCHAR2(25),
employee_full_name VARCHAR2(51),
userid
VARCHAR2(8),
start_date
DATE,
manager_id
NUMBER(7),
title
VARCHAR2(25),
dept_id
NUMBER(7),
salary
NUMBER(11, 2),
commission_pct NUMBER(4, 2),
CONSTRAINT s_employee_id_pk2 PRIMARY KEY (employee_id),
CONSTRAINT s_employee_userid_uk2 UNIQUE (userid),
CONSTRAINT s_employee_commission_pct_ck2
CHECK (commission_pct IN (10, 12.5, 15, 17.5,
20)));
Step 3: Start the Redefinition Process
Execute START_REDEF_TABLE Procedure, map Existing Columns to Temporary
Table Columns
BEGIN
dbms_redefinition.start_redef_table('plsql_user',
's_employee','temp_s_employee',
'employee_id employee_id,
employee_last_name employee_last_name,
employee_first_name employee_first_name,
employee_first_name||'' ''||employee_last_name
employee_full_name,
userid userid,
start_date start_date,
manager_id manager_id,
title title,
department_id dept_id,
salary * 1.5 salary,
commission_pct commission_pct');
END;
/
Step 4: Start the Redefinition Process
If Singe Quote Desired, Use 2 Single Quotes (Line 7)
Standard SELECT Capabilities Valid in Column Mapping
Once Step 3 Complete, 25 Records Inserted into Temporary Table
Step 5: Complete the Redefinition Process
Execute FINISH_REDEF_TABLE Procedure
Synchronizes the Redefinition and Applies the Temporary Architecture
and Contents to the Existing Table. Prior to Executing the
FINISH_REDEF_TABLE Procedure, Option to Execute SYNC_INTERIM_TABLE
Procedure to Synchronize Table Contents and Reduce the Completion
Process Time
DBMS_REDEFINITION Package Example
BEGIN
dbms_redefinition.sync_interim_table('plsql_user',
's_employee','temp_s_employee');
END;
/
BEGIN
dbms_redefinition.finish_redef_table('plsql_user',
's_employee','temp_s_employee');
END;
/
DESC s_employee
Name
Null? Type
------------------------------- -------- ----
EMPLOYEE_ID
NOT NULL NUMBER(7)
EMPLOYEE_LAST_NAME
NOT NULL VARCHAR2(25)
EMPLOYEE_FIRST_NAME
VARCHAR2(25)
EMPLOYEE_FULL_NAME
VARCHAR2(51)
USERID
VARCHAR2(8)
START_DATE
DATE
MANAGER_ID
NUMBER(7)
TITLE
VARCHAR2(25)
DEPT_ID
NUMBER(7)
SALARY
NUMBER(11,2)
COMMISSION_PCT
NUMBER(4,2)
SELECT * FROM s_employee WHERE employee_id = 1;
EMP_ID EMP_L_NAME EMP_F_NAME EMP_FULL_NAME USERID
------ ---------- ---------- ---------------- --------
1 VELASQUEZ
CARMEN CARMEN VELASQUEZ cvelasqu
START_DATE MANAGER_ID TITLE DEPT_ID SALARY
COMMISSION_PCT
---------- ---------- --------- ------- ------ --------------
03-MAR-90
PRESIDENT 50 3750
Step 6: Drop the Temporary Table
Aborting a Redefinition
When you invoke START_REDEF_TABLE, there's the chance that something
might go wrong. You might, for example, run out of tablespace while
copying data from the original table to the target. Or you might
suddenly realize that you've created your target table incorrectly.
Either way, you'll need to abort the redefinition process and start
over. To abort a redefinition, invoke the ABORT_REDEF_TABLE procedure.
The following aborts the redefinition for my example:
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE
('gennick','gnis','gnis_redef');
END;
/
Be sure to call ABORT_REDEF_TABLE in the event that START_REDEF_TABLE
fails with an error. The abort procedure removes the materialized view
and materialized view log created by START_REDEF_ TABLE and does other
needed cleanup. You can also call ABORT_REDEF_TABLE anytime before
invoking FINISH_REDEF_ TABLE if you simply change your mind about the
redefinition.
There are two things ABORT_REDEF_TABLE does not do: It does not delete
any of the data inserted into the target table by START_REDEF_TABLE,
and it does not drop the target table. When you abort a redefinition,
you'll need to deal with the target table and any data it
contains.
Database
Resource Manager
One of the most critical challenges for database administrators is to
maintain a given performance level with limited hardware resources.
Traditionally, it has been up to the operating system (OS) to regulate
the resource management among the various applications running on a
system including Oracle databases. However, since the OS can not
distinguish one Oracle user/session from another, it can not perform
any resource management activities among different users or
applications sharing a database.
The Database Resource Manager, introduced in Oracle8i, provides database administrators the capability to distribute system resources among various users and applications sharing a database in a manner consistent with business priorities. Using this tool, a DBAs can divide the various users and applications using a database into different resource consumer groups and allocate CPU resources to each one of them in terms of percentages by creating a resource plan.
A Resource Plan can help ensure that system resources are first made available to certain critical business operations before other users or applications can use them. It is also possible to cap the resource consumption of a group of users or an application so that the critical operations are not starved for resources when they need them. The ability to allocate resources at multiple levels and create nested resource plans provides database administrators with an extremely powerful and flexible mechanism to specify how the unused resources should be divided among different resource consumer groups.
Oracle9i features a significantly enhanced Database Resource Manager with new capabilities to enable automatic and proactive management of database workload so as to ensure compliance with service level objectives. Using Oracle9i Database Resource Manager, a database administrator can
The