- 浏览: 2160941 次
- 性别:
- 来自: 合肥
文章分类
- 全部博客 (401)
- Agile (16)
- Apache Commons (3)
- Architecture (8)
- DB.MongoDB (5)
- DB.Mysql (3)
- DB.Oracle (34)
- DirectoryService (1)
- DotNet (1)
- English (3)
- Groovy (0)
- Html (28)
- Java (67)
- Java.Aixs (7)
- Java.Cache (2)
- Java.jPBM (1)
- Java.Resin (6)
- Java.Spring (4)
- Java.Struts2 (5)
- Java.Tomcat (16)
- Javascript (45)
- Javascript.Google Map (2)
- Javascript.Jquery (8)
- Life (15)
- Maven&Ant (4)
- Network (5)
- OS.Linux (45)
- OS.Windows (10)
- OS.Windows.Office (1)
- PlayFramework (15)
- Python (28)
- Reading notes (11)
- Security (13)
- Server.Apache (3)
- Server.Nginx (7)
- Test (6)
- Tool (15)
- Work.Solution (15)
- Other (20)
- SSO&CAS&Identity (13)
最新评论
-
hutuxiansheng123:
防火墙、Iptables、netfilter/iptables、NAT 概述 -
dacoolbaby:
非常棒的正则表达式,非常适用。万分感谢。
用python分析nginx的access日志 -
loot00:
您好! 我也遇到了相同的错误信息。我是用f_link_lob ...
LOB variable no longer valid after subsequent fetch -
feihangchen:
@OnApplicationStop public clas ...
Play framework 1.2.3 Jobs定时任务、异步任务、引导任务、触发任务、关闭任务 -
洞渊龙王:
谢谢了
www.w3.org被qiang导致logback报错:Connect reset
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
Program identifie
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 the EXECUTIONS
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 the
DBMS_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
发表评论
-
linux下手动启动oracle监听
2012-01-16 11:19 1121lsnrctl start [root@vm ~]# fin ... -
更新插入时,用触发器更新【最后更新时间字段】
2011-12-29 15:28 1971CREATE OR REPLACE TRIGGER TR_TB ... -
Toad常用快捷键&常用功能
2011-12-29 08:30 8672F8 调出以前执行的sql命令 F9 执行全部sql Ctrl ... -
oracle 11g 卸载
2011-12-28 09:15 7587由于Toad不支持64位的oracle客户端,因此要卸载已经安 ... -
表A与表B是一对多关系,取表B中任一记录与A关联查询
2011-09-13 14:33 2719通过Group By + min方法取得表B中分组后的多条记录 ... -
超出表空间"users"的空间限额
2011-08-23 09:16 1280这是因为用户被数据库限制了在建表的表空间。 执行一下下面的语 ... -
Clob字段的值部分替换 replace和空判断
2011-08-10 11:36 3881--oracle中可以对Clob字段进行替换操作 U ... -
oracle 循环sql快速插入数据
2011-06-14 09:41 2090具体的insert sql可以通过plsql查询结果上方工具栏 ... -
oracle获取汉字拼音和拼音首字母
2011-06-01 16:23 1455select name,f_getspell(name),f_ ... -
oracle表复制
2011-05-13 15:51 1181select * into dest_tab from r ... -
输出完整的sql语句(带参数)
2011-05-10 15:44 3598#log4jdbc配置,只用在开发环境 jdbc.driver ... -
plsqldev常用技巧
2011-05-09 09:46 36331,PL/SQL美化器 2,F8 SQL执行 3,F5 执行计 ... -
oracle中排序和分页的相互影响
2011-04-14 11:39 1384如下sql: select * from (sele ... -
省市县物化视图
2011-03-23 09:34 978CREATE MATERIALIZED VIEW mv_b ... -
(转)Oracle数据库的自动导出备份脚本(windows环境)
2011-03-11 10:44 1723转自Oracle数据库的自动导出备份脚本(windows环境) ... -
oracle tnsping & ora-12154
2011-01-05 10:06 3615使用命令sqlplus user/pwd@tnsname的时候 ... -
oracle rebuild index and table move and rename
2010-12-30 15:55 6321Oracle里大量删除记录后 ... -
普通视图和物化视图区别,物化视图简单语法(转)
2010-12-20 11:51 2844转自普通视图和物化视 ... -
使用to_char(wm_concat(item))的时候报ORA-64203错误
2010-12-08 09:34 14405使用to_char(wm_concat(item))的时候报O ... -
每月100W数据统计处理解决方案
2010-12-06 15:12 2196任务:每月产生100W条数据(csv格式),要求对这些数据进行 ...
相关推荐
在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 执行计划是数据库性能调整的关键部分,...Oracle 执行计划是 Oracle 数据库性能调整的关键部分,了解这些背景知识和重要概念可以帮助我们更好地进行 SQL 语句性能调整和提高数据库效率。
标题"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语句后确定的数据获取...