1.问题
创建了表fundinfo，数据量大概为38w左右，使用同一个SQL语句在测试库与生产产生了不同的执行计划，并且在生产添加部分索引之后，反而SQL会非常慢，删除(port,acc)复合索引，速度明显加快。
2.表结构
CREATE TABLE fundinfo
(PLANID VARCHAR2(40),
NAME VARCHAR2(40),
IDTYPE VARCHAR2(20),
IDNO VARCHAR2(40),
ACC VARCHAR2(10),
PORT VARCHAR2(20),
MONEY NUMBER(21,11),
AMUNT NUMBER(21,11),
STATE VARCHAR2(30)
);
3.通过外部表并使用CTAS加载大约38w数据
--外部表加载CSV文件
--由于dos以及编码格式，加载时出现了乱码问题，通过使用UE-文件-转换，转换为UTF-8以及Unix格式，成功加载
CREATE TABLE hr.fundinfo_tmp
(PLANID VARCHAR2(40),
NAME VARCHAR2(40),
IDTYPE VARCHAR2(20),
IDNO VARCHAR2(40),
ACC VARCHAR2(10),
PORT VARCHAR2(20),
MONEY NUMBER(21,11),
AMUNT NUMBER(21,11),
STATE VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY expdp
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ","
(PLANID,NAME,IDTYPE,IDNO,ACC,PORT,MONEY,AMUNT,STATE)
)
LOCATION('csv_data.csv')
)
reject limit unlimited;
create table hr.fundinfo as select hr.fundinfo_tmp;
4.表添加索引，收集统计信息
--添加PLANID，IDNO，(PORT，ACC)，三个索引，port，acc为复合索引
CRAETE INDEX IDX_FUND_PID ON HR.FUNDINFO(PLANID);
CREATE INDEX IDX_FUND_ID ON HR.FUNDINFO(IDNO);
CREATE INDEX IDX_FUND_PC ON HR.FUNDINFO(PORT,ACC);
--收集统计信息
execute dbms_stats.gather_table_stats(ownname => 'xx',tabname => 'FUNDINFO' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);
5.查询SQL
select a.name 个人姓名,
a.idtype 证件类型,
a.idno 证件编号,a.port 组合,'3001' 账户编号,'30000004001' 投资组合编号,
round(sum(a.money),2) 申购金额,'' 申购比例,
(select b.money from fundinfo b where a.idno=b.idno and b.acc='299') 未纳税申购金额,
(select b.money from fundinfo b where a.idno=b.idno and b.acc='211') 已纳税申购金额,0
from fundinfo a where a.port='00000225' and a.acc in('211','299')
group by a.name, a.idtype, a.idno,a.port ;
6.测试库执行计划
--，测试库只需要大概一两分钟即可完成查询
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1273383357
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46636 | 2322K| | 1962 (1)| 00:00:24 |
|* 1 | TABLE ACCESS BY INDEX ROWID| FUNDINFO | 1 | 37 | | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FUNDINFO_IDNO | 13 | | | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| FUNDINFO | 1 | 37 | | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | FUNDINFO_IDNO | 13 | | | 3 (0)| 00:00:01 |
| 5 | HASH GROUP BY | | 46636 | 2322K| 3120K| 1962 (1)| 00:00:24 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 6 | TABLE ACCESS FULL | FUNDINFO | 46636 | 2322K| | 1369 (1)| 00:00:17 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ACC"='299' AND "B"."PORT"='00000225')
2 - access("B"."IDNO"=:B1)
3 - filter("B"."ACC"='211' AND "B"."PORT"='00000225')
4 - access("B"."IDNO"=:B1)
6 - filter("A"."PORT"='00000225' AND ("A"."ACC"='211' OR "A"."ACC"='299'))
7.生产库执行计划
--生产库执行计划，需要很长时间，且可以看出，生产库没有使用CBO，依旧使用Oracle已经放弃的RBO优化器
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3246304852
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID | FUNDINFO |
|* 2 | INDEX RANGE SCAN | FUNDINFO_PC |
|* 3 | TABLE ACCESS BY INDEX ROWID | FUNDINFO |
|* 4 | INDEX RANGE SCAN | FUNDINFO_PC |
| 5 | SORT GROUP BY | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | CONCATENATION | |
| 7 | TABLE ACCESS BY INDEX ROWID| FUNDINFO |
|* 8 | INDEX RANGE SCAN | FUNDINFO_PC |
| 9 | TABLE ACCESS BY INDEX ROWID| FUNDINFO |
|* 10 | INDEX RANGE SCAN | FUNDINFO_PC |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."IDNO"=:B1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("B"."PORT"='00000225' AND "B"."ACC"='299')
3 - filter("B"."IDNO"=:B1)
4 - access("B"."PORT"='00000225' AND "B"."ACC"='211')
8 - access("A"."PORT"='00000225' AND "A"."ACC"='299')
10 - access("A"."PORT"='00000225' AND "A"."ACC"='211')
Note
-----
- rule based optimizer used (consider using cbo)
8.问题查询
--可以看出等待port,acc复合索引，但是等待事件为null，不知名，结合测试库执行情况，说明语句以及执行计划选择有问题
select a.CURRENT_OBJ#,
d.object_name,
d.object_type,
a.EVENT,
sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time
from v$active_session_history a, dba_objects d
where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate
and a.CURRENT_OBJ# = d.object_id
group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT
order by total_wait_time desc;
CURRENT_OBJ# OBJECT_NAME OBJECT_TYPE EVENT TOTAL_WAIT_TIME
------------ ------------------------------ --------------------------------------------------------- ------------------------------ ---------------
267009 FUNDINFO_PC INDEX (null) 487277
9.问题解决
通过对比测试库与生产库执行计划，发现测试库走了全表扫描比较快。
生产库查询同一个表使用了两个标量子查询，数据均来源于同一个表，使用TABLE ACCESS BY INDEX ROWID进行了两次查询，TABLE ACCESS BY INDEX ROWID官方解释如下：
How Table Access by Rowid Works
To access a table by rowid, the database performs multiple steps.
The database does the following:
Obtains the rowids of the selected rows, either from the statement WHERE clause or through an index scan of one or more indexes
Table access may be needed for columns in the statement not present in the index.
Locates each selected row in the table based on its rowid
即通过两次TABLE ACCESS BY INDEX ROWID查询之后需要两次再回表查询，造成了巨大的开销。
所以综上问题解决如下：
方法1：改写SQL（改写SQL来源于墨天轮平台Moone支持）
--将标量子查询改为只访问一次原表
select a.name 个人姓名,
a.idtype 证件类型,
a.idno 证件编号,a.port 组合,'3001' 账户编号,'30000004001' 投资组合编号,
round(sum(a.money),2) 申购金额,'' 申购比例,
SUM(DECODE(acc,'299',money,0)) 未纳税申购金额,
SUM(DECODE(acc,'211',money,0)) 已纳税申购金额,0
from fundinfo a
where a.port='00000225' and a.acc in('211','299')
group by a.name, a.idtype, a.idno,a.port ;
方法2：删除port,acc符合索引
--为了临时解决问题，实际解决选择了删除复合索引，查询慢问题解决
DROP INDEX FUNDINFO_IDX_ACC_PORT;
方法3：修改数据库优化器为CBO（未尝试）
SQL> alter system set optimizer_mode=ALL_ROWS scope=both;
System altered.
SQL> show parameter optimizer_mode
--optimizer_mode五个取值
1. CHOOSE
这个是Oracle的默认值。采用这个值时，Oracle即可以采用基于规则RBO，也可以采用基于代价的CBO，到底使用那个值，取决于当前SQL的被访问的表中是不是有可以使用的统计信息。
如果有多个被访问的表，其中有一个或多个有统计信息，那么Oralce会对没有统计信息的表进行采样统计（即不全部采样），统计完成后，使用基于代价的优化方法CBO。
如果所有被访问的表都没有统计信息，Oracle就会采用基于规则的优化方法RBO。
2. ALL_ROWS
不管是不是有统计信息，全部采用基于成本的优化方法CBO。
3. FIRST_ROWS_n
FIRST_ROWS_n后面的n值可以为1,100,1000
不管是不是有统计信息，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录。
4. FIRST_ROWS
使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行的方法；这个参数主要用于向后兼容。
5. RULE
这个参数正好和ALL_ROWS相反，不管是不是统计信息，全部采用基于规则的优化方法。
下面为11.2该参数的值， 默认为all_rows，oracle已经不建议使用RBO。
10.思考
--在出现问题时，往往都是比较紧急情况下，而对于SQL效率性能问题，需要大量基础知识，虽然问题解决，但是
--暴露出自己SQL知识以及数据库基础不扎实问题，希望以后多多学习，多多深入应用，只有预先储备丰富的基础
--知识，才能在发生问题时抽丝剥茧解决问题。
转载：https://blog.csdn.net/jwocnimabi/article/details/101540187
查看评论