logical_reads_per_call and cpu_per_call from AWR reports

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?

I'm sorry to say this is not at all a tuning approach. In stead of tuning bad queries you will be restricting them, so that means you do not want to execute those queries.

I guess you guys do not have a performance expertise guy there in your team. Hire one.

Malay,

Tuning is long time solution.
This server is shared by other database. due to this high consuming queries, it is hampering other databases also.

hence management is asking to limit the resource consumption of queries.

They analyzed the options. Oracle profiles have parameters - CPU_PER_CALL,CPU_PER_SESSION, LOGICAL_READS_PER_CALL AND LOGICAL_READS_PER_SESSION

They want to set reasonable values for the above parameters and arrest the queries.

Could you please help me to find the max parameter values database reached for any particular time. I have AWR, ASH and ADDM reports. With that how come I can arrive like max CPU_PER_CALL is x seconds?

Look at Database Resource manager(DRM) might help in your case.