oracle相关问题定位

慢sql查询

当发现服务器资源告警，系统慢，可查找当前阻塞的的慢sql，必要时可以通过杀进程临时处理

## 查询语句

select *

from (select sa.SQL_TEXT,

sa.SQL_FULLTEXT,

b.sid,

b.serial#,

sa.EXECUTIONS 执行次数,

round(sa.ELAPSED_TIME / 1000000, 2) 总执行时间,

round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) 平均执行时间 ,

sa.COMMAND_TYPE,

sa.PARSING_USER_ID 用户ID,

u.username 用户名,

sa.HASH_VALUE

from v$sqlarea sa

left join all_users u

on sa.PARSING_USER_ID = u.user_id

left join v$session b

on sa.ADDRESS = b.SQL_ADDRESS

where sa.EXECUTIONS > 10

and u.username = 'WDSTUDY'

order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)

order by 平均执行时间 desc

## 临时处理操作（杀进程）

alter system kill session '29,3831'

其中29为sid,3831为serial#

锁表查询

## 查询语句

select b.username, b.sid, b.serial#, d.object_name, logon_time

from v$locked_object a, v$session b, dba_objects d

where a.session_id = b.sid

and d.object_id = a.object_id

order by b.logon_time;

## 临时处理操作（杀进程）

alter system kill session '299,32831'

其中299为sid,32831为serial#

sql cpu 使用查询

SELECT ASH.INST_ID,

ASH.SQL_ID,

(SELECT VS.SQL_TEXT

FROM GV$SQLAREA VS

WHERE VS.SQL_ID = ASH.SQL_ID

AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

ASH.SESSION_INFO,

COUNTS,

PCTLOAD * 100 || '%' PCTLOAD,

ASH.sid,

ASH.serial#

FROM (SELECT ASH.INST_ID,

ASH.SQL_ID,

b.sid,

b.serial#,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||

ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||

ASH.SESSION_TYPE) SESSION_INFO,

COUNT(*) COUNTS,

ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,

DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER

FROM GV$ACTIVE_SESSION_HISTORY ASH, v$sqlarea sa, v$session b

WHERE ASH.SESSION_TYPE <> 'BACKGROUND'

AND ASH.SESSION_STATE = 'ON CPU'

and sa.SQL_ID = ASH.SQL_ID

and sa.ADDRESS = b.SQL_ADDRESS

AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)

GROUP BY ASH.INST_ID,

ASH.SQL_ID,

b.sid,

b.serial#,

ASH.SQL_CHILD_NUMBER,

ASH.SQL_OPNAME,

(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||

ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||

ASH.SESSION_TYPE

)) ASH

WHERE RANK_ORDER <= 10

ORDER BY COUNTS DESC;

## 临时处理操作（杀进程）

alter system kill session '299,32831'

其中299为sid,32831为serial#

正在执行sql查询

SELECT b.sid oracleID,

b.username 登录Oracle用户名,

b.serial#,

c.EXECUTIONS 执行次数,

round(c.ELAPSED_TIME / 1000000, 2) 总执行时间,

round(c.ELAPSED_TIME / 1000000 / c.EXECUTIONS, 2) 平均执行时间 ,

spid 操作系统ID,

paddr,

sql_text 正在执行的SQL,

b.machine 计算机名

FROM v$process a, v$session b, v$sqlarea c

WHERE a.addr = b.paddr

AND b.sql_hash_value = c.hash_value

## 临时处理操作（杀进程）

检查表空间情况

select * from (

Select a.tablespace_name,

to_char(a.bytes/1024/1024,'99,999.999') total_bytes,

to_char(b.bytes/1024/1024,'99,999.999') free_bytes,

to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,

to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%'use

from (select tablespace_name,

sum(bytes) bytes

from dba_data_files

group by tablespace_name) a,

(select tablespace_name,

sum(bytes) bytes

from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name

union all

select c.tablespace_name,

to_char(c.bytes/1024/1024,'99,999.999') total_bytes,

to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,

to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,

to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use

from

(select tablespace_name,sum(bytes) bytes

from dba_temp_files group by tablespace_name) c,

(select tablespace_name,sum(bytes_cached) bytes_used

from v$temp_extent_pool group by tablespace_name) d

where c.tablespace_name = d.tablespace_name

)

order by tablespace_name;

## 解决表空间数据文件不足处理

临时表空间和表空间各增加30G

ALTER TABLESPACE MZCSTUDYTEMP_TBSP_TEMP ADD TEMPFILE '/opt/oracle/oradata/orcl/MZCSTUDYTEMP_TBSP_TEMP_add1.dbf' SIZE 10720M AUTOEXTEND OFF

ALTER TABLESPACE wdstudy_tbsp ADD DATAFILE '/opt/oracle/oradataadd/wstudy_tbsp_add1.dbf' SIZE 30720M AUTOEXTEND OFF

TextHTMLCSSJavascriptCC++C#JavaPythonSqlSwiftJSON

导出Oracle AWR

Oracle AWR（Automatic Workload Repository ⾃动⼯作负载库）这个⼯具可以⾃动采集Oracle运⾏

中的负载信息，并⽣成与性能相关的统计数据，通过数据分析帮助我们排查SQL问题。

# sqlplus 连接数据库

sqlplus /nolog

SQL> connect /as sysdba;

SQL> @?/rdbms/admin/awrrpt.sql

# 输⼊上⾯后弹窗命令窗⼝，要求输⼊导出类型，这⾥选择html

report_type: html

# 要求输⼊多少天的快照，默认只有8天，输⼊1，返回⼀天的数据

num_days: 1

上⾯会返回⼀天的每个⼩时的数据报告，每条记录前都有⼀个Snap Id,这⾥要求输⼊开始Id

begin_snap: # 我们找到出现系统慢的时间段的Id，然后输⼊开始的Id

end_snap: # 接着要求输⼊结束的Id，输⼊即可。

# 要求输⼊导出的⽂件名

report_name: # ⾃定义即可

# ⾃动导出成⽂件即可。

 默认导出目录在/home/oracle下