ORACLE 资料整理(二)性能优化

本文深入探讨了数据库性能优化的关键策略,包括如何通过建立索引、分析执行计划和监控SQL语句资源使用情况来提高数据库效率。提供了实用的SQL查询示例,如查找性能瓶颈、监控活跃会话和终止特定连接,帮助读者掌握数据库优化的核心技能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

性能优化。

可以通过建索引,检查where的字段长度设计是否合理引起的,条件字段的长度影响查找的速度。
`通过排查,执行计划分析,来判断。
查询数据库中的连接信息。其中SQL_COMMAND 是可以释放进程的sql语句:

select A.SID,B.SPID,A.SERIAL#,a.lockwait,A.USERNAME,A.OSUSER,a.logon_time,a.last_call_et/3600 LAST_HOUR,A.STATUS,
‘orakill ‘||sid||’ ‘||spid HOST_COMMAND,
‘alter system kill session ‘’’||A.sid||’,’||A.SERIAL#||’’’’ SQL_COMMAND
from vsessionA,Vsession A,VsessionA,VPROCESS B where A.PADDR=B.ADDR AND SID>6 and a.USERNAME =‘SBAP’;
按用户和机器名查询数据库连接情况:
SELECT username, machine, program, status, COUNT (machine) AS
连接数量
FROM v$session
GROUP BY username, machine, program, status
ORDER BY machine;
结束数据库中,某个连接的语句如下:‘50,7773’是上面sql语句中的’||A.sid||’,’||A.SERIAL#||’
alter system kill session ‘50,7773’

SELECT b.sql_text, --content of SQL
a.machine, --which machine run this code
a.username, a.module,
– the method to run this SQL
c.sofar / totalwork * 100,
–conplete percent
c.elapsed_seconds, --run time
c.time_remaining --remain to run time
FROM vsessiona,vsession a, vsessiona,vsqlarea b, v$session_longops c
WHERE a.sql_hash_value = b.hash_value(+) AND a.SID = c.SID(+)
AND a.serial# = c.serial#(+)
–AND a.sid=139

oracle 最近执行过的sql语句

:
SELECT sql_text, last_load_time
FROM v$sql
WHERE last_load_time IS NOT NULL
ORDER BY last_load_time DESC

----监控concurrent 正在执行的sql

SELECT a.sid, a.serial#, b.sql_text
FROM vsessiona,vsession a, vsessiona,vsqltext b
WHERE a.sql_address = b.address
–AND a.sid = <…>
ORDER BY b.piece

select * from vsqlareatwheret.PARSINGSCHEMANAMEin(′用户名′)orderbyt.LASTACTIVETIMEdesc用户名需要大写select∗fromvsqlarea t where t.PARSING_SCHEMA_NAME in (&#x27;用户名&#x27;) order by t.LAST_ACTIVE_TIME desc 用户名需要大写 select * from vsqlareatwheret.PARSINGSCHEMANAMEin()orderbyt.LASTACTIVETIMEdescselectfromvsqlarea t order by t.LAST_ACTIVE_TIME desc
注意 :执行此语句等等一些相关的语句 必须具有DBA 的权限 虽然这条语句很普通 但是需要的时候很管用 能够及时查出一个人执行sql语句情况
-------oracle 查看已经执行过的sql 这些是存在共享池中的 --------->
select * from vsqlareatorderbyt.LASTACTIVETIMEdesc−−−−−−−−−−−查看oracle会话−−−−−−−−−−−−−−−−−−−−−−−−−−−−》select∗fromvsqlarea t order by t.LAST_ACTIVE_TIME desc -----------查看oracle会话----------------------------》 select * from vsqlareatorderbyt.LASTACTIVETIMEdescoracleselectfromvsession t order by t.LAST_ACTIVE_TIME desc

查找前10条性能差的sql语句

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea   
order BY disk_reads DESC )where ROWNUM<10 ;  

说明:
EXECUTIONS表示同一条SQL语句一共执行了多少次,SORTS表示排序的次数,DISK_READS表示物理读的数量。
DISK_READS NUMBER
The sum of the number of disk reads over all child cursors
SORTS NUMBER
Sum of the number of sorts that were done for all the child cursors
EXECUTIONS NUMBER
Total number of executions, totalled over all the child cursors
分析性能差的sql

从VSQLAREA中查询最占用资源的查询selectb.usernameusername,a.diskreadsreads,a.executionsexec,a.diskreads/decode(a.executions,0,1,a.executions)rdsexecratio,a.sqltextStatementfromvSQLAREA中查询最占用资源的查询 select b.username username,a.disk_reads reads, a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio, a.sql_text Statement from vSQLAREAselectb.usernameusername,a.diskreadsreads,a.executionsexec,a.diskreads/decode(a.executions,0,1,a.executions)rdsexecratio,a.sqltextStatementfromvsqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;
用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。

V$SQL是内存共享SQL区域中已经解析的SQL语句。

列出使用频率最高的5个查询


select sql_text,executions
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from vsql)whereexecrank&lt;=5;消耗磁盘读取最多的sqltop5:selectdiskreads,sqltextfrom(selectsqltext,diskreads,denserank()over(orderbydiskreadsdesc)diskreadsrankfromvsql) where exec_rank &lt;=5; 消耗磁盘读取最多的sql top5: select disk_reads,sql_text from (select sql_text,disk_reads, dense_rank() over (order by disk_reads desc) disk_reads_rank from vsql)whereexecrank<=5;sqltop5selectdiskreads,sqltextfrom(selectsqltext,diskreads,denserank()over(orderbydiskreadsdesc)diskreadsrankfromvsql)
where disk_reads_rank <=5;

找出需要大量缓冲读取(逻辑读)操作的查询:
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
dense_rank() over
(order by buffer_gets desc) buffer_gets_rank
from v$sql)
where buffer_gets_rank<=5;

VSQLAREA本视图持续跟踪所有sharedpool中的共享cursor,在sharedpool中的每一条SQL语句都对应一列。本视图在分析SQL语句资源使用方面非常重要。VSQLAREA 本视图持续跟踪所有shared pool中的共享cursor,在shared pool中的每一条SQL语句都对应一列。本视图在分析SQL语句资源使用方面非常重要。 VSQLAREAsharedpoolcursorsharedpoolSQLSQL使VSQLAREA中的信息列
HASH_VALUE:SQL语句的Hash值。
ADDRESS:SQL语句在SGA中的地址。
这两列被用于鉴别SQL语句,有时,两条不同的语句可能hash值相同。这时候,必须连同ADDRESS一同使用来确认SQL语句。
PARSING_USER_ID:为语句解析第一条CURSOR的用户
VERSION_COUNT:语句cursor的数量
KEPT_VERSIONS:
SHARABLE_MEMORY:cursor使用的共享内存总数
PERSISTENT_MEMORY:cursor使用的常驻内存总数
RUNTIME_MEMORY:cursor使用的运行时内存总数。
SQL_TEXT:SQL语句的文本(最大只能保存该语句的前1000个字符)。
MODULE,ACTION:使用了DBMS_APPLICATION_INFO时session解析第一条cursor时的信息
VSQLAREA中的其它常用列SORTS:语句的排序数CPUTIME:语句被解析和执行的CPU时间ELAPSEDTIME:语句被解析和执行的共用时间PARSECALLS:语句的解析调用(软、硬)次数EXECUTIONS:语句的执行次数INVALIDATIONS:语句的cursor失效次数LOADS:语句载入(载出)数量ROWSPROCESSED:语句返回的列总数VSQLAREA中的其它常用列 SORTS: 语句的排序数 CPU_TIME: 语句被解析和执行的CPU时间 ELAPSED_TIME: 语句被解析和执行的共用时间 PARSE_CALLS: 语句的解析调用(软、硬)次数 EXECUTIONS: 语句的执行次数 INVALIDATIONS: 语句的cursor失效次数 LOADS: 语句载入(载出)数量 ROWS_PROCESSED: 语句返回的列总数 VSQLAREASORTS:CPUTIME:CPUELAPSEDTIME:PARSECALLS:()EXECUTIONS:INVALIDATIONS:cursorLOADS:()ROWSPROCESSED:VSQLAREA中的连接列Column View Joined Column(s)
HASH_VALUE, ADDRESS VSESSIONSQLHASHVALUE,SQLADDRESSHASHVALUE,ADDRESSVSESSION SQL_HASH_VALUE, SQL_ADDRESS HASH_VALUE, ADDRESS VSESSIONSQLHASHVALUE,SQLADDRESSHASHVALUE,ADDRESSVSQLTEXT, VSQL,VSQL, VSQL,VOPEN_CURSOR HASH_VALUE, ADDRESS
SQL_TEXT V$DB_OBJECT_CACHE NAME
示例:
1.查看消耗资源最多的SQL:

SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls   
FROM V$SQLAREA   
WHERE buffer_gets > 10000000 OR disk_reads > 1000000   
ORDER BY buffer_gets + 100 * disk_reads DESC;  

2.查看某条SQL语句的资源消耗:

SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls   
FROM V$SQLAREA   
WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');  

查找前10条性能差的sql语句

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea   
order BY disk_reads DESC )where ROWNUM<10 ;  

说明:
EXECUTIONS表示同一条SQL语句一共执行了多少次,SORTS表示排序的次数,DISK_READS表示物理读的数量。
DISK_READS NUMBER
The sum of the number of disk reads over all child cursors
SORTS NUMBER
Sum of the number of sorts that were done for all the child cursors
EXECUTIONS NUMBER
Total number of executions, totalled over all the child cursors

分析性能差的sql

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,    
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,    
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,    
SQL_TEXT    
FROM V$SQLAREA    
WHERE EXECUTIONS>0    
AND BUFFER_GETS >0    
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8  

查询共享池中已经解析过的SQL语句及其相关信息
–EXECUTIONS 所有子游标的执行这条语句次数
–DISK_READS 所有子游标运行这条语句导致的读磁盘次数
–BUFFER_GETS 所有子游标运行这条语句导致的读内存次数
–Hit_radio 命中率
–Reads_per_run 每次执行读写磁盘数
笼统的说EXECUTIONS,BUFFER_GETS,Hit_radio越高表示读内存多,磁盘少是比较理想的状态,因此越高越好
另外两个越高读磁盘次数越多,因此低点好
选出最占用资源的查询

select b.username username,a.disk_reads reads,a.executions exec,   
    a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,   
    a.sql_text statement   
    from v$sqlarea a,dba_users b   
    where a.parsing_user_id=b.user_id   
    and a.disk_reads>100000  

本视图包括Shared pool中SQL语句的完整文本,一条SQL语句可能分成多个块被保存于多个记录内。

V$SQLTEXT中的常用列

HASH_VALUE:SQL语句的Hash值
ADD


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值