Page 346-348
Format error
NoWait
Waiter
Latch Name
Where
Misses Sleeps Sleeps
------------------------ -------------------------------- ----------
---------- --------
archive process
latch kcrrpa
0 1
0
cache buffers
chains kcbgtcr: kslbegin excl
0 187
157
cache buffers
chains kcbrls: kslbegin
0
67 104
cache buffers
chains kcbgcur: kslbegin
0 13
10
cache buffers
chains kcbgtcr: fast path
0 12
28
cache buffers
chains kcbget: pin buffer
0 9
6
cache buffers
chains kcbzgb: scan from tail. nowait
0 7
0
cache buffers
chains kcbget: exchange
0 6
2
cache buffers
chains kcbzib: multi-block read: nowait
0 6
0
cache buffers
chains kcbzwb
0 3
2
cache buffers
chains kcbchg: kslbegin: bufs not pinne
0 2
3
cache buffers
chains kcbchg: kslbegin: call CR func
0 2
1
cache buffers
chains kcbzib: finish free bufs
0 2
4
cache buffers
chains kcbnew
0 2
0
cache buffers
chains kcbget: exchange rls
0 1
2
enqueue hash chains
ksqrcl
0 19
2
enqueue hash chains
ksqgtl3
0 1
18
enqueues
ksqdel
0 21
6
enqueues
ksqgel: create enqueue
0 12
26
library cache
kglpnc: child
0 522
593
library cache
kglpin: child: heap processing
0 141
1
library cache
kglupc: child
0 122
211
library cache
kglpndl: child: before processin
0 110
114
library cache
kglhdgc: child:
0 106
3
library cache
kgllkdl: child: cleanup
0 45
2
library cache
kglpnp: child
0 43
155
library cache
kglpndl: child: after processing
0 32
0
library cache
kglobpn: child:
0 22
0
library cache
kglhdgn: child:
0 21
65
library cache
kglic
0 9
16
library cache
kgldte: child 0
0 4
17
library cache
kgldti: 2child
0 1
0
library cache
kglpin
0 1
3
library cache pin
kglupc
0 1 0
redo allocation
kcrfwr
0 21
22
redo allocation
kcrfwi: more space
0 1
1
redo allocation
kcrfwr: redo allocation 0 0
1 0
resmgr group change
latc kskincrstat1
0 24
24
session allocation
ksufap: active sessions
0 12
0
session idle bit
ksupuc: clear busy
0 10
6
session idle bit
ksupuc: set busy
0 3
7
shared pool
kghalo
0 16
2
shared pool
kghupr1
0 7
25
shared pool
kghfrunp: alloc: wait
0 6
0
shared pool
kghfrunp: clatch: nowait
0 4
0
shared pool
kghfrunp: clatch: wait
0 2
3
Page 476
Format error
|
Dialect
|
Run Queue Column
|
Page-In Column
|
Page-Out Column
|
User Column
|
|
HP/UX
|
1
|
8
|
9
|
16
|
|
AIX
|
1
|
6
|
7
|
14
|
|
Solaris
|
1
|
8
|
9
|
20
|
|
Linux
|
1
|
8
|
9
|
14
|
|
|
System Column
|
Idle Column
|
Wait Column
|
|
|
HP/UX
|
17
|
18
|
NA
|
|
|
AIX
|
15
|
16
|
17
|
|
|
Solaris
|
21
|
22
|
NA
|
|
|
Linux
|
15
|
16
|
NA
|
|
Table 12.1:
Differences in vmstat Data by Operating System
Page 477
Format error
HP/UX vmstat columns:
cat /tmp/msg$$|sed 1,3d |\
awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9,
$16, $17, $18) }' |\
while read RUNQUE PAGE_IN PAGE_OUT USER_CPU
SYSTEM_CPU IDLE_CPU
IBM AIX vmstat columns:
cat /tmp/msg$$|sed 1,3d |\
awk '{ printf("%s %s %s %s %s %s\n", $1, $6, $7,
$14, $15, $16, $17) }' |\
while read RUNQUE PAGE_IN PAGE_OUT USER_CPU
SYSTEM_CPU IDLE_CPU WAIT_CPU
Sun Solaris vmstat columns:
cat /tmp/msg$$|sed 1,3d |\
awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9,
$20, $21, $22) }' |\
while read RUNQUE PAGE_IN PAGE_OUT USER_CPU
SYSTEM_CPU IDLE_CPU
Linux vmstat columns:
cat /tmp/msg$$|sed 1,3d |\
awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9,
$14, $15, $16) }' |\
while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU
Page 541:
"network RAM and CPU speeds" should read "Network, RAM and CPU
speeds".
Page 605
Format error
In the example below, because we have lots of RAM
defined in PGA (more than 5% of
pga_aggregate_target), the
standard full join is replaced by a
hash full join outer
operation:
with
emp
as
(
select
'joel' ename,
40 deptno
from
dual
union all
select
'mary' ename,
50 deptno
from
dual
)
select
e.ename,
d.dname
from
emp e
full join
dept d
using
(
deptno
);
ENAM DNAME
---- --------------
JOEL OPERATIONS
MARY
SALES
RESEARCH
ACCOUNTING
----------------------------------------------------------------------------------
| Id
| Operation
| Name
| Rows | Bytes |
Cost (%CPU)| Time
|
----------------------------------------------------------------------------------
|
0 |
SELECT STATEMENT
|
|
8 | 120 |
8 (13)| 00:00:01
|
|
1 |
VIEW
| VW_FOJ_0 |
8 | 120 |
8 (13)| 00:00:01
|
|* 2
|
HASH JOIN
FULL OUTER|
|
8 | 176 |
8 (13)| 00:00:01
|
|
3 |
VIEW
|
|
2 |
18 | 4
(0)| 00:00:01 |
|
4 |
UNION-ALL
|
|
|
|
|
|
|
5 |
FAST DUAL
|
|
1 |
|
2 (0)|
00:00:01 |
|
6 |
FAST DUAL
|
|
1 |
|
2 (0)|
00:00:01 |
|
7 |
TABLE ACCESS FULL
| DEPT
| 4 |
52 |
3 (0)|
00:00:01 |
----------------------------------------------------------------------------------
Page 885:
The table name should be sys.aux_stats$ and the disk read
timings should be expressed in milliseconds
The gathered statistics are
captured via the dbms_stats package (in 9.2 and above) and
CPU statistics are captured automatically in 10g and stored in the
sys.aux_stats$ view.
·
sreadtim:
Single block disk read time (in milliseconds)
·
mreadtim:
Multiblock disk read-time (in milliseconds)
·
cpuspeed: CPU speed in MHz
·
mbrc:
average
db_file_multiblock_read_count in number of blocks
·
maxthr:
Maximum I/O throughput (only used with Oracle parallel query)
·
slavethr:
Oracle parallel query slave (factotum) throughput
Page 923
Format error
n
The numbers of blocks in the
table: Small tables are
accessed faster with a full scan and forcing index usage may hurt
performance.
n
System statistics: the
dbms_stats.gather_system_stats procedure measures
external timing for index access (sequential reads) and full-scan
access (scattered reads). If Oracle sees expensive index disk
reads, it may ignore an index.
n
System parameters: There
are several initialization parameters that can impact the propensity
of the optimizer to choose an index:
o
db_file_multiblock_read_count:
Prior to Oracle 10.2 (when this setting became
automatic), this parameter helped govern the relative costs of
full-scan vs. index costs.
o
sort_area_size (if not using
pga_aggregate_target):
The sort_area_size influences the CBO when deciding whether
to perform an index access or a sort of the result set. The higher
the value for sort_area_size, the more likely that a sort will be
performed in RAM, and the more likely that the CBO will favor a sort
over presorted index retrieval.
n
Optimizer parameter values:
You can adjust several optimizer parameters to force Oracle to use
an index:
o
optimizer_mode: The
all_rows access method
often favors a parallel full-table scan over an index scan. The
first_rows_n optimizer_mode will often stop Oracle from ignoring an
index because it favors index access over computing resources.
o
optimizer_index_cost_adj: This
parameter alters the costing algorithm for access paths involving
indexes. The smaller the value, the lower the cost of index access.
o
Optimizer_index_caching –
The
optimizer_index_caching parameter is
set by the DBA to help the optimizer know, on average, how much of
an index resides inside the data buffer.
The setting for
optimizer_index_caching affects the CBO’s decision to use an
index for a table join (nested loops) or to favor a full-table scan.
For an example of how parameters can effect index
usage, a developer may be tempted to switch to the
all_rows optimizer_mode
(or add an all_rows hint
to their SQL), believing that it will improve throughput, while not
realizing that all_rows favors full-scans over index usage.
In a similar example, some developers will
mistakenly add a parallel
hint to a SQL statement to improve performance, not realizing that
Oracle parallel query only works with full-scans.
Page 656
Typo in code:
select
p1 "File #".
p2 "Block #",
p3 "Reason Code"
from
v$session_wait
where
event = 'buffer busy
waits';
Should be:
select
p1 "File #",
p2 "Block #",
p3 "Reason Code"
from
v$session_wait
where
event = 'buffer busy
waits';
