[转帖]Oracle性能优化之SQL优化_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3856 | 回复: 0   主题: [转帖]Oracle性能优化之SQL优化        下一篇 
derek
注册用户
等级:中校
经验:1550
发帖:209
精华:0
注册:2011-7-21
状态:离线
发送短消息息给derek 加好友    发送短消息息给derek 发消息
发表于: IP:您无权察看 2011-10-27 14:14:39 | [全部帖] [楼主帖] 楼主

1、优化器模式

    Oracle9i有两种优化器模式可以选择:

• Rule-based:
– Uses a ranking system
– Syntax- and data dictionary–driven
• Cost-based:
– Chooses least-cost path
– Statistics-driven


  Rule-based模式满足向后兼容,而Cost-based模式中的成本大部分来自于逻辑读的次数,推荐使用Cost-based模式。

    2、固定optimizer plan

    1)概念

    对于每一个查询,optimizer都会准备一个定义了操作执行顺序和方法的操作树(执行计划),oracle server根据这个执行计划执行语句。通过固定执行计划,可以强制应用通过一种理想的方式访问数据,并且一个稳定的执行计划可以经历数据库的变化而保持不变。固定执行计划通过创建stored outline实现,outline使用cost-based的optimizer,因为其由一系列的hints组成。

    执行计划的固定依赖于当判定一个查询是否存在stored outline时查询语句是否完全一致,与判定shared pool里一个执行计划是否可以重用时的匹配方式是一致的。

    Outline被保存在outln schema中。

    2) 创建stored outline

alter session set CREATE_STORED_OUTLINES = train;
create or replace OUTLINE co_cl_join
FOR CATEGORY train ON
select co.crs_id, ...
from courses co,classes cl
where co.crs_id = cl.crs_id;


  stored outline通过category组织,相同的sql语句可以在多个category同时拥有stored outline,如果categoey没有指定,缺省是default category。

    当CREATE_STORED_OUTLINES等于true或category名时,oracle会为所有被执行的sql语句创建stored outline,也可以通过create outline手工创建。

    3) 使用stored outline

    将USE_STORED_OUTLINES设置为true或category名。

alter session set USE_STORED_OUTLINES = train;


  当为一个查询寻找stored outline时,查询语句与stored outline里的语句必须完全一致,在outline里的hints也必须在查询语句中出现。

    3、private outline

    Private outline是当前保存的stored outline的副本,可以被编辑而不影响正在运行的系统,一个private outline只能被当前session看到,它的数据被保存在当前被解析的schema里。,知道显示的将其公布。

    当USE_PRIVATE_OUTLINES=TRUE时,一个已有outline的sql被提交时,optimizer会检查是否存在private outline,如果不存在,optimizer就不使用optimizer编译语句,而不会去检查公布的stored outline。

    4、在sql中使用hints

Create index gen_idx on customers(cust_gender);
Select
Cust_last_name,cust_street_address,cust_postal_code
From sh.customers where upper(gender)=’M’;
5、EXPLAIN PLAN


  可以不通过tracing,需要建立plan_table表:

Sql>@oracle_home/rdbms/admin/utlxplan;


  建立explain plan:

Explain plan for select last_name from hr.emp;


  查询plan_table中的explain plan,可以直接查询,也可以通过脚本utlxplx.sql(隐藏并行查询信息)、utlxplp.sql(显示并行查询信息)查询。

    6、管理统计信息

    利用analyize命令收集或删除信息。

    参数:

    Compute:统计精确的数据;

    Estimate:估计的统计数据。

    各类统计数据的位置:

    表:dba_tables;

    索引:dba_indexes;

    列:user_tab_col_statistics;

    柱状图(histogram)详细的描述了一个特定列中数据的分布情况,可以通过analyize table ... for columns... 命令创建,保存在dba_histogram/dba_tab_histograms中。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论