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.

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?

and optional report_name

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?

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?

Kindly note I am running SPREPSQL.SQL and while running this it is asking for hashvalue.

I am using Oracle 9i (9.0.0.)

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.

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.