Home » RDBMS Server » Performance Tuning » Profile Limits (10.2.0.4.0 - IBM AIX)
Profile Limits [message #431319] Mon, 16 November 2009 15:07 Go to next message
user7509
Messages: 1
Registered: November 2009
Junior Member
Hi,

My environment is

Oracle 10.2.0.4.0 - 2 node RAC environment
IBM AIX

We have few queries and procedures consuming lot of CPU resources, we want to restrict the resource consumption to prevent impacting other queries.

After lot of discussion, we thought of using PROFILES with limits for logical_reads_per_call, cpu_per_call, connect_time, idle_time and sessions_per_user.

Now, i need to find the values to set for logical_reads_per_call and cpu_per_call.

I have identified the peak load time and pulled the AWR report. Can somebody help me to find the method to find maximum logical_reads_per_call and cpu_per_call per statement from that AWR report.

I see a section "sql ordered by reads". for each statement, i see "physical reads", this one is total physical reads for the sql statement for the time AWR period right? how can i calculate logical_reads_per_call? is it (physical reads)/executions gives maximum logical_reads_per_call ?
I see a section "sql ordered by CPU Time". same way, how can i calculate cpu_per_call?

is there any better method to get the values for a particular past time?

[Updated on: Mon, 16 November 2009 18:55]

Report message to a moderator

Re: Profile Limits [message #431338 is a reply to message #431319] Mon, 16 November 2009 23:11 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As far as I know there is no way to get statistics per call.
You can get them per SQL statement, query DBA_HIST_SQL% views.

By the way, there is no relation between physical reads and logical ones (but the former is always less or equal to the latter).

Regards
Michel
Previous Topic: .trc files
Next Topic: ORA-00060 Deadlock issue with trigger
Goto Forum:
  


Current Time: Fri Sep 30 00:30:22 CDT 2016

Total time taken to generate the page: 0.09943 seconds