Oracle for Beginners to Expert

Thursday, September 25, 2014

SAMPLE STATSPACK REPORT

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
3494669306 DBPR11G 1 25-Sep-14 00:08 11.2.0.1.0 NO

Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
PRDG11G.localdom Linux x86 64-bit 1 0 0 7.8

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 1 25-Sep-14 10:40:08 31 1.0
End Snap: 2 25-Sep-14 10:40:59 31 1.0
Elapsed: 0.85 (mins) Av Act Sess: 0.0
DB time: 0.02 (mins) DB CPU: 0.02 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 1,376M Std Block Size: 8K
Shared Pool: 416M Log Buffer: 15,376K

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.0 1.0 0.00 0.02
DB CPU(s): 0.0 0.9 0.00 0.02
Redo size: 30,890.4 1,575,412.0
Logical reads: 180.6 9,212.0
Block changes: 96.2 4,907.0
Physical reads: 0.1 3.0
Physical writes: 0.0 0.0
User calls: 1.1 58.0
Parses: 16.6 844.0
Hard parses: 2.2 114.0
W/A MB processed: 0.5 27.0
Logons: 0.1 3.0
Executes: 27.2 1,387.0
Rollbacks: 0.0 0.0
Transactions: 0.0

Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.97 Optimal W/A Exec %: 100.00
Library Hit %: 90.79 Soft Parse %: 86.49
Execute to Parse %: 39.15 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 16.67 % Non-Parse CPU: 98.86

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 48.89 49.57
% SQL with executions>1: 51.91 53.90
% Memory for SQL w/exec>1: 58.95 62.19

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 1 60.2
control file parallel write 70 0 4 19.7
log file parallel write 124 0 2 15.6
os thread startup 1 0 39 2.7
log file sync 3 0 7 1.5
-------------------------------------------------------------

Host CPU (CPUs: 1 Cores: 0 Sockets: 0)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.15 0.06 1.29 0.90 97.59 0.29

Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 49.0
Host: Busy CPU time (s): 1.2
% of time Host is Busy: 2.4
Instance: Total CPU time (s): 1.2
% of Busy CPU used for Instance: 103.2
Instance: Total Database time (s): 10.8
%DB time waiting for CPU (Resource Mgr): 0.0

Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 7,986.1 7,986.1
SGA use (MB): 3,185.7 3,185.7
PGA use (MB): 178.3 176.4
% Host Mem used for SGA+PGA: 42.1 42.1
-------------------------------------------------------------

Time Model System Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Ordered by % of DB time desc, Statistic name

Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
DB CPU 0.9 92.8
sql execute elapsed time 0.9 92.2
parse time elapsed 0.2 16.4
hard parse elapsed time 0.2 15.4
PL/SQL compilation elapsed time 0.0 2.8
connection management call elapsed 0.0 2.3
PL/SQL execution elapsed time 0.0 .5
repeated bind elapsed time 0.0 .0
DB time 1.0
background elapsed time 9.8
background cpu time 0.3
-------------------------------------------------------------

Foreground Wait Events DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file sync 3 0 0 7 3.0 1.5
SQL*Net message from client 38 0 41 1086 38.0
-------------------------------------------------------------

Background Wait Events DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
control file parallel write 70 0 0 4 70.0 19.7
log file parallel write 124 0 0 2 124.0 15.6
os thread startup 1 0 0 39 1.0 2.7
control file sequential read 238 0 0 0 238.0 .3
rdbms ipc message 366 79 2,675 7308 366.0
DIAG idle wait 101 100 101 1000 101.0
dispatcher timer 1 100 60 60007 1.0
Streams AQ: qmn slave idle w 2 0 56 28002 2.0
Streams AQ: qmn coordinator 4 50 56 14001 4.0
pmon timer 18 94 51 2836 18.0
Space Manager: slave idle wa 10 100 50 5000 10.0
shared server idle wait 1 100 30 30012 1.0
SQL*Net message from client 9 0 0 0 9.0
-------------------------------------------------------------

Wait Events (fg and bg) DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
control file parallel write 70 0 0 4 70.0 19.7
log file parallel write 124 0 0 2 124.0 15.6
os thread startup 1 0 0 39 1.0 2.7
log file sync 3 0 0 7 3.0 1.5
control file sequential read 518 0 0 0 518.0 .3
rdbms ipc message 366 79 2,675 7308 366.0
DIAG idle wait 101 100 101 1000 101.0
dispatcher timer 1 100 60 60007 1.0
Streams AQ: qmn slave idle w 2 0 56 28002 2.0
Streams AQ: qmn coordinator 4 50 56 14001 4.0
pmon timer 18 94 51 2836 18.0
Space Manager: slave idle wa 10 100 50 5000 10.0
SQL*Net message from client 47 0 41 878 47.0
shared server idle wait 1 100 30 30012 1.0
-------------------------------------------------------------

Wait Event Histogram DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)

Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Disk file operations I/O 31 100.0
LGWR wait for redo copy 1 100.0
Parameter File I/O 16 100.0
SQL*Net break/reset to cli 2 100.0
asynch descriptor resize 120 100.0
control file parallel writ 70 7.1 42.9 48.6 1.4
control file sequential re 518 100.0
db file sequential read 3 100.0
log file parallel write 124 51.6 38.7 6.5 1.6 1.6
log file sync 3 66.7 33.3
os thread startup 1 100.0
DIAG idle wait 101 100.0
SQL*Net message from clien 47 70.2 10.6 6.4 2.1 2.1 8.5
SQL*Net message to client 45 100.0
Space Manager: slave idle 10 100.0
Streams AQ: qmn coordinato 4 50.0 50.0
Streams AQ: qmn slave idle 2 100.0
class slave wait 1 100.0
dispatcher timer 1 100.0
pmon timer 18 5.6 94.4
rdbms ipc message 366 3.3 .3 .3 .3 .3 47.5 48.1
shared server idle wait 1 100.0
-------------------------------------------------------------

SQL ordered by CPU DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Total DB CPU (s): 1
-> Captured SQL accounts for 96.5% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU

CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
0.82 1 0.82 89.6 0.88 13,193 2522684317
Module: sqlplus@PRDG11G.localdomain (TNS V1-V3)
BEGIN statspack.snap; END;

0.03 11 0.00 3.2 0.03 0 3547921727
SELECT value,type FROM v$parameter WHERE name = :1

0.02 9 0.00 1.8 0.02 0 2223195045
SELECT value FROM v$parameter WHERE name = 'spfile'

-------------------------------------------------------------

SQL ordered by Elapsed time for DB: DBPR11G Instance: DBPR11G Snaps: 1 -2
-> Total DB Time (s): 1
-> Captured SQL accounts for 95.4% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time

Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
0.88 1 0.88 88.6 0.82 7 2522684317
Module: sqlplus@PRDG11G.localdomain (TNS V1-V3)
BEGIN statspack.snap; END;

0.03 11 0.00 3.2 0.03 0 3547921727
SELECT value,type FROM v$parameter WHERE name = :1

0.02 9 0.00 2.0 0.02 0 2223195045
SELECT value FROM v$parameter WHERE name = 'spfile'

-------------------------------------------------------------

SQL ordered by Gets DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 9,212
-> Captured SQL accounts for 2.1% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets

CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
13,193 1 13,193.0 143.2 0.82 0.88 2522684317
Module: sqlplus@PRDG11G.localdomain (TNS V1-V3)
BEGIN statspack.snap; END;

-------------------------------------------------------------

SQL ordered by Reads DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> End Disk Reads Threshold: 1000 Total Disk Reads: 3
-> Captured SQL accounts for 0.0% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads

CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
7 1 7.0 233.3 0.82 0.88 2522684317
Module: sqlplus@PRDG11G.localdomain (TNS V1-V3)
BEGIN statspack.snap; END;

-------------------------------------------------------------

SQL ordered by Executions DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> End Executions Threshold: 100 Total Executions: 1,387
-> Captured SQL accounts for 10.0% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions

CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
19 0 0.0 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2

-------------------------------------------------------------

SQL ordered by Parse Calls DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> End Parse Calls Threshold: 1000 Total Parse Calls: 844
-> Captured SQL accounts for 12.8% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls

% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
11 11 1.30 3547921727
SELECT value,type FROM v$parameter WHERE name = :1

10 10 1.18 1254950678
select file# from file$ where ts#=:1

9 9 1.07 2223195045
SELECT value FROM v$parameter WHERE name = 'spfile'

-------------------------------------------------------------
truncate table STATS$TEMP_SQLSTATS
*
ERROR at line 1:
ORA-00942: table or view does not exist

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
Batched IO (bound) vector count 0 0.0 0.0
Batched IO (full) vector count 0 0.0 0.0
Batched IO block miss count 0 0.0 0.0
Batched IO buffer defrag count 0 0.0 0.0
Batched IO double miss count 0 0.0 0.0
Batched IO same unit count 0 0.0 0.0
Batched IO vector block count 0 0.0 0.0
Batched IO vector read count 0 0.0 0.0
Block Cleanout Optim referenced 0 0.0 0.0
CCursor + sql area evicted 8 0.2 8.0
CPU used by this session 88 1.7 88.0
CPU used when call started 91 1.8 91.0
CR blocks created 2 0.0 2.0
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DB time 100 2.0 100.0
DBWR checkpoint buffers written 0 0.0 0.0
DBWR checkpoints 0 0.0 0.0
DBWR transaction table writes 0 0.0 0.0
DBWR undo block writes 0 0.0 0.0
HSC Heap Segment Block Changes 321 6.3 321.0
Heap Segment Array Inserts 97 1.9 97.0
Heap Segment Array Updates 58 1.1 58.0
IMU Flushes 1 0.0 1.0
IMU Redo allocation size 356 7.0 356.0
IMU commits 0 0.0 0.0
IMU ktichg flush 0 0.0 0.0
IMU undo allocation size 1,224 24.0 1,224.0
LOB table id lookup cache misses 0 0.0 0.0
RowCR attempts 1 0.0 1.0
RowCR hits 1 0.0 1.0
SMON posted for undo segment shri 0 0.0 0.0
SQL*Net roundtrips to/from client 38 0.8 38.0
active txn count during cleanout 59 1.2 59.0
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 289 5.7 289.0
buffer is not pinned count 1,612 31.6 1,612.0
buffer is pinned count 292 5.7 292.0
bytes received via SQL*Net from c 15,486 303.7 15,486.0
bytes sent via SQL*Net to client 14,148 277.4 14,148.0
calls to get snapshot scn: kcmgss 1,643 32.2 1,643.0
calls to kcmgas 382 7.5 382.0
calls to kcmgcs 715 14.0 715.0
cell physical IO interconnect byt 12,401,152 243,159.8 ############
change write time 1 0.0 1.0
cleanout - number of ktugct calls 63 1.2 63.0
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 1 0.0 1.0
cluster key scan block gets 87 1.7 87.0
cluster key scans 85 1.7 85.0
commit batch/immediate performed 2 0.0 2.0
commit batch/immediate requested 2 0.0 2.0
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 6 0.1 6.0
commit cleanouts 513 10.1 513.0

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
commit cleanouts successfully com 507 9.9 507.0
commit immediate performed 2 0.0 2.0
commit immediate requested 2 0.0 2.0
commit txn count during cleanout 28 0.6 28.0
concurrency wait time 4 0.1 4.0
consistent changes 2 0.0 2.0
consistent gets 3,497 68.6 3,497.0
consistent gets - examination 1,602 31.4 1,602.0
consistent gets from cache 3,497 68.6 3,497.0
consistent gets from cache (fastp 1,739 34.1 1,739.0
cursor authentications 18 0.4 18.0
data blocks consistent reads - un 0 0.0 0.0
db block changes 4,907 96.2 4,907.0
db block gets 5,715 112.1 5,715.0
db block gets direct 0 0.0 0.0
db block gets from cache 5,715 112.1 5,715.0
db block gets from cache (fastpat 1,900 37.3 1,900.0
deferred (CURRENT) block cleanout 260 5.1 260.0
enqueue conversions 15 0.3 15.0
enqueue releases 1,762 34.6 1,762.0
enqueue requests 1,762 34.6 1,762.0
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 1,387 27.2 1,387.0
file io service time 0 0.0 0.0
file io wait time 20 0.4 20.0
free buffer requested 1,085 21.3 1,085.0
heap block compress 10 0.2 10.0
immediate (CR) block cleanout app 1 0.0 1.0
immediate (CURRENT) block cleanou 151 3.0 151.0
index crx upgrade (positioned) 0 0.0 0.0
index fast full scans (full) 2 0.0 2.0
index fetch by key 513 10.1 513.0
index scans kdiixs1 638 12.5 638.0
leaf node 90-10 splits 5 0.1 5.0
leaf node splits 25 0.5 25.0
lob reads 0 0.0 0.0
lob writes 0 0.0 0.0
lob writes unaligned 0 0.0 0.0
logons cumulative 3 0.1 3.0
max cf enq hold time 0 0.0 0.0
messages received 70 1.4 70.0
messages sent 70 1.4 70.0
min active SCN optimization appli 0 0.0 0.0
no work - consistent read gets 1,283 25.2 1,283.0
non-idle wait count 928 18.2 928.0
non-idle wait time 59 1.2 59.0
opened cursors cumulative 1,355 26.6 1,355.0
parse count (describe) 0 0.0 0.0
parse count (failures) 0 0.0 0.0
parse count (hard) 114 2.2 114.0
parse count (total) 844 16.6 844.0
parse time cpu 1 0.0 1.0
parse time elapsed 6 0.1 6.0
physical read IO requests 3 0.1 3.0
physical read bytes 24,576 481.9 24,576.0

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical read total IO requests 537 10.5 537.0
physical read total bytes 8,531,968 167,293.5 8,531,968.0
physical read total multi block r 0 0.0 0.0
physical reads 3 0.1 3.0
physical reads cache 3 0.1 3.0
physical reads cache prefetch 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 0 0.0 0.0
physical write bytes 0 0.0 0.0
physical write total IO requests 202 4.0 202.0
physical write total bytes 3,869,184 75,866.4 3,869,184.0
physical write total multi block 5 0.1 5.0
physical writes 0 0.0 0.0
physical writes direct 0 0.0 0.0
physical writes direct (lob) 0 0.0 0.0
physical writes from cache 0 0.0 0.0
physical writes non checkpoint 0 0.0 0.0
pinned cursors current 1 0.0 1.0
recovery blocks read 0 0.0 0.0
recursive calls 18,850 369.6 18,850.0
recursive cpu usage 78 1.5 78.0
redo blocks checksummed by FG (ex 1,599 31.4 1,599.0
redo blocks read for recovery 0 0.0 0.0
redo blocks written 3,077 60.3 3,077.0
redo buffer allocation retries 0 0.0 0.0
redo entries 3,341 65.5 3,341.0
redo k-bytes read for recovery 0 0.0 0.0
redo k-bytes read total 0 0.0 0.0
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo size 1,575,412 30,890.4 1,575,412.0
redo size for direct writes 0 0.0 0.0
redo synch time 3 0.1 3.0
redo synch writes 3 0.1 3.0
redo wastage 23,104 453.0 23,104.0
redo write time 22 0.4 22.0
redo writes 62 1.2 62.0
rollback changes - undo records a 4 0.1 4.0
rollbacks only - consistent read 0 0.0 0.0
rows fetched via callback 89 1.8 89.0
scheduler wait time 0 0.0 0.0
session cursor cache hits 1,228 24.1 1,228.0
session logical reads 9,212 180.6 9,212.0
session uga memory max 10,034,256 196,750.1 ############
shared hash latch upgrades - no w 71 1.4 71.0
sorts (memory) 132 2.6 132.0
sorts (rows) 6,865 134.6 6,865.0
sql area evicted 8 0.2 8.0
sql area purged 0 0.0 0.0
switch current to new buffer 85 1.7 85.0
table fetch by rowid 231 4.5 231.0
table fetch continued row 0 0.0 0.0
table scan blocks gotten 393 7.7 393.0
table scan rows gotten 1,209 23.7 1,209.0
table scans (short tables) 89 1.8 89.0
total cf enq hold time 60 1.2 60.0

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
total number of cf enq holders 9 0.2 9.0
total number of times SMON posted 0 0.0 0.0
transaction rollbacks 2 0.0 2.0
undo change vector size 491,948 9,646.0 491,948.0
user I/O wait time 0 0.0 0.0
user calls 58 1.1 58.0
user commits 1 0.0 1.0
user rollbacks 0 0.0 0.0
workarea executions - optimal 91 1.8 91.0
write clones created in backgroun 0 0.0 0.0
-------------------------------------------------------------

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Statistics with absolute values (should not be diffed)

Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 31 31
opened cursors current 30 30
session cursor cache count 2,952 2,987
-------------------------------------------------------------

Instance Activity Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------

OS Statistics DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name

Statistic Total
------------------------- ----------------------
BUSY_TIME 118
IDLE_TIME 4,780
IOWAIT_TIME 14
NICE_TIME 0
SYS_TIME 44
USER_TIME 63
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 8,374,050,816
NUM_CPUS 1
GLOBAL_RECEIVE_SIZE_MAX 4,194,304
GLOBAL_SEND_SIZE_MAX 1,048,576
TCP_RECEIVE_SIZE_DEFAULT 87,380
TCP_RECEIVE_SIZE_MAX 6,291,456
TCP_RECEIVE_SIZE_MIN 4,096
TCP_SEND_SIZE_DEFAULT 16,384
TCP_SEND_SIZE_MAX 4,194,304
TCP_SEND_SIZE_MIN 4,096
-------------------------------------------------------------

OS Statistics - detail DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Snap Snapshot
Id Day Time Load %Busy %User %System %WIO %WCPU
------ --------------- ------ ------ ------ ------- ------ ------
1 Thu 25 10:40:08 .1
2 Thu 25 10:40:59 .1 2.4 1.3 .9 0.3
-------------------------------------------------------------

IO Stat by Function - summary DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->Data Volume values suffixed with M,G,T,P are in multiples of 1024,
other values suffixed with K,M,G,T,P are in multiples of 1000
->ordered by Data Volume (Read+Write) desc

---------- Read --------- --------- Write -------- --- Wait ----
Data Requests Data Data Requests Data Avg
Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms)
--------------- ------ -------- -------- ------ -------- -------- ------ ------
Others 9M 11.3 .2M 2M 2.7 .0M 718 0.0
Buffer Cache Re .1 4 0.0
-------------------------------------------------------------

IO Stat by Function - detail DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->ordered by Data Volume (Read+Write) desc

----------- Read ---------- ----------- Write ---------
Small Large Small Large Small Large Small Large
Read Read Data Data Write Write Data Data
Function Reqs Reqs Read Read Reqs Reqs Writn Writn
------------------ ------ ------ ------ ------ ------ ------ ------ ------
Others 578 9M 140 2M
Buffer Cache Reads 4
-------------------------------------------------------------

Tablespace IO Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSTEM
4 0 0.0 1.0 0 0 0 0.0
-------------------------------------------------------------

File IO Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
SYSTEM /u01/app/oracle/oradata/DBPR11G/system01.dbf
4 0 0.0 1 1.0 0 0 0

-------------------------------------------------------------

File Read Histogram Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->Number of single block reads in each time range
->Tempfiles are not included
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
SYSTEM /u01/app/oracle/oradata/DBPR11G/system01.dbf
4 0 0 0 0 0

-------------------------------------------------------------

Instance Recovery Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> B: Begin snapshot, E: End snapshot

Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 13 1311 2613 3815 165888 3815
E 0 14 1814 3905 5562 165888 5562
-------------------------------------------------------------

Memory Target Advice DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Advice Reset: if this is null, the data shown has been diffed between
the Begin and End snapshots. If this is 'Y', the advisor has been
reset during this interval due to memory resize operations, and
the data shown is since the reset operation.

Memory Size Est. Advice
Memory Size (M) Factor DB time (s) Reset
--------------- ----------- ------------ ------
800 .3 1
1,600 .5 1
2,400 .8 1
3,200 1.0 1
4,000 1.3 1
4,800 1.5 1
5,600 1.8 1
6,400 2.0 1
-------------------------------------------------------------

Memory Dynamic Components DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Op - memory resize Operation
-> Cache: D: Default, K: Keep, R: Recycle
-> Mode: DEF: DEFerred mode, IMM: IMMediate mode

Begin Snap End Snap Op Last Op
Cache Size (M) Size (M) Count Type/Mode Last Op Time
---------------------- ---------- -------- ------- ---------- ---------------
D:buffer cache 1,376 0 INITIA/
PGA Target 1,344 0 STATIC
SGA Target 1,856 0 STATIC
java pool 16 0 STATIC
large pool 16 0 STATIC
shared pool 416 0 STATIC
-------------------------------------------------------------

Buffer Pool Advisory DB/Inst: DBPR11G/DBPR11G End Snap: 2
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate

Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 128 .1 16 1.0 12 17 27.0
D 256 .2 32 1.0 12 17 27.0
D 384 .3 47 1.0 12 17 27.0
D 512 .4 63 1.0 12 17 27.0
D 640 .5 79 1.0 12 17 27.0
D 768 .6 95 1.0 12 17 27.0
D 896 .7 110 1.0 12 17 27.0
D 1,024 .7 126 1.0 12 17 27.0
D 1,152 .8 142 1.0 12 17 27.0
D 1,280 .9 158 1.0 12 17 27.0
D 1,376 1.0 169 1.0 12 17 27.0
D 1,408 1.0 173 1.0 12 17 27.0
D 1,536 1.1 189 1.0 12 17 27.0
D 1,664 1.2 205 1.0 12 17 27.0
D 1,792 1.3 221 1.0 12 17 27.0
D 1,920 1.4 236 1.0 12 17 27.0
D 2,048 1.5 252 1.0 12 17 27.0
D 2,176 1.6 268 1.0 12 17 27.0
D 2,304 1.7 284 1.0 12 17 27.0
D 2,432 1.8 299 1.0 12 17 27.0
D 2,560 1.9 315 1.0 12 17 27.0
-------------------------------------------------------------

Buffer Pool Statistics DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000

Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 169K 100 10,889 4 0 0 0 1
-------------------------------------------------------------

PGA Aggr Target Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas
-> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of WorkArea memory under Manual control

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 27 0

PGA Aggr Target Histogram DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 42 42 0 0
64K 128K 2 2 0 0
128K 256K 5 5 0 0
512K 1024K 23 23 0 0
4M 8M 2 2 0 0
-------------------------------------------------------------

PGA Memory Advisory DB/Inst: DBPR11G/DBPR11G End Snap: 2
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0

Estd Extra Estd
PGA Aggr W/A MB Estd Time PGA Estd PGA
Target Size W/A MB Read/Written to Process Cache Overalloc
Est (MB) Factr Processed to Disk Bytes (s) Hit % Count
---------- ------ -------------- -------------- ---------- ------ ----------
168 0.1 186 0 0.0 100.0 0
336 0.3 186 0 0.0 100.0 0
672 0.5 186 0 0.0 100.0 0
1,008 0.8 186 0 0.0 100.0 0
1,344 1.0 186 0 0.0 100.0 0
1,613 1.2 186 0 0.0 100.0 0
1,882 1.4 186 0 0.0 100.0 0
2,150 1.6 186 0 0.0 100.0 0
2,419 1.8 186 0 0.0 100.0 0
2,688 2.0 186 0 0.0 100.0 0
4,032 3.0 186 0 0.0 100.0 0
5,376 4.0 186 0 0.0 100.0 0
8,064 6.0 186 0 0.0 100.0 0
10,752 8.0 186 0 0.0 100.0 0
-------------------------------------------------------------

Process Memory Summary Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc

Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 178.3 131.5 34.6 5.4 8.4 29 29 33
Other 143.0 4.3 7.1 28 28 33
Freeable 34.6 .0 3.1 5.0 17 11
PL/SQL .6 .5 .0 .1 0 0 31
SQL .1 .1 .0 .0 0 7 7
E -------- 176.4 130.9 35.1 5.3 8.3 29 29 33
Other 140.5 4.3 7.1 28 28 33
Freeable 35.1 .0 3.2 4.9 17 11
PL/SQL .6 .5 .0 .1 0 0 31
SQL .2 .1 .0 .0 0 6 7
-------------------------------------------------------------

Top Process Memory (by component) DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc

Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 25 ARC1 -------- 29.5 27.6 1.2 29.5 29.5
Other 28.3 28.3 28.3
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
27 ARC3 -------- 29.5 27.6 1.2 29.5 29.5
Other 28.3 28.3 28.3
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
24 ARC0 -------- 28.4 10.8 17.1 28.4 28.4
Freeable 17.1 .0 17.1
Other 11.4 11.4 11.4
PL/SQL .0 .0 .0 .0
26 ARC2 -------- 11.7 10.9 .0 11.7 11.7
Other 11.7 11.7 11.7
PL/SQL .0 .0 .0 .0
28 NSA2 -------- 11.6 10.8 .1 11.6 11.6
Other 11.4 11.4 11.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
11 LGWR -------- 11.5 10.9 .0 11.5 11.5
Other 11.5 11.5 11.5
PL/SQL .0 .0 .0 .0
20 TNS V1-V3 --- 10.2 2.0 7.1 10.2 10.2
Freeable 7.1 .0 7.1
Other 2.8 2.8 2.8
PL/SQL .2 .2 .2 .2
SQL .1 .1 .1 6.9
10 DBW0 -------- 6.6 6.3 .0 6.6 6.6
Other 6.6 6.6 6.6
PL/SQL .0 .0 .0 .0
21 NSV1 -------- 4.9 4.8 .0 4.9 4.9
Other 4.9 4.9 4.9
PL/SQL .0 .0 .0 .0
13 SMON -------- 4.7 1.1 2.5 4.7 4.7
Freeable 2.5 .0 2.5
Other 2.2 2.2 2.2
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 2.1
15 MMON -------- 4.6 1.9 1.1 4.6 4.6
Other 3.4 3.4 3.4
Freeable 1.1 .0 1.1
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 1.1
34 CJQ0 -------- 4.3 1.2 2.9 4.3 4.3
Freeable 2.9 .0 2.9
Other 1.3 1.3 1.3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 1.4
23 RSM0 -------- 2.2 .8 1.1 2.2 2.2
Freeable 1.1 .0 1.1
Other 1.1 1.1 1.1
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .3

Top Process Memory (by component) DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc

Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 19 DMON -------- 1.4 .8 .0 1.4 1.4
Other 1.4 1.4 1.4
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .2
14 RECO -------- 1.4 .8 .3 1.4 1.4
Other 1.2 1.2 1.2
Freeable .3 .0 .3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .5
41 Q000 -------- 1.4 1.1 .0 1.4 1.4
Other 1.2 1.2 1.2
PL/SQL .2 .2 .2 .2
SQL .0 .0 .0 .2
8 DIA0 -------- 1.4 1.1 .1 1.4 1.4
Other 1.3 1.3 1.3
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
17 D000 -------- 1.3 1.0 .0 1.3 1.3
Other 1.3 1.3 1.3
12 CKPT -------- 1.0 .7 .0 1.0 1.0
Other 1.0 1.0 1.0
PL/SQL .0 .0 .0 .0
E 25 ARC1 -------- 29.5 27.6 1.2 29.5 29.5
Other 28.3 28.3 28.3
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
27 ARC3 -------- 29.5 27.6 1.2 29.5 29.5
Other 28.3 28.3 28.3
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
24 ARC0 -------- 28.4 10.8 17.1 28.4 28.4
Freeable 17.1 .0 17.1
Other 11.4 11.4 11.4
PL/SQL .0 .0 .0 .0
26 ARC2 -------- 11.7 10.9 .0 11.7 11.7
Other 11.7 11.7 11.7
PL/SQL .0 .0 .0 .0
28 NSA2 -------- 11.6 10.8 .1 11.6 11.6
Other 11.4 11.4 11.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
11 LGWR -------- 11.5 10.9 .0 11.5 11.5
Other 11.5 11.5 11.5
PL/SQL .0 .0 .0 .0
20 TNS V1-V3 --- 8.3 1.4 6.5 8.3 8.3
Freeable 6.5 .0 6.5
Other 1.6 1.6 1.6
PL/SQL .2 .2 .2 .2
SQL .1 .1 .1 6.2
10 DBW0 -------- 6.6 6.3 .0 6.6 6.6
Other 6.6 6.6 6.6
PL/SQL .0 .0 .0 .0
21 NSV1 -------- 4.9 4.8 .0 4.9 4.9

Top Process Memory (by component) DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc

Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB)

(MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 21 Other 4.9 4.9 4.9
PL/SQL .0 .0 .0 .0
13 SMON -------- 4.7 1.1 2.5 4.7 4.7
Freeable 2.5 .0 2.5
Other 2.2 2.2 2.2
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 2.1
15 MMON -------- 4.6 1.9 2.3 4.6 4.6
Freeable 2.3 .0 2.3
Other 2.2 2.2 2.2
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 1.1
34 CJQ0 -------- 4.3 1.2 2.9 4.3 4.3
Freeable 2.9 .0 2.9
Other 1.3 1.3 1.3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 1.4
23 RSM0 -------- 2.2 .8 1.1 2.2 2.2
Freeable 1.1 .0 1.1
Other 1.1 1.1 1.1
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .3
19 DMON -------- 1.4 .8 .0 1.4 1.4
Other 1.4 1.4 1.4
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .2
14 RECO -------- 1.4 .8 .3 1.4 1.4
Other 1.2 1.2 1.2
Freeable .3 .0 .3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .5
41 Q000 -------- 1.4 1.1 .0 1.4 1.4
Other 1.2 1.2 1.2
PL/SQL .2 .2 .2 .2
SQL .0 .0 .0 .2
8 DIA0 -------- 1.4 1.1 .1 1.4 1.4
Other 1.3 1.3 1.3
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
17 D000 -------- 1.3 1.0 .0 1.3 1.3
Other 1.3 1.3 1.3
12 CKPT -------- 1.0 .7 .0 1.0 1.0
Other 1.0 1.0 1.0
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AQ deq hash table latch 1 0.0 0 0
ASM db client latch 100 0.0 0 0
ASM map operation hash t 1 0.0 0 0
Change Notification Hash 17 0.0 0 0
Consistent RBA 62 0.0 0 0
DML lock allocation 590 0.0 0 0
DMON Network Error List 1 0.0 0 0
DMON Work Queues Latch 4 0.0 0 0
Event Group Locks 4 0.0 0 0
FAL Queue 2 0.0 0 0
FIB s.o chain latch 16 0.0 0 0
FOB s.o list latch 46 0.0 0 0
File State Object Pool P 1 0.0 0 0
IPC stats buffer allocat 1 0.0 0 0
In memory undo latch 106 0.0 0 17 0.0
JS Sh mem access 1 0.0 0 0
JS queue access latch 1 0.0 0 0
JS queue state obj latch 396 0.0 0 0
JS slv state obj latch 1 0.0 0 0
KFC FX Hash Latch 1 0.0 0 0
KFC Hash Latch 1 0.0 0 0
KFCL LE Freelist 1 0.0 0 0
KGNFS-NFS:SHM structure 1 0.0 0 0
KGNFS-NFS:SVR LIST 1 0.0 0 0
KJC message pool free li 1 0.0 0 0
KJCT flow control latch 1 0.0 0 0
KMG MMAN ready and start 17 0.0 0 0
KTF sga latch 1 0.0 0 17 0.0
KWQP Prop Status 1 0.0 0 0
LGWR NS Write 1 0.0 0 0
Locator state objects po 1 0.0 0 0
Lsod array latch 1 0.0 0 0
MQL Tracking Latch 0 0 1 0.0
Managed Standby Recovery 1 0.0 0 0
Memory Management Latch 1 0.0 0 17 0.0
Memory Queue 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Subscriber 1 0.0 0 0
Mutex 1 0.0 0 0
Mutex Stats 1 0.0 0 0
NSV command ID generatio 1 0.0 0 0
NSV creation/termination 1 0.0 0 0
OS process 16 0.0 0 0
OS process allocation 27 0.0 0 0
OS process: request allo 6 0.0 0 0
PL/SQL warning settings 18 0.0 0 0
PX hash array latch 1 0.0 0 0

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
QMT 1 0.0 0 0
RSM process latch 6 0.0 0 0
Request id generation la 1 0.0 0 0
SGA blob parent 1 0.0 0 0
SGA bucket locks 1 0.0 0 0
SGA heap locks 1 0.0 0 0
SGA pool locks 1 0.0 0 0
SQL memory manager latch 3 0.0 0 50 0.0
SQL memory manager worka 3,514 0.0 0 0
Shared B-Tree 6 0.0 0 0
Streams Generic 1 0.0 0 0
Testing 1 0.0 0 0
Token Manager 1 0.0 0 0
WCR: sync 1 0.0 0 0
Write State Object Pool 1 0.0 0 0
XDB NFS Security Latch 1 0.0 0 0
XDB unused session pool 1 0.0 0 0
XDB used session pool 1 0.0 0 0
active checkpoint queue 17 0.0 0 0
active service list 122 0.0 0 562 0.0
archive control 448 0.0 0 0
archive process latch 3 0.0 0 0
buffer pool 1 0.0 0 0
business card 1 0.0 0 0
cache buffer handles 818 0.0 0 0
cache buffers chains 24,362 0.0 0 926 0.0
cache buffers lru chain 1,093 0.0 0 43 0.0
call allocation 112 0.0 0 0
cas latch 1 0.0 0 0
change notification clie 1 0.0 0 0
channel handle pool latc 7 0.0 0 0
channel operations paren 591 0.0 0 0
checkpoint queue latch 945 0.0 0 938 0.0
client/application info 20 0.0 0 0
compile environment latc 3 0.0 0 0
corrupted undo seg latch 14 0.0 0 0
cp cmon/server latch 1 0.0 0 0
cp pool latch 1 0.0 0 0
cp server hash latch 1 0.0 0 0
cvmap freelist lock 1 0.0 0 0
dml lock allocation 96 0.0 0 0
done queue latch 1 0.0 0 0
dummy allocation 7 0.0 0 0
enqueue hash chains 3,349 0.0 0 0
enqueues 2,186 0.0 0 0
fifth spare latch 1 0.0 0 0
file cache latch 78 0.0 0 0
flashback copy 1 0.0 0 0
gc element 1 0.0 0 0
gcs commit scn state 1 0.0 0 0

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
gcs partitioned table ha 1 0.0 0 0
gcs pcm hashed value buc 1 0.0 0 0
gcs resource freelist 1 0.0 0 0
gcs resource hash 1 0.0 0 0
gcs resource scan list 1 0.0 0 0
gcs shadows freelist 1 0.0 0 0
ges domain table 1 0.0 0 0
ges enqueue table freeli 1 0.0 0 0
ges group table 1 0.0 0 0
ges process hash list 1 0.0 0 0
ges process parent latch 1 0.0 0 0
ges resource hash list 1 0.0 0 0
ges resource scan list 1 0.0 0 0
ges resource table freel 1 0.0 0 0
ges value block free lis 1 0.0 0 0
global KZLD latch for me 1 0.0 0 0
global tx hash mapping 1 0.0 0 0
granule operation 1 0.0 0 0
hash table column usage 0 0 7,210 0.0
hash table modification 25 0.0 0 0
heartbeat check 1 0.0 0 0
intra txn parallel recov 1 0.0 0 0
io pool granule metadata 1 0.0 0 0
job workq parent latch 1 0.0 0 0
job_queue_processes para 12 0.0 0 0
k2q lock allocation 1 0.0 0 0
kdlx hb parent latch 1 0.0 0 0
kgb parent 1 0.0 0 0
kks stats 166 0.0 0 0
ksfv messages 1 0.0 0 0
kss move lock 3 0.0 0 0
ksuosstats global area 8 0.0 0 0
ksv allocation latch 4 0.0 0 0
ksv class latch 5 0.0 0 0
ksv msg queue latch 1 0.0 0 0
ksz_so allocation latch 6 0.0 0 0
kwqbsn:qsga 5 0.0 0 0
lgwr LWN SCN 62 0.0 0 0
list of block allocation 17 0.0 0 0
loader state object free 48 0.0 0 0
lob segment dispenser la 1 0.0 0 0
lob segment hash table l 1 0.0 0 0
lob segment query latch 1 0.0 0 0
lock DBA buffer during m 1 0.0 0 0
logical standby cache 1 0.0 0 0
logminer context allocat 1 0.0 0 0
logminer work area 1 0.0 0 0
longop free list parent 1 0.0 0 0
managed standby latch 1 0.0 0 0
mapped buffers lru chain 1 0.0 0 0

Latch Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
message pool operations 16 0.0 0 0
messages 810 0.0 0 0
mostly latch-free SCN 62 0.0 0 0
msg queue latch 1 0.0 0 0
name-service namespace b 1 0.0 0 0
object queue header heap 66 0.0 0 0
object queue header oper 1,971 0.0 0 0
object stats modificatio 175 0.0 0 0
parallel query alloc buf 9 0.0 0 0
parallel query stats 1 0.0 0 0
parameter list 4 0.0 0 0
parameter table manageme 6 0.0 0 0
peshm 1 0.0 0 0
pesom_free_list 1 0.0 0 0
pesom_hash_node 1 0.0 0 0
post/wait queue 6 0.0 0 3 0.0
process allocation 7 0.0 0 3 0.0
process group creation 6 0.0 0 0
process queue 1 0.0 0 0
process queue reference 1 0.0 0 0
qmn task queue latch 8 0.0 0 0
query server freelists 1 0.0 0 0
queuing load statistics 1 0.0 0 0
recovery domain hash lis 1 0.0 0 0
redo allocation 159 0.0 0 3,136 0.0
redo copy 1 0.0 0 3,136 0.1
redo writing 277 0.0 0 0
resmgr group change latc 4 0.0 0 0
resmgr:active threads 7 0.0 0 0
resmgr:actses change gro 4 0.0 0 0
resmgr:actses change sta 1 0.0 0 0
resmgr:free threads list 6 0.0 0 0
resmgr:plan CPU method 1 0.0 0 0
resmgr:resource group CP 1 0.0 0 0
resmgr:schema config 1 0.0 0 0
resmgr:session queuing 1 0.0 0 0
rm cas latch 1 0.0 0 0
row cache objects 28,610 0.0 0 0
rules engine rule set st 101 0.0 0 0
second spare latch 1 0.0 0 0
sequence cache 6 0.0 0 0
session allocation 107 0.0 0 0
session idle bit 141 0.0 0 0
session queue latch 1 0.0 0 0
session state list latch 8 0.0 0 0
session switching 16 0.0 0 0
session timer 18 0.0 0 0
shared pool 8,480 0.0 0 0
shared pool sim alloc 4 0.0 0 0
shared pool simulator 563 0.0 0 0

Dictionary Cache Stats DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot

Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 1 0.0 0 0 1
dc_global_oids 26 0.0 0 0 43
dc_histogram_defs 2,551 18.1 0 0 4,884
dc_object_grants 20 0.0 0 0 24
dc_objects 1,137 0.0 0 0 2,392
dc_profiles 1 0.0 0 0 1
dc_rollback_segments 24 0.0 0 0 22
dc_segments 815 8.1 0 169 780
dc_tablespace_quotas 404 0.0 0 0 2
dc_tablespaces 2,553 0.0 0 0 8
dc_users 3,217 0.0 0 0 270
global database name 37 0.0 0 0 1
outstanding_alerts 4 0.0 0 0 5
-------------------------------------------------------------

Library Cache Activity DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
->"Pct Misses" should be very low

Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
SQL AREA 184 52.2 2,497 11.9 46 38
TABLE/PROCEDURE 1,201 0.0 2,082 4.6 43 0
BODY 9 0.0 16 0.0 0 0
TRIGGER 1 0.0 1 0.0 0 0
INDEX 36 0.0 36 100.0 0 0
EDITION 3 0.0 4 0.0 0 0
DBLINK 3 0.0 0 0 0
SCHEMA 2 0.0 0 0 0
-------------------------------------------------------------

Rule Sets DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc

No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- - ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
-------------------------------------------------------------

Shared Pool Advisory DB/Inst: DBPR11G/DBPR11G End Snap: 2
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid

Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
128 .3 0 28 845 1.0 23 1.9 601
176 .4 49 3,548 845 1.0 23 1.9 76,407
224 .5 90 6,034 856 1.0 12 1.0 77,610
272 .7 90 6,034 856 1.0 12 1.0 77,610
320 .8 90 6,034 856 1.0 12 1.0 77,610
368 .9 90 6,034 856 1.0 12 1.0 77,610
416 1.0 90 6,034 856 1.0 12 1.0 77,610
464 1.1 90 6,034 856 1.0 12 1.0 77,610
512 1.2 90 6,034 856 1.0 12 1.0 77,610
560 1.3 90 6,034 856 1.0 12 1.0 77,610
608 1.5 90 6,034 856 1.0 12 1.0 77,610
656 1.6 90 6,034 856 1.0 12 1.0 77,610
704 1.7 90 6,034 856 1.0 12 1.0 77,610
752 1.8 90 6,034 856 1.0 12 1.0 77,610
800 1.9 90 6,034 856 1.0 12 1.0 77,610
848 2.0 90 6,034 856 1.0 12 1.0 77,610
-------------------------------------------------------------

SGA Target Advisory DB/Inst: DBPR11G/DBPR11G End Snap: 2

SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
464 .3 63 1.0 11,955
928 .5 63 1.0 11,955
1,392 .8 63 1.0 11,955
1,856 1.0 63 1.0 11,955
2,320 1.3 63 1.0 11,955
2,784 1.5 63 1.0 11,955
3,248 1.8 63 1.0 11,955
3,712 2.0 63 1.0 11,955
-------------------------------------------------------------

SGA Memory Summary DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 1,442,840,576
Fixed Size 2,217,952
Redo Buffers 16,343,040
Variable Size 1,879,050,272
-------------------- --------------------
sum 3,340,451,840
-------------------------------------------------------------

SGA breakdown difference DB/Inst: DBPR11G/DBPR11G Snaps: 1-2
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot

Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- --------
java p free memory 16.0 16.0 0.00
large PX msg pool .5 .5 0.00
large free memory 15.5 15.5 0.00
shared CCUR 9.6 9.8 2.74
shared FileOpenBlock 9.8 9.8 0.00
shared KGLH0 7.1 7.1 0.00
shared KGLHD 5.5 5.6 1.06
shared KGLS 14.1 14.3 1.12
shared KGLSG 5.0 5.0 0.00
shared PCUR 7.6 7.8 2.95
shared PLDIA 7.0 7.0 0.00
shared PLMCD 5.9 5.9 0.00
shared SQLA 43.3 45.0 3.94
shared db_block_hash_buckets 11.1 11.1 0.00
shared free memory 212.6 209.8 -1.33
shared row cache 7.2 7.2 0.00
buffer_cache 1,376.0 1,376.0 0.00
fixed_sga 2.1 2.1 0.00
log_buffer 15.6 15.6 0.00
-------------------------------------------------------------

SQL Memory Statistics DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 27.19 27.17 -.08
Cursor to Parent ratio: 1.10 1.10 -.11
Total Cursors: 1,733 1,763 1.70
Total Parents: 1,574 1,603 1.81
-------------------------------------------------------------

init.ora Parameters DB/Inst: DBPR11G/DBPR11G Snaps: 1-2

End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
archive_lag_target 0
audit_file_dest /u01/app/oracle/admin/DBPR11G/adu
mp
audit_trail DB
compatible 11.2.0.0.0
control_files /u01/app/oracle/oradata/DBPR11G/c
ontrol01.ctl, /u01/app/oracle/fla
sh_recovery_area/DBPR11G/control0
2.ctl
cursor_sharing EXACT
db_block_size 8192
db_domain
db_name DBPR11G
db_recovery_file_dest /u01/app/oracle/flash_recovery_ar
ea
db_recovery_file_dest_size 4070572032
dg_broker_start TRUE
diagnostic_dest /u01/app/oracle
dispatchers (PROTOCOL=TCP) (SERVICE=DBPR11GXD
B)
fal_client DBPR11G
fal_server DBSTP11G
log_archive_config dg_config=(DBPR11G,dbstp11g)
log_archive_dest_1 LOCATION=/u01/app/oracle/oradata/
arch/DBPR11G VALID_FOR=(ALL_LOGFI
LES,ALL_ROLES) DB_UNIQUE_NAME=DBP
R11G
log_archive_dest_2 service="DBSTP11G", LGWR ASYNC NO
AFFIRM delay=0 optional compressi
on=disable max_failure=0 max_conn
ections=1 reopen=300 db_unique_na
me="dbstp11g" net_timeout=30, val
id_for=(all_logfiles,primary_role
)
log_archive_dest_state_2 ENABLE
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
log_archive_min_succeed_dest 1
log_archive_trace 0
memory_target 3355443200
open_cursors 300
processes 150
remote_login_passwordfile EXCLUSIVE
standby_file_management AUTO
undo_tablespace UNDOTBS1
-------------------------------------------------------------

End of Report ( sp_1_2.lst )

25^th SEP 2014

STATSPACK ADMINISTRATION

Detail about statspack can be read from

$ORACLE_HOME/rdbms/admin/spdoc.txt

Taking performance snapshot

SQLPLUS PERFSTAT/PERFSTAT
EXEC STATSPACK.SNAP;
EXEC PERFSTAT.STATSPACK.SNAP(I_SNAP_LEVEL=>10);

Get list of snapshots

sql > select snap_id,snap_time from stats$snapshot;

3. Generate statspack report

sql> @$ORACLE_HOME/rdbms/admin/spreport.sql

– Provide info as require

4. Additional statspack scripts

a) sppurge.sql – Purge(delete) a range of snapshot id's between the given snap id's
b) spauto.sql – Schedule a dbms_job to automate the collection of statspack statistics

c) spdrop.sql – Deinstall statspack from database (Run as SYS)
d) sptrunc.sql – Truncate all data in statspack tables

Statspack Level

SQL> SELECT * FROM stats$level_description ORDER BY snap_level;

Level 0 captures general statistics, rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information.

Level 5 captures high resource usage SQL Statements, along with all data captured by lower levels.

Level 6 captures SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels.

Level 7 captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels.

Level 10 captures Child Latch statistics, along with all data captured by lower levels.

Wednesday, September 24, 2014

25^th SEP 2014

STATSPACK (Definition , Installation & Generation Report)

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters

source :-- http://docs.oracle.com/cd/B10501_01/server.920/a96533/statspac.htm

Installation of statspack

Step 1) login to the database as SYS user

sqlplus / as sysdba

Step 2) Check parameter job_queue_processes

sql> show parameter job_queue_processes

Its value should be > 0

if it is not set > 0 execute the below command

sql > alter system set job_queue_processes=5 scope=both;

Step 3) check if table perfstat exists on the database

SQL> select * from v$tablespace where NAME like '%PERF%';

If it donot exist create perfstat table

sql > create tablespace perfstat datafile '/PATH/perfstat.dbf' size 3G autoextend off;

/u01/app/oracle/oradata/DBPR11G

Step 4) Run the below sql query to deinstall any previous installation

SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql

Step 5) Run the below script to install statspack on the database . This script will create PERFSTAT user on the database

SQL > @$ORACLE_HOME/rdbms/admin/spcreate.sql

-- Enter perfstat as the default tablespace

-- Enter the name of the default tablespace

Step 6) Run the below pl/sql code to schedule the snapshot collection every 30 minutes .Run as sys user

variable jobno number;

variable instno number;

BEGIN

SELECT instance_number INTO :instno from v$instance;

DBMS_JOB.SUBMIT(:jobno,'statspack.snap;',trunc(sysdate,'HH24')

+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60),'trunc(sysdate,"HH24")

+((floor(to_number(to_char(sysdate,"MI"))/30)+1)*30)/(24*60)',TRUE,:instno);

COMMIT;

END;

Step 7) Generate statspace report

SQL > @$ORACLE_HOME/rdbms/admin/spreport

Follow the instruction

If Below error comes

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 3494669306 for database Id
Using 1 for instance number
declare
*
ERROR at line 1:
ORA-20200: Database/Instance 3494669306/1 does not exist in
STATS$DATABASE_INSTANCE
ORA-06512: at line 23

Resolution :--

SQL> execute statspack.snap;

24^th SEP 2014

DataGuard Broker :-- Switchover and failover physical standby

Definition DG :-- The data guard broker is a framework for monitoring Data Guard configurations.

DG (Data Guard) Configuration

On both Primary and standby sites , change the initialization parameter in the spfile to enable the data guard broker

sql > alter system set dg_broker_start=TRUE scope=both

Execute

dgmgrl

DGMGRL>

Now connect to the primary database

DGMGRL> connect sys/<password>@to_primary

connected

Create broker configuration

DGMGRL> create configuration 'broker1' as primary database is 'primary' connect identifier is 'primary';

'primary' is connect identified is the service name through which the broker is connected to the PRIMARY database)

Add standby database to the above configuration

DGMGRL>Add database 'standby' as connect identifier is to_standby maintained as physical;

("to_standby" in connect identifier is the serivce name through which broker is connected to the STANDBY database)

See the configuration

DGMGRL> show configuration

It will show current status as DISABLE

We need to enable the configuration

DGMGRL> enable configuration;

DGMGRL> show configuration

Current status will be shown as SUCCESS

SWITCHOVER :

Now we are ready to switch over the primary database role to standby database role

DGMGRL > switchover to 'Standby';

Primary database successfully converted to physical standby

DGMGRL> switchover to 'primary';

standby database is successfully converted to primary database

Failover :

DGMGRL>failover to 'standby';

DGMGRL> failover to 'Primary';

24^th SEP 2014

LOG SHIPPING CHECK & MANUAL SWITCHOVER PHYSICAL STANDBY

On Primary

Execute below command to enable log shipping to standby

sql > alter system set log_archive_dest_state_2=ENABLE scope=both

Check the status of Standby archiving destination

sql > select status,error from v$archive_dest where dest_id=2;

Note :-- The status should return -- valid . If it return error , then check connectivity between primary and standby

Physical Standby

On the standby database execuet the following command to start Managed Recovery Process (MRP) . The command is executed on Mount stage

SQL > alter database recover managed standby database disconnect from session;

Check log applied process

SQL > select name,applied,archived from v$archived_log;

SQL > select max(sequence#) from v$archived_log where applied='YES';

Role Transition

Oracle Data Guard supports two role transition operations:

switchover:

switchover allows the primary database to switch roles with one of its standby databases.There is no data loss during a switchover.After a switchover,each database continues to participate in the data guard configuration with its new role

Failover:

Failover transitions a standby database to the primary role in response to a primary database failure. If the primary database was not opening in either maximum protection mode or maximum availability mode before the failure,some data loss may occur. After a failover, the failed database no longer participates in the Data Guard configuration.It needs to be reinstated to become an active part of Data Guard configuration

Manual Switchover

On the primary Database (open stage)

Query v$database to check the role of Primary Database

sql > select database_role from v$database;

It will return primary

Now check the switchover status of the primary database

sql > select switchover_status from v$database

It will return "SESSIONS ACTIVE"

Now at this point database is ready for manual switchover

execute the below command

sql > alter database commit to switchover to physical standby with session shutdown;

Now your primary database has become physical standby.

Query the database_role column of v$database . It will return "PHYSICAL STANDBY"

Shutdown this new physical standby database and start in mount stage

On the PHYSICAL STANDBY Database (Mount Stage)

Query to check the database role

sql > select database_role from v$database;

It will return "PHYSICAL STANDBY"

Now check the switchover status of the standby database

sql > select switchover_status from v$database;

It will return "SESSIONS ACTIVE"

Cancel the MRP Process which is running in the background

sql > alter database recover managed standby database cancel;

Now you are ready to perform a manual switchover from physical standby to primary

Execute the below command using "SWITCH SESSION SHUTDOWN"

sql > alter database commit to switchover to primary with session shutdown;

Now your physical standby database has become primary .

Query database_role column of v$database . It will return "PRIMARY"

shutdown the database and again start in open stage.

Sep 24

Oracle Wait Class Definition

Note :-- I will keep updating this post as i progress

Oracle Wait Class Definition

1) PX Deq Credit: send blkd

English Defiintion :-- PX -- Parallel Execution
Deq -- To remove the first available element from queue
Credit -- Acknowledgment of Some Work
Send :-- to permit
blkd :-- blocked

Technical Definition :-- Parallel Execution process wants to send message to slave and its not allowed

Note :-- Any parallel execution process has two sub process . One process say p0 queries the database and send the output to p1 another parallel sub process to send back the output of the query back to client or program

alter table emp parallel (degree 2); // this will enable two parallel process. The parallel process of any table in can be viewed in degree column of user_tables

PX Deq Credit: send blkd and PX Deq Credit: need buffer are same . The first one is seem more on localsystems and second one is seen on RAC systems

There is no general recommendation to reduce this wait times

2) direct path read

English Definition :-- Direct :-- send toward a place or object
Path :--- a course of action
Read :-- To anticipate

Technical Definition :-- The direct path read occurs when oracle access datafile into the PGA instead of SGA buffer memory .

This happens under these conditions

1) Reading a Lob Segment

2) Doing a full-table scan

3) Reading a temp file

This happen in case when table in query has more degree of parallelism . Because oracle thinks that doing a full table scan is more beneficial than doing index scan

Tuesday, September 23, 2014

Sep 22

Issue :-- To import tables from 11g to 10g

Resolution:--

1st Approach

Step 1) login to 10g database
Step 2) tnsping the 11g database
Step 3) Connect to 11g database from 10g database using sqlplus system/<password>@11gdatabase
Step 4) Execute exp command from 10g database
exp system/<password> file=tab.dmp tables=schema.T1,schema.T2
Step 5) Import the file tab.dmp on 10g database
Step 6) imp system/<password> file=tab.dmp fromuser=<11g_schema_name> touser=<10g_schema_name>

2nd Approach

Step 1) login to 11g database .
Step 2) Take expdp of the schema
expdp system/**** schemas=<11g_schema_name> directory=< > dumpfile=< > VERSION=10.2
Step 3) Scp the dmp file to 10g database
Step 4) On 10g database
impdp \'/ as sysdba\' directory=< > dumpfile=< > remap_schema=<11g_Schema>:<10g_schema> remap_tablespace=<11g_tablespace>:<10g_tablespace>,<11g_t2>:<10g_t2>