v$sqltext:存储的是完整的SQL,SQL被分割
v$sqlarea:存储的SQL 和一些相关的信息,比如累计的执行次数,逻辑读,物理读等统计信息(统计)
v$sql:内存共享SQL区域中已经解析的SQL语句。(即时)
根据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
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;
列出使用频率最高的5个查询:
from (select sql_text,executions,
rank() over
(order by executions desc) exec_rank
from v$sql)
where exec_rank <=5;
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;
找出需要大量缓冲读取(逻辑读)操作的查询:
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;
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语句执行效率低下通常表现为资源过度消耗,这可能包括CPU时间、内存使用、磁盘I/O等。具体分析可从以下几个方面入手: 1. **响应时间不达标**:当查询响应时间超出预期时,应检查SQL语句是否涉及...
Oracle SQL 语句执行效率问题查找与解决方法 一、 Oracle SQL 语句执行效率问题查找方法 Oracle 数据库系统中, SQL 语句的执行效率问题是一个非常重要的问题。在实际应用中,我们经常会碰到一些性能不佳的 SQL ...
在Oracle数据库管理中,了解SQL语句的执行性能和频率对于系统优化至关重要。本文将详细介绍如何查看Oracle数据库中执行最慢和查询次数最多的SQL语句,以帮助DBA(数据库管理员)识别潜在的性能瓶颈。 首先,我们来...
SQL调优涉及分析和改进SQL语句的执行效率,包括优化查询结构、选择合适的索引、调整表结构、使用绑定变量、避免全表扫描等策略。理解执行计划可以帮助识别性能瓶颈,通过调整执行路径、选择最佳访问方法和减少数据...
在Oracle数据库系统中,SQL语句的执行效率直接影响到整个应用程序的响应速度和资源消耗。以下是对相关概念的详细解释: 1. **ROWID的概念**:ROWID是一个特殊的伪列,它唯一标识表中每一行的位置。尽管它看起来像一...
首先,了解SQL执行效率的基础在于理解查询执行计划。Oracle使用解析器解析SQL语句,然后生成执行计划,这个计划决定了数据如何被检索和处理。通过分析执行计划,我们可以发现潜在的性能瓶颈,如全表扫描、索引不当或...
这样可以提高 SQL 语句的执行效率。 Oracle 优化器在任何可能的时候都会对表达式进行评估,并且把特定的语法构造转换成等价的构造。这样可以提高 SQL 语句的执行速度。 常量优化是 SQL 优化的重要部分。常量的...
在Oracle SQL中,有以下几个关键知识点: 1. **数据类型**:Oracle支持多种数据类型,包括数值型(如NUMBER)、字符串型(如VARCHAR2和CHAR)、日期/时间型(如DATE)、二进制大对象(BLOB)等。理解这些数据类型...
标题"Oracle遍历数据库查找字符串"暗示了一个实用的解决方案,它允许用户快速搜索Oracle数据库中的特定文本字符串,无需手动编写复杂的SQL查询。这通常在数据审计、故障排查或日志分析等场景中非常有用。 描述中...
在Oracle数据库的开发中,PL/SQL(Procedural Language/Structured Query Language)是一种不可或缺的语言,它将SQL语句与过程式编程语言元素结合在一起,提供了更丰富的数据操作和管理能力。 PL/SQL是Oracle特有的...
执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单...当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要
首先,我们来看`sqlhc.sql`,这是SQL Health Check的主要脚本,它会执行一系列的检查,分析数据库中的SQL语句,查找可能存在的性能问题。通过运行这个脚本,你可以获得关于SQL执行计划、绑定变量、索引使用情况、...
Oracle数据库执行计划是数据库管理系统在处理SQL查询时所采用的一种策略,它决定了如何最有效地从数据库中检索数据。执行计划涉及到一系列的操作,包括数据的存取方法、索引的使用、连接操作以及优化器的选择。 ...
Oracle SQL执行优化是数据库性能调优的关键环节,主要涉及如何高效地执行SQL查询。本文将深入探讨Oracle常用表连接机制和索引机制,以及如何理解执行计划中的关键概念。 首先,我们要了解ROWID的概念。ROWID是...
1. **完全匹配已检验的语句**:确保执行的SQL语句与已经在共享池中的语句完全一致,这有助于避免不必要的解析和计划生成,从而提高执行效率。 2. **统一变量命名**:保持变量名称的一致性可以减少解析时的混淆,...
在Oracle SQL中,你可以进行数据的插入、更新、删除,以及复杂的查询和聚合操作。例如,你可以使用`SELECT`语句来检索数据,`INSERT`来添加新记录,`UPDATE`来修改现有记录,以及`DELETE`来删除不再需要的记录。 ...
绑定变量可以减少解析次数,提高执行效率。例如,在以下语句中,我们可以使用绑定变量来代替硬编码的值: SELECT * FROM USER_FILES WHERE USER_NO = ‘10001234’; 可以改写为: SELECT * FROM USER_FILES ...
Oracle执行计划和SQL调优是数据库管理中至关重要的部分,主要涉及如何优化SQL查询以提高数据库性能。在Oracle数据库系统中,理解执行计划是优化SQL性能的基础。 首先,执行计划是Oracle解析SQL语句后确定的数据获取...