没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
美河学习在线 www.eimhe.com
一 SQL TUNING
1 表的常用链接方式
NESTED LOOP: 对于被连接的数据子集较小的情况,嵌套循环连接是较好的选择。在嵌套循环中,外表驱动内
表,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(大于 10000 不合
适),要把返回子集较小的表作为外表(驱动表),而且在内表的连接字段上一定要有索引。
HASH JOIN : 哈希连接是大数据集连接时常用的方式,优化器使用两个表中较小的表,利用连接键在内存中建
立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。
这种方式适用于较小的表完全可以放入内存的情况,这样成本就是访问两个表的成本之和。但是在表很大的情
况下并不能完全放入内存,这时优化器将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临
时段。
MERGE JOIN 通常情况下哈希连接的效果都比排序合并连接要好。然而如果行源已经被排过序,在执行排序合
并连接时不需要再排序了,这时排序归并连接的性能会忧于哈希连接。
2 CBO 和 rule 的区别
Oracle 的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为 RBO)和基于代价的
优化方式(Cost-Based Optimization,简称为 CBO),
RBO 方式:优化器在分析 SQL 语句时,所遵循的是 Oracle 内部预定的一些规则。
CBO 方式:它是看语句的代价(Cost),这里的代价主要指 Cpu 和内存。优化器在判断是否用这种方式时,主要参照的
是表及索引的统计信息
优化模式包括 Rule、Choose、First rows、All rows 四种方式
Rule:基于规则的方式。
Choolse:默认的情况下 Oracle 用的便是这种方式。指的是当一个表或或索引有统计信息,则走 CBO 的方式,如
果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走 RBO 的方式。
First Rows:它与 Choose 方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最
先的几行,从总体上减少了响应时间。
All Rows:也就是我们所说的 Cost 的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体
上提高查询的吞吐量。没有统计信息则走 RBO 的方式。
设定选用哪种优化模式:
A 、 Instance 级别我们可以通过在 initSID.ora 文件中设定
OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS 如果没设定OPTIMIZER_MODE 参数则默认用
的是 Choose 方式。
B、Sessions 级别通过 ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS
来设定。
C、语句级别用 Hint(/*+ ... */)来设定
1、优化模式是 all_rows 的方式
2、表作过 analyze,有统计信息
3、表很小
美河学习在线 www.eimhe.com
3 不借助第三方工具,如何查看 sql 的执行计划
SET AUTOTRACE ON;
exlplain plan for sql
select * from table(dbms_xplan.diskplay)
4 如何定位重要(消耗资源多)的 SQL
1、statspack-- 在你库上业务最忙得时候抓 15 分钟的 report,看里面的 top sql
1 top 找到消耗资源多的 pid
2 确定是 oracle 的应用进程还是后台进程
3 根据 v$session,v$process,v$sqlarea 定位
或者通过以下方式:
1)查看值得怀疑的 SQL
selectsubstr(to_char(s.pct,'99.00'),2)||'%'load,
s.executions executes,
p.sql_text
from(selectaddress,
disk_reads,
executions,
pct,
rank()over(orderbydisk_readsdesc) ranking
from(selectaddress,
disk_reads,
executions,
100*ratio_to_report(disk_reads)over() pct
fromsys.v_$sql
wherecommand_type!=47)
wheredisk_reads>50*executions) s,
sys.v_$sqltext p
wheres.ranking<=5
andp.address=s.address
orderby1, s.address, p.piece;
2)查看消耗内存多的 sql
selectb.username ,a. buffer_gets ,a.executions,
a.disk_reads/decode(a.executions,0,1,a.executions),a.sql_text SQL
fromv$sqlarea a,dba_users b
wherea.parsing_user_id = b.user_id
anda.disk_reads >10000
order bydisk_readsdesc;
美河学习在线 www.eimhe.com
3)查看逻辑读多的 SQL
select*
from(selectbuffer_gets, sql_text
fromv$sqlarea
wherebuffer_gets>500000
orderbybuffer_getsdesc)
whererownum<=30;
4)查看执行次数多的 SQL
selectsql_text, executions
from(selectsql_text, executionsfromv$sqlareaorderbyexecutionsdesc)
whererownum<81;
5)查看读硬盘多的 SQL
selectsql_text, disk_reads
from(selectsql_text, disk_readsfromv$sqlareaorderbydisk_readsdesc)
whererownum<21;
6)查看排序多的 SQL
selectsql_text, sorts
from(selectsql_text, sortsfromv$sqlareaorderbysortsdesc)
whererownum<21;
7)分析的次数太多,执行的次数太少,要用绑变量的方法来写 sql
setpagesize600;
setlinesize120;
selectsubstr(sql_text,1,80) "sql",count(*),sum(executions) "totexecs"
fromv$sqlarea
whereexecutions<5
groupbysubstr(sql_text,1,80)
havingcount(*)>30
orderby2;
8)游标的观察
setpages300;
selectsum(a.value), b.name
fromv$sesstat a, v$statname b
wherea.statistic#=b.statistic#
andb.name='opened cursors current'
groupbyb.name;
selectcount(0)fromv$open_cursor;
美河学习在线 www.eimhe.com
selectuser_name, sql_text,count(0)
fromv$open_cursor
groupbyuser_name, sql_text
havingcount(0)>30;
9 查看当前用户&username 执行的 SQL
selectsql_text
fromv$sqltext_with_newlines
where(hash_value, address)in
(selectsql_hash_value, sql_address
fromv$session
whereusername='&username')
orderbyaddress, piece;
网友答案:select sql_text
from v$sql
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);
5 如何跟踪某个 session 的 sql
1) oracle 自带的 sql trace 程序可以跟踪本地 session
sys: alter system set sql_trace = true;对所有会话跟踪
schema: alter session set sql_trace = true; 对某个 session 会话跟踪 sql 语句
用 tkprof 来格式化跟踪文件输出
tkprof 输出内容包括 1 sql 语句 2 统计信息 3explain table 执行计划
2 )基于 DBMS_MONITOR 包来跟踪会话,这种情况一般是
通过该程序包可以跟踪从客户机到中间层、再到后端数据库的任何用户会话,从而可以较为容易的标识创建大量工
作量的特定用户。
会话:基于会话 ID 和序列号 DBMS_MONITOR.session_trace_enable(sid,serial#,true);
DBMS_MONITOR.session_trace_enable(sid,serial#,fal
se);
客户端标识符:允许跨越多个会话设置跟踪,基于登录 ID 指定终端用户。
客户端设置的参数:DBMS_SESSION.SET_IDENTIFIER 过程设置该值
运行语句:dbms_monitor.client_id_trace_enable('identifier',true,false);
dbms_monitor.client_id_trace_disable('identifier');
实例:基于实例名指定给定的实例
dbms_monitor.database_trace_enable(instance_name=>'orcl');
服务名:指定一组相关的应用程序,使用 DBMS_SERVICE.CREATE_SERVICE 过程设置该值,
dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl1',module_name=>'salary_update',action_na
me=>'insert_item');
dbms_monitor.serv_mod_act_trace_disable(service_name=>'orcl1',module_name=>'salary_update',action_n
ame=>'insert_item');
模块名:开发人员在其应用程序代码中使用 DBMS_APPLICATION_INFO.SET_MODULE 过程设置该值
操作名:开发人员在其应用程序中使用 DBMS_APPLICATION_INFO.SET_ACTION 过程设置该值 trcsess 提取跟踪文
剩余18页未读,继续阅读
资源评论
天神宙斯
- 粉丝: 0
- 资源: 7
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功