Home » RDBMS Server » Performance Tuning » Disk I/O started behaving differently when loading data (Oracle, 9.2.0.6.0)
Disk I/O started behaving differently when loading data [message #339175] Thu, 07 August 2008 00:13 Go to next message
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 #339183 is a reply to message #339175] Thu, 07 August 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please suggest what could be the possible reasons of such a change?

There are about an infinite reasons plus those that you didn't see when you say that nothing changes.

Start to know who/what is making IO.

Regards
Michel
Re: Disk I/O started behaving differently when loading data [message #339257 is a reply to message #339175] Thu, 07 August 2008 02:53 Go to previous messageGo to next message
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 #339328 is a reply to message #339257] Thu, 07 August 2008 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They are file reads, so search who/what make them.
Have a look at SQL sections.

Regards
Michel
Re: Disk I/O started behaving differently when loading data [message #339723 is a reply to message #339328] Fri, 08 August 2008 04:15 Go to previous messageGo to next message
Fadai
Messages: 9
Registered: August 2008
Junior Member


I have found the SQL statemet that is responsible for it.
During it's execution the Performance Monitor tool of Windows
shows very often peaks of Disk Read Time and Disk Write Time.

I've tried many plans of the query but these peaks show themselves.


Re: Disk I/O started behaving differently when loading data [message #339750 is a reply to message #339723] Fri, 08 August 2008 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And we're meant to do what?

If you give use the query, the explain plan it uses, details of the indexes available on the tables, along with the sizes of the tables, we may be able to help.
Re: Disk I/O started behaving differently when loading data [message #340002 is a reply to message #339750] Sun, 10 August 2008 23:19 Go to previous messageGo to next message
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 #340003 is a reply to message #339175] Sun, 10 August 2008 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
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))
/

Indexes need to be on the following columns:
where BF.DT_OPEN > WH.DT_OPEN
AND BF.DT_OPEN <= WH.DT_CLOSE
AND BF.REC_STATUS IN(0))
Re: Disk I/O started behaving differently when loading data [message #340055 is a reply to message #340003] Mon, 11 August 2008 04:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Disk I/O started behaving differently when loading data [message #340447 is a reply to message #340218] Tue, 12 August 2008 23:02 Go to previous messageGo to next message
Fadai
Messages: 9
Registered: August 2008
Junior Member



Anybody please suggest.
Re: Disk I/O started behaving differently when loading data [message #340448 is a reply to message #340447] Tue, 12 August 2008 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Fadai wrote on Tue, 12 August 2008 21:02



Anybody please suggest.


Nobody owes you any answer.
Not every SQL statement can be made faster.
Embrace YOUR reality!
Re: Disk I/O started behaving differently when loading data [message #340452 is a reply to message #340448] Wed, 13 August 2008 00:11 Go to previous message
Fadai
Messages: 9
Registered: August 2008
Junior Member


I didnt say that somebody owes me something.
I just asked for a suggestion.
Sorry for troubling.
Good bye.
Previous Topic: What it suggests?
Next Topic: Calculate leaf_blocks, avg_leaf_blocks_per_key etc.
Goto Forum:
  


Current Time: Wed Dec 07 03:18:32 CST 2016

Total time taken to generate the page: 0.13378 seconds