Up on investigation with awr i found the query that was causing the problems
First i've seen in awr top 5 wait event at nr 2 the read by other session wait event
( is a wait event that occurs when one session wants to read a block another session is reading from disc and loading into the memory (buffer cache).
The statements causing the problem was the following
select SUM_INSURED
from t_policy_ct_acce
where POLICY_CT_ID =
(select policy_ct_id
from t_policy_ct
where insured_id=:1 and ct_id=:2 and LIABILITY_STATUS in (1,2))
and interest_id in (
select interest_id
from t_ct_interest_defi
where interest_code in ('203','234','123','1613','1621','1625'))
That statement run every hour 80.000 times.
That what was causing the read by a other session.
on examing the execution plan on first glance there was nothing wrong
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 19 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | T_POLICY_CT_ACCE | 1 | 11 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_POLICY_CT_ACCE__POLICY_CT_ID | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T_POLICY_CT | 1 | 19 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_T_POLICY_CT__INSURED_ID | 3 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | T_CT_INTEREST_DEFI | 1 | 8 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_T_CT_INTEREST_DEFI | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
The problem are the table access by index rowid. That means that for the columns that are not in the index is has to go to disk to get the information or even it the table is in the buffer cache it still has to wait a few miliseconds. That don't seems a lot but if the statement is executed 80.000 per hour well you do the math.
CREATE INDEX DIT4_OWN.FAT_T_POLICY_CT_ACCE ON DIT4_OWN.T_POLICY_CT_ACCE
(POLICY_CT_ID, INTEREST_ID, SUM_INSURED)
LOGGING
TABLESPACE DIT4_I
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
COMPUTE STATISTICS
ONLNE;
CREATE INDEX DIT4_OWN.FAT_POLICY_CT ON DIT4_OWN.T_POLICY_CT
(INSURED_ID, CT_ID, LIABILITY_STATUS, POLICY_CT_ID)
LOGGING
TABLESPACE DIT4_I
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
COMPUTE STATISTICS
ONLINE;
The execution plan looks like this
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 19 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FAT_T_POLICY_CT_ACCE | 1 | 11 | 1 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
|* 4 | INDEX RANGE SCAN | FAT_POLICY_CT | 1 | 19 | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T_CT_INTEREST_DEFI | 1 | 8 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T_CT_INTEREST_DEFI | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
As you see the fat index are in place and you don't see for those 2 indexen the table acces by index rowid anymore.That solved the problem the batch job that used to run 22 hours war running in 5 and a half hour.
A Fat index is usefull but of course not always. The above problem was one where the fat index made the difference.
No comments:
Post a Comment