Solved

User I/O wait time

Posted on 2011-03-24
14
1,937 Views
Last Modified: 2012-05-11
We upgraded from 10gR2 to 11gR2. One job used to take 1 Hr 20 Min before upgrade and after upgrade it is taking >7 hours.

Upon identifying the update statement that is causing the issue, We compared the awr report for the corresponding sql.
In 10gR2,
Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                           713,574      713,574.1     2.6
CPU Time (ms)                               138,840      138,840.4     1.0
Executions                                        1            N/A     N/A
Buffer Gets                               4,543,957    4,543,957.0     0.7
Disk Reads                                1,706,942    1,706,942.0    13.2
Parse Calls                                       1            1.0     0.0
Rows                                          4,058        4,058.0     N/A
User I/O Wait Time (ms)                     642,449            N/A     N/A

And in 11gR2,
Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                        1.1874E+07   11,873,578.4     3.8
CPU Time (ms)                               251,760      251,760.0     0.1
Executions                                        1            N/A     N/A
Buffer Gets                               4,873,762    4,873,762.0     0.0
Disk Reads                                1,676,504    1,676,504.0     2.3
Parse Calls                                       1            1.0     0.0
Rows                                          4,064        4,064.0     N/A
User I/O Wait Time (ms)                  1.1732E+07            N/A     N/A

As you can see, there is a lot of difference in the user i/o wait time. The update statement is doing almost same number of i/o in 10gR2 and 11gR2.
How should I proceed with the issue.

Thanks in advance...
0
Comment
Question by:sanpradeep
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 6

Expert Comment

by:Javier Morales
ID: 35205443
Please, generate a awr report to identify the query/update that's running out of time.

Please, launch in the server

sql> @?/rdbms/admin/awrrpt

Open in new window


and find the statement in trouble.
Then, get  the Hash value of the query and run

sql> @?/rdbms/admin/awrsqrpt.sql

Open in new window


and provide the hash value of the query to find the details of the execution plan/ consumtion.
0
 

Author Comment

by:sanpradeep
ID: 35205511
That is already done. The results I have posted is of the awrsqlrpt.sql for the problematic update statement.
Issue is for almost the same i/o performed, it shows a lot of difference in user i/o wait time.
0
 
LVL 6

Expert Comment

by:Javier Morales
ID: 35205654
Please, may you provide the sql statistics details? (physical reads/buffer gets/etc. per execution?)
Without the update statement and the execution details, we're blind to say a diagnostic.

Thanks in advance,
0
 

Author Comment

by:sanpradeep
ID: 35205744
I have provided the details... anyhow.. am again typing the details you asked...
In 10g:
Stat Name                                 Statement     Per execution
Buffer Gets                               4,543,957    4,543,957.0  
Disk Reads                                1,706,942    1,706,942.0  
User I/O Wait Time (ms)               642,449            N/A    

In 11g:
Stat Name                                 Statement     Per execution
Buffer Gets                               4,873,762    4,873,762.0  
Disk Reads                                1,676,504    1,676,504.0  
User I/O Wait Time (ms)           1.1732E+07            N/A    

Thanks for looking into this...

0
 
LVL 8

Expert Comment

by:ReliableDBA
ID: 35206021
i suggest try the following and see.
 set optimizer_Features_Enable=<your 10g version here>

if this helps, we will review further.

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35206138
After the upgrade id you refresh your statistics?

What is the compatible parameter set to?

Did you migrate all memory related parameters to the new Automatic Memory Management?

Please post your current pool and memory parameter values.
0
 

Author Comment

by:sanpradeep
ID: 35207383
Thanks for your suggestions...

I will require some time to provide you the details as I am not the primary DBA. Have asked customers the same details...

However, the stats is up to date... and the plan of the query is same in both scenarios.
I have also asked for the AWR report of the instance when the job was executed. Will also confirm if there is lot of pressure in the I/O subsystem..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35209112
>>Will also confirm if there is lot of pressure in the I/O subsystem..

When you said 'upgrade' did you do it in place or move to new servers/disks then upgrade?
0
 

Author Comment

by:sanpradeep
ID: 35212438
in place... but there is a chance of loading the system heavily.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35214617
What about the three other questions asked in http:#a35206138 ?
0
 

Author Comment

by:sanpradeep
ID: 35222791
Sorry.. I didnt get the awr report yet.. But based on my previous interactions, I have the following details:

compatible       11.2.0
sga_target      1gb

The trace file in 10g showed avg wait time for IO is 0.4 milli sec.
In 11g, it is >4 milli sec.

Need to confirm if the buffer cache in 11g is smaller than that in 10g. As of now, am assuming the reason to be bigger buffer cache in 10g and hence it did less physical io.

Will update once I have the details...
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35223957
There are more memory parameters than sga.

Yes, you should at least have the parameters equal to the 10g.

Or, I suggest the new 11g Automatic Memory Management.

You unset ALL memory parameters and just set memory_target and memory_max_target.

The docs talk all about this.  I'm on mobile and can't provide the link.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35231741
Here's a decent link on AMM: Notice the pool sizes are not 'dynamic' and should be set to 0 in the spfile.

http://www.oracle-base.com/articles/11g/AutomaticMemoryManagement_11gR1.php


and of course the Oracle docs:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/memory001.htm#ADMIN11197
0
 

Author Comment

by:sanpradeep
ID: 35238190
Thanks for the links..
The new 11g automatic memory management features are not being used.

But the issue was about more user i/o wait after upgrade. And it was because of the parameters not being same.

Setting the memory_target and memory_max_target parameters should also help.. but the values should atleast be equal to 10g (SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated").
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now