📄 ch20.htm
字号:
execute count 144 144 36 .69
free buffer inspected 7 7 1.75 .03
free buffer requested 1383 1383 345.75 6.59
immediate (CR) block cleano 5 5 1.25 .02
logons cumulative 4 4 1 .02
messages received 38 38 9.5 .18
messages sent 39 39 9.75 .19
no work - consistent read g 4063 4063 1015.75 19.35
opened cursors cumulative 109 109 27.25 .52
parse count (hard) 23 23 5.75 .11
parse count (total) 120 120 30 .57
physical reads 1369 1369 342.25 6.52
physical writes 45 45 11.25 .21
recursive calls 2030 2030 507.5 9.67
redo blocks written 49 49 12.25 .23
redo entries 67 67 16.75 .32
redo size 25360 25360 6340 120.76
redo small copies 67 67 16.75 .32
redo synch writes 1 1 .25 0
redo wastage 644 644 161 3.07
redo writes 3 3 .75 .01
session logical reads 4698 4698 1174.5 22.37
session pga memory 645168 645168 161292 3072.23
session pga memory max 645168 645168 161292 3072.23
session uga memory 13772 13772 3443 65.58
session uga memory max 144296 144296 36074 687.12
sorts (memory) 85 85 21.25 .4
sorts (rows) 783 783 195.75 3.73
table fetch by rowid 2148 2148 537 10.23
table fetch continued row 2 2 .5 .01
table scan blocks gotten 1085 1085 271.25 5.17
table scan rows gotten 456 456 114 2.17
table scans (long tables) 2 2 .5 .01
table scans (short tables) 17 17 4.25 .08
total file opens 5 5 1.25 .02
user calls 139 139 34.75 .66
user commits 1 1 .25 0
write requests 6 6 1.5 .03
Average Write Queue Length
--------------------------
0
Event Name Count Total Time Avg Time
--------------------------------- ------------ ------------- -------------
SQL*Net message from client 150 0 0
SQL*Net message to client 150 0 0
control file sequential read 68 0 0
db file scattered read 137 0 0
db file sequential read 413 0 0
file identify 3 0 0
file open 9 0 0
log file sync 1 0 0
Event Name Count Total Time Avg Time
--------------------------------- ------------ ------------- -------------
control file parallel write 2 0 0
control file sequential read 2 0 0
db file parallel write 6 0 0
db file scattered read 1 0 0
db file sequential read 5 0 0
db file single write 2 0 0
log file parallel write 3 0 0
log file sync 1 0 0
pmon timer 70 0 0
rdbms ipc message 250 0 0
smon timer 1 0 0
LATCH_NAME GETS MISSES HIT_RATIO SLEEPS SLEEPS/MISS
------------------- ---------- ----------- ----------- ----------- -----------
Active checkpoint 69 0 1 0 0
Checkpoint queue l 1488 0 1 0 0
Token Manager 22 0 1 0 0
cache buffer handl 43 0 1 0 0
cache buffers chai 10389 0 1 0 0
cache buffers lru 1451 0 1 0 0
dml lock allocatio 11 0 1 0 0
enqueue hash chain 24 0 1 0 0
enqueues 62 0 1 0 0
ktm global data 1 0 1 0 0
library cache 3099 0 1 0 0
library cache load 64 0 1 0 0
list of block allo 1 0 1 0 0
messages 536 0 1 0 0
modify parameter v 4 0 1 0 0
multiblock read ob 288 0 1 0 0
ncodef allocation 3 0 1 0 0
process allocation 1 0 1 0 0
redo allocation 115 0 1 0 0
row cache objects 1318 0 1 0 0
session allocation 56 0 1 0 0
session idle bit 288 0 1 0 0
session switching 3 0 1 0 0
shared pool 1063 0 1 0 0
sort extent pool 1 0 1 0 0
system commit numb 193 0 1 0 0
transaction alloca 7 0 1 0 0
undo global data 101 0 1 0 0
LATCH_NAME NOWAIT_GETS NOWAIT_MISSES NOWAIT_HIT_RATIO
------------------- --------------- ---------------- ----------------
Token Manager 3 0 1
cache buffers chai 8643 0 1
cache buffers lru 1465 0 1
library cache 3 0 1
multiblock read ob 1 0 1
process allocation 1 0 1
row cache objects 3 0 1
vecio buf des 3 0 1
CLASS COUNT TIME
------------------ ---------------- ----------------
0 rows selected.
UNDO_SEGMENT TRANS_TBL_GETS TRANS_TBL_WAITS
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">UNDO_BYTES_WRITTEN SEGMENT_SIZE_BYTES XACTS
SHRINKS WRAPS
------------------ ------------------ ------------------
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">------------------ ------------------ ------------------
- ------------------ ------------------
0 8
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">0 2718 1226752
0 0 0
1 row selected.
NAME VALUE
-------------------------------------- --------------------------------------
background_dump_dest %RDBMS80%\trace
compatible 8.0.0.0.0
control_files D:\DATABASE\ctl1dogs.ora
db_block_size 2048
db_files 1024
db_name dogs
dml_locks 100
log_buffer 2048, 8192
log_checkpoint_interval 10000
max_dump_file_size 10240
processes 20, 50
remote_login_passwordfile SHARED
sequence_cache_hash_buckets 10
shared_pool_size 3500000
snapshot_refresh_processes 1
user_dump_dest %RDBMS80%\trace
NAME GET_REQS GET_MISS SCAN_REQ SCAN_MIS MOD_REQS COUNT CUR_USAG
--------------- -------- -------- -------- -------- -------- -------- --------
dc_free_extents 9 9 0 0 0 27 10
dc_rollback_seg 2 0 0 0 0 7 2
dc_users 28 0 0 0 0 14 1
dc_objects 169 16 0 0 0 144 138
dc_synonyms 2 1 0 0 0 12 1
dc_usernames 128 0 0 0 0 20 2
dc_object_ids 108 5 0 0 0 104 97
TABLE_SPACE
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">READS BLKS_READ READ_TIME
WRITES BLKS_WRT WRITE_TIME MEGABYTES
------------------------------------------------------------------------------
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM"> ---------- ---------- ---------- ----
---- ---------- ---------- ----------
DOGS
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM"> 0 0 0
0 0 0 10
SYSTEMB
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM"> 555 1378 0
53 53 0 26
TABLE_SPACE FILE_NAME
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">READS BLKS_READ READ_TIME
WRITES BLKS_WRT WRITE_TIME MEGABYTES
------------------------------ ------------------------------------------------
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM"
BORDER="0">; ---------- ---------- ---------- ----
---- ---------- ---------- ----------
DOGS D:\DATABASE\DOGS1.DBF
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM"> 0 0 0
0 0 0 10
SYSTEMB D:\DATABASE\SYSDOGS.ORA
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM"> 555 1378 0
53 53 0 26
START_TIME END_TIME
------------------------ ------------------------
15-jun-97 16:39:39 15-jun-97 16:43:09
BANNER
----------------------------------------------------------------
Oracle8 Server Release 8.0.2.0.2 - Beta
PL/SQL Release 3.0.2.0.2 - Beta
CORE Version 4.0.2.0.1 - Production
TNS for 32-bit Windows: Version 3.0.2.0.0 - Beta
NLSRTL Version 3.3.0.0.1 - Beta
</FONT></PRE>
<P><B>ANLYSIS: </B>As you can see, a wealth of data collected within Oracle can be
retrieved via the <TT>UTLBSTAT</TT> and <TT>UTLESTAT</TT> SQL scripts. This data
is retrieved mainly from the Oracle <TT>V$</TT> views. These views are the window
into the Oracle internal performance tables.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>When using a script such as <TT>UTLESTAT</TT>
where the output data is useful, employ the spool option under <TT>SVRMGR30</TT>.
This option copies the onscreen data into a file. If you specify a filename, that
name will be used. Use the following syntax:</P>
<P><FONT COLOR="#0066FF"><TT>SVRMGR> SPOOL stats;</TT></FONT>
<HR>
</BLOCKQUOTE>
<PRE></PRE>
<H2><FONT COLOR="#000077"><B>System Tuning</B></FONT></H2>
<P>I like to divide tuning into the following components:
<UL>
<LI>Software tuning--This involves both the OS and the RDBMS.
<P>
<LI>Hardware tuning--This includes proper system design and disk layout.
<P>
<LI>Application tuning--This involves properly designing the application and database
to take advantage of performance features and planning.
</UL>
<H3><FONT COLOR="#000077"><B>Oracle Tuning</B></FONT></H3>
<P>This section focuses on things that can be tuned in the Oracle RDBMS. A complete
list of Oracle tuning parameters is given in Appendix B, "Oracle Tuning Parameters."
<H4><FONT COLOR="#000077"><B>The SGA</B></FONT></H4>
<P>The System Global Area (SGA) contains the shared pool, the redo log buffer, and
the database block buffers.</P>
<P><FONT COLOR="#000077"><B>The Shared Pool</B></FONT></P>
<P>The shared pool contains the library cache, the data-dictionary cache, and the
shared session area (with the multithreaded server).</P>
<P><FONT COLOR="#000077"><B>The Library Cache</B></FONT></P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -