==================================================================================
TO CREATE STATSPACK
==================================================================================
SQL*Plus: Release 9.2.0.1.0 - Production on Tue May 4 02:03:23 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> CONN /AS SYSDBA;
Connected.
SQL>
SQL>
SQL> @ 'E:\oracle\ora92\rdbms\admin\SPCREATE.SQL';
... Installing Required Packages
Package created.
Grant succeeded.
View created.
Package body created.
View created.
Synonym created.
... Creating PERFSTAT user ...
Choose the PERFSTAT user's password.
Not specifying a password will result in the installation FAILING
Specify PERFSTAT password
Enter value for perfstat_password: PERF
PERF
PL/SQL procedure successfully completed.
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the STATSPACK tabl
and indexes. This will also be the PERFSTAT user's default t
Specifying the SYSTEM tablespace will result in the installat
FAILING, as using SYSTEM for performance data is not supporte
TABLESPACE_NAME CONTENTS
------------------------------ ---------
CWMLITE PERMANENT
DRSYS PERMANENT
EXAMPLE PERMANENT
INDX PERMANENT
ODM PERMANENT
TEMP TEMPORARY
TOOLS PERMANENT
UNDOTBS1 UNDO
USERS PERMANENT
XDB PERMANENT
10 rows selected.
Specify PERFSTAT user's default tablespace
Enter value for default_tablespace: USERS
Using USERS for the default tablespace
PL/SQL procedure successfully completed.
User altered.
User altered.
Choose the PERFSTAT user's temporary tablespace.
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.
Specify PERFSTAT user's temporary tablespace.
Enter value for tempory_tablespace: TEMP
Synonym created.
Table created.
Synonym created.
Table created.
1 row created.
1 row created.
Commit complete.
Table created.
Synonym created.
Synonym created.
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.
SQL> -- Create the statistics Package
SQL> @@spcpkg
SQL> Rem
SQL> Rem $Header: spcpkg.sql 17-apr-2002.16:59:10 vbarrier Exp $
SQL> Rem
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2002, Oracle Corporation. All rights
SQL> Rem
SQL> Rem NAME
SQL> Rem spcpkg.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*PLUS command file to create statistics package
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must be run as the STATSPACK owner, PERFSTAT
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem vbarrier 03/20/02 - 2184504
SQL> Rem spommere 03/19/02 - 2274095
SQL> Rem vbarrier 03/05/02 - Segment Statistics
SQL> Rem spommere 02/14/02 - cleanup RAC stats that are no lo
SQL> Rem spommere 02/08/02 - 2212357
SQL> Rem cdialeri 02/07/02 - 2218573
SQL> Rem cdialeri 01/30/02 - 2184717
SQL> Rem cdialeri 01/09/02 - 9.2 - features 2
SQL> Rem cdialeri 11/30/01 - 9.2 - features 1
SQL> Rem hbergh 08/23/01 - 1940915: use substrb on sql_text
SQL> Rem cdialeri 04/26/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 04/07/00 - 1261813
SQL> Rem cdialeri 03/28/00 - sp_purge
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 11/01/99 - Enhance, 1059172
SQL> Rem cgervasi 06/16/98 - Remove references to wrqs
SQL> Rem cmlim 07/30/97 - Modified system events
SQL> Rem gwood.uk 02/30/94 - Modified
SQL> Rem densor.uk 03/31/93 - Modified
SQL> Rem cellis.uk 11/15/89 - Created
SQL> Rem
SQL>
SQL> set echo off;
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL>
==================================================================================
TO CREATE SNAPSORT
==================================================================================
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\tinku>cd\
C:\>sqlplus perfstat/perf
SQL*Plus: Release 9.2.0.1.0 - Production on Tue May 4 02:17:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
SQL> desc stats$snapshot;
Name Null? Type
----------------------------------------- -------- ------------------------
SNAP_ID NOT NULL NUMBER(6)
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
SNAP_TIME NOT NULL DATE
STARTUP_TIME NOT NULL DATE
SESSION_ID NOT NULL NUMBER
SERIAL# NUMBER
SNAP_LEVEL NUMBER
UCOMMENT VARCHAR2(160)
EXECUTIONS_TH NUMBER
PARSE_CALLS_TH NUMBER
DISK_READS_TH NUMBER
BUFFER_GETS_TH NUMBER
SHARABLE_MEM_TH NUMBER
VERSION_COUNT_TH NUMBER
SEG_PHY_READS_TH NOT NULL NUMBER
SEG_LOG_READS_TH NOT NULL NUMBER
SEG_BUFF_BUSY_TH NOT NULL NUMBER
SEG_ROWLOCK_W_TH NOT NULL NUMBER
SEG_ITL_WAITS_TH NOT NULL NUMBER
SEG_CR_BKS_SD_TH NOT NULL NUMBER
SEG_CU_BKS_SD_TH NOT NULL NUMBER
ALL_INIT VARCHAR2(5)
SQL>
==================================================================================
REPORT GENARATE ON SNAPSORT
==================================================================================
SQL*Plus: Release 9.2.0.1.0 - Production on Tue May 4 02:03:23 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> CONN /AS SYSDBA;
Connected.
SQL> @ 'E:\oracle\ora92\rdbms\admin\spreport.sql';
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
588595869 MADAN 1 madan
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
588595869 1 MADAN madan SONY
Using 588595869 for database Id
Using 1 for instance number
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- --------------------
madan MADAN 1 04 May 2010 02:18 5
2 04 May 2010 02:18 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: tinku_rep
-> 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 1 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 24 8 24.4 0.0 .0 .0 .0 1,228
E 24 8 24.1 0.0 .0 .0 .0 1,228
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: MADAN Instance: madan Snaps: 1 -2
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 64 64 0 0
16K 32K 7 7 0 0
32K 64K 3 3 0 0
64K 128K 1 1 0 0
256K 512K 2 2 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: MADAN Instance: madan 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 Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
12 0.5 27.7 0.0 100.0 1
18 0.8 27.7 0.0 100.0 1
24 1.0 27.7 0.0 100.0 0
29 1.2 27.7 0.0 100.0 0
34 1.4 27.7 0.0 100.0 0
38 1.6 27.7 0.0 100.0 0
43 1.8 27.7 0.0 100.0 0
48 2.0 27.7 0.0 100.0 0
72 3.0 27.7 0.0 100.0 0
96 4.0 27.7 0.0 100.0 0
144 6.0 27.7 0.0 100.0 0
192 8.0 27.7 0.0 100.0 0
-------------------------------------------------------------
?Rollback Segment Stats for DB: MADAN Instance: madan Snaps: 1 -2
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ -------------- ------- --------------- -------- -------- --------
0 1.0 0.00 0 0 0 0
1 1.0 0.00 0 0 0 0
2 1.0 0.00 0 0 0 0
3 1.0 0.00 0 0 0 0
4 1.0 0.00 0 0 0 0
5 1.0 0.00 0 0 0 0
6 37.0 0.00 96,446 2 0 2
7 2.0 0.00 1,748 0 0 0
8 1.0 0.00 0 0 0 0
9 1.0 0.00 0 0 0 0
10 1.0 0.00 0 0 0 0
-------------------------------------------------------------
?Rollback Segment Storage for DB: MADAN Instance: madan Snaps: 1 -2
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 0 385,024
1 385,024 45,794 385,024
2 1,040,384 58,016 1,040,384
3 450,560 48,073 450,560
4 450,560 44,131 450,560
5 712,704 54,949 712,704
6 843,776 62,807 843,776
7 2,220,032 96,648 2,220,032
8 516,096 50,621 516,096
9 712,704 61,723 712,704
10 516,096 46,254 516,096
-------------------------------------------------------------
?Latch Activity for DB: MADAN Instance: madan 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
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA 3 0.0 0 0
SQL memory manager latch 1 0.0 0 2 0.0
SQL memory manager worka 207 0.0 0 0
active checkpoint queue 3 0.0 0 0
archive control 2 0.0 0 0
archive process latch 1 0.0 0 0
cache buffer handles 24 0.0 0 0
cache buffers chains 3,053 0.0 0 105 0.0
cache buffers lru chain 105 0.0 0 0
channel operations paren 4 0.0 0 0
checkpoint queue latch 128 0.0 0 105 0.0
child cursor hash table 201 0.0 0 0
dml lock allocation 74 0.0 0 0
enqueue hash chains 217 0.0 0 0
enqueues 109 0.0 0 0
file number translation 14 0.0 0 0
lgwr LWN SCN 4 0.0 0 0
library cache 5,803 0.0 0 0
library cache load lock 46 0.0 0 0
library cache pin 584 0.0 0 0
library cache pin alloca 386 0.0 0 0
list of block allocation 6 0.0 0 0
messages 41 0.0 0 0
mostly latch-free SCN 4 0.0 0 0
post/wait queue 6 0.0 0 1 0.0
redo allocation 376 0.0 0 0
redo copy 0 0 369 0.0
redo writing 18 0.0 0 0
row cache enqueue latch 285 0.0 0 0
row cache objects 285 0.0 0 0
sequence cache 3 0.0 0 0
session allocation 3 0.0 0 0
session idle bit 6 0.0 0 0
session timer 3 0.0 0 0
shared pool 1,041 0.0 0 0
simulator hash latch 201 0.0 0 0
simulator lru latch 15 0.0 0 1 0.0
undo global data 98 0.0 0 0
-------------------------------------------------------------
?Dictionary Cache Stats for DB: MADAN Instance: madan Snaps: 1 -2
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_object_ids 45 0.0 0 0 504
dc_objects 89 0.0 0 0 797
dc_segments 67 0.0 0 0 314
dc_tablespaces 6 0.0 0 0 10
dc_usernames 22 0.0 0 0 18
dc_users 20 0.0 0 0 22
-------------------------------------------------------------
Library Cache Activity for DB: MADAN Instance: madan Snaps: 1 -2
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
SQL AREA 52 0.0 291 17.2 0 0
TABLE/PROCEDURE 274 0.0 423 11.3 0 0
-------------------------------------------------------------
?Shared Pool Advisory for DB: MADAN Instance: madan End Snap: 2
-> 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
Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits
----------- ----- ---------- ------------ ------------ ------- ---------------
24 .5 9 3,180 5,231 1.0 102,858
32 .7 9 3,180 5,231 1.0 102,858
40 .8 9 3,180 5,231 1.0 102,858
48 1.0 9 3,180 5,231 1.0 102,858
56 1.2 9 3,180 5,231 1.0 102,858
64 1.3 9 3,180 5,231 1.0 102,858
72 1.5 9 3,180 5,231 1.0 102,858
80 1.7 9 3,180 5,231 1.0 102,858
88 1.8 9 3,180 5,231 1.0 102,858
96 2.0 9 3,180 5,231 1.0 102,858
-------------------------------------------------------------
?SGA Memory Summary for DB: MADAN Instance: madan Snaps: 1 -2
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 25,165,824
Fixed Size 453,492
Redo Buffers 667,648
Variable Size 109,051,904
----------------
sum 135,338,868
-------------------------------------------------------------
SGA breakdown difference for DB: MADAN Instance: madan Snaps: 1 -2
Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 33,554,432 33,554,432 0.00
large free memory 8,388,608 8,388,608 0.00
shared 1M buffer 2,098,176 2,098,176 0.00
shared Checkpoint queue 282,304 282,304 0.00
shared FileIdentificatonBlock 323,292 323,292 0.00
shared FileOpenBlock 695,504 695,504 0.00
shared KGK heap 3,756 3,756 0.00
shared KGLS heap 2,053,468 2,055,772 0.11
shared KQR M PO 698,896 698,896 0.00
shared KQR S PO 166,420 166,420 0.00
shared KQR S SO 2,816 2,816 0.00
shared KSXR large reply queue 166,104 166,104 0.00
shared KSXR pending messages que 841,036 841,036 0.00
shared KSXR receive buffers 1,033,000 1,033,000 0.00
shared MTTR advisory 10,900 10,900 0.00
shared PL/SQL DIANA 3,040,132 3,040,132 0.00
shared PL/SQL MPCODE 230,688 230,688 0.00
shared PLS non-lib hp 2,068 2,068 0.00
shared VIRTUAL CIRCUITS 265,160 265,160 0.00
shared character set object 274,508 274,508 0.00
shared dictionary cache 1,610,880 1,610,880 0.00
shared enqueue 171,860 171,860 0.00
shared event statistics per sess 1,718,360 1,718,360 0.00
shared fixed allocation callback 220 220 0.00
shared free memory 36,509,364 35,650,172 -2.35
shared joxs heap init 4,220 4,220 0.00
shared kgl simulator 646,160 646,160 0.00
shared ksm_file2sga region 148,652 148,652 0.00
shared library cache 3,695,176 3,766,508 1.93
shared message pool freequeue 834,752 834,752 0.00
shared miscellaneous 4,192,640 4,192,640 0.00
shared parameters 11,172 11,172 0.00
shared processes 144,000 144,000 0.00
shared sessions 410,720 410,720 0.00
shared sim memory hea 21,164 21,164 0.00
shared sql area 4,792,528 5,577,076 16.37
shared table definiti 1,176 2,184 85.71
shared trigger defini 3,436 3,436 0.00
shared trigger inform 1,108 1,108 0.00
shared trigger source 3,048 3,048 0.00
buffer_cache 25,165,824 25,165,824 0.00
fixed_sga 453,492 453,492 0.00
log_buffer 656,384 656,384 0.00
-------------------------------------------------------------
?init.ora Parameters for DB: MADAN Instance: madan Snaps: 1 -2
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 1
audit_sys_operations TRUE
audit_trail TRUE
background_dump_dest e:\oracle\admin\madan\bdump
compatible 9.2.0.0.0
control_files e:\oracle\oradata\madan\CONTROL01
core_dump_dest e:\oracle\admin\madan\cdump
db_block_size 8192
db_cache_size 25165824
db_domain
db_file_multiblock_read_count 16
db_name madan
dispatchers (PROTOCOL=TCP) (SERVICE=madanXDB)
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name madan
java_pool_size 33554432
job_queue_processes 10
large_pool_size 8388608
log_archive_start TRUE
open_cursors 300
pga_aggregate_target 25165824
processes 150
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
shared_pool_size 50331648
sort_area_size 524288
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest e:\oracle\admin\madan\udump
-------------------------------------------------------------
End of Report
SQL>
==================================================================================
STATS GENARATE ON SQL
==================================================================================
SQL> @ 'E:\oracle\ora92\rdbms\admin\spREPSQL.sql';
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
588595869 MADAN 1 madan
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
madan MADAN 1 04 May 2010 02:18 5
2 04 May 2010 02:18 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Hash Value
~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 2979542350
Hash Value specified is: 2979542350
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2_2979542350. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: TINKU_SQL
Using the report name TINKU_SQL
STATSPACK SQL report for Hash Value: 2979542350
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
MADAN 588595869 madan 1 9.2.0.1.0 NO SONY
Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
1 04-May-10 02:18:14 2 04-May-10 02:18:21 0.12
SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 0 .00
Disk Reads: 0
Rows processed: 0
CPU Time(s/ms): 0
Elapsed Time(s/ms): 0
Sorts: 0
Parse Calls: 0
Invalidations: 0
Version count: 1
Sharable Mem(K): 19
Executions: 0
SQL Text
~~~~~~~~
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked
Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
End of Report
SQL>
No comments:
Post a Comment