Oracle优化整理2

最新推荐文章于 2024-09-26 11:01:02 发布

hooligan000112003

最新推荐文章于 2024-09-26 11:01:02 发布

阅读量1.3k

点赞数

分类专栏： Oracle技术文章文章标签： oracle date 优化 payment 测试 user

本文链接：https://blog.youkuaiyun.com/hooligan000112003/article/details/595062

版权

Oracle技术文章专栏收录该内容

3 篇文章

订阅专栏

目前数据库优化需要注意的几点问题

1. 适当增加索引，尽可能使用已有的索引字段

如:

SELECT count(*) AS record_count

FROM (SELECT resume_id, name, gender, substr((sysdate - birthday) / 365, 1,

2) AS age, whcd_id, resident_area

FROM z_resumes

WHERE 1 = 1

AND resident_area = '010'

AND whcd_id = '30'

AND substr((sysdate - birthday) / 365, 1, 2) = 25);

增加索引idx_z_resumes_resident_area on z_resumes (resident_area)

如:

SELECT cid FROM insu_comp_baseinfo WHERE zzjgdm = :1

增加索引idx_insu_comp_zzjgdm on CITQUERY.insu_comp_baseinfo (zzjgdm)

如:

select *

from z_companies

where short_name = '爱筵餐饮'

增加索引IDX_Z_COMPANIES_SHORTNAME ON Z_COMPANIES (SHORT_NAME)

对部分语句建议使用索引字段

如：

select *

from sysm_busilog

where user_name = :"SYS_B_0"

and to_char(time,:"SYS_B_1") = :"SYS_B_2"

建议在类似上述的SQL语句中,用user_id替换user_name,减少对的全表扫描,因为user_id字段上建有索引

2. 索引字段不要用函数或表达式作用,否则将不使用索引

如:

SELECT WHCD

FROM PERSON_INFO

WHERE UPPER(ZJHM) = :b1

建议去掉UPPER函数,改为ZJHM = :b1

如:

SELECT COMPANIES.COMPANY_ID AS COMPANY_ID,

COMPANIES.REGISTER_COMPANY_CODE AS REGISTER_COMPANY_CODE,

CORPORATION.DWQC AS NAME,COMPANIES.MAN_IN_CHARGE AS

MAN_IN_CHARGE,COMPANIES.CONTACT_ADDRESS AS CONTACT_ADDRESS,

COMPANIES.POSTAL_CODE AS POSTAL_CODE,COMPANIES.DWLB_ID AS

DWLB_ID,COMPANIES.DWLX_ID AS DWLX_ID,

COMPANIES.REGISTER_ADDRESS AS REGISTER_ADDRESS,

COMPANIES.EMPLOYEE AS EMPLOYEE,COMPANIES.REGISTER_FUND AS

REGISTER_FUND,COMPANIES.REGISTER_YEAR_LIMIT AS

REGISTER_YEAR_LIMIT,COMPANIES.CONTACT_MAN AS CONTACT_MAN,

COMPANIES.TELEPHONE AS TELEPHONE,COMPANIES.INVESTORS AS

INVESTORS,COMPANIES.OTHER_CONTACTS AS OTHER_CONTACTS,

COMPANIES.DESCRIPTION AS DESCRIPTION,COMPANIES.ACTIVE_FLAG AS

ACTIVE_FLAG,COMPANIES.BE_CREATED_DATE AS BE_CREATED_DATE,

COMPANIES.LAST_UPDATED_DATE AS LAST_UPDATED_DATE,

COMPANIES.PUBLIC_FLAG AS PUBLIC_FLAG,COMPANIES.COMPANY_TYPE

AS COMPANY_TYPE,COMPANIES.IS_OUTER_FLAG AS IS_OUTER_FLAG,

COMPANIES.AGENCY_DISTRICT_ID AS AGENCY_DISTRICT_ID,

COMPANIES.SCOPE AS SCOPE

FROM Z_COMPANIES COMPANIES,CORPORATION CORPORATION,

COMPANY_PASSWORD COMPANY_PASSWORD

WHERE COMPANIES.COMPANY_ID=14560403

AND

COMPANIES.REGISTER_COMPANY_CODE=UPPER(CORPORATION.DWZZJGDM)

AND CORPORATION.CID=COMPANY_PASSWORD.CID

建议去掉UPPER,改为COMPANIES.REGISTER_COMPANY_CODE=CORPORATION.DWZZJGDM

如:

where to_char(time,'yyyymm')='200501'

建议尽可能不要使用DATE数据类型,因为一使用to_char函数作用就不用索引.

对于上述语句可酌情改为where time>=to_date('200501','yyyymm') and time<to_date('200502','yyyymm')

如:

select count(*) as record_count from

(SELECT POSITION.SALARY1 ||'-'|| POSITION.SALARY2 AS SALARY,

POSITION.YZSLCD_ID_2 AS YZSLCD_ID2,

POSITION.MIN_AGE ||'-'|| POSITION.MAX_AGE AS AGE,

POSITION.POSITION_ID AS POSITION_ID,

POSITION.NAME as POSITION_NAME,POSITION.WHCD_ID AS WHCD_NAME,

POSITION.TOTAL_NUMBER AS TOTAL_NUMBER,

POSITION.DIC_CITY_AND_AREA_ID_1 AS DIC_CITY_AND_AREA_NAME_1,

DIC_UNIT_PROPERTY.DWXZ_NAME as DWXZ_NAME,

POSITION.IS_ZJ AS IS_ZJ,POSITION.ZJ_ZZJGDM AS ZJ_ZZJGDM,

to_char(POSITION.be_created_date,'YY-MM-DD') as BE_CREATED_DATE,

POSITION.IS_ZJS

FROM Z_POSITIONS POSITION,DIC_UNIT_PROPERTY

WHERE (DIC_UNIT_PROPERTY.DWXZ_ID = POSITION.DWXZ_ID)

AND to_char(POSITION.BE_CREATED_DATE,'yyyymmdd')<=to_char(sysdate,'yyyymmdd')

AND (SUBSTR(position.DIC_STATION_POSITION_ID,1,4)='0002') AND (POSITION.HISTORY_FLAG='N')

AND (POSITION.ACTIVE_FLAG='Y') AND POSITION.END_DATE >= trunc(sysdate) AND POSITION.FULL_FLAG>0 );

建议将条件(SUBSTR(position.DIC_STATION_POSITION_ID,1,4)='0002')

改为position.DIC_STATION_POSITION_ID like '0002%')

3. 避免系统隐含的数据类型转换

如:

SELECT cid,pid

FROM COG_ROSTER_ANNUAL_W

WHERE cid=200008010754371

若cid为NUMBER数据类型,请使用cid=200008010754371,若cid为CHAR数据类型,请使用cid='200008010754371',

否则系统将自动进行数据类型转换,导致全表扫描而不使用索引

4. 尽可能减少不必要的语句

如:部分程序

先select count(*) from icq_payment where pid=:a1;

再select * from icq_payment where pid=:a1;

建议：可以直接执行后一条语句,辅以查询不到的意外处理.

5. 尽量用简单的语句,及减少不必要的判断条件

如:

select max(dh)

from mail_record group by bh having bh='2005020666';

尽量避免使用GROUP BY,HAVING等复杂子句,可使用下列语句达到同样效果:

select max(dh)

from mail_record where bh='2005020666';

如:

Select count(*)

From ICQ_PAYMENT

Where (QSNY <= ZZNY or ZZNY is null)

and pid='200008029162760'

该语句为查询社保个人缴费信息，点击率非常高，

而实际上QSNY永远=ZZNY,所以条件(QSNY <= ZZNY or ZZNY is null)没有意义，白白浪费CPU运算

6. 中文检索

对于中文查询,如姓名，单位名称,其他各类中文名称等,若业务需求允许，尽可能要求汉字必须从头输入.

如将条件position.name LIKE '%驾驶员%',改成position.name LIKE '驾驶员%',则可使用position表上name字段的索引,

若两头模糊查询，则只能全表扫描.将损失很多性能。

7. 特殊情况下，增加注释，强制使用索引

有些情况下系统选择的执行计划会不正确,如会选择使用全表扫描,而不使用索引,但经测试发现实际上使用索引效率高得多,

这时可强制使用索引:

如:

select count(*)

from mail_record,dic_status

where mail_record.zt = dic_status.status_id

and mail_record.scbj =0

and mail_record.zt ='0000';

测试发现,虽然mail_record上的zt字段有索引,但系统未使用,而进行全表扫描,

建议改为

select /*+ INDEX(A,IDX_MAIL_RECORD_ZT) */ count(*)

from mail_record A,dic_status B

where A.zt = B.status_id

and A.scbj =0

and A.zt ='0000';

在select后面增加的hint (/*+ INDEX(A,IDX_MAIL_RECORD_ZT) */) 的意思是

强制使用别名为A的mail_record表上的索引IDX_MAIL_RECORD_ZT.(在此种情况下一定要使用表的别名)

强制使用索引一定要经过测试.在没有把握或不能确定的情况下,尽可能不要使用hint,尤其是rule hint,即/*+ RULE */, 而让系统自己选择执行计划,因为在绝大部分情况下,系统选择执行计划还是相当不错的. 当确定要使用某种hint时,一定要经过测试.

补充资料：

在维护查询的性能时，我觉得选择得体的优化方法应该是个要注意的问题。oracle在早期的版本中最新运用的是基于规则优化器（RBO），从ORACLE 7开始引入了基于开销(成本)优化器（CBO），从此，也就有了analyze工具，它为表或索引生成统计和估计资料。
在INIT.ORA中可以为数据库设置确省的优化设置：optimizer_mode，在会话中可以动态的改变优化器模式，如set session set optimizer_goal.。oracle提供了四种优化模式：
(1)。 rule：选择oracle使用基于规则的优化。如果我们试图使用RBO对SQL语句进行优化时，可以设置为RULE。请注意，RBO经常不能选择最优的执行计划，因为它不能够选择最佳的索引
(2)。 CHOOSE：这是ORACLE的默认方式，它会根据是否能够找到统计数字来决定使用那种优化方式，如果没有统计数字的话，它会使用RULE；如果存在统计数字的话，它会根据是否存在索引等信息选择使用FIRST_ROW还是ALL_ROWS。当统计资料不完整时，比如说有的表有，另外一些没有，那么CHOOSE会使用基于成本的优化器，并且在运行是对另外的表做统计和估计，从而影响了效率，甚至导致严重的效率下降问题。
(3)。 FIRST_ROW：使用CBO，它能快速返回最初的几行，因为它通常使用完全索引扫描，从而会造成总体效率降低或者消耗更多的系统资源。往往适合于一些ONLINE系统，因为人们总是希望尽快看到反映。
(4)。 ALL_ROWS：它也是用CBO，但它的不表示是总体反应时间缩短，因为它通常使用并行全表扫描，而不使用完全索引扫描

另外，可以使用HINTS，在部分语句级指定优化方式