In
this practice we will look at latch waits and shared pool tuning.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Query
v$latch to determine if there are any misses,
sleeps, or wait times on memory latches.
|
SQL>
select gets, misses, sleeps, wait_time
2 from v$latch
3 where addr
= '8000A934';
GETS MISSES
SLEEPS WAIT_TIME ----------
---------- ---------- ----------
904741
0
0
0 SQL> |
2.
Check
parse_calls and executions in v$sqlarea
to see if the shared sql area is reusing code or if it’s being reloaded.
|
SQL>
select sql_text, parse_calls, executions
2 from v$sqlarea
3 where address =
'83107C78'
4 or address =
'83281258'; SQL_TEXT -------------------------------------------------------------------------------- PARSE_CALLS
EXECUTIONS -----------
---------- select
count(*) from sys.job$ where (next_date
> sysdate) and (next_date < (sysd ate+5/86400))
1
1131 update
seg$ set
type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13,
65535, NULL, :13),groups=decod e(:14,
65535, NULL, :14), cachehint=:15, hwmincr=:16,
spare1=DECODE(:17,0,NULL,: 17)
where ts#=:1 and file#=:2 and block#=:3
720
720 |
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
In
this practice we will look at automatic undo management.
Creating and assigning undo tablespaces.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Set
the p-file parameters enabling automatic undo management.
|
Edit
p-file adding the following entries: undo_management=AUTO undo_tablespace=UNDOTBS |
2.
Create
a new undo tablespace and make it current.
|
SQL>
create undo tablespace undo2
2 datafile '$HOME/ORADATA/u05/undo201.dbf'
size 50m; Tablespace
created. SQL>
alter system set undo_tablespace=undo2; System
altered. SQL> |
3.
Set
undo retention time in the p-file to 30 minutes..
|
Edit
the p-file adding the undo retention time: undo_management=AUTO undo_tablespace=UNDOTBS undo_retention=1800 |
This
completes this practice.
In
this practice we will look at configuring and monitoring manual rollback
segments.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Query
v$undostat to determine the undo blocks written.
|
SQL>
l
1 select begin_time,
end_time, undoblks
2 from v$undostat
3* where rownum < 11 SQL>
/ BEGIN_TIM
END_TIME UNDOBLKS ---------
--------- ---------- 10
rows selected. |
2.
Use
v$rollstat to determine the performance of the
rollback segments.
|
SQL>
l
1 select writes,
gets, waits, extents, shrinks, wraps
2* from v$rollstat SQL>
/
WRITES
GETS
WAITS
EXTENTS SHRINKS
WRAPS ----------
---------- ---------- ---------- ---------- ----------
16640
134
0
7
0
0
0
9
0
2
0
0
0
9
0
2
0
0
0
9
0
2
0
0
142
11
0
2
0
0
364
11
0
2
0
0
142
11
0
2
0
0
142
11
0
2
0
0
142
11
0
2
0
0 9
rows selected. SQL> |
3.
Query
v$rollstat again to determine a baseline for writes.
|
SQL>
select usn, writes
2 from v$rollstat;
USN WRITES ----------
----------
0 16640
9
142
10
142
11
142
12
142
13
364
14
142
15
268
16
284 9
rows selected. |
4.
Simulate
a running application by increasing the salary of the employees table.
|
SQL>
update employees
2 set salary =
salary*2; 179
rows updated. |
5.
Query
v$rollstat once more to see the number of writes the
update created.
|
SQL>
select usn, writes
2 from v$rollstat;
USN WRITES ----------
----------
0 16640
9
142
10
142
11
142
12
142
13
364
14 19872
15
268
16
284 9
rows selected. |
This completes this practice
In
this practice we will look at automatic undo management.
Creating and assigning undo tablespaces.
Assumptions
·
You
have a 9i database up and running
Instructions
1.
Set
the p-file parameters enabling automatic undo management.
|
Edit
p-file adding the following entries: undo_management=AUTO undo_tablespace=UNDOTBS |
2.
Create
a new undo tablespace and make it current.
|
SQL>
create undo tablespace undo2
2 datafile '$HOME/ORADATA/u05/undo201.dbf'
size 50m; Tablespace
created. SQL>
alter system set undo_tablespace=undo2; System
altered. SQL> |
3.
Set
undo retention time in the p-file to 30 minutes..
|
Edit
the p-file adding the undo retention time: undo_management=AUTO undo_tablespace=UNDOTBS undo_retention=1800 |
This completes this practice.