2 Replies Latest reply: Jun 21, 2013 7:27 AM by 1016493 RSS

    statspack level 6 not displaying select query information

    1016493

      Hello,

      I have done the following

      1. exec perfstat.statspack.snap(i_snap_level=>6); (Snap 1)

      2. select * from orders where order_name='Chicken';

      3 exec perfstat.statspack.snap(i_snap_level=>6); (Snap 2)

      4. @?/rdbms/admin/spreport

      for snapids 1 and 2

      But i cannot see statistics for query  select * from orders where order_name='Chicken';

      If i use query create table test as select * from all_objects then i can see that query in the report with its hashvalue

       

      Does any one know why i cannot see query select * from orders where order_name='Chicken'; on statsreport?

       

      regards

      Sameer

        • 1. Re: statspack level 6 not displaying select query information
          JohnWatson

          Your statement probably didn't  cross the thresholds for monitoring. From spdoc.txt,

           

          SQL 'Thresholds'

                 The SQL statements gathered by Statspack are those which exceed one of

                 six predefined threshold parameters:

                  - number of executions of the SQL statement            (default 100)

                  - number of disk reads performed by the SQL statement  (default 1,000)

                  - number of parse calls performed by the SQL statement (default 1,000)

                  - number of buffer gets performed by the SQL statement (default 10,000)

                  - size of sharable memory used by the SQL statement    (default 1m)

                  - version count for the SQL statement                  (default 20)

           

                 The values of each of these threshold parameters are used when

                 deciding which SQL statements to collect - if a SQL statement's

                 resource usage exceeds any one of the above threshold values, it

                 is captured during the snapshot.

           

                 The SQL threshold levels used are either those stored in the table

                 stats$statspack_parameter, or by the thresholds specified when

                 the snapshot is taken.