Statspack安装配置使用说明
eygle (eygle@itpub.net)
在数据库管理中，Oracle提供的statspack是一个很强大的工具，通过Statspack，可以收集系统信息，诊断数据库故障，也方便第三方技术支持进行远程阅读和建议。
看到很多人还不太了解这个工具的使用，论坛上同样的问题很多，所以写了一下一个说明文档，主要涉及安装配置和数据收集。
希望通过这些文字可以使不了解的朋友学会怎样配置和使用Statspack!
下面是正文：
Oracle Statspack 从Oracle8.1.6开始被引入Oracle,并马上成为DBA和Oracle专家用来诊断数据库性能的强有力的工具。通过Statspack我们可以很容易的确定Oracle数据库的瓶颈所在，记录数据库性能状态。因此了解和使用Statspack对于DBA来说至关重要。
在数据库中Statspack的脚本位于$Oracle_HOME/RDBMS/ADMIN目录下，对于Oracle8.1.6,是一组以stat开头的文件；对于Oracle8.1.7,是一组以sp开头的文件。
在Oracle8.1.6中，Statspack第一次发布，但是你也可以在以下链接找到可用于Oracle80~Oracle8.1.5的版本。
http://www.Oracle.com/oramag/Oracle/00-Mar/index.html?o20tun.html
http://www.Oracle.com/oramag/Ora ... tatspack-other.html
在816以前的版本使用Statspack，你需要使用statscbps.sql脚本建立一个v$buffer_pool_statistics视图，该脚本包含在以上链接下载的tar文件中。
访问该链接，你可能需要一个OTN帐号，申请该帐号是免费的。
在Statspack发布之前，我们通常能够使用诊断数据库的工具是两个脚本UTLBSTAT.SQL和UTLESTAT.SQL，BSTAT/ESTAT是一个非常简单的性能诊断工具。UTLBSTAT获得开始时很多V$视图的快照，UTLESTAT通过先前的快照和当前视图生成一个报表。实际上通过这个报表我们很难读出或者解释系统性能方面的问题。Statspack改变了这一切，通过连续的数据收集，Statspack能给我们提供至关重要的趋势分析。这是一个巨大的进步。
下面我们来讲一讲Statspack的安装，配置，使用，解读。
一. 系统参数
为了能够顺利安装和运行Statspack你可能需要设置以下系统参数：
1. job_queue_processes
为了能够建立自动任务，执行数据收集，该参数需要大于0。你可以在初试化参数文件中修改该参数。
2. timed_statistics
收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和 SQL 语句。要防止因从操作系统请求时间而引起的开销，请将该值设置为False。
使用statspack收集统计信息时建议将该值设置为 TRUE，否则收集的统计信息大约只能起到10%的作用，将timed_statistics设置为True所带来的性能影响与好处相比是微不足道的。
该参数使收集的时间信息存储在在V$SESSTATS 和V$SYSSTATS 动态性能视图中。
Timed_statistics参数可以在实例级进行更改
SQL> alter system set timed_statistics = true;
System altered
SQL>
二. 安装Statspack
安装Statspack需要用internal身份登陆，或者拥有SYSDBA(connect / as sysdba)权限的用户登陆。需要在本地安装或者通过telnet登陆到服务器。
在Oracle8.1.6版本中运行statscre.sql;在Oracle8.1.7版本中运行spcreate.sql。
首先登陆到数据库，最好转到$Oracle_HOME/RDBMS/ADMIN目录，这样我们执行脚本就可以方便些。
D:>cd Oracleora81rdbmsadmin
D:Oracleora81RDBMSADMIN>sqlplus internal
SQL*Plus: Release 8.1.7.0.0 - Production on 星期二 12月 3 16:54:53 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
请输入口令:
连接到:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> SELECT instance_name, host_name, VERSION, startup_time FROM v$instance;
INSTANCE_NAME HOST_NAME VERSION STARTUP_TI
-------------- ---------- --------- ------------
eygle AM-SERVER 8.1.7.0.0 22-11月-02
SQL>
检查数据文件路径及磁盘空间，以决定创建数据文件的位置：
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
---------------------------------------------------------------------D:OracleORADATAEYGLESYSTEM01.DBF
D:OracleORADATAEYGLERBS01.DBF
D:OracleORADATAEYGLEUSERS01.DBF
D:OracleORADATAEYGLETEMP01.DBF
D:OracleORADATAEYGLETOOLS01.DBF
D:OracleORADATAEYGLEINDX01.DBF
D:OracleORADATAEYGLEDR01.DBF
D:OracleORADATAEYGLEGGQIANG01.DBF
D:OracleORADATAEYGLEEQSP01.DBF
D:OracleORA81DATABASEMP5_DATA.DBF
D:OracleORA81DATABASEMP5_INDX.DBF
D:OracleORA81DATABASEMP5_TEMP.DBF
D:OracleORA81DATABASEMP5_IDNX.DBF
D:OracleORADATAEYGLEHRM01.DBF
D:OracleORADATAEYGLETIB.DBF
D:OracleORADATAEYGLEEQSP02.DBF
D:OracleORADATAEYGLERMAN_TS.DBF
D:OracleORADATAEYGLEEQSP03.DBF
D:OracleORADATAEYGLEEAPP01.DBF
D:OracleORADATAEYGLEEQSP04.DBF
D:OracleORADATAEYGLEAM01.DBF
D:OracleORADATAEYGLESYSTEM02.DBF
D:OracleORADATAEYGLEFNC01.DBF
D:OracleORADATAEYGLEHH_AM01.ORA
已选择24行。
SQL>
创建存储数据的表空间，如果采样间隔较短，周期较长，打算长期使用，那么你可能需要一个大一点的表空间，如果每个半个小时采样一次，连续采样一周，数据量是很大的。本例创建一个500M的测试表空间。
SQL> CREATE tablespace perfstat
2 datafile 'd:Oracleoradataeygleperfstat.dbf'
3 size 500M;
表空间已创建。
SQL>
检查是否存在安装所需要的脚本文件
SQL> host dir sp*
驱动器 D 中的卷没有标签。
卷的序列号是 5070-5982
D:Oracleora81RDBMSADMIN 的目录
2000-02-18 14:34 1,841 spauto.sql
2000-06-15 15:21 64,492 spcpkg.sql
2000-02-18 14:34 916 spcreate.sql
2000-06-15 15:21 31,985 spctab.sql
2000-06-15 15:21 6,623 spcusr.sql
2000-06-15 15:53 47,820 spdoc.txt
2000-06-15 15:22 794 spdrop.sql
2000-06-15 15:21 3,732 spdtab.sql
2000-06-15 15:21 1,334 spdusr.sql
2000-06-15 15:22 7,002 sppurge.sql
2000-07-12 15:07 72,992 spreport.sql
2000-06-15 15:22 2,278 sptrunc.sql
2000-02-18 14:34 612 spuexp.par
2000-06-15 15:22 31,122 spup816.sql
14 个文件 273,543 字节
0 个目录 3,974,799,360 可用字节
接下来我们就可以开始安装Statspack了。这期间会提示你输入缺省表空间和临时表空间的位置,输入我们为perfstat用户创建的表空间和你的临时表空间。
SQL> @spcreate
.
.
.
.
Specify PERFSTAT user's default tablespace
输入 default_tablespace 的值: perfstat
Using perfstat for the default tablespace
用户已更改。
用户已更改。
Specify PERFSTAT user's temporary tablespace
输入 temporary_tablespace 的值: temp
如果安装成功，你可以看到如下的输出信息：
SQL> host type spcpkg.lis
Creating Package STATSPACK...
程序包已创建。
没有错误。
Creating Package Body STATSPACK...
程序包主体已创建。
没有错误。
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
你可以查看.lis文件查看安装时的错误信息。
SQL> host dir *.lis
驱动器 D 中的卷没有标签。
卷的序列号是 5070-5982
D:Oracleora81RDBMSADMIN 的目录
2002-12-03 17:25 204 spcpkg.lis
2002-12-03 17:25 2,276 spctab.lis
2002-12-03 17:25 3,965 spcusr.lis
2002-12-03 17:23 1,187 spdtab.lis
2002-12-03 17:24 351 spdusr.lis
5 个文件 7,983 字节
0 个目录 3,965,304,832 可用字节
SQL> host find “ORA-“ *.lis
SQL> host find "err" *.lis
---------- SPAUTO.LIS
---------- SPCPKG.LIS
SPCPKG complete. Please check spcpkg.lis for any errors.
---------- SPCTAB.LIS
SPCTAB complete. Please check spctab.lis for any errors.
---------- SPCUSR.LIS
SPCUSR complete. Please check spcusr.lis for any errors.
---------- SPDTAB.LIS
在UNIX上，你可以通过以下命令查看相应的错误信息
$ ls *.lis
spauto.lis spcpkg.lis spctab.lis spcusr.lis spdtab.lis spdusr.lis
$ grep ORA- *.lis
$ grep err *.lis
spcpkg.lis:SPCPKG complete. Please check spcpkg.lis for any errors.
spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.
spcusr.lis:SPCUSR complete. Please check spcusr.lis for any errors.
spdtab.lis:SPDTAB complete. Please check spdtab.lis for any errors.
spdusr.lis:SPDUSR complete. Please check spdusr.lis for any errors.
在这一步，如果出现错误，那么你可以运行spdrop.sql脚本来删除这些对象。然后重新运行spcreate.sql来创建这些对象。运行 SQL*Plus, 以具有SYSDBA 权限的用户登陆：
SQL> @spdrop.sql
.
.
.
同义词已丢弃。off;
视图已丢掉。
同义词已丢弃。
视图已丢掉。
同义词已丢弃。
用户已丢弃
NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.
SQL>
三. 测试安装好的Statspack
运行statspack.snap可以产生系统快照，运行两次，然后执行spreport.sql就可以生成一个基于两个时间点的报告。
如果一切正常，说明安装成功。
SQL>execute statspack.snap
PL/SQL procedure successfully completed.
SQL>execute statspack.snap
PL/SQL procedure successfully completed.
SQL>@spreport.sql
…
可是有可能你会得到以下错误：
SQL> exec statspack.snap;
BEGIN statspack.snap; END;
*
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "PERFSTAT.STATSPACK", line 978
ORA-06512: at "PERFSTAT.STATSPACK", line 1612
ORA-06512: at "PERFSTAT.STATSPACK", line 71
ORA-06512: at line 1
这是Oracle的一个Bug，Bug号1940915。
该Bug自8.1.7.3后修正。
这个问题只会出现在多位的字符集,需要修改spcpkg.sql脚本，$Oracle_HOME/rdbms/admin/spcpkg.sql，将"substr" 修改为 "substrb"，然后重新运行该脚本。
该脚本错误部分：
select l_snap_id
, p_dbid
, p_instance_number
, substr(sql_text,1,31)
．．．．．．．．．．．
substr 会将多位的字符, 当作一个byte.substrb 则会当作多个byte。在收集数据时， statpack 会将 top 10 的 sql 前 31 个字节 存入数据表中,若在SQL的前31 个字有中文，就会出现此错误。
四. 规划自动任务
Statspack正确安装以后，我们就可以设置定时任务，开始收集数据了。可以使用spatuo.sql来定义自动任务。
先来看看spauto.sql的关键内容：
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
这个job任务定义了收集数据的时间间隔：
一天有24个小时，1440分钟，那么：
1/24 HH 每小时一次
1/48 MI 每半小时一次
1/144 MI 每十分钟一次
1/288 MI 每五分钟一次
我们可以修改spauto.sql来更改执行间隔，如：
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
然后我们执行spauto，这样我们就建立了一个每30分钟执行一次的数据收集计划。你可以查看spauto.lis来获得输出信息：
SQL>
SQL> --
SQL> -- Schedule a snapshot to be run on this instance every hour, on the hour
SQL>
SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
2 select instance_number into :instno from v$instance;
3 dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);
4 commit;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL>
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
----------
22
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
job_queue_processes integer 12
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
SQL> select job, next_date, next_sec
2 from user_jobs
3 where job = :jobno;
JOB NEXT_DATE NEXT_SEC
---------- ---------- ----------------
22 04-12月-02 15:00:00
SQL>
五. 生成分析报告
调用spreport.sql可以生成分析报告：
SQL> @spreport
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1277924236 EYGLE 1 eygle
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
eygle EYGLE 1 04 12月 2002 14:4 5
8
2 04 12月 2002 15:0 5
0
3 04 12月 2002 15:1 5
0
4 04 12月 2002 15:2 5
0
………………..
87 05 12月 2002 02:2 5
3
88 05 12月 2002 02:3 5
3
89 05 12月 2002 02:4 5
3
90 05 12月 2002 02:5 5
3
91 05 12月 2002 03:0 5
3
92 05 12月 2002 03:1 5
3
93 05 12月 2002 03:2 5
3
94 05 12月 2002 03:3 5
3
95 05 12月 2002 03:4 5
3
96 05 12月 2002 03:5 5
3
97 05 12月 2002 04:0 5
3
98 05 12月 2002 04:1 5
3
eygle EYGLE 99 05 12月 2002 04:2 5
3
100 05 12月 2002 04:3 5
3
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值: 1
Begin Snapshot Id specified: 1
输入 end_snap 的值: 100
End Snapshot Id specified: 100
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_100. To use this name,
press to continue, otherwise enter an alternative.
输入 report_name 的值: rep1205.txt
Using the report name rep1205.txt
这样就生成了一个报告，可是如果中间停过机，那么你可能收到以下错误信息：
ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
EYGLE 1277924236 eygle 1 8.1.7.0.0 NO AM-SERVER
:ela := ;
*
ERROR 位于第 4 行:
ORA-06550: 第 4 行, 第 17 列:
PLS-00103: 出现符号 ";"在需要下列之一时：
(-+modnotnull
avg
countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute
foralltimetimestampintervaldate
符号 "null" 被替换为 ";" 后继续。
ORA-06550: 第 6 行, 第 16 列:
PLS-00103: 出现符号 ";"在需要下列之一时：
(-+modnotnullavg
countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute
foralltimetimestampintervaldate
六. 移除定时任务
移除一个定时任务，可以如下操作:
SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL
---------- ------------------------------ ------------------------------ ------------------------------ ----------- 22 PERFSTAT 2002-12-5:14:33:26 2002-12-5 14:43:00 trunc(SYSDATE+1/144,'MI')
SQL> execute dbms_job.remove('22')
PL/SQL procedure successfully completed
七. 删除历史数据
删除stats$snapshot数据表中的相应数据，其他表中的数据会相应的级连删除：
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
166
SQL> delete from stats$snapshot where snap_id < = 166;
143 rows deleted
你可以更改snap_id的范围以保留你需要的数据。
在以上删除过程中，你可以看到所有相关的表都被锁定。
SQL> select a.object_id,a.Oracle_username ,b.object_name
from v$locked_object a,dba_objects b
where a.object_id = b.object_id
/
OBJECT_ID Oracle_USERNAME OBJECT_NAME
------------------------------------- ---------------------------------------------------------------------156 PERFSTAT SNAP$
39700 PERFSTAT STATS$LIBRARYCACHE
39706 PERFSTAT STATS$ROLLSTAT
39712 PERFSTAT STATS$SGA
39754 PERFSTAT STATS$PARAMETER
39745 PERFSTAT STATS$SQL_STATISTICS
39739 PERFSTAT STATS$SQL_SUMMARY
39736 PERFSTAT STATS$ENQUEUESTAT
39733 PERFSTAT STATS$WAITSTAT
39730 PERFSTAT STATS$BG_EVENT_SUMMARY
39724 PERFSTAT STATS$SYSTEM_EVENT
39718 PERFSTAT STATS$SYSSTAT
39715 PERFSTAT STATS$SGASTAT
39709 PERFSTAT STATS$ROWCACHE_SUMMARY
39703 PERFSTAT STATS$BUFFER_POOL_STATISTICS
39697 PERFSTAT STATS$LATCH_MISSES_SUMMARY
39679 PERFSTAT STATS$SNAPSHOT
39682 PERFSTAT STATS$FILESTATXS
39688 PERFSTAT STATS$LATCH
174 PERFSTAT JOB$
20 rows selected
八. 调整STATSPACK的收集门限
Statspack有两种类型的收集选项：
级别（level）：控制收集数据的类型
门限（threshold）：设置收集的数据的阈值。
1．级别（level）
Statspack共有三种快照级别，默认值是5
a.level 0: 一般性能统计。包括等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等等。
b.level 5: 增加SQL语句。除了包括level0的所有内容，还包括SQL语句的收集，收集结果记录在stats$sql_summary中。
c.level 10: 增加子锁存统计。包括level5的所有内容。并且还会将附加的子锁存存入stats$lathc_children中。在使用这个级别时需要慎重，建议在Oracle support的指导下进行。
可以通过statspack包修改缺省的级别设置
SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>’true’);
通过这样的设置，以后的收集级别都将是0级。
如果你只是想本次改变收集级别，可以忽略i_modify_parameter参数。
SQL>execute statspack.snap(i_snap_level=>10);
2．快照门限
快照门限只应用于stats$sql_summary表中获取的SQL语句。
因为每一个快照都会收集很多数据，每一行都代表获取快照时数据库中的一个SQL语句，所以stats$sql_summary很快就会成为Statspack中最大的表。
门限存储在stats$statspack_parameter表中。让我们了结一下各种门限：
a. executions_th 这是SQL语句执行的数量(默认值是100)
b. disk_reads_tn 这是SQL语句执行的磁盘读入数量（默认值是1000）
c. parse_calls_th 这是SQL语句执行的解析调用的数量（默认值是1000）
d. buffer_gets_th 这是SQL语句执行的缓冲区获取的数量（默认值是10000）
任何一个门限值超过以上参数就会产生一条记录。
通过调用statspack.modify_statspack_parameter函数我们可以改变门限的默认值。
例如：
SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000;
一一. 在815上的安装配置
a. 数据库状况
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0, 64 bit - Producti
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for HPUX: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
b. 运行statscbps.sql
SQL> @statscbps.sql
View created.
Synonym created.
Grant succeeded.
SQL>
其他步骤都是相同的。
STATSPACK report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
CHINABIG 3264260008 chinabig 1 10.1.0.2.0 NO chinabbs
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 1 16-Aug-05 11:06:26 28 3.7
End Snap: 3 16-Aug-05 11:22:03 28 4.4
Elapsed: 15.62 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,008M Std Block Size: 8K
Shared Pool Size: 432M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 4,218.76 12,629.33
Logical reads: 56.06 167.82
Block changes: 24.13 72.25
Physical reads: 0.10 0.31
Physical writes: 2.19 6.56
User calls: 12.70 38.03
Parses: 4.85 14.53
Hard parses: 0.19 0.56
Sorts: 0.90 2.70
Logons: 0.03 0.08
Executes: 6.30 18.85
Transactions: 0.33
% Blocks changed per Read: 43.05 Recursive Call %: 69.40
Rollback per transaction %: 61.66 Rows per Sort: 23.06
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.98
Buffer Hit %: 99.81 In-memory Sort %: 100.00
Library Hit %: 96.42 Soft Parse %: 96.17
Execute to Parse %: 22.90 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 82.82 % Non-Parse CPU: 85.84
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 33.98 35.62
% SQL with executions>1: 71.93 72.78
% Memory for SQL w/exec>1: 72.34 71.05
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time 21 24.90
class slave wait 4 20 24.02
Queue Monitor Task Wait 4 20 24.02
control file parallel write 321 11 13.75
log file parallel write 430 7 8.39
-------------------------------------------------------------
Wait Events DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
class slave wait 4 4 20 5006 0.0
Queue Monitor Task Wait 4 4 20 5006 0.0
control file parallel write 321 0 11 36 1.0
log file parallel write 430 0 7 16 1.4
log file sync 77 0 1 15 0.2
process startup 4 0 1 207 0.0
db file sequential read 104 0 1 6 0.3
log file switch completion 3 0 1 179 0.0
log buffer space 6 0 0 41 0.0
control file sequential read 1,235 0 0 0 3.9
db file single write 22 0 0 9 0.1
LGWR wait for redo copy 116 2 0 1 0.4
control file single write 5 0 0 24 0.0
log file single write 2 0 0 20 0.0
db file scattered read 3 0 0 11 0.0
SQL*Net more data to client 240 0 0 0 0.8
log file sequential read 2 0 0 6 0.0
SQL*Net break/reset to clien 8 0 0 1 0.0
SQL*Net message from client 11,566 0 6,113 528 37.0
jobq slave wait 913 897 2,742 3003 2.9
virtual circuit status 32 32 938 29306 0.1
Queue Monitor Wait 29 29 870 30009 0.1
wakeup time manager 3 0 810 ###### 0.0
Queue Monitor Slave Wait 1 1 60 60010 0.0
SQL*Net message to client 11,566 0 0 0 37.0
-------------------------------------------------------------
Background Wait Events DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
Queue Monitor Task Wait 4 4 20 5006 0.0
control file parallel write 317 0 11 36 1.0
log file parallel write 430 0 7 16 1.4
process startup 4 0 1 207 0.0
db file single write 22 0 0 9 0.1
LGWR wait for redo copy 116 2 0 1 0.4
control file single write 4 0 0 20 0.0
control file sequential read 59 0 0 1 0.2
db file sequential read 29 0 0 2 0.1
log file single write 2 0 0 20 0.0
log file sequential read 2 0 0 6 0.0
rdbms ipc message 2,800 2,603 6,361 2272 8.9
Queue Monitor Wait 30 30 900 30009 0.1
smon timer 3 3 900 ###### 0.0
-------------------------------------------------------------
Wait Event Histogram DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> ordered by event (idle events last)
Event
--------------------------------------------------
0 - 1 ms 1 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
LGWR wait for redo copy
103 1 3 9 0 0
Queue Monitor Task Wait
0 0 0 0 0 4
SQL*Net break/reset to client
6 1 1 0 0 0
SQL*Net more data to client
240 0 0 0 0 0
class slave wait
0 0 0 0 0 4
control file parallel write
0 0 0 0 212 109
control file sequential read
1,223 2 6 4 0 0
control file single write
0 0 0 0 4 1
db file scattered read
0 0 2 0 1 0
db file sequential read
25 16 38 22 3 0
db file single write
0 1 9 12 0 0
log buffer space
0 0 0 2 1 3
log file parallel write
1 32 202 117 39 38
log file sequential read
1 0 0 1 0 0
log file single write
0 0 0 1 1 0
log file switch completion
0 0 0 0 1 2
log file sync
9 5 11 34 15 3
process startup
0 0 0 0 0 4
Queue Monitor Slave Wait
0 0 0 0 0 1
Queue Monitor Wait
0 0 0 0 0 29
SQL*Net message from client
6,225 2,940 1,308 420 107 566
SQL*Net message to client
11,565 0 0 1 0 0
dispatcher timer
0 0 0 0 0 15
jobq slave wait
0 0 0 0 0 912
pmon timer
2 0 1 0 0 311
rdbms ipc message
51 14 11 18 12 2,694
smon timer
Wait Event Histogram DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> ordered by event (idle events last)
Event
--------------------------------------------------
0 - 1 ms 1 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
0 0 0 0 0 3
virtual circuit status
0 0 0 0 0 32
wakeup time manager
0 0 0 0 0 3
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 52,527
-> Captured SQL accounts for 102.0% 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
--------------- ------------ -------------- ------ -------- --------- ----------
16,465 2 8,232.5 31.3 11.26 12.46 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
5,358 47 114.0 10.2 0.46 0.46 486107790
Module: JDBC Thin Client
select count(*) as sum from qq_collection t where 1 =1 and che
ck_state = '????'
5,104 44 116.0 9.7 1.06 1.13 1994511710
Module: JDBC Thin Client
select * from (select rownum as rnum,a.* from (select * from qq
_collection t where 1 =1 )a)b where rnum>0 and rnum<= 20
4,987 43 116.0 9.5 0.85 0.95 4252244215
Module: JDBC Thin Client
select * from (select rownum as rnum,a.* from (select * from qq
_collection t where 1 =1 and check_state = '????')a)b wher
e rnum>0 and rnum<= 20
2,454 134 18.3 4.7 0.52 0.75 1471113845
Module: JDBC Thin Client
select count(*) as sum from qq_collection t where 1 =1
1,764 15 117.6 3.4 0.51 0.52 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J
OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :
b := 0; END IF; END;
1,473 139 10.6 2.8 0.25 0.32 3756044252
Module: JDBC Thin Client
select pron_id,name_zhs from an_province where cnty_id=10
1,390 139 10.0 2.6 0.15 0.15 1677199194
Module: JDBC Thin Client
select pron_id from an_province where cnty_id=10
1,289 414 3.1 2.5 0.40 0.52 3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
= nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
ds = range_preds + decode(bitand(:flag,8),0,0,1),
1,112 139 8.0 2.1 0.15 0.15 1029614476
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=60
1,051 139 7.6 2.0 0.15 0.19 46046750
SQL ordered by Gets DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 52,527
-> Captured SQL accounts for 102.0% 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
--------------- ------------ -------------- ------ -------- --------- ----------
Module: JDBC Thin Client
select cnty_id,name_zhs from an_country
990 108 9.2 1.9 0.20 0.42 1200253560
Module: EM_PING
INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATIO
N, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL)
VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 ,
1,32), :B5 , SUBSTR(:B4 ,1,128), SUBSTR(:B3 ,1,128), SUBSTR(:B2
,1,128), SUBSTR(:B1 ,1,256))
973 139 7.0 1.9 0.09 0.09 1579577967
Module: JDBC Thin Client
select pron_id from an_province where cnty_id=40
973 139 7.0 1.9 0.09 0.09 2289062628
Module: JDBC Thin Client
select pron_id,name_zhs from an_province where cnty_id=40
973 139 7.0 1.9 0.09 0.09 2545521930
Module: JDBC Thin Client
select pron_id,name_zhs from an_province where cnty_id=20
973 139 7.0 1.9 0.09 0.09 2668754060
Module: JDBC Thin Client
select pron_id from an_province where cnty_id=30
973 139 7.0 1.9 0.10 0.10 2875353132
Module: JDBC Thin Client
select pron_id from an_province where cnty_id=20
973 139 7.0 1.9 0.09 0.09 3214299155
Module: JDBC Thin Client
select pron_id,name_zhs from an_province where cnty_id=30
896 318 2.8 1.7 0.31 0.38 2803285
update sys.mon_mods$ set inserts = inserts + :ins, updates = upd
ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl
ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop
_segments + :dropseg, timestamp = :time where obj# = :objn
834 139 6.0 1.6 0.14 0.15 1385231702
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=370
834 139 6.0 1.6 0.13 0.13 1697585701
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=70
834 139 6.0 1.6 0.12 0.12 1870688995
SQL ordered by Gets DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 52,527
-> Captured SQL accounts for 102.0% 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
--------------- ------------ -------------- ------ -------- --------- ----------
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=170
834 139 6.0 1.6 0.13 0.13 3510779235
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=280
813 139 5.8 1.5 0.20 0.41 3848303744
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=10
720 60 12.0 1.4 0.10 0.10 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
695 139 5.0 1.3 0.11 0.11 192976342
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=250
695 139 5.0 1.3 0.12 0.12 2348716846
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=140
556 139 4.0 1.1 0.11 0.11 101139391
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=100
556 139 4.0 1.1 0.11 0.11 274581527
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=120
556 139 4.0 1.1 0.12 0.12 470686153
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=330
556 139 4.0 1.1 0.11 0.11 825447348
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=190
556 139 4.0 1.1 0.10 0.10 853952923
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=160
556 139 4.0 1.1 0.11 0.11 917401348
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=110
556 139 4.0 1.1 0.11 0.11 1118916513
Module: JDBC Thin Client
SQL ordered by Gets DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 52,527
-> Captured SQL accounts for 102.0% 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
--------------- ------------ -------------- ------ -------- --------- ----------
select cty_id,name_zhs from an_city where pron_id=320
556 139 4.0 1.1 0.10 0.10 1196505328
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=180
556 139 4.0 1.1 0.11 0.11 1448488993
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=340
556 139 4.0 1.1 0.11 0.11 2840877670
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=240
556 139 4.0 1.1 0.11 0.11 2896829240
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=150
556 139 4.0 1.1 0.11 0.11 3512589974
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=50
556 139 4.0 1.1 0.11 0.11 4181840911
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=200
546 182 3.0 1.0 0.12 0.12 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> End Disk Reads Threshold: 1000 Total Disk Reads: 98
-> Captured SQL accounts for 133.7% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
30 108 0.3 30.6 0.20 0.42 1200253560
Module: EM_PING
INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATIO
N, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL)
VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 ,
1,32), :B5 , SUBSTR(:B4 ,1,128), SUBSTR(:B3 ,1,128), SUBSTR(:B2
,1,128), SUBSTR(:B1 ,1,256))
19 134 0.1 19.4 0.52 0.75 1471113845
Module: JDBC Thin Client
select count(*) as sum from qq_collection t where 1 =1
17 84 0.2 17.3 0.07 0.15 3676231057
Module: JDBC Thin Client
INSERT /*+ IDX(0) */ INTO "CHINABIG"."MLOG$_QQ_COLLECTION" (dmlt
ype$$,old_new$$,snaptime$$,change_vector$$,sequence$$,m_row$$,"I
D","COMPANY","NAME","QQ_NUMBER","SEX","DEPARTMENT","POSITION","M
OBILE","EMAIL","WEBSITE","COUNTRY_ID","PRON_ID","CITY_ID","ADDRE
SS","ZIPCODE","TEL","FAX","MAIN_PRODUCT","CATEGORY","SALE","CHEC
12 139 0.1 12.2 0.20 0.41 3848303744
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=10
10 139 0.1 10.2 0.15 0.19 46046750
Module: JDBC Thin Client
select cnty_id,name_zhs from an_country
8 139 0.1 8.2 0.25 0.32 3756044252
Module: JDBC Thin Client
select pron_id,name_zhs from an_province where cnty_id=10
5 5 1.0 5.1 0.02 0.04 199663413
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
5 123 0.0 5.1 0.09 0.10 1980292411
SELECT T.WARNING_OPERATOR, T.WARNING_THRESHOLD, T.CRITICAL_OPERA
TOR, T.CRITICAL_THRESHOLD, T.NUM_OCCURENCES, T.NUM_WARNINGS, T.N
UM_CRITICALS, T.MESSAGE, T.MESSAGE_NLSID FROM MGMT_METRIC_THRESH
OLDS T WHERE T.TARGET_GUID = :B4 AND T.METRIC_GUID = :B3 AND T.C
OLL_NAME = :B2 AND T.KEY_VALUE = :B1
4 318 0.0 4.1 0.31 0.38 2803285
update sys.mon_mods$ set inserts = inserts + :ins, updates = upd
ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl
ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop
_segments + :dropseg, timestamp = :time where obj# = :objn
4 414 0.0 4.1 0.40 0.52 3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
SQL ordered by Reads DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> End Disk Reads Threshold: 1000 Total Disk Reads: 98
-> Captured SQL accounts for 133.7% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
= nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
ds = range_preds + decode(bitand(:flag,8),0,0,1),
3 7 0.4 3.1 0.02 0.03 986338823
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
3 19 0.2 3.1 0.02 0.03 2062595902
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o
where d_obj#=:1 and p_obj#=obj#(+) order by order#
3 41 0.1 3.1 0.04 0.04 2703824309
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and remoteowner is null and linkname is null and subname i
s null
2 7 0.3 2.0 0.02 0.02 386388955
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
2 140 0.0 2.0 0.04 0.05 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
1 141 0.0 1.0 0.28 0.46 260339297
insert into sys.col_usage$ values ( :objn, :coln, decode(bit
and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b
itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode
(bitand(:flag,16),0,0,1), decode(bitand(:flag,32),0,0,1), :t
ime)
1 7 0.1 1.0 0.00 0.01 336764478
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from idl_char$ where obj#=:1 and part=:2 and version=:3 order by
piece#
1 19 0.1 1.0 0.01 0.01 1198893840
select order#,columns,types from access$ where d_obj#=:1
1 31 0.0 1.0 0.01 0.01 3468666020
select text from view$ where rowid=:1
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> End Executions Threshold: 100 Total Executions: 5,899
-> Captured SQL accounts for 158.1% 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
------------ --------------- ---------------- ----------- ---------- ----------
414 292 0.7 0.00 0.00 3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
= nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
ds = range_preds + decode(bitand(:flag,8),0,0,1),
318 272 0.9 0.00 0.00 2803285
update sys.mon_mods$ set inserts = inserts + :ins, updates = upd
ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl
ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop
_segments + :dropseg, timestamp = :time where obj# = :objn
318 0 0.0 0.00 0.00 2396279102
lock table sys.mon_mods$ in exclusive mode nowait
209 0 0.0 0.00 0.00 2554034351
lock table sys.col_usage$ in exclusive mode nowait
182 16 0.1 0.00 0.00 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
166 166 1.0 0.00 0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
141 141 1.0 0.00 0.00 260339297
insert into sys.col_usage$ values ( :objn, :coln, decode(bit
and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b
itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode
(bitand(:flag,16),0,0,1), decode(bitand(:flag,32),0,0,1), :t
ime)
140 85 0.6 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
139 556 4.0 0.00 0.00 46046750
Module: JDBC Thin Client
select cnty_id,name_zhs from an_country
139 1,390 10.0 0.00 0.00 88436648
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=260
139 2,363 17.0 0.00 0.00 101139391
Module: JDBC Thin Client
SQL ordered by Executions DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> End Executions Threshold: 100 Total Executions: 5,899
-> Captured SQL accounts for 158.1% 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
------------ --------------- ---------------- ----------- ---------- ----------
select cty_id,name_zhs from an_city where pron_id=100
139 139 1.0 0.00 0.00 162380188
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=350
139 1,668 12.0 0.00 0.00 192976342
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=250
139 2,085 15.0 0.00 0.00 274581527
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=120
139 973 7.0 0.00 0.00 380740558
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=310
139 2,363 17.0 0.00 0.00 470686153
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=330
139 139 1.0 0.00 0.00 724716151
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=30
139 1,946 14.0 0.00 0.00 825447348
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=190
139 1,529 11.0 0.00 0.00 853952923
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=160
139 2,363 17.0 0.00 0.00 917401348
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=110
139 3,197 23.0 0.00 0.00 1029614476
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=60
139 2,085 15.0 0.00 0.00 1118916513
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=320
139 1,529 11.0 0.00 0.00 1196505328
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=180
139 3,336 24.0 0.00 0.00 1385231702
Module: JDBC Thin Client
SQL ordered by Executions DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> End Executions Threshold: 100 Total Executions: 5,899
-> Captured SQL accounts for 158.1% 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
------------ --------------- ---------------- ----------- ---------- ----------
select cty_id,name_zhs from an_city where pron_id=370
139 1,529 11.0 0.00 0.00 1448488993
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=340
139 139 1.0 0.00 0.00 1579577967
Module: JDBC Thin Client
select pron_id from an_province where cnty_id=40
139 4,309 31.0 0.00 0.00 1677199194
Module: JDBC Thin Client
select pron_id from an_province where cnty_id=10
139 2,224 16.0 0.00 0.00 1697585701
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=70
139 556 4.0 0.00 0.00 1703696539
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=220
139 1,112 8.0 0.00 0.00 1826907535
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=230
139 1,946 14.0 0.00 0.00 1870688995
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=170
139 139 1.0 0.00 0.00 2173037359
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=360
139 139 1.0 0.00 0.00 2289062628
Module: JDBC Thin Client
select pron_id,name_zhs from an_province where cnty_id=40
139 2,224 16.0 0.00 0.00 2348716846
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=140
139 139 1.0 0.00 0.00 2436022283
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=20
139 139 1.0 0.00 0.00 2545521930
Module: JDBC Thin Client
select pron_id,name_zhs from an_province where cnty_id=20
139 139 1.0 0.00 0.00 2668754060
Module: JDBC Thin Client
SQL ordered by Executions DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> End Executions Threshold: 100 Total Executions: 5,899
-> Captured SQL accounts for 158.1% 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
------------ --------------- ---------------- ----------- ---------- ----------
select pron_id from an_province where cnty_id=30
139 278 2.0 0.00 0.00 2723834099
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=90
139 2,363 17.0 0.00 0.00 2840877670
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=240
139 139 1.0 0.00 0.00 2875353132
Module: JDBC Thin Client
select pron_id from an_province where cnty_id=20
139 1,946 14.0 0.00 0.00 2896829240
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=150
139 139 1.0 0.00 0.00 2926474579
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=300
139 139 1.0 0.00 0.00 3102291056
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=270
139 139 1.0 0.00 0.00 3214299155
Module: JDBC Thin Client
select pron_id,name_zhs from an_province where cnty_id=30
139 2,919 21.0 0.00 0.00 3510779235
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=280
139 1,946 14.0 0.00 0.00 3512589974
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=50
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> End Parse Calls Threshold: 1000 Total Parse Calls: 4,548
-> Captured SQL accounts for 184.2% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
360 141 7.92 260339297
insert into sys.col_usage$ values ( :objn, :coln, decode(bit
and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b
itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode
(bitand(:flag,16),0,0,1), decode(bitand(:flag,32),0,0,1), :t
ime)
318 318 6.99 2803285
update sys.mon_mods$ set inserts = inserts + :ins, updates = upd
ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl
ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop
_segments + :dropseg, timestamp = :time where obj# = :objn
318 318 6.99 2396279102
lock table sys.mon_mods$ in exclusive mode nowait
209 209 4.60 2554034351
lock table sys.col_usage$ in exclusive mode nowait
209 414 4.60 3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
= nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
ds = range_preds + decode(bitand(:flag,8),0,0,1),
139 139 3.06 46046750
Module: JDBC Thin Client
select cnty_id,name_zhs from an_country
139 139 3.06 88436648
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=260
139 139 3.06 101139391
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=100
139 139 3.06 162380188
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=350
139 139 3.06 192976342
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=250
139 139 3.06 274581527
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=120
139 139 3.06 380740558
Module: JDBC Thin Client
SQL ordered by Parse Calls DB/Inst: CHINABIG/chinabig Snaps: 1-3
-> End Parse Calls Threshold: 1000 Total Parse Calls: 4,548
-> Captured SQL accounts for 184.2% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
select cty_id,name_zhs from an_city where pron_id=310
139 139 3.06 470686153
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=330
139 139 3.06 724716151
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=30
139 139 3.06 825447348
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=190
139 139 3.06 853952923
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=160
139 139 3.06 917401348
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=110
139 139 3.06 1029614476
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=60
139 139 3.06 1118916513
Module: JDBC Thin Client
select cty_id,name_zhs from an_city where pron_id=320
139 139 3.06 1196505328
Module: JDBC Thin Client
select cty_id,name_
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/57915/viewspace-799071/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/57915/viewspace-799071/
扫一扫
2004
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
