'PERFORMANCE' is a relative thing; it can be measured but the numbers are only meaningful if you have previous numbers to compare them to.
RE - performance issues with existing queries
1. What are the indications that there is a performance issue? Is it really slow or did a user just report that 'it seems slow'? Does an existing query take substantially longer to run now than it did before?
2. What changes might have occured in the database that might have degraded performance? db upgrade? batch load of data into one of the tables? one or more missing or disabled indexes?
3. Are the statistics out of date? Indexes missing or disabled? Number of records in one ore more tables changed dramatically?
4. Compare the current execution plan with the excecution plan when the query performed satisfactorily (which many shops never bother to create and save). Is the same plan and joining being performed? Are the same indexess being used?
RE - performance issues for new queries
1. Gather info about how many records are in each table being queried. In your case: how many records are there in ITEM_CHART and MATERIAL tables?
2. Gather info about many records from each table are expected to be accessed. In your case: how many records in the MATERIAL table will have the parameter value :B1? 1% of the records? 40% of the records? How many records would you expect to be in the result table? Five? Fifty million?
3. ALWAYS create an execution plan. This tellsl you what Oracle expects to do to get the result. Look for problem areas such as CARTESIAN joins, excessive SORTs, indexes not being used.
4. Test the query on very small data sets to make sure it is performing the way you expect.
5. Once it performs well create another execution plan and save it for use in the future to troubleshoot performance and other issues.
I rarely look or even care about the actual statistics for a query. Oracle will generally do the best job possible based on the information it has available to it. So I focus on making sure I have given Oracle the best info possible and haven't left anything out.
One example:
Rows Execution Plan
0 SELECT STATEMENT MODE: ALL_ROWS
0 MERGE JOIN (CARTESIAN)
0 INDEX MODE: ANALYZED (SKIP SCAN) OF 'IDX_MATERIAL'
(INDEX)
0 BUFFER (SORT)
0 MAT_VIEW ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ITEM_CHART' (MAT_VIEW)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'IDX_ITEM_CHART' (INDEX)
When asking performance-related questions it is helpful if you provide the number of records in each table of the query and what indexes are available on them.
Assuming that you actually have a performance problem the likely reason is the CARTESIAN join that is being used.
Your execution plan shows 'MERGE JOIN (CARTESIAN)' which means that every row of ITEM_CHART is being joined to every row of MATERIAL.
This indicates that one or both of the join columns (ITEM_ID = B.MATL_NO) either do not have the proper index needed or it is not being used.
If one of the indexes is missing you need to add it.
If both indexes exist make sure they are enabled.
One main reason an index may not be used is if the statistics do not exist or are out of date.
分享到:
相关推荐
《成功之路:Oracle 11g学习笔记》专为开发人员编写,先引导开发新手熟悉Oracle环境,然后进入数据库开发,并要掌握项目开发中的一些技巧。开发技巧展示是《成功之路:Oracle 11g学习笔记》的一大特色(这一点很值得...
linux下oracle11g安装笔记,希望能有帮助
该文档是李兴华针对oracle 11g 的课堂笔记,是pdf格式文本。。。。
成功之路:Oracle_11g学习笔记17成功之路:Oracle_11g学习笔记17成功之路:Oracle_11g学习笔记17
资源名称:成功之路:Oracle 11g学习笔记内容简介:本书专为开发人员编写,先引导开发新手熟悉Oracle环境,然后进入数据库开发,并要掌握项目开发中的一些技巧。开发技巧展示是本书的一大特色(这一点很值得资深的...
成功之路+Oracle11g学习笔记 非常好的学习oracle 基础的一本书 适合初学者
带目录版,放心下载,方便观看。成功之路Oracle11g学习笔记
Oracle DB 11g Performance Tuning
oracle 11g performance tuning 英文版
成功之路Oracle11g学习笔记-赵振平 请下载2个附件
Oracle10g学习笔记,Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记
有关oracle数据库基础学习OCP课程的基础051的课程笔记
Oracle 11g Performance Tuning Recipes
Oracle Database 11g Performance Tuning,oracle内部技术文档,正宗
我的Oracle 11g OCP学习笔记,很详细,分享给大家学习!
成功之路 Oracle 11g 中文学习笔记 专为开发人员编写,先引导开发新手熟悉Oracle环境,然后进入数据库开发,并要掌握项目开发的一些技巧。开发技巧展示是本书一大特色(这一点很值得资深的开发人员借鉴)! 本书共24...
oracle11g学习笔记一 1 一、以下是sqlplus常用命令 2 一、Oracle用户管理 4 二、权限和角色 4 权限包含系统权限和对象权限 5 角色 5 权限的传递 6 使用profile 管理用户口令 8 (1)帐号锁定 8 (2)给账户解锁 8 (3)...
Oracle11g备份恢复笔记文档,有详解实例,方便学习。如有差错还请谅解
Oracle 官方培训文档,11g DB性能调优
Oracle 11g学习笔记基础篇(pdf)