Home » RDBMS Server » Performance Tuning » Contention (oracle 9i on AIX)
Contention [message #360630] Fri, 21 November 2008 12:58 Go to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
Hi gurus,

I have a situation where I find a particular sql statement taking 5 hrs to execute in a production server . The event I find is due to DB File Sequential Read.

The sql statement runs fine over weekends but slows done abnormally on weekdays.

I feel there is an IO contention on that datafile which contains the objects referred by the sql statement but need to make sure of that . Buffer cache hit ratio is 98%.


Can anybody pls guide me to confirm it.

Thanks
Re: Contention [message #360674 is a reply to message #360630] Sat, 22 November 2008 01:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
DB File Sequential Read usually means index scans.

Large batch jobs often perform better with Full Table Scans than index scans. You may find that you are using SQL inside a cursor loop - that would give big DB File Sequential Reads.

See these aritcles:
http://www.orafaq.com/node/1981
http://www.orafaq.com/node/1399

Find the poor performing SQLs and tune them individually.

Ross Leishman
Re: Contention [message #360686 is a reply to message #360674] Sat, 22 November 2008 03:42 Go to previous messageGo to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
Hi

Thanks for the reply since the batch job runs very fast on weekends so I feel sql tuning is not required it only slows when there is heavy load on the prod db.

I need to confirm if there is any contention like IO contention pls guide me to find out if there is any contention.


Thanks
Re: Contention [message #360687 is a reply to message #360686] Sat, 22 November 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
iostat

Regards
Michel
Re: Contention [message #360706 is a reply to message #360687] Sat, 22 November 2008 20:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So from what you're telling us, it does EXACTLY the same thing on weekends, processing EXACTLY the same number of rows, right?

Ross Leishman
Re: Contention [message #360725 is a reply to message #360630] Sun, 23 November 2008 09:30 Go to previous messageGo to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
yes exactly it is processing the same number of rows on weekends as well as on weekdays.

There is no difference in the execution plan as well.

Only there is a huge time difference . On weekdays the query execution takes 5 hrs (with an event as DB File sequential Read)

whereas on weekends it completes within 30 mins.

Pls note this is on a prodcution server and load on it on weekdays is huge compared to the load over weekends.


Thanks
Re: Contention [message #360962 is a reply to message #360630] Mon, 24 November 2008 08:14 Go to previous messageGo to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
I am trying to run catio.sql to list IO per object . I looked into into metalink as well the known problems with the script is already been rectified .

But whenever I run the following

exec sample_io(5,60);

I get the following error

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.SAMPLE_IO", line 23
ORA-06512: at line 1

Can anybody pls help me with this.

I am attaching the script as an attachment .The DB version I am running is

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

Thanks
  • Attachment: catio.sql
    (Size: 8.26KB, Downloaded 294 times)
Re: Contention [message #360969 is a reply to message #360630] Mon, 24 November 2008 08:33 Go to previous messageGo to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
Pls ignore my previous update as I changed the following

tail_of_lru raw(4);
this_tail_of_lru raw(4);
into
tail_of_lru raw(8);
this_tail_of_lru raw(8);

and it ran successfully.
Re: Contention [message #361277 is a reply to message #360630] Tue, 25 November 2008 11:43 Go to previous messageGo to next message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
Hi Gurus

Although I could run the procedure sample_io correctly .

But select * from io_per_object did not return any rows.

No rows fetched.

Can anybody pls help me with this .

The only modification I made in catio.sql is as follows
tail_of_lru raw(4);
this_tail_of_lru raw(4);
into
tail_of_lru raw(8);
this_tail_of_lru raw(8);

Is there any other modification I need to make.

Please help me.

Any quick response is highly appreciated.

Thanks
Re: Contention [message #361547 is a reply to message #360630] Wed, 26 November 2008 22:38 Go to previous message
dbcop
Messages: 37
Registered: September 2006
Location: india
Member
A last question before I close this topic

I need to find out the sql queries that are occupying most of the buffer cache in turn causing buffer busy waits for others.

I got a query like this

SQL> SELECT count(*) buffers,obj FROM x$bh WHERE obj in ('459993','469384','460 012') group by obj;

BUFFERS OBJ
---------- ----------
22963 459993
19584 460012
12583 469384

but not getting what the output represents is it showing the number of buffers occupied by the each object?


Thanks
Previous Topic: Index on Partition Table (merged)
Next Topic: Creating SQL tuning set and how it will help
Goto Forum:
  


Current Time: Fri Dec 09 13:57:55 CST 2016

Total time taken to generate the page: 0.08727 seconds