`
kt431128
  • 浏览: 39127 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

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

 
阅读更多

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;
 
v$sqlarea字段定义:http://happyhou.blog.sohu.com/60494432.html
SQL_TEXT VARCHAR2(1000) First thousand characters of the SQL text for the current cursor
SQL_ID VARCHAR2(13) SQL identifier of the parent cursor in the library cache
SHARABLE_MEM NUMBER Amount of shared memory used by a cursor. If multiple child cursors exist, then the sum of all shared memory used by all child cursors.
PERSISTENT_MEM NUMBER Fixed amount of memory used for the lifetime of an open cursor. If multiple child cursors exist, the fixed sum of memory used for the lifetime of all the child cursors.
RUNTIME_MEM NUMBER Fixed amount of memory required during execution of a cursor. If multiple child cursors exist, the fixed sum of all memory required during execution of all the child cursors.
SORTS NUMBER Sum of the number of sorts that were done for all the child cursors
VERSION_COUNT NUMBER Number of child cursors that are present in the cache under this parent
LOADED_VERSIONS NUMBER Number of child cursors that are present in the cache and have their context heap (KGL heap 6) loaded
OPEN_VERSIONS NUMBER The number of child cursors that are currently open under this current parent
USERS_OPENING NUMBER Number of users that have any of the child cursors open
FETCHES NUMBER Number of fetches associated with the SQL statement
EXECUTIONS NUMBER Total number of executions, totalled over all the child cursors
END_OF_FETCH_COUNT NUMBER Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the END_OF_FETCH_COUNT column should be less or equal to the value of theEXECUTIONS column.
USERS_EXECUTING NUMBER Total number of users executing the statement over all child cursors
LOADS NUMBER Number of times the object was loaded or reloaded
FIRST_LOAD_TIME VARCHAR2(19) Timestamp of the parent creation time
INVALIDATIONS NUMBER Total number of invalidations over all the child cursors
PARSE_CALLS NUMBER Sum of all parse calls to all the child cursors under this parent
DISK_READS NUMBER Sum of the number of disk reads over all child cursors
DIRECT_WRITES NUMBER Sum of the number of direct writes over all child cursors
BUFFER_GETS NUMBER Sum of buffer gets over all child cursors
APPLICATION_WAIT_TIME NUMBER Application wait time
CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time
CLUSTER_WAIT_TIME NUMBER Cluster wait time
USER_IO_WAIT_TIME NUMBER User I/O Wait Time
PLSQL_EXEC_TIME NUMBER PL/SQL execution time
JAVA_EXEC_TIME NUMBER Java execution time
ROWS_PROCESSED NUMBER Total number of rows processed on behalf of this SQL statement
COMMAND_TYPE NUMBER Oracle command type definition
OPTIMIZER_MODE VARCHAR2(25) Mode under which the SQL statement was executed
PARSING_USER_ID NUMBER User ID of the user that has parsed the very first cursor under this parent
PARSING_SCHEMA_ID NUMBER Schema ID that was used to parse this child cursor
KEPT_VERSIONS NUMBER Number of child cursors that have been marked to be kept using theDBMS_SHARED_POOL package
ADDRESS RAW(4 | 8) Address of the handle to the parent for this cursor
HASH_VALUE NUMBER Hash value of the parent statement in the library cache
OLD_HASH_VALUE NUMBER Old SQL hash value
MODULE VARCHAR2(64) Contains the name of the module that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_MODULE
MODULE_HASH NUMBER Hash value of the module that is named in the MODULE column
ACTION VARCHAR2(64) Contains the name of the action that was executing at the time that the SQL statement was first parsed as set by calling DBMS_APPLICATION_INFO.SET_ACTION
ACTION_HASH NUMBER Hash value of the action that is named in the ACTION column
SERIALIZABLE_ABORTS NUMBER Number of times the transaction fails to serialize, producing ORA-08177 errors, totalled over all the child cursors
CPU_TIME NUMBER CPU time (in microseconds) used by this cursor for parsing/executing/fetching
ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor for parsing/executing/fetching
IS_OBSOLETE VARCHAR2(1) Indicates whether the cursor has become obsolete (Y ) or not (N ). This can happen if the number of child cursors is too large.
CHILD_LATCH NUMBER Child latch number that is protecting the cursor
PROGRAM_ID NUMBER

Program identifie

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

分享到:
评论

相关推荐

    Oracle中SQL语句执行效率的查找与解决

    在Oracle中,SQL语句执行效率低下通常表现为资源过度消耗,这可能包括CPU时间、内存使用、磁盘I/O等。具体分析可从以下几个方面入手: 1. **响应时间不达标**:当查询响应时间超出预期时,应检查SQL语句是否涉及...

    Oracle的SQL语句执行效率问题查找与解决方法文.pdf

    Oracle SQL 语句执行效率问题查找与解决方法 一、 Oracle SQL 语句执行效率问题查找方法 Oracle 数据库系统中, SQL 语句的执行效率问题是一个非常重要的问题。在实际应用中,我们经常会碰到一些性能不佳的 SQL ...

    oracle查看执行最慢与查询次数最多的sql语句

    在Oracle数据库管理中,了解SQL语句的执行性能和频率对于系统优化至关重要。本文将详细介绍如何查看Oracle数据库中执行最慢和查询次数最多的SQL语句,以帮助DBA(数据库管理员)识别潜在的性能瓶颈。 首先,我们来...

    ORACLE执行计划和SQL调优

    SQL调优涉及分析和改进SQL语句的执行效率,包括优化查询结构、选择合适的索引、调整表结构、使用绑定变量、避免全表扫描等策略。理解执行计划可以帮助识别性能瓶颈,通过调整执行路径、选择最佳访问方法和减少数据...

    ORACLE执行计划和SQL调优.pptx

    在Oracle数据库系统中,SQL语句的执行效率直接影响到整个应用程序的响应速度和资源消耗。以下是对相关概念的详细解释: 1. **ROWID的概念**:ROWID是一个特殊的伪列,它唯一标识表中每一行的位置。尽管它看起来像一...

    ORACLE+SQL效率优化.rar

    首先,了解SQL执行效率的基础在于理解查询执行计划。Oracle使用解析器解析SQL语句,然后生成执行计划,这个计划决定了数据如何被检索和处理。通过分析执行计划,我们可以发现潜在的性能瓶颈,如全表扫描、索引不当或...

    OracleSQL的优化.pdf

    这样可以提高 SQL 语句的执行效率。 Oracle 优化器在任何可能的时候都会对表达式进行评估,并且把特定的语法构造转换成等价的构造。这样可以提高 SQL 语句的执行速度。 常量优化是 SQL 优化的重要部分。常量的...

    Oracle SQL(SQL for Oracle)

    在Oracle SQL中,有以下几个关键知识点: 1. **数据类型**:Oracle支持多种数据类型,包括数值型(如NUMBER)、字符串型(如VARCHAR2和CHAR)、日期/时间型(如DATE)、二进制大对象(BLOB)等。理解这些数据类型...

    oracle遍历数据库查找字符串

    标题"Oracle遍历数据库查找字符串"暗示了一个实用的解决方案,它允许用户快速搜索Oracle数据库中的特定文本字符串,无需手动编写复杂的SQL查询。这通常在数据审计、故障排查或日志分析等场景中非常有用。 描述中...

    Oracle数据库开发工具PL/SQL

    在Oracle数据库的开发中,PL/SQL(Procedural Language/Structured Query Language)是一种不可或缺的语言,它将SQL语句与过程式编程语言元素结合在一起,提供了更丰富的数据操作和管理能力。 PL/SQL是Oracle特有的...

    关于Oracle多表连接,提高效率,性能优化操作

    执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单...当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要

    ORACLE SQL优化工具sqlhc

    首先,我们来看`sqlhc.sql`,这是SQL Health Check的主要脚本,它会执行一系列的检查,分析数据库中的SQL语句,查找可能存在的性能问题。通过运行这个脚本,你可以获得关于SQL执行计划、绑定变量、索引使用情况、...

    Oracle数据库执行计划

    Oracle数据库执行计划是数据库管理系统在处理SQL查询时所采用的一种策略,它决定了如何最有效地从数据库中检索数据。执行计划涉及到一系列的操作,包括数据的存取方法、索引的使用、连接操作以及优化器的选择。 ...

    ORACLE SQL执行优化

    Oracle SQL执行优化是数据库性能调优的关键环节,主要涉及如何高效地执行SQL查询。本文将深入探讨Oracle常用表连接机制和索引机制,以及如何理解执行计划中的关键概念。 首先,我们要了解ROWID的概念。ROWID是...

    Oracle中优化SQL的原则

    1. **完全匹配已检验的语句**:确保执行的SQL语句与已经在共享池中的语句完全一致,这有助于避免不必要的解析和计划生成,从而提高执行效率。 2. **统一变量命名**:保持变量名称的一致性可以减少解析时的混淆,...

    mastering oracle sql

    在Oracle SQL中,你可以进行数据的插入、更新、删除,以及复杂的查询和聚合操作。例如,你可以使用`SELECT`语句来检索数据,`INSERT`来添加新记录,`UPDATE`来修改现有记录,以及`DELETE`来删除不再需要的记录。 ...

    ORACLE培训SQL性能优化.pptx

    绑定变量可以减少解析次数,提高执行效率。例如,在以下语句中,我们可以使用绑定变量来代替硬编码的值: SELECT * FROM USER_FILES WHERE USER_NO = ‘10001234’; 可以改写为: SELECT * FROM USER_FILES ...

    ORACLE执行计划和SQL调优知识概述.pptx

    Oracle执行计划和SQL调优是数据库管理中至关重要的部分,主要涉及如何优化SQL查询以提高数据库性能。在Oracle数据库系统中,理解执行计划是优化SQL性能的基础。 首先,执行计划是Oracle解析SQL语句后确定的数据获取...

Global site tag (gtag.js) - Google Analytics