| Disk I/O started behaving differently when loading data [message #339175] |
Thu, 07 August 2008 00:13  |
Fadai
Messages: 9
Registered: August 2008
|
Junior Member |
|
|
Hi all.
We have a database against which new data is processed each night.
Data is loaded with the aid of ordinary SQL statements....
inserting new records and updating changed ones, nearly 35 tables processed.
It used to take not more than three hours but yesterday it took
6.5 hours after which we gathered statistics on all tables (they was 4 days old)
and started the load today which didnt do any better.
No parameters of the database were chagned.
Buffer cache is nearly 1400 mb and shared pool is 360 mb.
We noticed that on the Performance console of the server CPU showing not more than 20% load but Avg. Disk Queue Length shows 100 load during all the loading process.
Please suggest what could be the possible reasons of such a change?
What to start with so that to drill down to the root cause?
Thanks much.
|
|
|
|
|
|
| Re: Disk I/O started behaving differently when loading data [message #339257 is a reply to message #339175] |
Thu, 07 August 2008 02:53  |
Fadai
Messages: 9
Registered: August 2008
|
Junior Member |
|
|
Hi.
Thats the Top 5 Events from the statspack
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 3,489,820 59,141 46.75
db file scattered read 1,585,818 26,082 20.62
direct path write 413,969 10,285 8.13
PX Deq Credit: send blkd 65,610 8,000 6.32
CPU time 7,167 5.67
What other parts of the statspack must be attented to in this case?
What does "db file sequential read" and "db file scattered read"
indicate to and how to stuggle with this.
Please suggest
Thanks.
|
|
|
|
|
|
|
|
|
|
| Re: Disk I/O started behaving differently when loading data [message #340002 is a reply to message #339750] |
Sun, 10 August 2008 23:19 |
Fadai
Messages: 9
Registered: August 2008
|
Junior Member |
|
|
Hi. Thanks for the reply.
Here is my query
SELECT BF.* FROM LDR_BF.FCT_CHARGE_PRC BF, DWH.FCT_CHARGE_PRC WH
WHERE WH.ID_ORD_LIAB = BF.ID_ORD_LIAB
AND WH.ID_CONTRACTS = BF.ID_CONTRACTS
AND WH.ID_FINSTR = BF.ID_FINSTR
AND WH.ID_CONOPER = BF.ID_CONOPER
AND WH.ID_TACC = BF.ID_TACC
AND BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE
AND BF.REC_STATUS IN(0)
I have a unique index on the DWH.FCT_CHARGE_PRC named
AK_CHARGE_PRC_FCT_CHAR_CLOSE
that includes all the columns in the WHERE clause of the query :
ID_CONTRACTS, ID_ORD_LIAB, ID_FINSTR, ID_CONOPER, ID_TACC, DT_OPEN, DT_CLOSE
Here is the current plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=33776 Card=1 Bytes=1
85)
1 0 HASH JOIN (Cost=33776 Card=1 Bytes=185)
2 1 TABLE ACCESS (FULL) OF 'FCT_CHARGE_PRC' (Cost=201 Card=8
5112 Bytes=12000792)
3 1 INDEX (FAST FULL SCAN) OF 'AK_CHARGE_PRC_FCT_CHAR_CLOSE'
(UNIQUE) (Cost=15495 Card=21017300 Bytes=924761200)
Now I'm trying to change it so that it goes faster.
I tried
/*+ USE_NL(BF, WH)*/
/*+ FULL(WH)*/
/*+ USE_MERGE(BF WH)*/
but none of them made it speed up.
What else could you please suggest in a case like this one?
Thanks again.
|
|
|
|
|
|
| Re: Disk I/O started behaving differently when loading data [message #340055 is a reply to message #340003] |
Mon, 11 August 2008 04:53 |
Fadai
Messages: 9
Registered: August 2008
|
Junior Member |
|
|
Thanks for the reply.
I created one combined index on
BF.DT_OPEN, BF.DT_CLOSE AND BF.REC_STATUS
and another one on WH.DT_OPEN and WH.DT_CLOSE
As you see the plan shows that the new created indexes are not used but the plan became different
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1030 Card=254 Bytes=
33274)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'FCT_CHARGE_PRC' (Cost=14 Card=25
4 Bytes=33274)
3 1 FILTER
4 3 INDEX (RANGE SCAN) OF 'AK_CHARGE_PRC_FCT_CHAR_CLOSE' (
UNIQUE) (Cost=4 Card=1 Bytes=44)
There is no data in LDR_BF.FCT_CHARGE_PRC now, this is why I cannot say for sure if the suggested form of the query speeded it up , alhough Cost, Cardinality and Bytes colums were decreased and the resultant FILTER operation has no Cost at all.
Pretty soon we shall see if that gave the expected result.
Thanks very much.
|
|
|
|
| Re: Disk I/O started behaving differently when loading data [message #340104 is a reply to message #340055] |
Mon, 11 August 2008 08:25 |
Fadai
Messages: 9
Registered: August 2008
|
Junior Member |
|
|
Hi again.
Just interesting how would you rewrite the query
if we needed columns from both tables.
I tried this
SELECT BF.*, WH.* FROM LDR_BF.FCT_CHARGE_PRC BF,
( SELECT WH.* FROM DWH.FCT_CHARGE_PRC WH, LDR_BF.FCT_CHARGE_PRC BF
WHERE BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE
AND BF.REC_STATUS IN(0) ) WH
WHERE WH.ID_ORD_LIAB = BF.ID_ORD_LIAB
AND WH.ID_CONTRACTS = BF.ID_CONTRACTS
AND WH.ID_FINSTR = BF.ID_FINSTR
AND WH.ID_CONOPER = BF.ID_CONOPER
AND WH.ID_TACC = BF.ID_TACC
Seems didnt help much in speed.
Thanks
|
|
|
|
| Re: Disk I/O started behaving differently when loading data [message #340218 is a reply to message #340104] |
Tue, 12 August 2008 00:48 |
Fadai
Messages: 9
Registered: August 2008
|
Junior Member |
|
|
This works
SELECT BF.*
FROM LDR_BF.FCT_CHARGE_PRC BF
where (BF.ID_ORD_LIAB , BF.ID_CONTRACTS, BF.ID_FINSTR, BF.ID_CONOPER, BF.ID_TACC ) IN
(select WH.ID_ORD_LIAB, WH.ID_CONTRACTS, WH.ID_FINSTR, WH.ID_CONOPER, WH.ID_TACC
from DWH.FCT_CHARGE_PRC WH
where BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE
AND BF.REC_STATUS IN(0))
if we select columns from only BF.
Please suggest how you would rewrite the query if needed
columns from both tables.
Thank you.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
