Statspack - Secrets of the Oracle Database - page 313

Figure 25-1. AWR Report section showing unabridged text of SQL statements

If you have read the Oracle10 g Statspack documentation in spdoc.txt then you might know
that in Oracle10 g the file sprepcon.sql (Statspack report configuration) contains a number of
parameters that control the appearance of Statspack reports as well as which statements make
it into the report. 5 Here's the relevant excerpt from spdoc.txt :

SQL section report settings - num_rows_per_hash
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This is the upper limit of the number of rows of SQL Text to print for
each SQL statement appearing in the SQL sections of the report. This
variable applies to each SQL statement (i.e. hash_value). The default value
is 4, which means at most 4 lines of the SQL text will be printed for
each SQL statement. To change this value, change the value of the variable
num_rows_per_hash.
e.g.
define num_rows_per_hash = 10;

And this is the relevant excerpt of sprepcon.sql :

-- SQL related report settings

-- Number of Rows of SQL to display in each SQL section of the report
define top_n_sql = 65;

-- Number of rows of SQL Text to print in the SQL sections of the report
-- for each hash_value
define num_rows_per_hash = 4;
…
define top_pct_sql = 1.0;

In Oracle9 i , sprepcon.sql does not exist and num_rows_per_hash is undocumented.

5.