Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2010
    Posts
    81

    Unanswered: What query produced this report

    Hello,

    someone has just handed me this report and being a non oracle person, I would like to know which SQL produced this. I want to run this query prior to some testing and then afterwards. This is on an oracle 10.2.0.4 databases running on Solaris. Terms being floated around are explain plan and trace - but I have no real idea what they are discussing.

    Many thanks in advance

    ================================================== ========
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS



    call count cpu elapsed disk query current rows

    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

    Parse 531 0.07 0.08 0 0 0 0

    Execute 531 0.31 0.36 0 16 22 8

    Fetch 545 0.83 0.76 0 39496 0 304

    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

    total 1607 1.21 1.21 0 39512 22 312

    ================================================== ========

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Looks like a TKPROF output to me.

    So what you posted was not produced by SQL Query, but by a tool named tkprof which formats a binary trace file.

  3. #3
    Join Date
    Jun 2010
    Posts
    81

    entire database statistics

    So is it possible to use explain plan or another utility to generate the entire statistics for the dtaabase?

    I understand tkprof will read the trace file, but how can I generate this report about the entire database.

    Many thanks

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Addm

    What you need is an "ADDM" report.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jun 2010
    Posts
    81

    Generate ADDM Report

    Hello,

    I ran addmrpt.sql and it generated a file with the following contents

    DETAILED ADDM REPORT FOR TASK 'TASK_851' WITH ID 851

    How do I generate a report from this please?

    Thanks in advance

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool EM will do it...

    Use the EM dbconsole.
    Or...
    addmrpt.sql requires you supply a 'begin' snapshot and an 'ending' snapshot from the list of snapshots that the script provides.

    The resulting 'txt' file is the generated report.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jun 2010
    Posts
    81

    Follow up

    Hi, the details I provided is what was generated in the output text file.
    'DETAILED ADDM REPORT FOR TASK 'TASK_29' WITH ID 29'

    I was hoping for a more detailed output similar to this

    ================================================== ========
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows

    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

    Parse 531 0.07 0.08 0 0 0 0
    Execute 531 0.31 0.36 0 16 22 8
    Fetch 545 0.83 0.76 0 39496 0 304
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 1607 1.21 1.21 0 39512 22 312

    Many thanks

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool AWR not set?

    The ADDM report supplies all the information you need.
    Perhaps you have not activated the job(s) to actually collect the database statistics.

    You need to use EM dbconsole to activate this collection process:

    EM -> Server -> Statistics Management and select Automatic Workload Repository
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Jun 2010
    Posts
    81

    generate explain plan

    Hello and thanks for the info about generating an ADDM report.
    Could you possibly outline the steps I would need to use to execute an explain plan and generate a trace file.

    Many thanks

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    R.t.f.m.

    You could start by reading the fine Oracle® manuals, you will find everything there:


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Jun 2010
    Posts
    81

    generating trace files

    Hello,

    I ran this query on the local databases this morning -->

    EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits => TRUE, binds => FALSE, instance_name => 'dbname');

    And wonderded does this collect Database statistics on the entire DB

    If so where would I find the trace file to investigate or review as I see no new files generated in -->
    /u01/app/oracle/product/10.2.0/Db_1/admin/dbname/bdump

    Thanks for your assistance

  12. #12
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Post#8

    Did you try the instructions stated in post #8?

    Also check out the DBMS_WORKLOAD_REPOSITORY package in the fine Oracle® Database PL/SQL Packages and Types Reference.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  13. #13
    Join Date
    Jun 2010
    Posts
    81

    Finished

    Many thanks - solved

Posting Permissions

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