OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Contention (oracle 9i on AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Contention [message #360630]

Fri, 21 November 2008 12:58

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

Report message to a moderator

Re: Contention [message #360674 is a reply to message #360630]

Sat, 22 November 2008 01:11

rleishman
Messages: 3728
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

Report message to a moderator

Re: Contention [message #360686 is a reply to message #360674]

Sat, 22 November 2008 03:42

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

Report message to a moderator

Re: Contention [message #360687 is a reply to message #360686]

Sat, 22 November 2008 04:03

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

iostat

Regards
Michel

Report message to a moderator

Re: Contention [message #360706 is a reply to message #360687]

Sat, 22 November 2008 20:04

rleishman
Messages: 3728
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

Report message to a moderator

Re: Contention [message #360725 is a reply to message #360630]

Sun, 23 November 2008 09:30

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

Report message to a moderator

Re: Contention [message #360962 is a reply to message #360630]

Mon, 24 November 2008 08:14

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 1297 times)

Report message to a moderator

Re: Contention [message #360969 is a reply to message #360630]

Mon, 24 November 2008 08:33

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.

Report message to a moderator

Re: Contention [message #361277 is a reply to message #360630]

Tue, 25 November 2008 11:43

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

Report message to a moderator

Re: Contention [message #361547 is a reply to message #360630]

Wed, 26 November 2008 22:38

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 01:04:46 CDT 2024