Retreive new hashvalue and display their load
来源：互联网 发布：mysql的url地址 编辑：程序博客网 时间：2024/05/08 09:09
alter session set nls_territory=france;
alter session set nls_numeric_characters=", ";
WITH
p AS (
SELECT
to_date('24/01/07 06:00','DD/MM/YY HH24:MI') /* First start date to compare */ bnewsnap,
to_date('26/01/07 06:05','DD/MM/YY HH24:MI') /* first end date to compare */ enewsnap,
to_date('17/01/07 06:00','DD/MM/YY HH24:MI') /* second start date to compare */ bexistsnap,
to_date('19/01/07 06:05','DD/MM/YY HH24:MI') /* second end date to compare */ eexistsnap
FROM dual
)
select
es.hash_value hash_value,
bs.snap_id beg_snap_id,
es.snap_id end_snap_id,
to_char(b.snap_time, 'DD/MM/YY HH24:MI:SS') beg_snap_time,
to_char(e.snap_time, 'DD/MM/YY HH24:MI:SS') end_snap_time,
to_char(es.executions - nvl(bs.executions,0),'999G999G999') execs ,
to_char(es.buffer_gets - nvl(bs.buffer_gets,0),'999G999G999') gets
, to_char(decode(es.executions - nvl(bs.executions,0),0, 0
, (es.buffer_gets - nvl(bs.buffer_gets,0))/ (es.executions - nvl(bs.executions,0))),'999G999G999D9') getspx
, to_char(es.disk_reads - nvl(bs.disk_reads,0),'999G999G999') reads
, to_char(decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.disk_reads - nvl(bs.disk_reads,0)) / (es.executions - nvl(bs.executions,0))),'999G999G999D9') ReadsPx
, to_char(es.rows_processed - nvl(bs.rows_processed,0),'999G999G990') "rows"
, to_char(decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.rows_processed - nvl(bs.rows_processed,0))/ (es.executions - nvl(bs.executions,0))),'999G999G999D9') RowsPx
, to_char((es.cpu_time - nvl(bs.cpu_time,0))/1000000,'999G999G999') cputsec
, to_char(decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.cpu_time - nvl(bs.cpu_time,0))/1000)/ (es.executions - nvl(bs.executions,0)),'999G999G999D9') cputpxmsec
, to_char((es.elapsed_time - nvl(bs.elapsed_time,0))/1000000,'999G999G999') elatsec
, to_char(decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.elapsed_time - nvl(bs.elapsed_time,0))/1000)/(es.executions - nvl(bs.executions,0)),'999G999G990D9') elapxmsec
, to_char(es.sorts - nvl(bs.sorts,0),'999G999G990') sorts
, to_char(decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.sorts - nvl(bs.sorts,0))/ (es.executions - nvl(bs.executions,0))),'999G999G999') sortpx
, to_char(es.parse_calls - nvl(bs.parse_calls,0),'999G999G990') parcall
, to_char(decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.parse_calls - nvl(bs.parse_calls,0))/ (es.executions - nvl(bs.executions,0))),'999G999G999D9') parsepx
, es.invalidations - nvl(bs.invalidations,0) inval
, es.version_count vcount
, to_char(es.sharable_mem/1024,'999G999G999') sharememoryKB
from
v$database d
, stats$sql_summary es
, stats$sql_summary bs
, p
, stats$snapshot b
, stats$snapshot e
where
(e.snap_time BETWEEN p.bnewsnap AND p.enewsnap AND MOD(b.snap_id,1)=0 AND d.dbid=b.dbid )
AND (b.snap_id=e.snap_id-1 AND b.dbid=e.dbid AND b.instance_number=e.instance_number)
AND (b.snap_id=bs.snap_id AND b.dbid=bs.dbid AND b.instance_number=bs.instance_number)
AND (e.snap_id=es.snap_id AND e.dbid=es.dbid AND e.instance_number=es.instance_number)
and bs.dbid(+) = es.dbid
and bs.instance_number(+) = es.instance_number
and bs.hash_value(+) = es.hash_value
and bs.address(+) = es.address
and bs.text_subset(+) = es.text_subset
and es.hash_value not in (SELECT hash_value
FROM p,
stats$snapshot sns
INNER JOIN stats$sql_summary sqs ON sns.snap_id = sqs.snap_id
WHERE (sns.snap_time BETWEEN p.bexistsnap AND p.eexistsnap)
)
and es.executions - nvl(bs.executions,0)>0
order by e.snap_time;
---------------------------------------------------------------------------------------------------------------------------------------------
The indicators listed are :
HASH_VALUE ROWSPX
BEG_SNAP_ID CPUTSEC
END_SNAP_ID CPUTPXMSEC
BEG_SNAP_TIME ELATSEC
END_SNAP_TIME ELAPXMSEC
EXECS SORTS
GETS SORTPX
GETSPX PARCALL
READS PARSEPX
READSPX INVAL
rows VCOUNT
SHAREMEMORYKB
GETSPX, READSPX, ROWSPX, CPUPXMSEC, ELAPXMSEC, SORTPX and PARSEPX are indators by execution.
Exemple of output (on tladbx30 for the last upgrade, not all lines and not all columns)
BEG_ END_
SNAP SNAP
HASH_VALUE _ID _ID BEG_SNAP_TIME END_SNAP_TIME EXECS GETS GETSPX
1 158795534 4400 4401 24/01/07 06:00:07 24/01/07 07:00:03 3 33 751 11 250,3
2 158795534 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 3 33 751 11 250,3
3 1548175165 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 24 164 935 6 872,3
4 3377200449 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 113 6 364 56,3
5 3250389959 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 113 4 705 41,6
6 158795534 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 6 67 522 11 253,7
7 2374230868 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 751 11 093 14,8
8 3250389959 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 2 501 110 651 44,2
9 3377200449 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 2 501 143 986 57,6
10 2933510729 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 28 115 778 4 134,9
- Retreive new hashvalue and display their load
- Load and Display an Image
- opencv学习－load image and display
- __Virtual function and their implementation
- OpenCV自带例子（一）Load and Display an Image
- Opencv2.4.3_tutorials(学习指南)——load and display an Image
- How to load 3d model from different domain and display it with Three.js
- Protocol Layers and Their Service Models
- override overload hide and their differences
- Common Problems (and Their Solutions) for java
- WORDS AND THEIR STORIES - Swan Song
- Words and Their Stories: Numbers Part Two
- IIS 7.5 deploying errors and their solutions
- cpu-feature-flags-and-their-meanings
- Edge-Avoiding Wavelets and their Applications
- Quartz frameworks and their APIs（用途）
- Windows Users and Their Misconceptions About Linux
- Property list types and their various representations
- 小新语录
- List sessions and some details about them
- 程序员的.net时代
- Complete sessions information
- Quick check of asm disk space
- Retreive new hashvalue and display their load
- DELPHI中ADOQUERY的传参数法与Oracle的GROUP BY子句
- DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])
- atl 中错误支持的使用
- 农民自立自强才是脱贫的关键
- 珍惜...
- 程序员的境界，你属于哪一种呢？
- log4net 日志输出类型列举
- iSCSI target HowTo on Enterprise Linux (RHEL4)
