logical_reads_per_call and cpu_per_call from AWR reports
My environment is
Oracle 10.2.0.4.0 - 2 node RAC environment
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?