DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: logical_reads_per_call and cpu_per_call from AWR reports

  1. #1
    Join Date
    Nov 2009
    Posts
    2

    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?

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    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?

  4. #4
    Join Date
    Aug 2009
    Posts
    21
    Look at Database Resource manager(DRM) might help in your case.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width