用于查看哪些实例的哪些操作使用了大量的临时段
SELECT to_number(decode(SID NULL SID)) sid
operation_type OPERATIONtrunc(EXPECTED_SIZE/) ESIZE
trunc(ACTUAL_MEM_USED/) MEM trunc(MAX_MEM_USED/) MAX MEM
NUMBER_PASSES PASS trunc(TEMPSEG_SIZE/) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY ;
查询有热块查询的SQL语句
select hash_value
from v$sqltext a
(select distinct aownerasegment_nameasegment_type from
dba_extents a
(select dbarfildbablk
from (select dbarfildbablk
from x$bh order by tch desc) where rownum < ) b
where aRELATIVE_FNO = bdbarfil
and aBLOCK_ID <= bdbablk and ablock_id + ablocks > bdbablk) b
where asql_text like %||bsegment_name||% and bsegment_type = TABLE
order byahash_valueaaddressapiece;
全表扫描
select opnametargetbnum_rowsbtablespace_namecount(target) from v$session_longops aall_all_tables b
where aTARGET=bowner||||btable_name
having count(target)> group by opnametargetbnum_rowsbtablespace_name
查看磁盘排序和缓存排序次数
selectto_char(snsnap_timeyyyymmdd hh) time_
avg(newmenvalue oldmenvalue) sorts_memeory
avg(newdskvalue olddskvalue) disk_sort
fromstats$sysstat oldmen
stats$sysstat newmen
stats$sysstat newdsk
stats$sysstat olddsk
stats$snapshot sn
where newdsksnap_id=snsnap_id
and olddsksnap_id=snsnap_id
and newmensnap_id=snsnap_id
and newdsksnap_id=snsnap_id
and oldmenname=sorts (memory)
and newmenname=sorts (memory)
and olddskname=sorts (disk)
and newdskname=sorts (disk)
group byto_char(snsnap_timeyyyymmdd hh)
执行最慢的前个SQL？？？
select * from (
select
to_char(snap_timedd Mon HH:mi:ss) mydate
executions exec
loadsloads
parse_callsparse
disk_reads reads
buffer_getsgets
rows_processed rows_proc
sortssorts
sql_text
hash_value
from
perfstatstats$sql_summary sql
perfstatstats$snapshot sn
where
sqlsnap_id >
(select min(snap_id) min_snap
from stats$snapshot where snap_time > sysdate$days_back)
and
sqlsnap_id = snsnap_id
order by $sortskey desc)tt where rownum<;
SQL缓存池的命中率查询（pinhitratiogethitratio应该大于%以上）
select namespacegethitratiopinhitratioreloadsinvalidations
from v$librarycache
where namespace in (SQL AREATABLE/PROCEDUREBODYTRIGGER)
数据库的常规参数我就不说了除了V$parameter中的常规参数外ORACLE还有大量的隐含参数下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述
SELECT NAME
VALUE
decode(isdefault TRUEYN) as Default
decode(ISEMTRUEYN) as SesMod
decode(ISYMIMMEDIATE I
DEFERRED D
FALSE N) as SysMod
decode(IMODMODIFIEDU
SYS_MODIFIEDSN) as Modified
decode(IADJTRUEYN) as Adjusted
description
FROM ( GV$SYSTEM_PARAMETER
SELECT xinst_id as instance
xindx+
ksppinm as NAME
ksppity
ksppstvl as VALUE
ksppstdf as isdefault
decode(bitand(ksppiflg/)TRUEFALSE) as ISEM
decode(bitand(ksppiflg/)
IMMEDIATEDEFERREDFALSE) as ISYM
decode(bitand(ksppstvf)MODIFIEDFALSE) as IMOD
decode(bitand(ksppstvf)TRUEFALSE) as IADJ
ksppdesc as DESCRIPTION
FROM x$ksppi x
x$ksppsv y
WHERE xindx = yindx
AND substr(ksppinm) = _
AND xinst_id = USERENV(Instance)
)
ORDER BY NAME
想知道现在哪个用户正在利用临时段吗？这个语句将告诉你哪个用户正在利用临时段
SELECT btablespace bsegfile# bsegblk# bblocks asid aserial#
ausername aosuser astatuscsql_text
FROM v$session av$sort_usage b v$sql c
WHERE asaddr = bsession_addr
AND asql_address = caddress(+)
ORDER BY btablespace bsegfile# bsegblk# bblocks;
查看磁盘碎片
select tablespace_namesqrt(max(blocks)/sum(blocks))*
(/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by
查看表空间的名称及大小
select ttablespace_name round(sum(bytes/(*))) ts_size
from dba_tablespaces t dba_data_files d
where ttablespace_name = dtablespace_name
group by ttablespace_name;
查看表空间物理文件的名称及大小
select tablespace_name file_id file_name
round(bytes/(*)) total_space
from dba_data_files
order by tablespace_name;
查看回滚段名称及大小
select segment_name tablespace_name rstatus
(initial_extent/) InitialExtent(next_extent/) NextExtent
max_extents vcurext CurExtent
From dba_rollback_segs r v$rollstat v
Where rsegment_id = vusn(+)
order by segment_name
耗资源的进程（top session)
select sschemaname schema_namedecode(sign( command)
to_char(command) Action Code # || to_char(command) ) actionstatus
session_status sosuser os_user_name ssid pspid sserial# serial_num
nvl(susername [Oracle process]) user_name sterminal terminal
sprogram program stvalue criteria_valuefrom v$sesstat st v$session s v$process p
where stsid = ssid and ststatistic# = to_number() and (ALL = ALL
or sstatus = ALL) and paddr = spaddr order by stvalue descpspid asc susername asc sosuser asc
查看锁（lock）情况
select /*+ RULE */ lsosuser os_user_name lsusername user_name
decode(lstype RW Row wait enqueue lock TM DML enqueue lock TX
Transaction enqueue lock UL User supplied lock) lock_type
oobject_name object decode(lslmode null Row Share
Row Exclusive Share Share Row Exclusive Exclusive null)
lock_modeoowner lssid lsserial# serial_num lsid lsid
from sysdba_objects o ( select sosuser susername ltype
llmode ssid sserial# lid lid from v$session s
v$lock l where ssid = lsid ) lswhere oobject_id = lsid andoowner
<> SYS order by oowner oobject_name
查看低效率的SQL语句
SELECT EXECUTIONS DISK_READS BUFFER_GETS
ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS) Hit_radio
ROUND(DISK_READS/EXECUTIONS) Reads_per_run
SQL_TEXT
FROM V$SQLAREA
WHEREEXECUTIONS>
AND BUFFER_GETS >
AND (BUFFER_GETSDISK_READS)/BUFFER_GETS <
ORDER BY DESC
