- 浏览: 2142306 次
- 性别:
- 来自: 合肥
文章分类
- 全部博客 (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 1087lsnrctl start [root@vm ~]# fin ... -
更新插入时,用触发器更新【最后更新时间字段】
2011-12-29 15:28 1930CREATE OR REPLACE TRIGGER TR_TB ... -
Toad常用快捷键&常用功能
2011-12-29 08:30 8628F8 调出以前执行的sql命令 F9 执行全部sql Ctrl ... -
oracle 11g 卸载
2011-12-28 09:15 7554由于Toad不支持64位的oracle客户端,因此要卸载已经安 ... -
表A与表B是一对多关系,取表B中任一记录与A关联查询
2011-09-13 14:33 2677通过Group By + min方法取得表B中分组后的多条记录 ... -
超出表空间"users"的空间限额
2011-08-23 09:16 1249这是因为用户被数据库限制了在建表的表空间。 执行一下下面的语 ... -
Clob字段的值部分替换 replace和空判断
2011-08-10 11:36 3826--oracle中可以对Clob字段进行替换操作 U ... -
oracle 循环sql快速插入数据
2011-06-14 09:41 2048具体的insert sql可以通过plsql查询结果上方工具栏 ... -
oracle获取汉字拼音和拼音首字母
2011-06-01 16:23 1391select name,f_getspell(name),f_ ... -
oracle表复制
2011-05-13 15:51 1149select * into dest_tab from r ... -
输出完整的sql语句(带参数)
2011-05-10 15:44 3560#log4jdbc配置,只用在开发环境 jdbc.driver ... -
plsqldev常用技巧
2011-05-09 09:46 35921,PL/SQL美化器 2,F8 SQL执行 3,F5 执行计 ... -
oracle中排序和分页的相互影响
2011-04-14 11:39 1348如下sql: select * from (sele ... -
省市县物化视图
2011-03-23 09:34 945CREATE MATERIALIZED VIEW mv_b ... -
(转)Oracle数据库的自动导出备份脚本(windows环境)
2011-03-11 10:44 1688转自Oracle数据库的自动导出备份脚本(windows环境) ... -
oracle tnsping & ora-12154
2011-01-05 10:06 3582使用命令sqlplus user/pwd@tnsname的时候 ... -
oracle rebuild index and table move and rename
2010-12-30 15:55 6255Oracle里大量删除记录后 ... -
普通视图和物化视图区别,物化视图简单语法(转)
2010-12-20 11:51 2803转自普通视图和物化视 ... -
使用to_char(wm_concat(item))的时候报ORA-64203错误
2010-12-08 09:34 14299使用to_char(wm_concat(item))的时候报O ... -
每月100W数据统计处理解决方案
2010-12-06 15:12 2159任务:每月产生100W条数据(csv格式),要求对这些数据进行 ...
相关推荐
Oracle中SQL语句执行效率的查找与解决..
Oracle的SQL语句执行效率问题查找与解决方法文.pdf
主要给大家介绍了oracle查看执行最慢与查询次数最多的sql语句,文中给出完整的示例代码,相信对大家的学习或者工作具有一定的参考价值,有需要的朋友们下面来一起看看吧。
执行路径:ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用:我们发现,单...当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要
如 修改、插入行、删除行、提 交(将表格中的数据改变写入相应的数据库表中)、多功能拷贝、将选择的单元格数据导出为 XLS /CSV /INSERT SQL /HTML /XML 等格式、单条记录操作、能方便地查找\替换单元格中的数据、...
多线程、多连接、智能/彩色SQL编辑器、中英文双语界面并能切换、支持 PL/SQL、批量SQL运行、高效的块操作、方便的表格操作,不需要安装 Oracle 客户端,能运行于所有的主流平台包括 Windows、Linux 、Unix 及 Mac ...
Oracle的SQL语句执行效率问题查找与解决方法。
10.2.5 将子查询因子化应用到PL/SQL中 270 10.3 递归子查询 273 10.3.1 一个CONNECT BY的例子 274 10.3.2 使用RSF的例子 275 10.3.3 RSF的限制条件 276 10.3.4 与CONNECT BY的不同点 276 10.4 复制CONNECT BY...
Oracle SQL Handler,是专为 Oracle 数据库开发人员及操作人员精心打造的一款 Oracle 客户端工具: 超智能的SQL编辑器;超方便的表格操作(可以通过修改表格的单元格数据去更新数据 库表);多种格式的数据导出包括...
当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须 完全相同(包括空格,换行等). 共享的语句必须满足三个...
包括:数据库设计方法.doc,Database Design.ppt,Oracle的SQL语句执行效率问题查找与解决方法.doc,Oracle数据库设计要做到五戒.txt,oracle性能优化总结.doc,SQL语句全优化.doc,数据库设计中的14个实用技巧.doc,优化...
Oracle数据库内部培训资料,oracle专业优化文档,Oracle的SQL语句执行效率问题查找与解决方法文档,oracle性能优化总结文档,数据库设计方法文档,SQL语句全优化文档,数据库设计中的14个实用技巧文档,优化Oracle库表...
Oracle语句优化53个... 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句。 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等)。
ORACLE用户是学习ORACLE数据库中的基础知识,下面就介绍下类系统常用的默认ORACLE用户: 1. sys用户:超级用户,完全是个SYSDBA(管理数据库的人)。拥有dba,sysdba,sysoper等角色或权限。是oracle权限最高的用户,...
包含Oracle的SQL语句执行效率问题查找与解决方法文档,oracle性能优化总结文档,数据库设计方法文档,SQL语句全优化文档,数据库设计中的14个实用技巧文档,优化Oracle库表设计的若干方法等...对于想系统研究数据库和...
另外,在显示结果的同时,SQL的执行计划和统计信息也被显示出来,这样您可以一边看着Oracle的内部优化算法,一边做出高效率的程序来. Object Browser也支持托拽机能.对已经保存好的...
存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 1.9.2 准备 create table t_user ( username varchar2(20), ...