1. 优化概述
该优化方案针对经分数据库，由于日常承载业务量庞大，且平日业务繁忙时段0:00-14:00 CPU负载率在90%-100%之间，造成系统CPU资源紧张压力加大，对于此种情况，我们采集月初1号到4号出账期间系统性能数据进行针对性优化。
1.1 6个TOP SQL具体调整内容：
通过账期间性能数据分析，本次选取执行次数前列（top50之内的查询语句）、消耗逻辑读、cpu、物理读前列的6个SQL进行优化调整，观察系统整体负载趋势，为后期优化奠定基础。
1.2 优化实施时间构成细则
|
序号 |
实施内容 |
备注 |
|
1 |
SQL ID: 6bxr919bj3xaz |
具体内容详见：2.1 SQL ID: 6bxr919bj3xaz |
|
2 |
SQL ID: 941kgjj0xcvzz |
具体内容详见：2.2 SQL ID: 941kgjj0xcvzz |
|
3 |
SQL ID: 9jxag4fjbaadk |
具体内容详见：2.3 SQL ID: 9jxag4fjbaadk |
|
4 |
SQL ID: c6rs2xmjkc4f3 |
具体内容详见：2.4 SQL ID: c6rs2xmjkc4f3 |
|
5 |
SQL ID: 7vg112j2q99x5 |
具体内容详见：2.5 SQL ID: 7vg112j2q99x5 |
|
6 |
SQL ID: 8dk8p4hrpdy46 |
具体内容详见：2.6 SQL_ID:8dk8p4hrpdy46 |
2.
优化实施
具体操作根据上述的图表，依次进行实施:
2.1 SQL ID: 6bxr919bj3xaz
SQLID 6bxr919bj3xaz的主要内容如下:
|
6bxr919bj3xaz |
采样时段 |
执行次数 |
单次执行时间（ms） |
逻辑读 |
|
优化前 |
20141201 23:58:47- 20141204 10:58:19 |
3,999 |
30,310.45
|
847,185,198 |
|
优化后 |
|
|
|
|
|
改进率 |
|
|
|
|
该SQL语句在节点2上TOP SQL按逻辑读排名第一，按CPU排名第七（11月2日-11月4日采样时间内供执行20740次）
SELECT NVL(MAX(STEP_SN), 0)
FROM ZBA_DWD.SQLPARSER_LOG_DETAIL
WHERE LOG_SN = :B1
非分区表：
select table_owner, table_name, partition_name
from ALL_TAB_PARTITIONS
where table_name = 'SQLPARSER_LOG_DETAIL';
no rows selected
列数据信息：
字段类型：
select T.COLUMN_NAME, T.DATA_TYPE, T.AVG_COL_LEN, T.HISTOGRAM
from dba_tab_cols t
where t.owner = 'ZBA_DWD'
AND t.TABLE_NAME = 'SQLPARSER_LOG_DETAIL';
COLUMN_NAME DATA_TYPE AVG_COL_LEN HISTOGRAM
------------ ------------ ----------- ---------------
LOG_SN NUMBER 5 NONE
STEP_SN NUMBER 3 NONE
STEP_TIME DATE 8 NONE
STEP_TYPE VARCHAR2 4 NONE
STEP_DETAIL CLOB 471 NONE
segments大小：
select segment_name,segment_type,owner,tablespace_name,bytes/1024/1024 MB from dba_segments where segment_name='SQLPARSER_LOG_DETAIL' and owner='ZBA_DWD';
SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME MB
---------------------- --------------- ------------ --------------- ----------
SQLPARSER_LOG_DETAIL TABLE ZBA_DWD TBS_ZB_DWD_13 7084
表约束：
select a.owner,
a.table_name,
a.constraint_name,
a.constraint_type,
b.column_name,
b.position
from dba_constraints a, dba_cons_columns b
where a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.constraint_type in ('P','R')
and a.table_name = 'SQLPARSER_LOG_DETAIL'
and a.owner = 'ZBA_DWD';
no rows selected
选择度：
SQL> select count(distinct LOG_SN) from ZBA_DWD.SQLPARSER_LOG_DETAIL;
COUNT(DISTINCTLOG_SN)
---------------------
2393694
SQL> select count(*) from ZBA_DWD.SQLPARSER_LOG_DETAIL;
COUNT(*)
----------
4901802
建立索引：
create index sqlparser_ld_idx1 on ZBA_DWD.SQLPARSER_LOG_DETAIL(LOG_SN);
2.2 SQL ID: 941kgjj0xcvzz
SQLID 941kgjj0xcvzz的主要内容如下:
|
941kgjj0xcvzz |
采样时段 |
执行次数 |
单次执行时间（ms） |
逻辑读 |
|
优化前 |
20141201 23:58:47- 20141204 10:58:19 |
119 |
488,794.11
|
7,089,076,139
|
|
优化后 |
|
|
|
|
|
改进率 |
|
|
|
|
UPDATE ZB_SRC_INTEGRITY_REPORT_DETAIL T1
SET T1.QUARTER =
(SELECT CASE
WHEN T2.NUM = 0 THEN
0
ELSE
ROUND((T1.NUM - T2.NUM) / T2.NUM, 4) * 100
END
FROM ZB_SRC_INTEGRITY_REPORT_DETAIL T2
WHERE T2.MONTH_ID || T2.DAY_ID =
TO_CHAR(TO_DATE(:B1, 'yyyymmdd') - 1, 'yyyymmdd')
AND T1.FILE_ID = T2.FILE_ID
AND T1.PROV_ID = T2.PROV_ID)
WHERE T1.MONTH_ID || T1.DAY_ID = :B1
非分区表：
SQL> select table_owner,table_name,partition_name from ALL_TAB_PARTITIONS where table_name='ZB_SRC_INTEGRITY_REPORT_DETAIL';
no rows selected
索引情况,存在索引：
select INDEX_NAME,
DISTINCT_KEYS,
clustering_factor,
PARTITIONED,
num_rows,
last_analyzed
from dba_indexes
where table_name = 'ZB_SRC_INTEGRITY_REPORT_DETAIL'
and owner = 'ZB_SRC';
INDEX_NAME DISTINCT_KEYS CLUSTERING_FACTOR PARTITIONED NUM_ROWS LAST_ANALYZED
------------------ ------------- ----------------- ----------- ---------- -------------
INDEX_PROV_ID121 34 4787 NO 160730 2013/9/5 星期四
INDEX_FILE_ID121 1360 32372 NO 160730 2013/9/5 星期四
但在其中的查询中并未使用第二个索引，怀疑和统计信息收集时间太久有关。
列数据信息：
字段类型：
select T.COLUMN_NAME, T.DATA_TYPE, T.AVG_COL_LEN, T.HISTOGRAM
from dba_tab_cols t
where t.owner = 'ZB_SRC'
AND t.TABLE_NAME = 'ZB_SRC_INTEGRITY_REPORT_DETAIL';
COLUMN_NAME DATA_TYPE AVG_COL_LEN HISTOGRAM
------------- ---------- ----------- ---------------
REPORT_TYPE VARCHAR2 NONE
DAY_ID VARCHAR2 NONE
MONTH_ID VARCHAR2 NONE
NORM_NAME VARCHAR2 NONE
BUS_TYPE VARCHAR2 NONE
PROV_ID VARCHAR2 NONE
FILE_ID VARCHAR2 NONE
FILE_NAME VARCHAR2 NONE
NUM NUMBER NONE
QUARTER NUMBER NONE
IMP_RESULT VARCHAR2 NONE
IMP_TIME VARCHAR2 NONE
segmens大小：
select segment_name,segment_type,owner,tablespace_name,bytes/1024/1024 MB from dba_segments where segment_name='ZB_SRC_INTEGRITY_REPORT_DETAIL' and owner='ZB_SRC';
SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME MB
----------------------------------- --------------- ---------- --------------- ----------
ZB_SRC_INTEGRITY_REPORT_DETAIL TABLE ZB_SRC TBS_ZB_SRC 449
表约束：
select a.owner,
a.table_name,
a.constraint_name,
a.constraint_type,
b.column_name,
b.position
from dba_constraints a, dba_cons_columns b
where a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.constraint_type in ('P','R')
and a.table_name = 'ZB_SRC_INTEGRITY_REPORT_DETAIL'
and a.owner = 'ZB_SRC';
no rows selected
查看选择度：
select count(distinct file_id),
count(distinct prov_id),
count(distinct month_id),
count(distinct day_id),
count(*)
from zb_src.zb_SRC_INTEGRITY_REPORT_DETAIL;
COUNT(DISTINCTFILE_ID) COUNT(DISTINCTPROV_ID) COUNT(DISTINCTMONTH_ID) COUNT(DISTINCTDAY_ID) COUNT(*)
---------------------- ---------------------- ----------------------- --------------------- ----------
12308 34 23 31 1564942
建立虚拟列索引和联合索引：
create index ZB_SRC.idx_zbsrc_irdmdid on ZB_SRC.ZB_SRC_INTEGRITY_REPORT_DETAIL(MONTH_ID||T1.DAY_ID);
create index ZB_SRC.idx_zbsrc_irdfpid on ZB_SRC.ZB_SRC_INTEGRITY_REPORT_DETAIL(file_id,prov_id);
drop index ZB_SRC.INDEX_FILE_ID121;
drop index ZB_SRC.INDEX_PROV_ID121;
2.3 SQL ID: 9jxag4fjbaadk
SQLID 9jxag4fjbaadk的主要内容如下:
|
9jxag4fjbaadk |
采样时段 |
执行次数 |
单次执行时间（ms） |
逻辑读 |
|
优化前 |
20141201 23:58:47- 20141204 10:58:19 |
426,477 |
844.34
|
5,829,398,602
|
|
优化后 |
|
|
|
|
|
改进率 |
|
|
|
|
select (case
when to_char(ftp_file_update_date, 'yyyy-MM-dd HH24:mi:ss') >= :1 then
1
else
0
end) "flag",
version_no "version_no",
substr(interface_name, length(interface_name) - 6, 3) "tail_name",
substr(interface_name, length(interface_name) - 8, 3) "tail_q_name",
to_char(ftp_file_update_date, 'yyyy-MM-dd HH24:mi:ss') "file_time",
interface_name "interface_name"
from ZB_SYS_FILE_FTP_DATE
where prov_id = :2
and file_id = :3
and acct_date = :4
and file_class = :5
非分区表：
select table_owner, table_name, partition_name
from ALL_TAB_PARTITIONS
where table_name = 'ZB_SYS_FILE_FTP_DATE';
no rows selected
其上并没索引：
select owner,
INDEX_NAME,
table_owner,
TABLE_NAME,
DISTINCT_KEYS,
PARTITIONED
from dba_indexes
where table_name = 'ZB_SYS_FILE_FTP_DATE';
no rows selected
列数据信息：
字段类型：
select T.COLUMN_NAME, T.DATA_TYPE, T.AVG_COL_LEN, T.HISTOGRAM
from dba_tab_cols t
where t.owner = 'DATA_APP'
AND t.TABLE_NAME = 'ZB_SYS_FILE_FTP_DATE';
COLUMN_NAME DATA_TYPE AVG_COL_LEN HISTOGRAM
------------------------------ ---------- ----------- ---------------
PROV_ID VARCHAR2 4 FREQUENCY
FILE_ID VARCHAR2 13 HEIGHT BALANCED
ACCT_DATE VARCHAR2 7 HEIGHT BALANCED
TAB_NAME VARCHAR2 24 NONE
IF_CODE_MAP NUMBER 3 NONE
FILE_TYPE VARCHAR2 2 NONE
FILE_NAME VARCHAR2 23 NONE
SPECIFICATE_TYPE VARCHAR2 2 NONE
INTERFACE_NAME VARCHAR2 29 NONE
FILE_DATE DATE 8 NONE
FILE_CLASS VARCHAR2 2 FREQUENCY
FTP_FILE_UPDATE_DATE DATE 8 NONE
VERSION_NO NUMBER 4 NONE
FILE_DATE_FIRST DATE 8 NONE
segmens大小
select segment_name,
segment_type,
owner,
tablespace_name,
bytes/1024/1024 MB
from dba_segments
where segment_name = 'ZB_SYS_FILE_FTP_DATE'
and owner = ' DATA_APP';
SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_N MB
------------------- ------------------ --------------- ------------ ----------
ZB_SYS_FILE_FTP_DATE TABLE DATA_APP TBS_ZB_MAN 466
表约束：
select a.owner,
a.table_name,
a.constraint_name,
a.constraint_type,
b.column_name,
b.position
from dba_constraints a, dba_cons_columns b
where a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.constraint_type in ('P','R')
and a.table_name = 'ZB_SYS_FILE_FTP_DATE'
and a.owner = 'DA_APP';
no rows selected
查看选择度：
select count(distinct prov_id),
count(distinct file_id),
count(distinct acct_date),
count(distinct file_class),
count(*)
from DATA_APP.ZB_SYS_FILE_FTP_DATE;
COUNT(DISTINCTPROV_ID) COUNT(DISTINCTFILE_ID) COUNT(DISTINCTACCT_DATE) COUNT(DISTINCTFILE_CLASS) COUNT(*)
---------------------- ---------------------- ------------------------ ------------------------- ----------
31 326 1143 2 2995167
建立索引：
Create index zb_sffd_idx1 on ZB_SRC.ZB_SYS_FILE_FTP_DATE(acct_date, file_id, prov_id);
2.4 SQL ID: c6rs2xmjkc4f3
SQLID c6rs2xmjkc4f3的主要内容如下:
|
c6rs2xmjkc4f3 |
采样时段 |
执行次数 |
单次执行时间（ms） |
逻辑读 |
|
优化前 |
20141201 23:58:47- 20141204 10:58:19 |
48,955 |
634.45 |
764,905,424 |
|
优化后 |
|
|
|
|
|
改进率 |
|
|
|
|
DELETE ODS_EXECUTE_LOG
WHERE ACCT_MONTH = :B4
AND PKG_NAME = UPPER(:B3)
AND PROCNAME = UPPER(:B2)
AND PROV_ID = :B1
非分区表：
select table_owner, table_name, partition_name
from ALL_TAB_PARTITIONS
where table_name = 'ODS_EXECUTE_LOG';
no rows selected
存在索引：
SQL> select owner,INDEX_NAME,table_owner,TABLE_NAME,DISTINCT_KEYS,PARTITIONED from dba_indexes where table_name='ODS_EXECUTE_LOG';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME DISTINCT_KEYS PAR
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------- ---
ZBA_ODS IDX_ODS_EXECUTE_LOG ZBA_ODS ODS_EXECUTE_LOG 79467 NO
ZBA_DWD IDX_ODS_EXECUTE_LOG ZBA_DWD ODS_EXECUTE_LOG 1436276 NO
查看索引情况：
SQL> select index_owner,
INDEX_NAME,
table_owner,
TABLE_NAME,
COLUMN_NAME,
COLUMN_POSITION
from dba_ind_columns
where index_name = 'IDX_ODS_EXECUTE_LOG';
INDEX_OWNE INDEX_NAME TABLE_OWNE TABLE_NAME COLUMN_NAME COLUMN_POSITION
---------- ------------------------------ ---------- -------------------- --------------- ---------------
ZBA_DWD IDX_ODS_EXECUTE_LOG ZBA_DWD ODS_EXECUTE_LOG PROV_ID 3
ZBA_DWD IDX_ODS_EXECUTE_LOG ZBA_DWD ODS_EXECUTE_LOG PROCNAME 2
ZBA_DWD IDX_ODS_EXECUTE_LOG ZBA_DWD ODS_EXECUTE_LOG ACCT_MONTH 1
ZBA_ODS IDX_ODS_EXECUTE_LOG ZBA_ODS ODS_EXECUTE_LOG PROV_ID 3
ZBA_ODS IDX_ODS_EXECUTE_LOG ZBA_ODS ODS_EXECUTE_LOG PROCNAME 2
ZBA_ODS IDX_ODS_EXECUTE_LOG ZBA_ODS ODS_EXECUTE_LOG ACCT_MONTH 1
6 rows selected.
未使用到索引，执行SQL表的owner并非为该索引所示owner。
列数据信息：
字段类型：
select T.COLUMN_NAME, T.DATA_TYPE, T.AVG_COL_LEN, T.HISTOGRAM
from dba_tab_cols t
where t.owner = 'ZBA_ODS'
AND t.TABLE_NAME = 'ODS_EXECUTE_LOG';
COLUMN_NAME DATA_TYPE AVG_COL_LEN HISTOGRAM
------------ ---------- ----------- ---------------
ACCT_MONTH VARCHAR2 9 HEIGHT BALANCED
PKG_NAME VARCHAR2 12 NONE
PROCNAME VARCHAR2 26 FREQUENCY
PROV_ID VARCHAR2 4 FREQUENCY
STARTDATE DATE 8 NONE
ENDDATE DATE 8 NONE
RESULT VARCHAR2 8 FREQUENCY
DURATION NUMBER 10 NONE
NOTE VARCHAR2 8 NONE
ROW_COUNT NUMBER 5 HEIGHT BALANCED
TABLE_NAME VARCHAR2 27 NONE
segmens大小：
select segment_name,segment_type,owner,tablespace_name,bytes/1024/1024 MB from dba_segments where segment_name='ODS_EXECUTE_LOG' order by bytes desc;
SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME MB
----------------- ------------- ------------ --------------- ----------
ODS_EXECUTE_LOG TABLE ZB_SRC TBS_ZB_SRC 538
ODS_EXECUTE_LOG TABLE ZBA_QC TBS_ZB_MAN 401
ODS_EXECUTE_LOG TABLE ZBA_CZC TBS_ZBA_JM 256
ODS_EXECUTE_LOG TABLE ZBA_DWD TBS_ZB_DWD_13 211
ODS_EXECUTE_LOG TABLE ZBA_DWA TBS_ZB_DWA_13 203
ODS_EXECUTE_LOG TABLE ZBA_ODS TBS_ZB_ODS 174
ODS_EXECUTE_LOG TABLE ZBA_DM TBS_ZB_DM 136
ODS_EXECUTE_LOG TABLE ZB_DWA TBS_ZB_DWA_13 123
ODS_EXECUTE_LOG TABLE ZB_DWD TBS_ZB_DWD_13 112
ODS_EXECUTE_LOG TABLE ZB_OUT TBS_ZB_SERV 106
ODS_EXECUTE_LOG TABLE ZB_ODS TBS_ZB_ODS 89
ODS_EXECUTE_LOG TABLE MID_SRC TBS_MID_3G 80
ODS_EXECUTE_LOG TABLE ZBA_ANA TBS_ZBA_JM 64
ODS_EXECUTE_LOG TABLE ZBA_DEV TBS_ZB_TMP 59
ODS_EXECUTE_LOG TABLE ZB_SERV TBS_ZB_SERV_NEW 29
ODS_EXECUTE_LOG TABLE ZBA_OUT TBS_ZB_SERV 27
ODS_EXECUTE_LOG TABLE ZB_HESPER TBS_MID_3G 14
ODS_EXECUTE_LOG TABLE ZBA_DI TBS_ZB_MAN 11
ODS_EXECUTE_LOG TABLE PM_DSS DM 0.6875
ODS_EXECUTE_LOG TABLE ZBA_HD TBS_ZBA_HD 0.5625
ODS_EXECUTE_LOG TABLE ZB_MAN TBS_ZB_MAN 0.5625
ODS_EXECUTE_LOG TABLE SD_WANGYONG TBS_SD 0.5625
ODS_EXECUTE_LOG TABLE DFM_DM DM 0.1875
ODS_EXECUTE_LOG TABLE ZB_DIM TBS_ZB_DIM 0.1875
表约束：
select a.owner,
a.table_name,
a.constraint_name,
a.constraint_type,
b.column_name,
b.position
from dba_constraints a, dba_cons_columns b
where a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.constraint_type in ('P', 'R')
and a.table_name = 'ODS_EXECUTE_LOG'
no rows selected
选择度：
SQL>SELECT count(distinct acct_month),
2 count(distinct pkg_name),
3 count(distinct procname),
4 count(distinct prov_id),
5 count(*)
6 from ZBA_DWA.ODS_EXECUTE_LOG;
COUNT(DISTINCTACCT_MONTH) COUNT(DISTINCTPKG_NAME) COUNT(DISTINCTPROCNAME) COUNT(DISTINCTPROV_ID) COUNT(*)
------------------------- ----------------------- ----------------------- ---------------------- ----------
1216 90 666 62 1329390
建立索引：
create index IDX_ODS_EL ON owner.ODS_EXECUTE_LOG(ACCT_MONTH,PROCNAME,PROV_ID,PKG_NAME);
2.5 SQL ID: 7vg112j2q99x5
SQLID 7vg112j2q99x5的主要内容如下:
|
ctd07c13dpvxg |
采样时段 |
执行次数 |
单次执行时间（ms） |
逻辑读 |
|
优化前 |
20141201 23:58:47- 20141204 10:58:19 |
6,022,183 |
2.2 |
|
|
优化后 |
|
|
|
|
|
改进率 |
|
|
|
|
SELECT "PROV_ID", "FILE_ID", "FILE_TYPE", "ACCT_DATE"
FROM "ZB_SRC"."ZB_PROV_NO_FILE_LIST" "D"
WHERE "PROV_ID" = :1
AND "FILE_TYPE" = 'D'
AND "FILE_ID" || 'HP' = :2
AND "ACCT_DATE" = SUBSTR(:3, 1, 6);
非分区表：
SQL> select table_owner,table_name,partition_name from ALL_TAB_PARTITIONS where table_name='ZB_PROV_NO_FILE_LIST';
no rows selected
其上并无索引：
SQL> select owner,INDEX_NAME,table_owner,TABLE_NAME,DISTINCT_KEYS,PARTITIONED from dba_indexes where table_name='ZB_PROV_NO_FILE_LIST';
no rows selected
列数据信息：
字段类型：
select T.OWNER,T.TABLE_NAME,T.COLUMN_NAME,T.DATA_TYPE,T.AVG_COL_LEN,T.HISTOGRAM
from dba_tab_cols t
where t.owner = 'ZB_SRC' AND t.TABLE_NAME = 'ZB_PROV_NO_FILE_LIST';
COLUMN_NAME DATA_TYPE AVG_COL_LEN HISTOGRAM
-------------------- ------------ ----------- ---------------
PROV_ID VARCHAR2 4 FREQUENCY
FILE_ID VARCHAR2 15 FREQUENCY
TAB_NAME VARCHAR2 25 NONE
IF_CODE_MAP NUMBER 3 NONE
FILE_TYPE VARCHAR2 2 FREQUENCY
FILE_NAME VARCHAR2 23 NONE
INTERFACE_NAME VARCHAR2 30 NONE
INTERFACE_CLASS VARCHAR2 3 NONE
EFF_DATE VARCHAR2 5 NONE
ACCT_DATE VARCHAR2 7 FREQUENCY
UPDATE_DATE VARCHAR2 2 NONE
segment大小
select segment_name,segment_type,owner,tablespace_name,bytes/1024/1024 MB from dba_segments where segment_name='ZB_PROV_NO_FILE_LIST';
SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_NAME MB
-------------------- ------------------ -------- ------------------ ----------
ZB_PROV_NO_FILE_LIST TABLE ZB_SRC TBS_ZB_SRC 4
表约束：
select a.owner,
a.table_name,
a.constraint_name,
a.constraint_type,
b.column_name,
b.position
from dba_constraints a, dba_cons_columns b
where a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.constraint_type in ('P','R')
and a.table_name = 'ZB_PROV_NO_FILE_LIST'
and a.owner = 'ZB_SRC';
no rows selected
查看选择度：
SQL> select count(distinct PROV_ID),count(distinct FILE_TYPE),count(distinct FILE_ID),count(distinct ACCT_DATE),count(*) from ZB_SRC.ZB_PROV_NO_FILE_LIST;
COUNT(DISTINCTPROV_ID) COUNT(DISTINCTFILE_TYPE) COUNT(DISTINCTFILE_ID) COUNT(DISTINCTACCT_DATE) COUNT(*)
---------------------- ------------------------ ---------------------- ------------------------ ----------
31 2 104 48 29137
建立索引：
create index zb_pnfl_idx1 on ZB_SRC.ZB_PROV_NO_FILE_LIST(FILE_ID, ACCT_DATE,PROV_ID, FILE_TYPE,);
2.6 SQL ID: 8dk8p4hrpdy46
SQLID 8dk8p4hrpdy46的主要内容如下:
|
8dk8p4hrpdy46 |
采样时段 |
执行次数 |
单次执行时间（ms） |
逻辑读 |
|
优化前 |
20141201 23:58:47- 20141204 10:58:19 |
1,033,629 |
1.28 |
4,132,548
|
|
优化后 |
|
|
|
|
|
改进率 |
|
|
|
|
select round(compare_end_time / 86400000) num,
mod(compare_end_time, 86400000) v_num,
a.id,
a.up_type_id,
a.business_type_id,
a.report_type_id,
a.compare_begin_time,
a.compare_end_time,
a.data_days,
a.compare_data_time
from dm.DM_UP_SYS_UP_MANA a
where a.compare_end_time > 86399999
and REPORT_TYPE_ID = '01'
and IS_AVAILABLE = '01'
非分区表：
SQL> select table_owner,table_name,partition_name from ALL_TAB_PARTITIONS where table_name='DM_UP_SYS_UP_MANA';
no rows selected
表属主：
select owner,table_name from dba_tables where table_name='DM_UP_SYS_UP_MANA';
OWNER TABLE_NAME
--------------- ----------------------------------------
DM DM_UP_SYS_UP_MANA
DATA_APP DM_UP_SYS_UP_MANA
SD_WANGYONG DM_UP_SYS_UP_MANA
DM用户下的表上并没有索引
SQL> select owner,INDEX_NAME,table_owner,TABLE_NAME,DISTINCT_KEYS,PARTITIONED from dba_indexes where table_name='DM_UP_SYS_UP_MANA';
no rows selected
列数据信息：
字段类型：
select T.COLUMN_NAME,T.DATA_TYPE,T.AVG_COL_LEN,T.HISTOGRAM
from dba_tab_cols t
where t.owner = 'DM' AND t.TABLE_NAME = 'DM_UP_SYS_UP_MANA';
COLUMN_NAME DATA_TYPE AVG_COL_LEN HISTOGRAM
------------------------------ ---------- ----------- ---------------
ID VARCHAR2 6 NONE
UP_TYPE_ID VARCHAR2 3 NONE
BUSINESS_TYPE_ID VARCHAR2 4 NONE
REPORT_TYPE_ID VARCHAR2 3 FREQUENCY
COMPARE_BEGIN_TIME NUMBER 3 NONE
COMPARE_END_TIME NUMBER 5 FREQUENCY
COMPARE_DATA_TIME NUMBER 4 NONE
OPERATOR VARCHAR2 9 NONE
OPERATE_TIME DATE 8 NONE
IS_AVAILABLE VARCHAR2 3 FREQUENCY
DATA_DAYS NUMBER 3 NONE
segmens大小
select segment_name,segment_type,owner,tablespace_name,bytes/1024/1024 MB from dba_segments where segment_name='DM_UP_SYS_UP_MANA' and owner='MD';
SEGMENT_NAME SEGMENT_TYPE OWNER TABLESPACE_N MB
------------------- ------------------ --------------- ------------ ----------
DM_UP_SYS_UP_MANA TABLE DM DM 0.1875
表约束：
select a.owner,
a.table_name,
a.constraint_name,
a.constraint_type,
b.column_name,
b.position
from dba_constraints a, dba_cons_columns b
where a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.constraint_type in ('P','R')
and a.table_name = 'DM_UP_SYS_UP_MANA'
and a.owner = 'DM';
no rows selected
查看选择度：
SQL> select count(distinct compare_end_time),count(distinct REPORT_TYPE_ID),count(distinct IS_AVAILABLE),count(*) from dm.DM_UP_SYS_UP_MANA;
COUNT(DISTINCTCOMPARE_END_TIME) COUNT(DISTINCTREPORT_TYPE_ID) COUNT(DISTINCTIS_AVAILABLE) COUNT(*)
------------------------------- ----------------------------- --------------------------- ----------
20 3 1 27
建立索引：
create index dm_usum_idx1 on dm.DM_UP_SYS_UP_MANA(compare_end_time);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29337971/viewspace-1816605/ ，如需转载，请注明出处，否则将追究法律责任。
