help:statspace's stats$sql_summary

From: Joe Sath <dbadba62_at_hotmail.com>
Date: Tue, 26 Mar 2002 01:13:30 GMT
Message-ID: <_WPn8.10899$8r.5532@nwrddc01.gnilink.net>

In statspack's stats$sql_summary, it has almost everything except parse_user_id which v$sqlarea has. Without parse_user_id, the sql statement is not useful, when you try to explain plan it, you will always get error that "table or view not available".

Say a statement: select * from test.
if several schema have table test, what are you going to do?

Look into perfstat.snapshot package, I found that stats$sql_summary is populated as follows,
insert into stats$sql_summary

            ( snap_id


            , dbid

            , instance_number

            , text_subset

            , sharable_mem

            , sorts

            , module

            , loaded_versions

            , executions

            , loads

            , invalidations

            , parse_calls

            , disk_reads

            , buffer_gets

            , rows_processed

            , address

            , hash_value

            , version_count

            )
       select l_snap_id


            , p_dbid

            , p_instance_number

            , substr(sql_text,1,31)

            , sharable_mem

            , sorts

            , module

            , loaded_versions

            , executions

            , loads

            , invalidations

            , parse_calls

            , disk_reads

            , buffer_gets

            , rows_processed

            , address

            , hash_value

            , version_count

         from v$sqlxs
        where    buffer_gets   > l_buffer_gets_th
              or disk_reads    > l_disk_reads_th
              or parse_calls   > l_parse_calls_th
              or executions    > l_executions_th
              or sharable_mem  > l_sharable_mem_th
              or version_count > l_version_count_th
              ;

Is there any particular reason that oracle use v$sqlxs instead of v$sqlarea, if I add one more colum (parse_user_id) to stats$sql_summary and make some change to the package snapshot, will statspack work just as fine?

Thanks for your help. Received on Mon Mar 25 2002 - 19:13:30 CST