This
practice covers obtaining a listing of Oracle’s X$
tables.
Instructions
1.
Connect to SQL*Plus as the user SYSTEM.
|
<SQL*Plus:
Release 9.0.1.0.0 - Production on Tue Apr 23 (c)
Copyright 2001 Oracle Corporation.
All rights reserved. Connected
to: Oracle9i
Enterprise Edition Release 9.0.1.0.0 - Beta With the
Partitioning option JServer
Release 9.0.1.0.0 - Beta SQL> connect
system/manager Connected. SQL>
> |
2.
List all the X$
tables.
|
< SQL> set
pause on SQL> select
name from v$fixed_table where name like 'X$%'
2 / NAME ------------------------------ X$KQFTA X$KQFVI X$KQFVT X$KQFDT X$KQFCO X$KSLLT X$KSLLD X$KSLED X$KSLES X$KSLEI X$KSLLW X$KSLPO X$KSLWSC X$KSUSE> |
3.
List the v$
views that are available.
|
< SQL> l
1* select name from v$fixed_table where name like 'X$%' SQL> c/X$/V$
1* select name from v$fixed_table where name like 'V$%' SQL> / NAME ------------------------------ V$WAITSTAT V$BH V$GC_ELEMENT V$CR_BLOCK_SERVER V$GC_ELEMENTS_WITH_COLLISIONS V$FILE_CACHE_TRANSFER V$TEMP_CACHE_TRANSFER V$CLASS_CACHE_TRANSFER V$LOCK_ELEMENT V$BSP V$LOCKS_WITH_COLLISIONS V$FILE_PING V$TEMP_PING V$CLASS_PING V$ROWCACHE> |
You have completed the practice
This
practice covers verifying whether cursors are being reused.
Instructions
1.
What is the gethitratio
for the SQL
AREA
and other namespaces?
|
<SQL>
select namespace, gethitratio
2 from
v$librarycache
3 / NAMESPACE
GETHITRATIO ---------------
----------- SQL AREA
.942952431 TABLE/PROCEDURE
.83973571 BODY
.285714286 TRIGGER
.5 INDEX
0 CLUSTER
.967567568 OBJECT
1 PIPE 1 JAVA
SOURCE
1 JAVA
RESOURCE
.5 JAVA DATA
0 11 rows
selected. SQL>
> |
2.
For additional
practice, list user executions that show the number of users executing,
and also the number of executions and loads.
|
< SQL> select
sql_text, users_executing, executions, loads from
V$sqlarea where users_executing > 0 / SQL_TEXT
USERS_EXEC EXECUTIONS LOADS ------------------------------
---------- ---------- ---------- select
sql_text, users_executi
1
0
1 ng,
executions, loads from V$s qlarea
where users_executing > 0> |
3.
Execute the same statement again and notice that executions has
incremented
by
1.
|
< SQL> / SQL_TEXT
USERS_EXEC EXECUTIONS
LOADS ------------------------------
---------- ---------- ---------- select
sql_text, users_executi
1
1
1 ng,
executions, loads from V$s qlarea
where users_executing > 0> |
You have completed the practice
In
this practice, you measure the data
dictionary cache hit ratio.
Instructions
1.
Display the dictionary parameters and their gets and getmisses.
|
<SQL>
select parameter, gets, getmisses
2 from v$rowcache
3 / PARAMETER
GETS GETMISSES --------------------------------
---------- ---------- dc_free_extents
1696
32 dc_used_extents
0
0 dc_segments
1703
126 dc_tablespaces
1939
4 dc_tablespace_quotas
0
0 dc_files
0
0 dc_users
4572
27 dc_rollback_segments
2419
21 dc_objects
2019
982 dc_global_oids
25
13 dc_constraints
0
0 dc_object_ids
4434
150 dc_sequences
3
3 dc_usernames
222
8 dc_database_links
0
0 dc_histogram_defs
1779
136 table scns
0
0 dc_outlines
0
0 dc_profiles
12
1 encrypted
object row cache
0
0 encryption
profile row cache
0
0 ifs_acl_cache_entries
0
0 dc_users
0
0 dc_histogram_data
0
0 dc_histogram_data_values
0
0 partition
scns
0
0 dc_user_grants
1296
14 dc_app_role
0
0 28 rows
selected. > |
2. The previous example requires that you perform math for each parameter to compute your dictionary hit ratio. For extra credit, using V$ROWCACHE, compute the entire dictionary hit ratio.
|
01234567890123456789012345678901234567890123456789012345678901224 <SQL>
select ( 1 - (sum(getmisses)/ (sum(gets) +
2 sum(getmisses))))
* 100 "Hit Rate"
3 FROM
v$rowcache
4 /
Hit Rate ---------- 93.6434109 SQL>
> |
You have completed the practice
In this practice, you pin a large object in the shared pool.
Types of objects to keep (pin) in the shared pool:
Facts about objects to keep (pin) in the shared pool:
Instructions
1. First, verify that the sys.standard package is not marked kept.
|
SQL> select name, kept 2 from v$db_object_cache 3 where name like 'STAND%' 4 / NAME KEP --------------- --- STANDARD NO SQL> > |
2. Use DBMS_SHARED_POOL to pin the package. Notice the P in the statement that was not shown in the presentation. This is the default for procedures, packages and functions. For triggers the indicator is R, T is the indicator for type, and so on.
|
SQL> begin 2 DBMS_SHARED_POOL.KEEP( 3 'SYS.STANDARD', 'P'); 4 end; 5 / PL/SQL procedure successfully completed. SQL> > |
3. Now, verify that
sys.standard is marked kept.
|
SQL> select name, kept 2 from v$db_object_cache 3 where name like 'STAND%' 4 / NAME KEP --------------- --- STANDARD YES STANDARD YES SQL> > |
You have completed the practice.
In
this practice, you ascertain the maximum uga
usage as one step in the process of sizing the large pool.
Facts
about objects to keep (pin) in the shared pool:
If you are using shared servers, you should set the large pool, so the UGA is stored there rather than inside the shared pool.
Instructions
1.
Create a query whose output displays
the maximum UGA space used by all users.
|
< SQL>
select SUM(value) ||'bytes' "Total max memory"
2 from
V$SESSTAT, V$STATNAME
3 where
name = 'session uga memory max'
4 and
v$sesstat.statistic# = v$statname.statistic#
5 / Total max
memory --------------------------------------------- 2412556
bytes SQL>
> |
You
have completed the practice.
In
this practice, you will identify anonymous PL/SQL blocks.
Facts
about eliminating anonymous PL/SQL blocks:
There
are two solutions
Instructions
1.
Identify any anonymous PL/SQL blocks currently in the shared pool above
a certain threshold. Normally,
that threshold would be more than 500 keystrokes.
To force some output, we’ll set the threshold to 50.
|
< SQL>
select sql_text from v$sqlarea
2 where
command_type = 47
3 and
length(sql_text) > 50; SQL_TEXT --------------------------------------------- /*
OracleOEM */ BEGIN
smp_vdm_notification_ pkg.purge_ui_notification;
END; /*
OracleOEM */ BEGIN smp_vdp.check_and_add_o ms(:1,
:2, :3); END; DECLARE
opstring_in VARCHAR2(31) := :1;
session_num_in NUMBER := :2;
logmnr _uid_inout
BINARY_INTEGER := :3; BEGIN sys.logmnr_krvrdluid3(opstring_in,
session_nu m_in,
logmnr_uid_inout);
:3 := logmnr_ui d_inout;
END; SQL> > |
You have completed the practice
SIZING BUFFER CACHE
In
this topic you will practice allocating memory without shutting down the
database. Also, you will set
the parameter DB_CACHE_ADVICE to gather information on setting the various
database buffer caches.
Assumptions
·
The
examples in this practice use an spfile for configuration.
If you are using a parameter file then you must edit this file manually.
Instructions
1.
Determine the current size of the dynamic memory areas of the database.
|
SQL>
show parameter cache_size NAME
TYPE
VALUE ------------------------------
----------- -------- db_16k_cache_size
big integer 0 db_2k_cache_size
big integer 0 db_32k_cache_size
big integer 0 db_4k_cache_size
big integer 0 db_8k_cache_size
big integer 0 db_cache_size
big integer 20971520 db_keep_cache_size
big integer 0 db_recycle_cache_size
big integer 0 SQL>
show parameter shared_pool_size NAME
TYPE
VALUE ------------------------------
----------- -------- shared_pool_size
big integer 62914560 SQL>
show parameter sga_max_size NAME
TYPE
VALUE ------------------------------
----------- -------- sga_max_size
big integer 122449892 SQL>
show sga Total
System Global Area 122449892
bytes Fixed
Size
282596 bytes Variable
Size
100663296 bytes Database
Buffers
20971520 bytes Redo
Buffers
532480 bytes SQL> |
2.
Add two granules to the current maximum size of the SGA and restart the
database.
|
SQL>
alter system set sga_max_size=124m
2 scope=spfile; System
altered. SQL>
conn / as sysdba Connected. SQL>
shutdown immediate Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>
startup ORACLE
instance started. Total
System Global Area 130838540
bytes Fixed
Size
282636 bytes Variable
Size
109051904 bytes Database
Buffers
20971520 bytes Redo
Buffers
532480 bytes Database
mounted. Database
opened. SQL>
|
3.
Dynamically add 1 granule each to the shared pool and the default
database buffer cache.
|
SQL>
show parameter shared_pool_size NAME
TYPE
VALUE ------------------------------
----------- --------shared_pool_size
big integer 62914560 SQL>
alter system set shared_pool_size=64m; System
altered. SQL>
show parameter shared_pool_size NAME
TYPE
VALUE ------------------------------
----------- --------shared_pool_size
big integer 67108864 SQL>
show parameter db_cache_size NAME
TYPE
VALUE ------------------------------
----------- -------- db_cache_size
big
integer 20971520 SQL>
alter system set db_cache_size=24m; System
altered. SQL>
show parameter db_cache_size NAME
TYPE
VALUE ------------------------------
----------- -------- db_cache_size
big integer 25165824 SQL>
show sga Total
System Global Area 130838540
bytes Fixed
Size
282636 bytes Variable
Size
104857600 bytes Database
Buffers
25165824 bytes Redo
Buffers
532480 bytes SQL>
|
4.
Describe the view V$DB_CACHE_ADVICE.
|
SQL>
desc v$db_cache_advice Name
Null? Type ---------------------------
-------- ----------- ID
NUMBER NAME
VARCHAR2(20) BLOCK_SIZE
NUMBER ADVICE_STATUS
VARCHAR2(3) SIZE_FOR_ESTIMATE
NUMBER BUFFERS_FOR_ESTIMATE
NUMBER ESTD_PHYSICAL_READ_FACTOR
NUMBER ESTD_PHYSICAL_READS
NUMBER SQL> |
5.
Set the DB_CACHE_ADVICE parameter to start gathering statistics on the
database buffer caches.
|
SQL>
alter system set db_cache_advice=ready; System
altered. SQL>
alter system set db_cache_advice=on; System
altered. SQL>
|
6.
Query the V$DB_CACHE_ADVICE view.
|
SQL>
select name, advice_status, size_for_estimate,
2 estd_physical_reads
3 from
v$db_cache_advice; NAME
ADV SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS ---------
--- ----------------- ------------------- DEFAULT
ON
2.2969
0 DEFAULT
ON
4.5938
0 DEFAULT
ON
6.8906
0 DEFAULT
ON
9.1875
0 DEFAULT
ON
11.4844
0 DEFAULT
ON
13.7813
0 DEFAULT
ON
16.0781
0 DEFAULT
ON
18.375
0 DEFAULT
ON
20.6719
0 ... |
This
completes this practice.
This
practice covers adding the recycle and buffer pools to the SGA.
Assumptions
·
The
examples in this practice use an spfile for configuration. If
you are using a parameter file then you must edit this file manually.
Instructions
1.
Show the parameters that are used to size the database buffer caches.
|
SQL>
show parameter cache_size NAME
TYPE
VALUE ---------------------------
----------- ----------- db_16k_cache_size
big integer 0 db_2k_cache_size
big integer 0 db_32k_cache_size
big integer 0 db_4k_cache_size
big integer 0 db_8k_cache_size
big integer 0 db_cache_size
big integer 16777216 db_keep_cache_size
big integer 0 db_recycle_cache_size
big integer 0 SQL> |
2.
Add 1 granule each to the recycle and keep pools and then show the
parameters that are used to size the database buffer caches again.
|
SQL>
alter system set db_keep_cache_size=4m; System
altered. SQL>
alter system set db_recycle_cache_size=4m; System
altered. SQL>
show parameter cache_size NAME
TYPE
VALUE ---------------------------
----------- --------- db_16k_cache_size
big integer 0 db_2k_cache_size
big integer 0 db_32k_cache_size
big integer 0 db_4k_cache_size
big integer 0 db_8k_cache_size
big integer 0 db_cache_size
big integer 16777216 db_keep_cache_size
big integer 4194304 db_recycle_cache_size
big integer 4194304 SQL> |
3.
Alter tables and indexes to begin using the new buffer pools.
|
SQL>
create table emp
2 as select * from
hr.employees; Table
created. SQL>
alter table emp
2 storage(buffer_pool
keep); Table
altered. SQL>
create index emp_employee_id_idx on
2 emp(employee_id)
3 storage(buffer_pool
keep)
4 tablespace indx; Index
created. SQL>
alter table hr.job_history
2 storage(buffer_pool
recycle); Table
altered. SQL> |
This
completes this practice.
This
practice covers how to use automatic segment space management to trace segment
free space instead of using free lists.
Assumptions
·
The
examples in this practice use an spfile for configuration.
If you are using a parameter file then you must edit this file manually.
Instructions
1.
Check for any tablespace currently in the database that is using auto
segment space management.
|
SQL>
select tablespace_name, extent_management,
2 segment_space_management
3* from dba_tablespaces TABLESPACE_NAME
EXTENT_MAN SEGMEN ------------------------------
---------- ------ SYSTEM
DICTIONARY MANUAL UNDOTBS
LOCAL
MANUAL CWMLITE
LOCAL
MANUAL DRSYS
LOCAL
MANUAL EXAMPLE
LOCAL
MANUAL INDX
LOCAL
MANUAL TEMP
LOCAL
MANUAL TOOLS
LOCAL
MANUAL USERS
LOCAL
MANUAL 9
rows selected. SQL> |
2.
Create a tablespace that uses automatic segment space management.
|
SQL>
create tablespace user2
2 extent management
local
3 segment space
management auto; Tablespace
created. SQL> *
This example uses Oracle Managed Files (OMF). |
3.
Again check for any tablespace that is using auto segment space
management.
|
SQL>
select tablespace_name, extent_management,
2 segment_space_management
3 from
dba_tablespaces; TABLESPACE_NAME
EXTENT_MAN SEGMEN ------------------------------
---------- ------ SYSTEM
DICTIONARY MANUAL UNDOTBS
LOCAL
MANUAL CWMLITE
LOCAL
MANUAL DRSYS
LOCAL
MANUAL EXAMPLE
LOCAL
MANUAL INDX
LOCAL
MANUAL TEMP
LOCAL
MANUAL TOOLS
LOCAL
MANUAL USERS
LOCAL
MANUAL USER2
LOCAL
AUTO 10
rows selected. SQL> |
4.
Create or move segments into the new tablespace, therefore causing them
to use the new automatic segment space management feature.
|
SQL>
create table emp
2 tablespace user2
3 as select * from
hr.employees; Table
created. SQL>
alter table hr.job_history
2 move tablespace
user2; Table
altered. SQL> |
This
completes this practice.
This
practice covers how to configure an instance to use multiple database writer
processes (DBWn).
Assumptions
·
The
examples in this practice use an spfile for configuration.
If you are using a parameter file then you must edit this file manually.
Instructions
1.
To determine the need for multiple DBWn processes check for the ‘free
buffer waits’ event. A high
number for this event signals that the current DBWn processes are having trouble
keeping up with the demand for cleaning out dirty buffers from the database
buffer caches.
|
SQL>
select total_waits
2 from
v$system_event
3 where event =
'free buffer waits'; TOTAL_WAITS -----------
742 SQL>
|
2.
Verify the current number of database writer processes (DBWn). Hint:
Query v$bgprocess for background processes where paddr is not equal to ‘00’.
|
SQL>
desc v$bgprocess Name
Null? Type ------------------------
-------- --------------- PADDR
RAW(4) NAME
VARCHAR2(5) DESCRIPTION
VARCHAR2(64) ERROR
NUMBER SQL>
select name, description
2 from v$bgprocess
3 where paddr !=
'00'; NAME
DESCRIPTION -----
------------------------------ PMON
process cleanup DBW0
db writer process 0 ARC0
Archival Process 0 LGWR
Redo etc. CKPT
checkpoint SMON
System Monitor Process RECO
distributed recovery 8
rows selected. SQL> |
3.
Increase the number of database writer processes (DBWn) by 1.
Since this is a static parameter you must shutdown and restart the
instance.
|
SQL>
alter system set db_writer_processes=2
2 scope=spfile; System
altered. SQL>
shutdown immediate Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>
startup ORACLE
instance started. Total
System Global Area 139227188
bytes Fixed
Size
282676 bytes Variable
Size
113246208 bytes Database
Buffers
25165824 bytes Redo
Buffers
532480 bytes Database
mounted. Database
opened. SQL> |
4.
Verify the increase in the number of database writer processes in the
instance.
|
SQL>
select name, description
2 from v$bgprocess
3 where paddr !=
'00'
4 and name like 'DBW%'; NAME
DESCRIPTION -----
----------------------- DBW0
db writer process 0 DBW1
db writer process 1 SQL>
|
This
completes this practice.
In
this practice we will perform the steps to view and resize the log buffer and
the java pool.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Log into the database as “system” with a password of “manager”.
Query v$session_wait to see if there are any wait events against the log
buffer and v$sysstat to see what the waits are.
|
SQL>
connect system/manager Connected. SQL>
select sid, event, seconds_in_wait, state
2 from
v$session_wait
3 where event = 'log
buffer space'; no
rows selected SQL>
select r.value "Retries", e.value "Entries",
2 r.value/e.value*100
"Percentage"
3 from v$sysstat r,
v$sysstat e
4 where r.name =
'redo buffer allocation retries'
5 and e.name = 'redo
entries';
Retries Entries
Percentage ----------
---------- ----------
14942 2126508
.702654305 SQL> |
2.
Adjust the log_buffer parameter in the init.ora file if the percentage of
waits are greater than 1 percent.
|
########################## # #
Date:
#
Updated: #
File Name: initdb01.ora #
File Version: 1.1.2 # ########################### background_dump_dest=/temp/oraclass/ADMIN/BDUMP compatible=9.0.0 control_files=/temp/oraclass/ORADATA/u01/ctrl01.ctl core_dump_dest=/temp/oraclass/ADMIN/CDUMP db_block_size=4096 db_cache_size=4M db_domain=world db_name=db01 global_names=TRUE instance_name=db01 log_buffer
= 64000 shared_pool_size
= 50000000 java_pool_size
= 50000000 java_max_sessionspace_size
= 50000000 "initdb01.ora"
31 lines, 785 characters |
3.
Query the java pool entries of v$sgastat to determine java pool
utilization.
|
SQL>
select * from v$sgastat
2 where pool = 'java
pool'; POOL
NAME
BYTES -----------
-------------------------- ---------- java
pool free memory
50331648 SQL> |
This completes this practice
In
this practice we will perform the steps to view and resize the log buffer and
the java pool.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Log into the database as “system” with a password of “manager”.
Query v$session_wait to see if there are any wait events against the log
buffer and v$sysstat to see what the waits are.
|
SQL>
connect system/manager Connected. SQL>
select sid, event, seconds_in_wait, state
2 from
v$session_wait
3 where event = 'log
buffer space'; no
rows selected SQL>
select r.value "Retries", e.value "Entries",
2 r.value/e.value*100
"Percentage"
3 from v$sysstat r,
v$sysstat e
4 where r.name =
'redo buffer allocation retries'
5 and e.name = 'redo
entries';
Retries Entries
Percentage ----------
---------- ----------
14942 2126508
.702654305 SQL> |
2.
Adjust the log_buffer parameter in the init.ora file if the percentage of
waits are greater than 1 percent.
|
########################## # #
Date:
#
Updated: #
File Name: initdb01.ora #
File Version: 1.1.2 # ########################### background_dump_dest=/temp/oraclass/ADMIN/BDUMP compatible=9.0.0 control_files=/temp/oraclass/ORADATA/u01/ctrl01.ctl core_dump_dest=/temp/oraclass/ADMIN/CDUMP db_block_size=4096 db_cache_size=4M db_domain=world db_name=db01 global_names=TRUE instance_name=db01 log_buffer
= 64000 shared_pool_size
= 50000000 java_pool_size
= 50000000 java_max_sessionspace_size
= 50000000 "initdb01.ora"
31 lines, 785 characters |
3.
Query the java pool entries of v$sgastat to determine java pool
utilization.
|
SQL>
select * from v$sgastat
2 where pool = 'java
pool'; POOL
NAME
BYTES -----------
-------------------------- ---------- java
pool free memory
50331648 SQL> |
This
completes this practice.