Oracle常用sql(第一部分)
查看表空间使用情况
1 | SELECT |
查询数据库高速缓冲区命中率
相关计算公式：1-(‘physical reads cache’/(‘db block gets from cache’ + ‘consistent gets from cache’))1
2select name, value from v$sysstat
where name in ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');
查询命中率比较低的Sql(低效Sql)
1 | SELECT |
找出总消耗时间最多的前10条语句
1 | SELECT sql_id,child_number,sql_text, elapsed_time |
按等待时间排序等待事件
1 | SELECT wait_class, event, total_waits AS waits, |
查询会话历史表中锁等待的Sql和对象
(最近一个小时左右), 可以将v$active_session_history替换为dba_hist_active_sess_history以显示更长时间范围内的等待对象。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21WITH ash_query AS (
SELECT substr(event,6,2) lock_type,program,
h.module, h.action, object_name,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
OVER (), 2) pct_of_time
FROM v$active_session_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY substr(event,6,2) ,program, h.module, h.action,
object_name, sql_text, username)
SELECT lock_type,module, username, object_name, time_ms,pct_of_time,
sql_text
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;
查询遭遇最多行级锁等待的数据库对象
1 | SELECT object_name, VALUE row_lock_waits, |
查询锁的持有者和等待获取锁的会话
1 | WITH sessions AS |
查询消耗PGA内存最多的5个进程
查询消耗PGA内存最多的5个进程和当前正在执行的Sql1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22WITH pga AS
(SELECT sid,
ROUND(SUM(CASE name WHEN 'session pga memory'
THEN VALUE / 1048576 END),2) pga_memory_mb,
ROUND(SUM(CASE name WHEN 'session pga memory max'
THEN VALUE / 1048576 END),2) max_pga_memory_mb
FROM v$sesstat
JOIN v$statname USING (statistic#)
WHERE name IN ('session pga memory','session pga memory max' )
GROUP BY sid)
SELECT sid, username,s.module,
pga_memory_mb,
max_pga_memory_mb, substr(sql_text,1,70) sql_text
FROM v$session s
JOIN (SELECT sid, pga_memory_mb, max_pga_memory_mb,
RANK() OVER (ORDER BY pga_memory_mb DESC) pga_ranking
FROM pga)
USING (sid)
LEFT OUTER JOIN v$sql sql
ON (s.sql_id=sql.sql_id and s.sql_child_number=sql.child_number)
WHERE pga_ranking <=5
ORDER BY pga_ranking
合并PGA+SGA的内存顾问适用于11g
Combined (PGA+SGA) memory advice report for 11g1
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
28
29
30WITH db_cache_times AS
(SELECT current_size current_cache_mb,
size_for_estimate target_cache_mb,
(estd_physical_read_time - current_time)
cache_secs_delta
FROM v$db_cache_advice,
(SELECT size_for_estimate current_size,
estd_physical_read_time current_time
FROM v$db_cache_advice
WHERE size_factor = 1
AND name = 'DEFAULT' AND block_size = 8192)
WHERE name = 'DEFAULT' AND block_size = 8192),
pga_times AS
(SELECT current_size / 1048576 current_pga_mb,
pga_target_for_estimate / 1048576 target_pga_mb,
estd_time-base_time pga_secs_delta
FROM v$pga_target_advice ,
(SELECT pga_target_for_estimate current_size,
estd_time base_time
FROM v$pga_target_advice
WHERE pga_target_factor = 1))
SELECT current_cache_mb||'MB->'||target_cache_mb||'MB' Buffer_cache,
current_pga_mb||'->'||target_pga_mb||'MB' PGA,
pga_secs_delta,cache_secs_delta,
(pga_secs_delta+cache_secs_delta) total_secs_delta
FROM db_cache_times d,pga_times p
WHERE (target_pga_mb+target_cache_mb)
<=(current_pga_mb+current_cache_mb)
AND (pga_secs_delta+cache_secs_delta) <0
ORDER BY (pga_secs_delta+cache_secs_delta);
执行结果
BUFFER_CACHE PGA PGA_SECS_DELTA CACHE_SECS_DELTA TOTAL_SECS_DELTA
1760MB->2288MB 1120->560MB 0 -2008 -2008
1760MB->2112MB 1120->560MB 0 -1612 -1612
1760MB->1936MB 1120->560MB 0 -901 -901
1760MB->1936MB 1120->840MB 0 -901 -901
结果分析：给高速缓存区增加528MB(2288MB-1760MB)内存可以节约2008秒的时间。减少PGA内存560MB(1120MB-560MB)未受到影响。
查询显示PGA内存管理方式
1 | --alter system set workarea_size_policy=manual; --将pga内存管理设置为手动管理 |
NAME TYPE VALUE
workarea_size_policy string AUTO
值为AUTO时表示自动管理，为MANUAL是表示手动管理
查询数据库中子表上没有索引的外键
1 | SELECT c.owner, |
