Hello World

吞风吻雨葬落日 欺山赶海踏雪径

0%

Oracle性能查询

发表于 更新于 分类于

Oracle性能查询脚本

SQL9I

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
col  MODULE for a10;
col OUTLINE_CATEGORY for a10;
col FIRST_LOAD_TIME for a20
col LAST_LOAD_TIME for a20
col MODULE for a50
col PARSING_USER for a14

select t.HASH_VALUE ,
       t.CHILD_NUMBER child#,
       t.PLAN_HASH_VALUE as plan_value,
       t.EXECUTIONS as exec_cnt,
       t.FIRST_LOAD_TIME,
       t.LAST_LOAD_TIME,
       round(t.DISK_READS/t.EXECUTIONS,2) as disk_per,
       round(t.BUFFER_GETS/t.EXECUTIONS ,2) as buffer_per,
       t.ROWS_PROCESSED,
       round(t.CPU_TIME/t.EXECUTIONS/1000000) as cpu_per,
       round(t.ELAPSED_TIME/t.EXECUTIONS/1000000) as elapse_per,
       t.OUTLINE_CATEGORY,
       u.username as PARSING_USER,
       t.MODULE
  from v$sql t,dba_users u
 where t.HASH_VALUE = &sql_hash_value
 and t.PARSING_USER_ID=u.user_id
 and t.EXECUTIONS >=1
 ;

SQL10

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
col  MODULE for a10;
col OUTLINE_CATEGORY for a16;
col FIRST_LOAD_TIME for a20
col LAST_LOAD_TIME for a20
col MODULE for a50
col sql_id for a14

select t.HASH_VALUE ,
			 t.sql_id ,
       t.CHILD_NUMBER child#,
       t.PLAN_HASH_VALUE as plan_value,
       t.EXECUTIONS as exec_cnt,
       t.FIRST_LOAD_TIME,
       t.LAST_LOAD_TIME,
       round(t.DISK_READS/t.EXECUTIONS,2) as disk_per,
       round(t.BUFFER_GETS/t.EXECUTIONS ,2) as buffer_per,
       t.ROWS_PROCESSED,
       round(t.CPU_TIME/t.EXECUTIONS/1000000) as cpu_per,
       round(t.ELAPSED_TIME/t.EXECUTIONS/1000000) as elapse_per,
       t.OUTLINE_CATEGORY,
       t.PARSING_SCHEMA_NAME,
       t.MODULE
  from v$sql t
 where t.SQL_ID='&sql_id'
 and t.EXECUTIONS >=1
 ;

SQL 11

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
col  MODULE for a10;
col OUTLINE_CATEGORY for a16;
col FIRST_LOAD_TIME for a20
col LAST_LOAD_TIME for a20
col MODULE for a50
col sql_id for a14

select t.HASH_VALUE ,
			 t.sql_id ,
       t.CHILD_NUMBER child#,
       t.PLAN_HASH_VALUE as plan_value,
       t.EXECUTIONS as exec_cnt,
       t.FIRST_LOAD_TIME,
       t.LAST_LOAD_TIME,
       round(t.DISK_READS/t.EXECUTIONS,2) as disk_per,
       round(t.BUFFER_GETS/t.EXECUTIONS ,2) as buffer_per,
       t.ROWS_PROCESSED,
       round(t.CPU_TIME/t.EXECUTIONS/1000000) as cpu_per,
       round(t.ELAPSED_TIME/t.EXECUTIONS/1000000) as elapse_per,
       t.OUTLINE_CATEGORY,
       t.SQL_PLAN_BASELINE,
       t.PARSING_SCHEMA_NAME,
       t.MODULE
  from v$sql t
 where t.SQL_ID='&sql_id'
 and t.EXECUTIONS >=1
 ;