oracle的查询性能,ORACLE 性能查询

SQL9I

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

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

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

;