• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10576
  • Last Modified:

ORA-02395:exceeded call limit on IO usage

ORA-02395:exceeded call limit on IO usage

Some users are sometimes seeing this error.

Sometimes when re-running they don't get the error.

Sometimes other users don't get the error.

It's inconsistent.

How can I verify the ceiling for the various users on this parameter to see if the users are consistently set up.

I'm guessing the best way out of this problem is to adjust the timing of running the query or to tune the query itself.

Is this parameter a ceiling across mutlitple queries? In other words, If I run a whole pile of full table scans, am I more likely to hit this ceiling than if I just run one?

Thanks,

G
0
gswitz
Asked:
gswitz
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Did you Google that error?

http://ora-02395.ora-code.com/

ORA-02395: exceeded call limit on IO usage

Cause: An attempt was made to exceed the maximum I/O for a call, a parse, execute, or fetch, allowed by the LOGICAL_READS_PER_CALL clause of the user profile.

Action: If this happens often, ask the database administrator to increase the LOGICAL_READS_PER_CALL limit of the user profile.
0
 
gswitzAuthor Commented:
Yes, actually. But how do I query to find LOGICAL_READS_PER_CALL value so I can check it before going to a DBA.

--Unfortunately I can't run this in the target environment.
--I just like doing my homework before reaching out to the sysdbas
SELECT name, value
from gv$parameter
where lower(name) = 'resource_limit';

I appreciate your help as always.

Thanks,

Geoff
0
 
slightwv (䄆 Netminder) Commented:
From the action statement above:  ... limit of the user profile.

It is a profile parameter:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6010.htm#SQLRF01310

To see current profiles and the value for that parameter:
select profile, limit from dba_profiles
where resource_name='LOGICAL_READS_PER_CALL'
/

If you only have the DEFAULT profile then things are easy, just increase the value.

If you have several profiles, you need to pick the one used by the users in question and increase the value for that profile.


You change it with the ALTER PROFILE command:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2008.htm#SQLRF00813
0
 
gswitzAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now