1: SGA

select (case when pool is null then 'Buffer cache'
else pool end) "SGA area",round(sum(sga.megs),1) megs
from (
select pool,name,sum(bytes)/(1000*1024) megs
from V$SGASTAT
group by pool,name
) sga
where pool is not null
group by pool

union

select name "SGA area",round(sum(bytes)/(1000*1024),1) megs
from V$SGASTAT
where pool is null
group by name

B: Shared pool

SELECT name,bytes / (1024 * 1024) megs
FROM v$sgastat
WHERE pool = 'shared pool'
AND name IN ('sql area',
'dictionary cache',
'library cache',
'free memory',
'miscellaneous'
)
ORDER BY megs desc

2: Instance

SELECT
instance_number,instance_name,host_name,version,startup_time,
status,parallel,thread#,archiver,log_switch_wait,
logins,shutdown_pending,database_status,instance_role
FROM v$instance

3: database

SELECT
name,created,log_mode,checkpoint_change#,
archive_change#,controlfile_type,controlfile_created,controlfile_sequence#,controlfile_change#,
controlfile_time,open_resetlogs,version_time,open_mode
FROM v$DATABASE

4: Options

select parameter "Parameter",value "Value" from v$option

5: Parameters select name "Options",value "Setting",isdefault "Default",description "Description",
isses_modifiable "Session modified"
from v$parameter

6:Toad Session SQL

/* Formatted by PL/Formatter v2.2.0.4 on 2005/03/15 15:18 */
SELECT machine "Machine",
process "Process",
osuser "OS user",
username "User name",
schemaname "Schema name",
status "Status",
lockwait "Lockwait",
sid "SID",
serial# "Serial",
module "Module",
action "Action"
FROM v$session
WHERE schemaname <> 'SYS'
ORDER BY machine desc

7: Top Sessions

SELECT s.username, s.sid, s.serial#, upper(decode(s.command, 1, 'Create Table',
2, 'Insert', 3, 'Select', 4, 'Create Cluster', 5, 'Alter Cluster', 6,
'Update', 7, 'Delete', 8, 'Drop Cluster', 9, 'Create Index', 10,
'Drop INdex', 11, 'ALter Index', 12, 'Drop Table', 13, 'Create Sequence',
14, 'ALter Sequence', 15, 'ALter Table', 16, 'Drop Sequence', 17,
'Grant', 18, 'Revoke', 19, 'Create Synonym', 20, 'Drop Synonym', 21,
'Create View', 22, 'Drop View', 23, 'Validate Index', 24,
'Create Procedure', 25, 'Alter Procedure', 26, 'Lock Table', 27,
'No Operation', 28, 'Rename', 29, 'Comment', 30, 'Audit', 31, 'NoAudit',
32, 'Create Database Link', 33, 'Drop Database Link', 34,
'Create Database', 35, 'Alter Database', 36, 'Create Rollback Segment',
37, 'Alter Rollback Segment', 38, 'Drop Rollback Segment', 39,
'Create Tablespace', 40, 'ALter Tablespace', 41, 'Drop TAblespace', 42,
'ALter Sessions', 43, 'ALter User', 44, 'Commit', 45, 'Rollback', 46,
'Savepoint', 47, 'PL/SQL Execute', 48, 'Set Transaction', 49,
'ALter System switch Log', 50, 'Explain Plan', 51, 'Create User', 52,
'Create Role', 53, 'Drop User', 54, 'Drop Role', 55, 'Set Role', 56,
'Create Schema', 57, 'Create Control File', 58, 'ALter Tracing', 59,
'Create Trigger', 60, 'Alter Trigger', 61, 'Drop Trigger', 62,
'Analyze Table', 63, 'Analyze Index', 64, 'Analyze Cluster', 65,
'Create Profile', 66, 'Drop Profile', 67, 'Alter Profile', 68,
'Drop Procedure', 69, 'Drop Procedure', 70, 'Alter Resource Cost', 71,
'Create Snapshot Log', 72, 'ALter Snapshot LOg', 73, 'Drop Snapshot Log',
74, 'Create Snapshot', 75, 'Alter Snapshot', 76, 'Drop Snapshot', 79,
'ALter Role', 85, 'Truncate Table', 86, 'Truncate Cluster', 88,
'Alter View', 91, 'Create Function', 92, 'ALter Function', 93,
'Drop FUnction', 94, 'Create Package', 95, 'ALter Package', 96,
'Drop Package', 97, 'Create Package Body', 98, 'ALter Package Body', 99,
'Drop Package Body')) command, t.value cpu_time,
i.physical_reads phy_reads, i.block_gets blk_gets, s.status, s.lockwait,
q.sql_text, s.sql_hash_value
FROM v$session s, v$sesstat t, v$statname n, v$sess_io i, v$sqlarea q
WHERE s.username IS NOT NULL
AND (t.value > 100
OR i.physical_reads > 100
OR i.block_gets > 100)
AND s.sid = t.sid
AND t.statistic# = n.statistic#
AND n.name = 'CPU used by this session'
AND s.sid = i.sid
AND s.sql_address = q.address
AND s.sql_hash_value = q.hash_value
ORDER BY t.value DESC

8: RBS Active:

select n.usn,n.name,s.tablespace_name,l.status,s.initial_extent,s.next_extent,
l.extents,s.BYTES,l.RSSIZE,l.xacts "Active Trans"
from V$ROLLNAME N ,V$ROLLSTAT L,dba_segments S
where n.usn=l.usn
and s.segment_name=n.name
and s.SEGMENT_TYPE='ROLLBACK'

9: Space Used

SELECT s.tablespace_name "Tablespace",
-- "Megs Alloc","Megs Free","Megs Used","Pct Free","Pct Used",
df.total "Megs Alloc", fs.bytes "Megs Free",df.total-fs.bytes "Megs Used",
(fs.bytes/df.total)*100 "Pct Free %" ,(100-(fs.bytes/df.total)*100) "Pct Used",
s.initial_extent/(1024*1024) "Init Ext",nvl(s.next_extent/(1024*1024),0) "Next Ext",s.min_extents/(1024*1024) "Min Ext",s.max_extents/(1024*1024) "Max Ext",
s.status "Status",s.contents "Contents",s.allocation_type "Allocation_type"
from DBA_TABLESPACES S,
( select tablespace_name,sum(bytes/(1024*1024)) bytes
from DBA_FREE_SPACE
group by tablespace_name ) FS,
( select tablespace_name,SUM(BYTES/(1024*1024)) total
from DBA_DATA_FILES
group by tablespace_name) DF
where s.tablespace_name=Fs.tablespace_name
and s.tablespace_name=df.tablespace_name

10: Datafile I/O (Cannot Get totalIO and total block IO)

select df.tablespace_name "Tablespace",df.file_name "Filename",ft.phyrds "Reads",

ft.PHYBLKRD "Block Read",
ft.phywrts "Write",ft.PHYBLKWRT "Block write"
from dba_data_files df,V$FILESTAT ft
where df.file_id=ft.file#

[@more@]

DBA 常用的10個SQL

相關文章