Php文档 Php问答行业资讯 Php论坛 Php手册 Php博客

游戏榜单

软件榜单

关闭导航

热搜榜

热门下载

热门标签

关闭搜索

php爱好者> php文档>实际中性能问题的一条SQL语句

实际中性能问题的一条SQL语句

时间：2011-03-15 来源：penglele2009

根据创建的函数和一些平时我经常用的DBA调优语句我找出了这条latch buffer问题的语句
总体思路：
1：找出SQL语句，这是调优数据库性能问题的关键
2：查看执行计划，分析执行计划
3：查看表大小
4：分析表的统计信息
5：创建新的索引
6：最后，比较关键：重改SQL语句，联系开发修改程序代码。

1：查看执行计划
SQL> explain plan for
2 select PRICE_PROMO
3 from wiliam.TB_PROMO
4 where CORPID = 'XANDER'
5 and to_number(START_DATE) <= 20110314
6 and to_number(END_DATE) >= 20110314
7 and (PROMO_RANGE = 'A'
8 or (PROMO_RANGE = 'P'
9 and PROMO_ID in (select PROMO_ID from wiliam.TB_PROMO_CUST
10 where CUST_CODE = 'DRFZJ04')))
11 and PRO_CODE = 'D3100-W' order by ADD_TIME desc;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT ORDER BY | | | | |
|* 2 | FILTER | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| TB_PROMO | | | |
|* 4 | INDEX RANGE SCAN | PK_TB_PROMO | | | |
|* 5 | TABLE ACCESS FULL | TB_PROMO_CUST | | | |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("SYS_ALIAS_1"."PROMO_RANGE"='A' OR
"SYS_ALIAS_1"."PROMO_RANGE"='P' AND EXISTS (SELECT 0 FROM
"WILIAM"."TB_PROMO_CUST" "TB_PROMO_CUST" WHERE "TB_PROMO_CUST"."PR
OMO_ID"=:B1

AND "TB_PROMO_CUST"."CUST_CODE"='DRFZJ04'))
3 - filter("SYS_ALIAS_1"."PRO_CODE"='D3100-W' AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
TO_NUMBER("SYS_ALIAS_1"."END_DATE")>=20110314 AND
TO_NUMBER("SYS_ALIAS_1"."START_DATE")<=20110314)
4 - access("SYS_ALIAS_1"."CORPID"='XANDER')
5 - filter("TB_PROMO_CUST"."PROMO_ID"=:B1 AND
"TB_PROMO_CUST"."CUST_CODE"='DRFZJ04')

Note: rule based optimization

27 rows selected.
分析：从上面可以看出，执行计划模式选择的是RBO，而且表tb_promo_cust走的table full scan，通过进一步分析，全表扫描不正确，应该走索引。

2：分析两个表的统计信息，使用dbms_stats,是执行计划走CBO，
SQL> exec dbms_stats.gather_table_stats('wiliam','TB_PROMO_CUST',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('wiliam','TB_PROMO',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

3：进一步看执行计划
SQL> explain plan for
2 select PRICE_PROMO
3 from wiliam.TB_PROMO
4 where CORPID = 'XANDER'
5 and to_number(START_DATE) <= 20110314
6 and to_number(END_DATE) >= 20110314
7 and (PROMO_RANGE = 'A'
8 or (PROMO_RANGE = 'P'
9 and PROMO_ID in (select PROMO_ID from wiliam.TB_PROMO_CUST
10 where CUST_CODE = 'DRFZJ04')))
11 and PRO_CODE = 'D3100-W' order by ADD_TIME desc;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 7 |
| 1 | SORT ORDER BY | | 1 | 55 | 7 |
|* 2 | FILTER | | | | |
|* 3 | TABLE ACCESS FULL | TB_PROMO | 1 | 55 | 5 |
|* 4 | TABLE ACCESS FULL | TB_PROMO_CUST | 1 | 13 | 25 |
-----------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("SYS_ALIAS_1"."PROMO_RANGE"='A' OR
"SYS_ALIAS_1"."PROMO_RANGE"='P' AND EXISTS (SELECT /*+ */ 0 FROM
"WILIAM"."TB_PROMO_CUST" "TB_PROMO_CUST" WHERE
"TB_PROMO_CUST"."PROMO_ID"=:B1 AND "TB_PROMO_CUST"."CUST_CODE"='DR
FZJ04

'))
3 - filter("SYS_ALIAS_1"."CORPID"='XANDER' AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
TO_NUMBER("SYS_ALIAS_1"."START_DATE")<=20110314 AND
TO_NUMBER("SYS_ALIAS_1"."END_DATE")>=20110314 AND
"SYS_ALIAS_1"."PRO_CODE"='D3100-W')
4 - filter("TB_PROMO_CUST"."PROMO_ID"=:B1 AND
"TB_PROMO_CUST"."CUST_CODE"='DRFZJ04')

Note: cpu costing is off

27 rows selected.
分析：从上面可以看出，tb_promo走主键，tb_promo_cust还是走full scan;

4：进一步看表的大小，我们可以看到选择27行，但是表的大小，tb_promo_cust有10w行，所以可以判断，此表没有创建索引，或者索引失效。
SQL> select count(*) from wiliam.tb_promo_cust;

COUNT(*)
----------
103167

SQL> select count(*) from wiliam.tb_promo;

COUNT(*)
----------
6198

5：查看两个表索引：
SQL> select table_name,index_name,column_name,column_position
2 from user_ind_columns
3 where table_name in ('wiliam.tb_promo','wiliam.tb_promo_cust');

no rows selected

SQL> select index_name,index_type from user_indexes
2 where table_name ='TB_PROMO';

no rows selected

SQL> select index_name,index_type from user_indexes
2 where table_name ='TB_PROMO_CUST';

no rows selected
分析：通过上面可以看到两个表没有创建索引。

6：创建索引,根据表 tb_promo_cust 查询where 条件中字段cust_code 创建索引 idx_tb_promo
默认索引表空间是misidx;

create index idx_tb_promo on wiliam.tb_promo_cust(cust_code)
tablespace misidx;

7:进一步看执行计划，执行时间只有0.106秒，很好
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 7 |
| 1 | SORT ORDER BY | | 1 | 55 | 7 |
|* 2 | FILTER | | | | |
|* 3 | TABLE ACCESS FULL | TB_PROMO | 1 | 55 | 5 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TB_PROMO_CUST | 1 | 13 | 4 |
|* 5 | INDEX RANGE SCAN | IDX_TB_PROMO | 347 | | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SYS_ALIAS_1"."PROMO_RANGE"='A' OR
"SYS_ALIAS_1"."PROMO_RANGE"='P' AND EXISTS (SELECT /*+ */ 0 FROM
"WILIAM"."TB_PROMO_CUST" "TB_PROMO_CUST" WHERE
"TB_PROMO_CUST"."CUST_CODE"='DRFZJ04' AND "TB_PROMO_CUST"."PROMO_I
3 - filter("SYS_ALIAS_1"."CORPID"='XANDER' AND

27 rows selected

SQL> set timing on;
SQL>
SQL> explain plan for
2 select PRICE_PROMO
3 from wiliam.TB_PROMO
4 where CORPID = 'XANDER'
5 and to_number(START_DATE) <= 20110314
6 and to_number(END_DATE) >= 20110314
7 and (PROMO_RANGE = 'A'
8 or (PROMO_RANGE = 'P'
9 and PROMO_ID in (select PROMO_ID from wiliam.TB_PROMO_CUST
10 where CUST_CODE = 'DRFZJ04')))
11 and PRO_CODE = 'D3100-W' order by ADD_TIME desc;

Explained

Executed in 0 seconds

SQL> select * from table(dbms_xplan.display);

27 rows selected

Executed in 0.109 seconds

8：最后再来分析SQL语句
tb_promo 查询在字段start_date,end_date,使用了函数TO_NUMBER,肯定不会走索引，所以此代码可以修改。
其次，在字段corpid='xander'应该使用绑定变量，corpid=$corpid,减少软解析和latch 争用。
select PRICE_PROMO
from wiliam.TB_PROMO
where CORPID =$corpid
and START_DATE<= to_date('2011/03/14' 'yyyy/mm/dd')
and END_DATE>=to_date('2011/03/14' 'yyyy/mm/dd')
and (PROMO_RANGE = 'A'
or (PROMO_RANGE = 'P'
and PROMO_ID in (select PROMO_ID from wiliam.TB_PROMO_CUST
where CUST_CODE = $cust_code')))
and PRO_CODE = 'D3100-W' order by ADD_TIME desc;

后来和开发联系，按照我改的改程序代码。