In
this practice we will perform the steps to determine disk drive read and write
contention and take corrective action.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Log
into the database as “system” with a password of “manager”.
Query v$filestat and dba_data_files to determine any contention between
the datafiles.
|
SQL>
connect system/manager Connected. SQL>
l
1 select
d.tablespace_name tablespace, d.file_name, f.phyrds, f.phywrts
2 from v$filestat f,
dba_data_files d
3 where f.file# =
d.file_id
4* order by tablespace_name, file_name SQL>
/ TABLESPACE
FILE_NAME
PHYRDS PHYWRTS ----------
---------------------------------------- ---------- ---------- INDX
/temp/oraclass/ORADATA/u03/indx01.dbf
5
2 SAMPLE
/temp/oraclass/ORADATA/u02/sample01.dbf
15
2 SYSTEM
/temp/oraclass/ORADATA/u01/system01.dbf
1004
3 TOOLS
/temp/oraclass/ORADATA/u05/tools01.dbf
4
2 UNDOTBS
/temp/oraclass/ORADATA/u02/undotbs.dbf
37
14 USERS
/temp/oraclass/ORADATA/u03/users01.dbf
5
2 6
rows selected. SQL> |
2.
Assuming
that you have contention between the indx01.dbf and users01.dbf datafiles, move
the indx01.dbf file to a new location. In
this case we are moving it to u04.
|
Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>
host cp $HOME/ORADATA/u03/indx01.dbf $HOME/ORADATA/u04/indx01.dbf SQL>
startup mount ORACLE
instance started. Total
System Global Area 113603896
bytes Fixed
Size
279864 bytes Variable
Size
109051904 bytes Database
Buffers
4194304 bytes Redo
Buffers
77824 bytes Database
mounted. SQL>
alter database rename file '$HOME/ORADATA/u03/indx01.dbf'
2 to '$HOME/ORADATA/u04/indx01.dbf'; Database
altered. SQL>
alter database open; Database
altered. SQL> |
This completes this practice
In
this practice we will look at some of the things which could impact checkpoints
and overall performance of the system.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
View
you alert log to determine how often your logs are switching.
Resize logs to switch about every 30 minutes.
|
DATAFILE '/temp/oracla Mon
Mar 18 20:22:19 2002 Thread
1 advanced to log sequence 3
Current log# 1 seq# 3 mem#
0: /temp/oraclass/ORADATA/u03/log01a.rdo Mon
Mar 18 Thread
1 advanced to log sequence 4
Current log# 2 seq# 4 mem#
0: /temp/oraclass/ORADATA/u03/log02a.rdo Mon
Mar 18 Thread
1 advanced to log sequence 5
Current log# 1 seq# 5 mem#
0: /temp/oraclass/ORADATA/u03/log01a.rdo Mon
Mar 18 Thread
1 advanced to log sequence 6
Current log# 2 seq# 6 mem#
0: /temp/oraclass/ORADATA/u03/log02a.rdo Mon
Mar 18 Thread
1 advanced to log sequence 7
Current log# 1 seq# 7 mem#
0: /temp/oraclass/ORADATA/u03/log01a.rdo Mon
Mar 18 Thread
1 advanced to log sequence 8
Current log# 2 seq# 8 mem#
0: /temp/oraclass/ORADATA/u03/log02a.rdo Mon
Mar 18 Thread
1 advanced to log sequence 9
Current log# 1 seq# 9 mem#
0: /temp/oraclass/ORADATA/u03/log01a.rdo Mon
Mar 18 |
2.
Query
the log_checkpoint_interval, log_checkpoint_timeout,
fast_start_io_target, and fast_start_mttr_target
parameters to determine if excessive checkpoints are occurring from small
settings in these parameters.
|
SQL>
show parameter checkpoint fast NAME
TYPE
VALUE ------------------------------------
----------- ------------------------------ log_checkpoint_interval
integer 0 log_checkpoint_timeout
integer 1800 log_checkpoints_to_alert
boolean
FALSE SQL>
show parameter fast NAME
TYPE
VALUE ------------------------------------
----------- ------------------------------ fast_start_io_target
integer 0 fast_start_mttr_target
integer 0 fast_start_parallel_rollback
string
LOW |
3.
Query
v$instance_recovery to see what the target and
estimated time for instance recovery would be.
|
SQL>
desc v$instance_recovery Name
Null? Type -----------------------------------------
-------- ---------------------------- RECOVERY_ESTIMATED_IOS
NUMBER ACTUAL_REDO_BLKS
NUMBER TARGET_REDO_BLKS
NUMBER LOG_FILE_SIZE_REDO_BLKS
NUMBER LOG_CHKPT_TIMEOUT_REDO_BLKS
NUMBER LOG_CHKPT_INTERVAL_REDO_BLKS
NUMBER FAST_START_IO_TARGET_REDO_BLKS
NUMBER TARGET_MTTR
NUMBER ESTIMATED_MTTR
NUMBER CKPT_BLOCK_WRITES
NUMBER SQL>
select target_mttr, estimated_mttr
2 from v$instance_recovery; TARGET_MTTR
ESTIMATED_MTTR -----------
--------------
0
22 |
This completes this practice
In
this practice we will look at how redo logs can affect performance.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Query
v$logfile to determine your logfile configuration.
Three groups helps ensure that there will
always be a log group available. Two
members or mirrors ensures data integrity.
|
SQL>
select * from v$logfile;
GROUP# STATUS TYPE
MEMBER ----------
------- ------- ----------------------------------------
1
ONLINE /temp/oraclass/ORADATA/u03/log01a.rdo
2
ONLINE /temp/oraclass/ORADATA/u03/log02a.rdo
3
ONLINE /temp/oraclass/ORADATA/u03/log03a.rdo
3
ONLINE /temp/oraclass/ORADATA/u04/log03b.rdo
1
ONLINE /temp/oraclass/ORADATA/u04/log01b.rdo
2
ONLINE /temp/oraclass/ORADATA/u04/log02b.rdo 6
rows selected. |
2.
Configure
your init.ora(p-file) to ensure your allowing for
multiple archive processes. More
archive processes ensures that you will always have an archive process available
to wite.
|
log_archive_start=true log_archive_dest=$HOME/ORADATA/ARCHIVE1 log_archive_duplex_dest=$HOME/ORADATA/ARCHIVE2 log_archive_max_processes=3 log_archive_format=db01%s.arc |
3.
Perform
several log switches and verify how many archive processes are active.
|
SQL>
alter system switch logfile; System
altered. SQL> SQL>
select * from v$archive_processes;
PROCESS STATUS
LOG_SEQUENCE STAT ----------
---------- ------------ ----
0 ACTIVE
0 IDLE
1 ACTIVE
0 IDLE
2 ACTIVE
0 IDLE
3 STOPPED
0 IDLE
4 STOPPED
0 IDLE
5 STOPPED
0 IDLE
6 STOPPED
0 IDLE
7 STOPPED
0 IDLE
8 STOPPED
0 IDLE
9 STOPPED
0 IDLE 10
rows selected. |
This completes this practice
In
this practice we will look at temporary tablespaces
and sort operations.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Check
the current temporary tablespaces and create two new
ones, one for HR applications and one for AP applications.
|
SQL>
select file#, status, bytes, name
2 from v$tempfile;
FILE# STATUS
BYTES NAME ----------
------- ---------- ----------------------------------------
1 ONLINE 157286400
/temp/oraclass/ORADATA/u02/temp01.dbf SQL>
create temporary tablespace hr_temp
2 tempfile
'$HOME/ORADATA/u03/hr_temp01.dbf' size 20m; Tablespace
created. SQL>
create temporary tablespace ap_temp
2 tempfile
'$HOME/ORADATA/u04/ap_temp01.dbf' size 20m; Tablespace
created. SQL> |
2.
Change
user sh’s default temporary tablespace to hr_temp
or any user you have created. Perform
any operation that would cause disk sorts then check the sort segment to verify
that the user utilized the hr_temp sort space.
|
SQL>
alter user sh temporary tablespace hr_temp; User
altered. SQL>
select tablespace_name, current_users,
used_extents
2 from v$sort_segment; TABLESPACE_NAME
CURRENT_USERS USED_EXTENTS ------------------------
------------- ------------ TEMP
0
0 HR_TEMP
1
1 |
3.
Check
v$sort_usage to see how many extents and blocks
where used in the sort.
|
SQL>
select session_num, tablespace, extents,
blocks
2 from v$sort_usage; SESSION_NUM
TABLESPACE
EXTENTS BLOCKS -----------
------------------------------- ---------- ----------
69 HR_TEMP
1
256 SQL> |
This
completes this practice.