常用的SQL語句

Disk Intensive SQL

SQL WITH MOST DISK READ NOTES:
Username - Name of the user
Disk Reads - Total number of disk reads for this statement
Executions - Total number of times this statement has been executed
Reads/Execs - Number of reads per execution
SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select
a.USERNAME,

DISK_READS,

EXECUTIONS,

round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS)) "Reads/Execs",

SQL_TEXT
from
dba_users a, v$session, v$sqlarea
where
PARSING_USER_ID=USER_ID
and
ADDRESS=SQL_ADDRESS(+)
and
DISK_READS > 10000
order  
by DISK_READS desc, EXECUTIONS desc

Buffer Intensive SQL

SQL WITH MOST BUFFER SCAN NOTES:
Username - Name of the user
Buffer Gets - Total number of buffer gets for this statement
Executions - Total number of times this statment has been executed
Gets/Execs - Number of buffer gets per execution

SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
select
EXECUTIONS,

BUFFER_GETS,

round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS) / 400,2) "Gets/Execs",

SQL_TEXT
from
v$sqlarea
where  
BUFFER_GETS / decode(EXECUTIONS,0,1, EXECUTIONS) / 400 > 10
order  
by EXECUTIONS desc


Buffer SQL / Most Loads

SQL WITH MOST LOAD NOTES:
Loads - Number of times the cursor has been loaded after the body of the cursor has been aged out of the cache while the text of the SQL statement remained in it, or after the cursor is invalidated
First Load Time - Time at which the cursor was first loaded into the SGA
Sorts - Number of sorts performed by the SQL statement
SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select
LOADS,

FIRST_LOAD_TIME,

SORTS,

SQL_TEXT
from
v$sqlarea
where  
LOADS > 50
order  
by EXECUTIONS desc


Open Cursors By User

OPEN CURSORS BY USER NOTES:
Username - Name of user
SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select
nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')' username,

SQL_TEXT
from
v$open_cursor oc,

v$session s
where
s.SADDR = oc.SADDR
order
by 1


Running Cursors By User

RUNNING CURSORS BY USER NOTES:
Username - Name of user
SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code
select
nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')' username,

SQL_TEXT
from
v$open_cursor oc, v$session s
where
s.SQL_ADDRESS = oc.ADDRESS
and
s.SQL_HASH_VALUE = oc.HASH_VALUE
order
by 1


LOW HIT RATIO Open Cursors

OPEN CURSORS WITH LOW HIT RATIO NOTES:
Username - Name of user
SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select
nvl(se0.USERNAME,'ORACLE PROC')||'('||se0.SID||')' username,

SQL_TEXT
from
v$open_cursor oc0, v$session se0
where
se0.SADDR = oc0.SADDR   
and
se0.USERNAME != 'SYS'
and   
60 < (

select
"Hit Ratio"

from
(
select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",

sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",

sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",

sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads",

(

(sum(decode(NAME, 'consistent gets',value, 0)) +

sum(decode(NAME, 'db block gets',value, 0)) -

sum(decode(NAME, 'physical reads',value, 0)))

/

(sum(decode(NAME, 'consistent gets',value, 0)) +

sum(decode(NAME, 'db block gets',value, 0))) * 100)

"Hit Ratio"
from
v$sesstat ss, v$statname sn, v$session se
where
ss.SID = se.SID
and
sn.STATISTIC# = ss.STATISTIC#
and
VALUE != 0
and
sn.NAME in ('db block gets', 'consistent gets', 'physical reads')
group
by se.USERNAME, se.SID
) XX

where
nvl(se0.USERNAME,'ORACLE PROC')||'('||se0.SID||')' = "User Session")
order
by nvl(se0.USERNAME,'ORACLE'), se0.SID


LOW HIT RATIO Running Cursors

RUNNING CURSORS WITH LOW HIT RATIO NOTES:
Username - Name of user
SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select
nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||'),

SQL_TEXT
from
v$open_cursor oc0, v$session se0
where  
se0.SQL_ADDRESS = oc0.ADDRESS
and
se0.SQL_HASH_VALUE = oc0.HASH_VALUE
and
se0.username != 'SYS'
and   
60 > (

select
"Hit Ratio"

from (
select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",

sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",

sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",

sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads",

(

(sum(decode(NAME, 'consistent gets',value, 0)) +

sum(decode(NAME, 'db block gets',value, 0)) -

sum(decode(NAME, 'physical reads',value, 0)))

/

(sum(decode(NAME, 'consistent gets',value, 0)) +

sum(decode(NAME, 'db block gets',value, 0))) * 100) "Hit Ratio"
from
v$sesstat ss, v$statname sn, v$session se
where
ss.SID = se.SID
and
sn.STATISTIC# = ss.STATISTIC#
and
VALUE != 0
and
sn.NAME in ('db block gets', 'consistent gets', 'physical reads')
group
by se.USERNAME, se.SID
)
        where
nvl(se0.username,'ORACLE PROC')||'('||se0.sid||')' = "User Session")
order
by nvl(se0.username,'ORACLE'), se0.sid


LOW HIT RATIO Objects Access

OBJECTS BEING USED BY USERS WITH LOW HIT RATIO NOTES:
Username - Name of the user
Object Owner - Owner of the object
Object - Name of the object
select
nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||')' username,

OWNER,

OBJECT
from
v$access ac, v$session se0
where
ac.SID    = se0.SID
and  
ac.TYPE   = 'TABLE'
and
60 < (

select
"Hit Ratio"

from
(
select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",

sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",

sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",

sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads",

(

(sum(decode(NAME, 'consistent gets',value, 0)) +

sum(decode(NAME, 'db block gets',value, 0)) -

sum(decode(NAME, 'physical reads',value, 0)))

/

(sum(decode(NAME, 'consistent gets',value, 0)) +

sum(decode(NAME, 'db block gets',value, 0))) * 100) "Hit Ratio"
from
v$sesstat ss,

v$statname sn,

v$session se
where
ss.SID = se.SID
and
sn.STATISTIC# = ss.STATISTIC#
and
VALUE != 0
and
sn.NAME in ('db block gets', 'consistent gets', 'physical reads')
group
by se.USERNAME, se.SID
)

where
nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||')' = "User Session")
order