分公司CCDB数据库的优化
查看数据库归档空间的使用率，发现使用率过高
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 99.71 0 77
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
查看文件系统使用情况
Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 6062080 3567000 2475816 59% /
/dev/vg00/lvol1 1835008 335832 1487544 18% /stand
/dev/vg00/lvol8 8912896 3093152 5774936 35% /var
/dev/vg00/lvol7 5996544 3107152 2866920 52% /usr
/dev/vg00/lvtuxedo 2097152 18012 1949200 1% /tuxedo
/dev/vg00/lvol4 8388608 626032 7707864 8% /tmp
/dev/vg00/lvol6 11403264 7334360 4037304 64% /opt
/dev/vg00/lvol5 1048576 17882 966410 2% /home
/dev/vg00/bmc 41943040 3600707 35945948 9% /bmc
/dev/vg12/lvdata 870318080 295543087 538863027 35% /oracle
/dev/vg11/lvfs02 45842432 31647582 13307712 70% /work
/dev/vg11/lvfs01 163840000 144825221 17826710 89% /oracle_bak
根据文件系统使用情况，扩容数据库归档空间，增加一倍
SQL> alter system set db_recovery_file_dest_size = 70G scope=both;
扩容之后的使用率如下
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 49.85 0 77
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
查看各个表空间的使用状况，注意使用率超过80%的表空间
SELECT tablespace_name,
ROUND(used_percent, 2) used_percent,
ROUND(total_M, 2) total_M,
ROUND(used_M, 2) used_M
FROM (SELECT d.tablespace_name,
NVL(SUM(used_blocks), 0) * 8 / 1024 used_M,
SUM(blocks) * 8 / 1024 total_m,
NVL(SUM(used_blocks), 0) * 100 / SUM(blocks) used_percent
FROM v$sort_segment v, dba_temp_files d
WHERE d.tablespace_name = v.tablespace_name(+)
GROUP BY d.tablespace_name
UNION ALL
SELECT ts.NAME tablespace_name,
ts.size_M - fs.size_M used_M,
ts.size_M total_M,
(ts.size_M - fs.size_M) / ts.size_M * 100 used_percent
FROM (SELECT ts.NAME, SUM(bytes) / 1024 / 1024 size_M
FROM v$datafile df, v$tablespace ts
WHERE df.ts# = ts.ts#
GROUP BY ts.NAME) ts,
(SELECT tablespace_name, SUM(bytes / 1024 / 1024) size_M
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) fs
WHERE ts.NAME = fs.tablespace_name) where used_percent >80
ORDER BY used_percent DESC;
TABLESPACE_NAME USED_PERCENT TOTAL_M USED_M
------------------------------ ------------ ---------- ----------
CLUDW 94.92 900 854.31
ZJCCDTTBS 76.97 147452 113487.75
ZJCCIDXTBS 61.78 34816 21510.31
SYSAUX 48.9 4096 2002.81
USERS 24.06 512 123.19
SYSTEM 9.03 8192 739.94
UNDOTBS1 8.36 22709 1898.63
TEMP 0 4177 0
注意CLUDW表空间使用率已接近95%，建议及时扩容
查询数据库等待事件top10,关注前前几个等待事件
select *
from (
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Total"
from v$session_Wait
group by event
order by 4 desc
)
where rownum<=10;
EVENT Prev Curr Total
---------------------------------------------------------------- ---------- ---------- ----------
SQL*Net message from client 0 256 256
read by other session 0 17 17
rdbms ipc message 0 13 13
db file scattered read 1 6 7
Space Manager: slave idle wait 0 2 2
DIAG idle wait 0 2 2
asynch descriptor resize 1 0 1
pmon timer 0 1 1
direct path read 0 1 1
VKTM Logical Idle Wait 0 1 1
通过等待事件，看到数据库有较多的全表扫描和物理读操作
查看Top 10 bad SQL
col SQL_TEXT for a100
SELECT *
FROM (SELECT parsing_user_id executions,
sorts,
command_type,
disk_reads,
SQL_ID,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE rownum < 11;
DISK_READS SQL_ID SQL_TEXT
---------- ------------- ----------------------------------------------------------------------------------------------------
673093191 8rb66963ddbvh delete ob_papercase where paperid=:1 and papercaseid=:2
80733977 97uc9fz6ya4jk update clcc_ivrsati set mddt=:1,curflag=:2 where curflag=:3 and mddt<:4
17123914 fd7a037zv2jr3 select clcc_ivrsati.* from clcc_ivrsati where phone=:1 and curflag=:2
16377108 3d3a3cgwrkzrp update clcc_ivrsati set mddt=:1,curflag=:2 where curflag in ('0','1') and phone=:3
14964924 3a4tm3cxpg2m8 update clcc_ivrsati set mddt=:1,curflag=:2 where curflag=:3 and phone=:4
10139620 bk0ra9kb1hrm7 update clcc_ivrsati set mddt=:1,result=:2,curflag=:3 where curflag=:4 and phone=:5
3235483 c5gwhhdc33adu select count(*) from ob_contact , ob_joblist , ob_task left join ob_trans_sum on ob_task.pd_transs
umid=ob_trans_sum.pd_transsumid left join ob_dptattr on ob_task.center_code=ob_dptattr.segme
nt_code left join service_mode on ob_task.servicemode=service_mode.smode left join sal
es_channel on ob_task.sales_channel=sales_channel.sccode and ob_task.data_from=sales_channel.scfrom
where (ob_task.pd_jobtype=:1) and (ob_task.pd_status in ('9')) and (ob_task.pd_updatedt>=:2) an
d (ob_task.pd_updatedt<=:3) and (ob_contact.cust_name=:4) and (ob_task.pd_updatedt<>ob_task.pd_drawd
ate) and ( ob_task.cust_no=ob_contact.cust_no and ob_task.data_from=ob_contact.data_from
and ob_task.pd_jobid=ob_joblist.jobid ) and ( receive_branch=:5 )
3232057 664v9fng2mf17 select distinct ob_contact.cust_name, ob_task.pd_jobname, ob_task.pd_drawdate
, ob_task.data_from, ob_contact.cust_branch, ob_contact.cust_branch_ccdb, ob_ta
sk.branch_no_ccdb, ob_task.branch_no, ob_task.pol_name, ob_task.receive_branch,
ob_task.trans_branch, ob_task.trans_view , ob_task.cust_no, ob_task.key_date,
ob_task.memo,ob_task.pd_referenceid,ob_task.pd_jobid,ob_task.pd_updatedt,ob_task.pd_customerid,ob_
task.pd_curstatus,ob_task.pd_status_desc,ob_task.pd_status,ob_task.pd_jobtype,ob_task.pd_jobtype_des
c,ob_task.clerk_no,ob_task.clerk_name,ob_task.clerk_branch_no,addition_info,ob_task.endcooldate, ob_
task.coolperiod,do_agent,ob_task.service_no,service_mode.description as servicemode,ob_task.agent_re
sp,ob_trans_sum.start_time,ob_trans_sum.end_time from ob_contact,ob_joblist, ob_task le
ft join ob_trans_sum on ob_task.pd_transsumid=ob_trans_sum.pd_transsumid left join ob_dptattr
461272 ffqzg8f66ajd7 insert into ob_task_hist (branch_no,branch_no_ccdb,data_from,data_no,cust_no,cg_no,sg_no,key_date,se
rvice_no,servicemode,relation_type,relation_type_desc,dial_custno,dial_custname,dial_custtype,trans_
time,trans_agent,trans_view,trans_branch,receive_time,receive_agent,receive_branch,do_time,do_agent,
memo,pol_code,pol_version,pol_type,pol_name,reason_notcontact,addition_info,agent_branchno,clerk_no,
clerk_name,clerk_branch_no,filtrate_batchno,pd_customerid,pd_curstatus,pd_dialcnt,pd_calllist,pd_ccc
,pd_ccc_desc,pd_ccc_cflag,pd_ccc_rflag,pd_agentid,pd_phnnum,pd_startdt,pd_connectdt,pd_dropdt,pd_rel
easedt,pd_caseid,pd_jobcaseid,pd_phntype,pd_phntype_desc,pd_dt2d,pd_device,pd_recalltime,pd_campaign
id,pd_sort,pd_prio,pd_dialnum,pd_channel,pd_login_caseid,pd_jobid,pd_followagent,pd_dialmode,pd_upda
teagent,pd_updatedt,pd_talktime,pd_acwtime,pd_idletime,pd_referenceid,pd_jobname,pd_jobtype,pd_paper
id,pd_papername,pd_agenttype,pd_status,pd_paperstatus,pd_recallphn,pd_groupid,pd_groupname,pd_cdc,pd
457260 f1kv95xw6cw9x update ob_task set branch_no=:1,branch_no_ccdb=:2,data_from=:3,data_no=:4,cust_no=:5,cg_no=:6,sg_no=
:7,key_date=:8,service_no=:9,servicemode=:10,relation_type=:11,relation_type_desc=:12,dial_custno=:1
3,dial_custname=:14,dial_custtype=:15,trans_time=:16,trans_agent=:17,trans_view=:18,trans_branch=:19
,receive_time=:20,receive_agent=:21,receive_branch=:22,do_time=:23,do_agent=:24,memo=:25,pol_code=:2
6,pol_version=:27,pol_type=:28,pol_name=:29,reason_notcontact=:30,addition_info=:31,agent_branchno=:
32,clerk_no=:33,clerk_name=:34,clerk_branch_no=:35,filtrate_batchno=:36,pd_customerid=:37,pd_curstat
us=:38,pd_dialcnt=:39,pd_calllist=:40,pd_ccc=:41,pd_ccc_desc=:42,pd_ccc_cflag=:43,pd_ccc_rflag=:44,p
d_agentid=:45,pd_phnnum=:46,pd_startdt=:47,pd_connectdt=:48,pd_dropdt=:49,pd_releasedt=:50,pd_caseid
=:51,pd_jobcaseid=:52,pd_phntype=:53,pd_phntype_desc=:54,pd_dt2d=:55,pd_device=:56,pd_recalltime=:57
,pd_campaignid=:58,pd_sort=:59,pd_prio=:60,pd_dialnum=:61,pd_channel=:62,pd_login_caseid=:63,pd_jobi
查询各种指标，根据各种指标反映的问题是否和等待事件有关联关系，进一步确定问题所在
select a.cache_hit_percent,
e.rowcache_hitratio,
d.pin_ration_percent,
d.get_ratio_percent,
f.mem_sort_percent,
b.latch_ratio_percent
from (SELECT ROUND((1 - ((s1.VALUE - s4.VALUE - s5.VALUE) /
(s2.VALUE + s3.VALUE - s4.VALUE - s5.VALUE))) * 100,
2) cache_hit_percent
FROM v$sysstat s1,
v$sysstat s2,
v$sysstat s3,
v$sysstat s4,
v$sysstat s5
WHERE s1.NAME = 'physical reads'
AND s2.NAME = 'consistent gets'
AND s3.NAME = 'db block gets'
AND s4.NAME = 'physical reads direct (lob)'
AND s5.NAME = 'physical reads direct') a,
(select round(100 * (1 - sum(misses) / sum(gets)), 2) latch_ratio_percent
from v$latch) b,
(select round(100 * c.pin_RATIO / b.total, 2) pin_ration_percent,
round(100 * (a.get_ratio / b.total), 2) get_ratio_percent
from (select sum(pinhitratio) pin_ratio from v$LIBRARYCACHE) c,
(select sum(gethitratio) get_ratio from v$LIBRARYCACHE) a,
(select count(*) total from v$LIBRARYCACHE) b) d,
(select round(100 * (1 - sum(getmisses) / sum(gets)), 2) rowcache_hitratio
from v$rowcache) e,
(SELECT round(100 * s1.VALUE / (s2.VALUE + s1.VALUE), 2) mem_sort_percent
FROM v$sysstat s1, v$sysstat s2
WHERE s1.NAME = 'sorts (memory)'
AND s2.NAME = 'sorts (disk)') f;
CACHE_HIT_PERCENT ROWCACHE_HITRATIO PIN_RATION_PERCENT GET_RATIO_PERCENT MEM_SORT_PERCENT LATCH_RATIO_PERCENT
----------------- ----------------- ------------------ ----------------- ---------------- -------------------
88.47 99.64 66.82 53.85 100 99.78
CACHE_HIT_PERCENT值比较低，SGA不足，必有明显的物理读
查看内存配置，查看内存配置是否合理（结合查询到的各种指标，初步判断内存是否合理）
select component,
current_size / 1024 / 1024 cur_size_M,
max_size / 1024 / 1024 max_size_M
from v$sga_dynamic_components;
COMPONENT CUR_SIZE_M MAX_SIZE_M
---------------------------------------------------------------- ---------- ----------
shared pool 1200 1888
large pool 16 16
java pool 16 16
streams pool 16 16
DEFAULT buffer cache 2704 3040
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
DEFAULT 2K buffer cache 0 0
DEFAULT 4K buffer cache 0 0
DEFAULT 8K buffer cache 0 0
DEFAULT 16K buffer cache 0 0
DEFAULT 32K buffer cache 0 0
Shared IO Pool 0 0
ASM Buffer Cache 0 0
查询三个top10sql语句，发现执行次数最多、内存、硬盘读取量大的sql,根据address查询在系统中的执行计划，是否需要进一步优化
col t1_id for a20
col SQL_DISK_READS for a100
select 'most_disk_read_top ' || rownum t1_id,
sql_disk_reads,
disk_reads,
sql_id
from (select sql_text sql_disk_reads, disk_reads, sql_id
from v$sqlarea
order by disk_reads desc)
where rownum < 11
union all
select 'buffer_cache_top ' || rownum t2_id,
sql_buffer_gets,
buffer_gets,
sql_id
from (select sql_text sql_buffer_gets, buffer_gets, sql_id
from v$sqlarea
order by buffer_gets desc)
where rownum < 11
union all
select 'most_exce_top ' || rownum t3_id,
sql_executions,
executions,
sql_id
from (select sql_text sql_executions, executions, sql_id
from v$sqlarea
order by executions desc)
where rownum < 11;
T1_ID SQL_DISK_READS
-------------------- ----------------------------------------------------------------------------------------------------
most_disk_read_top 1 delete ob_papercase where paperid=:1 and papercaseid=:2
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 111 (100)| |
| 1 | DELETE | OB_PAPERCASE | | | | |
| 2 | TABLE ACCESS FULL| OB_PAPERCASE | 2 | 88 | 111 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
most_disk_read_top 2 update clcc_ivrsati set mddt=:1,curflag=:2 where curflag=:3 and mddt<:4
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 2 (100)| |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
| 2 | TABLE ACCESS FULL| CLCC_IVRSATI | 1 | 25 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
most_disk_read_top 3 select clcc_ivrsati.* from clcc_ivrsati where phone=:1 and curflag=:2
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7945 (100)| |
| 1 | TABLE ACCESS FULL| CLCC_IVRSATI | 690 | 132K| 7945 (1)| 00:01:36 |
----------------------------------------------------------------------------------
most_disk_read_top 4 update clcc_ivrsati set mddt=:1,curflag=:2 where curflag in ('0','1') and phone=:3
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 7945 (100)| |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
| 2 | TABLE ACCESS FULL| CLCC_IVRSATI | 690 | 20010 | 7945 (1)| 00:01:36 |
-----------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 4 (100)| |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CLCC_IVRSATI | 1 | 29 | 4 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | CLCCIVR_SATI_IDX3 | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
most_disk_read_top 5 update clcc_ivrsati set mddt=:1,curflag=:2 where curflag=:3 and phone=:4
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 7945 (100)| |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
| 2 | TABLE ACCESS FULL| CLCC_IVRSATI | 1 | 29 | 7945 (1)| 00:01:36 |
-----------------------------------------------------------------------------------
most_disk_read_top 6 update clcc_ivrsati set mddt=:1,result=:2,curflag=:3 where curflag=:4 and phone=:5
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 7945 (100)| |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
| 2 | TABLE ACCESS FULL| CLCC_IVRSATI | 463 | 14353 | 7945 (1)| 00:01:36 |
-----------------------------------------------------------------------------------
most_disk_read_top 7 select count(*) from ob_contact , ob_joblist , ob_task left join ob_trans_sum on ob_task.pd_transs
umid=ob_trans_sum.pd_transsumid left join ob_dptattr on ob_task.center_code=ob_dptattr.segme
nt_code left join service_mode on ob_task.servicemode=service_mode.smode left join sal
es_channel on ob_task.sales_channel=sales_channel.sccode and ob_task.data_from=sales_channel.scfrom
where (ob_task.pd_jobtype=:1) and (ob_task.pd_status in ('9')) and (ob_task.pd_updatedt>=:2) an
d (ob_task.pd_updatedt<=:3) and (ob_contact.cust_name=:4) and (ob_task.pd_updatedt<>ob_task.pd_drawd
ate) and ( ob_task.cust_no=ob_contact.cust_no and ob_task.data_from=ob_contact.data_from
and ob_task.pd_jobid=ob_joblist.jobid ) and ( receive_branch=:5 )
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 16605 (100)| |
| 1 | SORT AGGREGATE | | 1 | 167 | | |
| 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 2 | 334 | 16605 (1)| 00:03:20 |
| 4 | NESTED LOOPS OUTER | | 2 | 312 | 16605 (1)| 00:03:20 |
| 5 | HASH JOIN OUTER | | 2 | 294 | 16605 (1)| 00:03:20 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 2 | 282 | 16601 (1)| 00:03:20 |
| 8 | TABLE ACCESS FULL | OB_CONTACT | 2 | 66 | 16595 (1)| 00:03:20 |
| 9 | INDEX RANGE SCAN | OB_TASK_IND6 | 1 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| OB_TASK | 1 | 108 | 3 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | OB_DPTATTR | 4 | 24 | 3 (0)| 00:00:01 |
| 12 | INDEX RANGE SCAN | SALES_CHANNEL_IDX3 | 1 | 9 | 0 (0)| |
| 13 | INDEX UNIQUE SCAN | U272_372 | 1 | 11 | 0 (0)| |
--------------------------------------------------------------------------------------------------------
most_disk_read_top 8 select distinct ob_contact.cust_name, ob_task.pd_jobname, ob_task.pd_drawdate
, ob_task.data_from, ob_contact.cust_branch, ob_contact.cust_branch_ccdb, ob_ta
sk.branch_no_ccdb, ob_task.branch_no, ob_task.pol_name, ob_task.receive_branch,
ob_task.trans_branch, ob_task.trans_view , ob_task.cust_no, ob_task.key_date,
ob_task.memo,ob_task.pd_referenceid,ob_task.pd_jobid,ob_task.pd_updatedt,ob_task.pd_customerid,ob_
task.pd_curstatus,ob_task.pd_status_desc,ob_task.pd_status,ob_task.pd_jobtype,ob_task.pd_jobtype_des
c,ob_task.clerk_no,ob_task.clerk_name,ob_task.clerk_branch_no,addition_info,ob_task.endcooldate, ob_
task.coolperiod,do_agent,ob_task.service_no,service_mode.description as servicemode,ob_task.agent_re
sp,ob_trans_sum.start_time,ob_trans_sum.end_time from ob_contact,ob_joblist, ob_task le
ft join ob_trans_sum on ob_task.pd_transsumid=ob_trans_sum.pd_transsumid left join ob_dptattr
most_disk_read_top 9 select count(*) from ob_contact , ob_joblist , ob_task left join ob_trans_sum on ob_task.pd_transs
umid=ob_trans_sum.pd_transsumid left join ob_dptattr on ob_task.center_code=ob_dptattr.segme
nt_code left join service_mode on ob_task.servicemode=service_mode.smode left join sal
es_channel on ob_task.sales_channel=sales_channel.sccode and ob_task.data_from=sales_channel.scfrom
where (ob_task.pd_jobtype=:1) and (ob_contact.cust_name=:2) and (ob_dptattr.cc_dptattr in ('6')
) and ( ob_task.cust_no=ob_contact.cust_no and ob_task.data_from=ob_contact.data_from
and ob_task.pd_jobid=ob_joblist.jobid ) and ( receive_branch=:3 )
most_disk_read_top 1 select distinct ob_contact.cust_name, ob_task.pd_jobname, ob_task.pd_drawdate
0 , ob_task.data_from, ob_contact.cust_branch, ob_contact.cust_branch_ccdb, ob_ta
sk.branch_no_ccdb, ob_task.branch_no, ob_task.pol_name, ob_task.receive_branch,
ob_task.trans_branch, ob_task.trans_view , ob_task.cust_no, ob_task.key_date,
ob_task.memo,ob_task.pd_referenceid,ob_task.pd_jobid,ob_task.pd_updatedt,ob_task.pd_customerid,ob_
task.pd_curstatus,ob_task.pd_status_desc,ob_task.pd_status,ob_task.pd_jobtype,ob_task.pd_jobtype_des
c,ob_task.clerk_no,ob_task.clerk_name,ob_task.clerk_branch_no,addition_info,ob_task.endcooldate, ob_
task.coolperiod,do_agent,ob_task.service_no,service_mode.description as servicemode,ob_task.agent_re
sp,ob_trans_sum.start_time,ob_trans_sum.end_time from ob_contact,ob_joblist, ob_task le
ft join ob_trans_sum on ob_task.pd_transsumid=ob_trans_sum.pd_transsumid left join ob_dptattr
buffer_cache_top 1 delete ob_papercase where paperid=:1 and papercaseid=:2
buffer_cache_top 2 update clcc_ivrsati set mddt=:1,curflag=:2 where curflag=:3 and mddt<:4
buffer_cache_top 3 select OperType.* from OperType order by OPER_NO
buffer_cache_top 4 select oper_no , description from opertype
buffer_cache_top 5 SELECT MESSAGE FROM SCROLLMSG WHERE ID=(SELECT MAX(ID) FROM SCROLLMSG WHERE DEFAULT_FLAG=1 AND BEG
IN_TIME< SYSDATE AND END_TIME >SYSDATE) AND MESSAGE IS NOT NULL
buffer_cache_top 6 SELECT MESSAGE FROM SCROLLMSG WHERE ID=(SELECT MAX(ID) FROM SCROLLMSG WHERE DEFAULT_FLAG<>1 OR DEF
AULT_FLAG IS NULL) AND MESSAGE IS NOT NULL
buffer_cache_top 7 select CITY_DESC.* from CITY_DESC order by CITYID
buffer_cache_top 8 select cityid , description , phone_code , province , gateway_code , ob_local_phonecode , ob_phoneco
de , ob_mobileprefix , ob_trunkcount from city_desc order by cityid
buffer_cache_top 9 SELECT PROVINCE FROM CITY_DESC WHERE PHONE_CODE='0571'
buffer_cache_top 10 select customtype.* from customtype order by custype
most_exce_top 1 select service_mode.* from service_mode order by smode
most_exce_top 2 select customtype.* from customtype order by custype
most_exce_top 3 select CITY_DESC.* from CITY_DESC order by CITYID
most_exce_top 4 select OperType.* from OperType order by OPER_NO
most_exce_top 5 SELECT SYS_NAME , SYS_VALUES FROM SYS_SET
most_exce_top 6 select T_SOURCEOFRECORD.* from T_SOURCEOFRECORD order by CODE
most_exce_top 7 select DEGREE_T.* from DEGREE_T order by DEGREE
most_exce_top 8 select TEMPERAMENT.* from TEMPERAMENT order by temp_no
most_exce_top 9 select MEET_TYPE.* from MEET_TYPE order by IF_TYPE
most_exce_top 10 select MEET_STATUS.* from MEET_STATUS order by MEET_STATUS
查询相关sql里面相关表上面是否有索引
select index_name,table_name from dba_indexes where table_name ='OB_PAPERCASE';
select index_name,table_name from dba_indexes where table_name ='clcc_ivrsati';---这个表没有索引
select index_name,table_name from dba_indexes where table_name ='OB_CONTACT';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
U258_341 OB_CONTACT
select index_name,table_name from dba_indexes where table_name ='OB_DPTATTR';----这个表没有索引
查看用户是哪个
select owner from dba_tables where table_name='OB_PAPERCASE';
OWNER
------------------------------
CCDB
从表空间的使用率上可以猜测到，ZJCCIDXTBS表空间应该是存放索引的。
根据上面的sql创建对应的索引
create index ccdb.ind_ob_papercase1 on ccdb.ob_papercase(paperid,papercaseid) tablespace ZJCCIDXTBS;
查看CLCC_IVRSATI表的数据情况和列上面的数据情况
SQL> select count(*) from ccdb.CLCC_IVRSATI;
COUNT(*)
----------
1507476
SQL> select distinct(curflag) from ccdb.CLCC_IVRSATI;
CURFLAG
----------
3
2
1
4
0
切换session的用户
alter session set current_schema=ccdb;
create index ccdb.ind_CLCC_IVRSATI2 on ccdb.CLCC_IVRSATI(mddt) tablespace ZJCCIDXTBS;
create index ccdb.ind_CLCC_IVRSATI3 on ccdb.CLCC_IVRSATI(phone,curflag) tablespace ZJCCIDXTBS;
索引创建之后查看预估的执行计划
explain plan for update clcc_ivrsati set mddt=:1,curflag=:2 where curflag=:3 and mddt<:4;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 25041 | 709K| 7972 (1)| 00:01:36 |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| CLCC_IVRSATI | 25041 | 709K| 7972 (1)| 00:01:36 |
|* 3 | INDEX RANGE SCAN | IND_CLCC_IVRSATI2 | 13568 | | 61 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
explain plan for select clcc_ivrsati.* from clcc_ivrsati where phone=:1 and curflag=:2;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7054 | 1391K| 4648 (1)| 00:00:56 |
| 1 | TABLE ACCESS BY INDEX ROWID| CLCC_IVRSATI | 7054 | 1391K| 4648 (1)| 00:00:56 |
|* 2 | INDEX RANGE SCAN | IND_CLCC_IVRSATI3 | 7078 | | 22 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
explain plan for update clcc_ivrsati set mddt=:1,curflag=:2 where curflag in ('0','1') and phone=:3;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 317 | 9193 | 212 (0)| 00:00:03 |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| IND_CLCC_IVRSATI3 | 317 | 9193 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
explain plan for update clcc_ivrsati set mddt=:1,curflag=:2 where curflag=:3 and phone=:4;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 7054 | 199K| 4648 (1)| 00:00:56 |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
|* 2 | INDEX RANGE SCAN| IND_CLCC_IVRSATI3 | 7054 | 199K| 22 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
explain plan for update clcc_ivrsati set mddt=:1,result=:2,curflag=:3 where curflag=:4 and phone=:5;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 7054 | 213K| 4648 (1)| 00:00:56 |
| 1 | UPDATE | CLCC_IVRSATI | | | | |
|* 2 | INDEX RANGE SCAN| IND_CLCC_IVRSATI3 | 7054 | 213K| 22 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
select * from table(dbms_xplan.display);
分析下面的sql
explain plan for
SELECT count(*)
FROM ob_contact
,ob_joblist
,ob_task
LEFT JOIN ob_trans_sum ON ob_task.pd_transsumid = ob_trans_sum.pd_transsumid
LEFT JOIN ob_dptattr ON ob_task.center_code = ob_dptattr.segment_code
LEFT JOIN service_mode ON ob_task.servicemode = service_mode.smode
LEFT JOIN sales_channel ON ob_task.sales_channel = sales_channel.sccode
AND ob_task.data_from = sales_channel.scfrom
WHERE (ob_task.pd_jobtype = :1)
AND (ob_task.pd_status IN ('9'))
AND (ob_task.pd_updatedt >= :2) and (ob_task.pd_updatedt <= :3)
AND (ob_contact.cust_name = :4)
AND (ob_task.pd_updatedt <> ob_task.pd_drawdate)
AND (
ob_task.cust_no = ob_contact.cust_no
AND ob_task.data_from = ob_contact.data_from
AND ob_task.pd_jobid = ob_joblist.jobid
)
AND (receive_branch = :5);
select COLUMN_NAME from dba_ind_columns where INDEX_NAME='U258_341';
COLUMN_NAME
----------------------------------------
CUST_NO
DATA_FROM
可以看到sql里面的ob_contact.cust_name = :4并没有索引支持，于是创建索引
create index ccdb.ind_OB_CONTACT2 on ccdb.OB_CONTACT(CUST_NAME) tablespace ZJCCIDXTBS;
再次查看执行计划
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 167 | 16 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 167 | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 2 | 334 | 16 (7)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 2 | 312 | 16 (7)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 2 | 294 | 16 (7)| 00:00:01 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 2 | 282 | 12 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| OB_CONTACT | 2 | 66 | 6 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IND_OB_CONTACT2 | 2 | | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | OB_TASK_IND6 | 1 | | 2 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | OB_TASK | 1 | 108 | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | OB_DPTATTR | 4 | 24 | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | SALES_CHANNEL_IDX3 | 1 | 9 | 0 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | U272_372 | 1 | 11 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
