CPU爆满问题

查询慢sql

SELECT 
    sql_id,
    executions,
    round(elapsed_time/1000000) total_sec,
    round(elapsed_time/1000000/decode(executions,0,1,executions),4) avg_sec,
    round(cpu_time/1000000) cpu_sec,
    buffer_gets,
    disk_reads,
    parsing_schema_name,
    sql_text
FROM 
    v$sql
WHERE 
    cpu_time/1000000 > 10  -- CPU时间大于5秒
    AND elapsed_time/1000000 > 20  -- 总执行时间大于10秒
ORDER BY 
    cpu_time DESC;

查找该SQL的完整语句

v$sql.sql_text 只存储 SQL 的前 1000 字符，所以长 SQL 会被截断。

SELECT sql_id, sql_fulltext
FROM v$sql
WHERE sql_id = 'xxxxxxxxxxx';

查找执行该SQL的会话

SELECT sid, serial#, username, status, machine, program 
FROM v$session 
WHERE sql_id = 'xxxxxxxxxxxx' AND status = 'ACTIVE';

打印停止语句

SELECT
    'ALTER SYSTEM KILL SESSION ''' ||
    s.sid || ',' || s.serial# ||
    ''' IMMEDIATE;' AS kill_sql
FROM v$session s
WHERE s.sql_id = 'xxxxxxxxxxxx'
  AND s.username IS NOT NULL
  AND s.sid <> SYS_CONTEXT('USERENV','SID');

执行输出的语句

登录到操作系统，进入sqlplus执行

查询完整的sql语句

上面的查询输出的sql语句不完整，可以用这个查询完整语句，最好拼接上SQL_ID

SELECT 
    sql_id, 
    executions, 
    round(elapsed_time/1000000) total_sec, 
    round(elapsed_time/1000000/decode(executions,0,1,executions),4) avg_sec, 
    round(cpu_time/1000000) cpu_sec, 
    buffer_gets, 
    disk_reads, 
    parsing_schema_name, 
    dbms_lob.substr(sql_fulltext, 4000, 1) as full_sql_text
FROM 
    v$sql
WHERE 
    cpu_time/1000000 > 10
    AND elapsed_time/1000000 > 20
    AND SQL_ID = 'xxxxxxxxxxxx'
ORDER BY 
    cpu_time DESC;

表空间不足问题

列出各个表空间的使用情况

SELECT
	b.tablespace_name 表空间,
	ROUND( SUM( b.bytes ) / 1024 / 1024 / 1024, 2 ) 总大小,
	ROUND(
		( SUM( b.bytes ) - SUM( NVL( a.free_bytes, 0 ) ) ) / 1024 / 1024 / 1024,
		2 
	) 已使用,
	ROUND( SUM( NVL( a.free_bytes, 0 ) ) / 1024 / 1024 / 1024, 2 ) 剩余,
	ROUND(
		( SUM( b.bytes ) - SUM( NVL( a.free_bytes, 0 ) ) ) / SUM( b.bytes ) * 100,
		1 
	) 使用率,
	COUNT( b.file_id ) 数据文件数,
CASE
		
	WHEN SUM( CASE WHEN b.autoextensible = 'YES' THEN 1 ELSE 0 END ) > 0 THEN
	'YES' ELSE 'NO' 
	END 可扩展,
	ROUND( SUM( NVL( b.maxbytes, b.bytes ) ) / 1024 / 1024 / 1024, 2 ) 最大可达,
CASE
		
		WHEN ( SUM( b.bytes ) - SUM( NVL( a.free_bytes, 0 ) ) ) / SUM( b.bytes ) >= 0.95 THEN
		'紧急' 
		WHEN ( SUM( b.bytes ) - SUM( NVL( a.free_bytes, 0 ) ) ) / SUM( b.bytes ) >= 0.90 THEN
		'告警' 
		WHEN ( SUM( b.bytes ) - SUM( NVL( a.free_bytes, 0 ) ) ) / SUM( b.bytes ) >= 0.80 THEN
		'注意' ELSE '正常' 
	END 状态 
FROM
	dba_data_files b
	LEFT JOIN ( SELECT file_id, SUM( bytes ) free_bytes FROM dba_free_space GROUP BY file_id ) a ON a.file_id = b.file_id 
GROUP BY
	b.tablespace_name 
ORDER BY
	( SUM( b.bytes ) - SUM( NVL( a.free_bytes, 0 ) ) ) / SUM( b.bytes ) DESC;

查看具体某个表空间的情况

请修改XXXX为具体的表名称

select b.file_id    文件ID,
    b.tablespace_name    表空间,
    b.file_name          物理文件名,
    b.bytes              总字节数,
    (b.bytes-sum(nvl(a.bytes,0)))      已使用,
    sum(nvl(a.bytes,0))                剩余,
    sum(nvl(a.bytes,0))/(b.bytes)*100  剩余百分比
    from dba_free_space a,dba_data_files b
    where a.file_id=b.file_id AND b.tablespace_name = 'XXXX'
    group by b.tablespace_name,b.file_name,b.file_id,b.bytes
    order by b.tablespace_name;

扩容表空间语句

请修改XXXX为具体的表名称,YYYYY修改为dbf文件路径,立刻扩容10G的表空间
AUTOEXTEND ON NEXT 1G MAXSIZE 32G表示后续会1G逐渐自动扩容至最大32G。

ALTER TABLESPACE XXXX
  ADD DATAFILE '/u01/app/oracle/oradata/YYYYY.dbf'
  SIZE 10G
  AUTOEXTEND ON NEXT 1G MAXSIZE 32G;