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

Thread: Hashvalue In Statspack

  1. #1
    Join Date
    Jun 2003
    Location
    India
    Posts
    118

    Hashvalue In Statspack

    When we run SQL report in statspack it asks for HASH value.

    What is that. ? If we enter nothing, it comes out of SQL prompt after giving error.
    vishal sood
    OCP 8

  2. #2
    Join Date
    Jun 2000
    Posts
    295
    To generate stat report, you need run:
    @?/rdbms/admin/spreport

    When you run this SQL, it should ask the Begin and End Snapshot Ids
    only.

    Did I miss something?

  3. #3
    Join Date
    Jun 2000
    Posts
    295
    and optional report_name

  4. #4
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    and when we run sprepsql.sql then first it asks for start snap and end snap then it asks for hashvalue and then report name.

    I am talking about hashvalue. What is that?
    vishal sood
    OCP 8

  5. #5
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    spreport.sql shouldnt ask for the hash value as this is derived from stats$sql_summary and v$sql_text - are you sure the spreport.sql hasnt been modified by anyone? You could try running it with set verify on to see where the hash_value is being asked for.

    P.S What version are you running?

  6. #6
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    Kindly note I am running SPREPSQL.SQL and while running this it is asking for hashvalue.

    I am using Oracle 9i (9.0.0.)
    vishal sood
    OCP 8

  7. #7
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    From looking at the script, it produces a report on a specific SQL statement. The hash_value is Oracle's way of locating a specific bit of SQL in the shared pool. YOu need to look in stats$sql_summary to identify the hash_value of the SQL statement you want to run the report on.

  8. #8
    Join Date
    Jun 2000
    Posts
    295
    From spdoc.txt:

    sprepsql.sql - Reports on a single hash_value

    Once the instance report has been analyzed, often there are high-load SQL statements which should be examined to determine if they are causing unnecessary load.

    The SQL report sprepsql.sql, displays statistics, the complete SQL
    text and (if level 6 snapshot has been taken), information on any SQL Plan(s) associated with that statement.

    The SQL statement to be reported on is identified by the
    statement's Hash Value (which is a numerical representation of the statement's SQL text). The Hash Value for each statement is
    displayed in the high-load SQL sections of the instance report.

    That is:
    You use spreport.sql to get the instance report. From it you can
    find "Hash Value".

    Hope it helps.

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