www.javainfo.com.cn 上干货 欢迎收藏

oracle中查找执行效率低下的SQL

kt431128 发布于 9个月前，共有 0 条评论

v$sqltext：存储的是完整的SQL,SQL被分割

v$sqlarea：存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息（统计）

v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）

 

 

select opname, target, to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, elapsed_seconds elapsed, 

executions execs, buffer_gets/decode(executions, 0, 1, executions) bufgets, module, sql_text

from v$session_longops sl, v$sqlarea sa

where sl.sql_hash_value = sa.hash_value

and upper(substr(module, 1, 4)) <> 'RMAN'

and substr(opname, 1, 4 ) <> 'RMAN'

and sl.start_time > trunc(sysdate)

 

order by start_time;

 

根据sid查找完整sql语句：

select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = '&sid'    )

order by piece asc

 

select a.CPU_TIME,--CPU时间 百万分之一（微秒）

       a.OPTIMIZER_MODE,--优化方式

       a.EXECUTIONS,--执行次数

       a.DISK_READS,--读盘次数

       a.SHARABLE_MEM,--占用shared pool的内存多少

       a.BUFFER_GETS,--读取缓冲区的次数

       a.COMMAND_TYPE,--命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)

       a.SQL_TEXT,--Sql语句

       a.SHARABLE_MEM,

       a.PERSISTENT_MEM,

       a.RUNTIME_MEM,

       a.PARSE_CALLS,

       a.DISK_READS,

       a.DIRECT_WRITES,

       a.CONCURRENCY_WAIT_TIME,

       a.USER_IO_WAIT_TIME

  from SYS.V_$SQLAREA a

 WHERE PARSING_SCHEMA_NAME = 'CHEA_FILL'--表空间

 order by a.CPU_TIME desc

 

引用：http://jenniferok.iteye.com/blog/700985

从V$SQLAREA中查询最占用资源的查询

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 

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 v$sql) 

where exec_rank <=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 v$sql) 

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;

注：以下指标取自Oracle的性能分析工具Statspack所提供的性能分析指标。 

指标名称

指标描述

指标范围

指标单位

1．关于实例效率（Instance Efficiency Percentages）的性能指标

缓冲区未等待率

(Buffer Nowait %)

指在缓冲区中获取Buffer的未等待比率。

该指标的值应接近100%，如果该值较低，则可能要增大buffer cache。

%

Redo缓冲区未等待率

(Redo NoWait %)

指在Redo缓冲区获取Buffer的未等待比率。

该指标的值应接近100%，如果该值较低，则有2种可能的情况：

1.online redo log没有足够的空间；

2.log切换速度较慢。

%

缓冲区命中率

(Buffer Hit %)

指数据块在数据缓冲区中的命中率。

该指标的值通常应在90%以上，否则，需要调整。如果持续小于90%，可能要加大db_cache_size。但有时，缓存命中率低并不意味着cache设置小了，可能是潜在的全表扫描降低了缓存命中率。

%

内存排序率

(In-memory Sort %)

指排序操作在内存中进行的比率。当查询需要排序的时候，数据库会话首先选择在内存中进行排序，当内存大小不足的时候，将使用临时表空间进行磁盘排序，但磁盘排序效率和内存排序效率相差好几个数量级。

该指标的值应接近100%，如果指标的值较低，则表示出现了大量排序时的磁盘I/O操作，可考虑加大sort_area_size参数的值。

%

共享区命中率

(Library Hit%)

该指标主要代表sql在共享区的命中率。

该指标的值通常应在95%以上，否则需要考虑加大共享池（修改shared_pool_size参数值），绑定变量，修改cursor_sharing等参数。

%

软解析的百分比

(Soft Parse %)

该指标是指Oracle对sql的解析过程中，软解析所占的百分比。软解析（soft parse）是指当Oracle接到Client提交的Sql后会首先在共享池（Shared Pool）里面去查找是否有之前已经解析好的与刚接到的这一个Sql完全相同的Sql。当发现有相同的Sql就直接用之前解析好的结果，这就节约了解析时间以及解析时候消耗的CPU资源。

该指标的值通常应在95%以上，如果低于80%，那么就可能sql基本没被重用，sql没有绑定变量，需要考虑绑定变量。

%

闩命中率

 (Latch Hit%)

指获得Latch的次数与请求Latch的次数的比率。

 

该指标的值应接近100%，如果低于99%，可以考虑采取一定的方法来降低对Latch的争用。

%

SQL语句执行与

解析的比率

(Execute to Parse %)

指SQL语句执行与解析的比率。SQL语句一次解析后执行的次数越多，该比率越高，说明SQL语句的重用性很好。

 

该指标的值应尽可能到高，如果过低，可以考虑设置

session_cached_cursors参数。

%

共享池内存使用率

(Memory Usage %)

该指标是指在采集点时刻，共享池（share pool）内存被使用的比例。

这指标的值应保持在75%~90%,如果这个值太低，就浪费内存，如果太高，会使共享池外部的组件老化，如果SQL语句被再次执行，则就会发生硬分析。

%

2．关于等待事件（Wait events）的性能指标

文件分散读取

(db file scattered read(cs))

该等待事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。

如果这个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或没有创建合适的索引。尽管在特定条件下执行全表扫描可能比索引扫描更有效，但如果出现这种等待时，最好检查一下这些全表扫描是否必要。

厘秒

文件顺序读取

(db file sequential read(cs))

该等待事件通常与单个数据块相关的读取操作有关。

如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，或者可能不合适地使用了索引。对于大量事务处理、调整良好的系统，这一数值大多是很正常的，但在某些情况下，它可能暗示着系统中存在问题。应检查索引扫描，以保证每个扫描都是必要的，并检查多表连接的连接顺序。另外DB_CACHE_SIZE也是这些等待出现频率的决定因素。

厘秒

缓冲区忙

(buffer busy(cs))

当一个会话想要访问缓存中的某个块，而这个块正在被其它会话使用时，将会产生该等待事件。这时候，其它会话可能正在从数据文件向缓存中的这个块写入信息，或正在对这个块进行修改。

出现这个等待事件的频度不应大于1%。如果这个等待事件比较显著，则需要根据等待事件发生在缓存中的哪一块（如字段头部、回退段头部块、回退段非头部块、数据块、索引块等），采取相应的优化方法。

 

厘秒

 

(enqueue(cs))

enqueue是一种保护共享资源的锁定机制。该锁定机制保护共享资源，如记录中的数据，以避免两个人在同一时间更新同一数据。enqueue包括一个排队机制，即FIFO(先进先出)排队机制。注意：Oracle的latch机制不是FIFO。Enqueue等待通常指的是ST enqueue、HW enqueue、TX4 enqueue和TM enqueue。

如果enqueue等待事件比较显著，则需要根据enqueue等待类型，采取相应的优化方法。

厘秒

闩释放

(latch free(cs))

该等待事件意味着进程正在等待其他进程已持有的latch。

latch是一种低级排队机制(它们被准确地称为相互排斥机制),用于保护系统全局区域(SGA)中共享内存结构。latch就像是一种快速地被获取和释放的内存锁。latch用于防止共享内存结构被多个用户同时访问。

对于常见的Latch等待通常的解决方法：

1）Share pool latch：在OLTP应用中应该更多的使用绑定变量以减少该latch的等待。

2）Library cache latch：同样的需要通过优化sql语句使用绑定变量减少该latch的等待。

厘秒

日志文件同步

(log file sync(cs))

这个等待事件是指当一个会话完成一个事务（提交或者回滚数据）时，必须等待LGWR进程将会话的redo信息从日志缓冲区写到日志文件后，才能继续执行下去。

这个等待事件的时间过长，可能是因为commit太频繁或者lgwr进程一次写日志的时间太长（可能是因为一次log io size太大），可调整_log_io_size，结合log_buffer,使得(_log_io_size*db_block_size)*n = log_buffer，这样可避免和增大log_buffer引起冲突，或者可以将日志文件存放在高速磁盘上

厘秒

如有疑问 请留言 欢迎提供建议