1 2 3 Previous Next 33 Replies Latest reply: May 18, 2008 9:31 PM by Aman.... Go to original post RSS
      • 15. Re: performance(statspack) related problem
        Jonathan Lewis
        >
        SQL> select table_name,column_name,table_owner
        from dba_ind_columns where table_
        name='BILLPHARMACY';
        TABLE_NAME            COLUMN_NAME
        TABLE_OWNER
        ARMACY          BILLID                         IP
        BILLPHARMACY          PATIENTID                   IP
        You need to include the index_name in the query so that we can tell if that's one index or two; and you need to add an "order by index_name, column_position" so that we can tell the column ordering for multi-column indexes.

        I'm still waiting for a couple of examples of the execution plans you get from dbms_xplan().

        Regards
        Jonathan Lewis
        http://jonathanlewis.wordpress.com
        http://www.jlcomp.demon.co.uk
        • 16. Re: performance(statspack) related problem
          user00726
          SQL> select table_name,to_char(last_analyzed, 'dd/mon/yyyy hh24:mi') from dba_ta
          bles where table_name='BILLPHARMACY';
          ♀TABLE_NAME TO_CHAR(LAST_ANAL
          ------------------------------ -----------------
          BILLPHARMACY 02/may/2008 15:38
          • 17. Re: performance(statspack) related problem
            user00726
            SQL> select index_name,column_name,column_position from dba_ind_columns where ta
            ble_name='BILLPHARMACY' order by index_name, column_position;
            INDEX_NAME COLUMN_NAME COLUMN_POSITION

            BILLPHARMACY1 BILLID 1
            BILLPHARMACY1 PATIENTID 2
            • 18. Re: performance(statspack) related problem
              user00726
              Johanathan

              i am here givinh you a explain plan....
              SQL> explain plan for select discount,discamount from ip.billpharmacy where pati
              entid='PAT09037';

              Explained.

              Elapsed: 00:00:00.00
              SQL> select * from table(dbms_xplan.display);
              Elapsed: 00:00:00.00

              Execution Plan
              ----------------------------------------------------------
                        0
              SELECT STATEMENT Optimizer=CHOOSE

                        1                  0
                COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'
              • 19. Re: performance(statspack) related problem
                Jonathan Lewis
                You have patientid as the second column of an index (billid, patientid). Making a sensible guess about what the columns mean, it won't be used for a query that does:

                select * from billpharmacy where patientid = {constant}

                You will need another index on the table on just (patientid). As I think I've mentioned earlier though, this could make this class of queries much more efficient, but cause other queries to change execution path and slow down - so test carefully.

                Your 'dbms_xplan()' example was run with autotrace still enabled. (It's worth getting it right, though, just in case the "obvious" index on patientid needs to be modified for NLS support).


                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                http://www.jlcomp.demon.co.uk
                • 20. Re: performance(statspack) related problem
                  Aman....
                  Hi sir,
                  Your 'dbms_xplan()' example was run with autotrace still enabled. (It's worth getting it right, though, just in case the "obvious" index on patientid needs to be modified for NLS support).

                  How did you find it that dbms_xplan was run with autotrce enabled?And what effect it makes in getting hte output from dbms_xplan?
                  Regards
                  Aman....
                  • 21. Re: performance(statspack) related problem
                    Charles Hooper
                    Lokesh,
                    Please try to create the DBMS_XPLAN again. The example on this page might help:
                    http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

                    Notes on the above link: the first hint in the example SQL statement is required, but do not add the ordered use_nl(t1) index(t1) hints.

                    Aman,
                    The clues that autotrace was enabled:
                    Elapsed: 00:00:00.00
                    and
                    COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'
                    The side effect of having autotrace enabled is explained clearly in the above link - in short, the call to with 'ALLSTATS LAST' does not retrieve the DBMS_XPLAN for the specific query executed. If a 10046 trace is enabled at the same time that DBMS_XPLAN is called, (PICKLER FETCH) OF 'DISPLAY' will be included in the 10046 trace, but that inclusion in the 10046 trace does not prevent the call to DBMS_XPLAN from working as expected.

                    Charles Hooper
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.
                    • 22. Re: performance(statspack) related problem
                      Aman....
                      Charles,
                      Thanks for the information but please pardon me , I am not able to understand it.Can you please explain it one more time?
                      Regards
                      Aman....
                      • 23. Re: performance(statspack) related problem
                        108476
                        Hi Aman,
                        I am not able to understand it.
                        Don;t feel bad, it's quite complicated.

                        For a plain English, high-level overview, just take your STATSPACK report and paste it into the free STATSPACK analyzer. It's a open-source project for replicating human analysis of performance reports:

                        http://www.statspackanalyzer.com

                        Then, if you have any questions, you can post the analysis results . . . .

                        --------------------

                        Hope this helps. . .

                        Donald K. Burleson
                        Oracle Press author
                        Author of "Oracle Tuning: The Definitive Reference":
                        http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
                        • 24. Re: performance(statspack) related problem
                          Aman....
                          Hi Don,
                          Don;t feel bad, it's quite complicated.
                          No I don't feel bad. I am known to get struck inthe complicated issues or rather to make "simple issues" "complicated " ;-).
                          Yes I know about Statspack Analyzer. At the moment, my only doubt is regarding the dbms_xplan and autotrace option involved.Please correct me if I am wrong,doesn't need a full statspack report.This can be stimulated with a single query with autotrace on.
                          Regards
                          Aman....
                          • 25. Re: performance(statspack) related problem
                            Charles Hooper
                            Charles,
                            Thanks for the information but please pardon me , I
                            am not able to understand it.Can you please explain
                            it one more time?
                            Regards
                            Aman....
                            Aman,
                            I think by including the output of a 10046 trace along with the output of DBMS_XPLAN, it might be a little more clear what I tried to explain in the previous post:
                            A quick test setup:
                            CREATE TABLE T1 (C1 NUMBER(12));

                            INSERT INTO
                              T1
                            SELECT
                              ROWNUM
                            FROM
                              DUAL
                            CONNECT BY
                              LEVEL<=10000;

                            COMMIT;
                            Let's try an experiment with a simple SQL statement:
                            SELECT
                              C1
                            FROM
                              T1
                            WHERE
                              C1 BETWEEN 90 AND 150;

                            With the following settings:
                            OPTIMIZER_MODE=CHOOSE
                            OPTIMIZER_FEATURES_ENABLE=9.0.1
                            STATISTICS_LEVEL=ALL  (session level)

                            SQL_ID  84bwrtyfrxhzx, child number 0
                            -------------------------------------
                            SELECT    C1  FROM    T1  WHERE    C1 BETWEEN 90 AND 150

                            Plan hash value: 3617692013

                            ---------------------------------------------------------------------------
                            | Id  | Operation         | Name | Starts | A-Rows |   A-Time   | Buffers |
                            ---------------------------------------------------------------------------
                            |*  1 |  TABLE ACCESS FULL| T1   |      1 |     61 |00:00:00.01 |      23 |
                            ---------------------------------------------------------------------------

                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                               1 - filter(("C1"<=150 AND "C1">=90))

                            Note
                            -----
                               - rule based optimizer used (consider using cbo)
                            (That warning surprised be a bit... as this is running on Oracle 11.1.0.6)

                            Partial output from a 10046 at level 12 enabled at the same time:
                            =====================
                            PARSING IN CURSOR #3 len=56 dep=0 uid=63 oct=3 lid=63 tim=657639757986 hv=2642330621 ad='1af34a2c' sqlid='84bwrtyfrxhzx'
                            SELECT
                              C1
                            FROM
                              T1
                            WHERE
                              C1 BETWEEN 90 AND 150
                            END OF STMT
                            PARSE #3:c=0,e=701,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=657639757982
                            BINDS #3:
                            EXEC #3:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=657639758209
                            WAIT #3: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657639758249
                            FETCH #3:c=15600,e=713,p=0,cr=23,cu=0,mis=0,r=61,dep=0,og=4,tim=657639759004
                            STAT #3 id=1 cnt=61 pid=0 pos=1 obj=72628 op='TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=99 us)'
                            WAIT #3: nam='SQL*Net message from client' ela= 9637 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657639779290
                            =====================
                            ...
                            PARSING IN CURSOR #4 len=80 dep=0 uid=63 oct=3 lid=63 tim=657640013624 hv=266232738 ad='22039234' sqlid='0nfjn6n7xwsx2'
                            SELECT
                              *
                            FROM
                              TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'))
                            END OF STMT
                            PARSE #4:c=46800,e=234176,p=2,cr=199,cu=0,mis=1,r=0,dep=0,og=4,tim=657640013619
                            WAIT #4: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657640013799
                            WAIT #4: nam='SQL*Net message from client' ela= 570 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657640014444
                            BINDS #4:
                            EXEC #4:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=657640014557
                            WAIT #4: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657640495379
                            FETCH #4:c=171601,e=480979,p=15,cr=888,cu=0,mis=0,r=21,dep=0,og=4,tim=657640495566
                            STAT #4 id=1 cnt=21 pid=0 pos=1 obj=0 op='COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=888 pr=15 pw=15 time=81 us)'
                            In the above 10046 trace, note the STAT line containing "COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR" on the last line - that is the execution plan for the call to DBMS_XPLAN.DISPLAY_CURSOR, and not the original query that was of interest. That was the plan that Lokesh provided to Jonathan, and not the one that Jonathan requested.

                            Let's try again to remove the "rule based optimizer used" warning:
                            OPTIMIZER_MODE=ALL_ROWS
                            OPTIMIZER_FEATURES_ENABLE=11.1.0.6
                            STATISTICS_LEVEL=ALL  (session level)

                            The DBMS_XPLAN:
                            SQL_ID  84bwrtyfrxhzx, child number 1
                            -------------------------------------
                            SELECT    C1  FROM    T1  WHERE    C1 BETWEEN 90 AND 150

                            Plan hash value: 3617692013

                            ------------------------------------------------------------------------------------
                            | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
                            ------------------------------------------------------------------------------------
                            |*  1 |  TABLE ACCESS FULL| T1   |      1 |     61 |     61 |00:00:00.01 |      23 |
                            ------------------------------------------------------------------------------------

                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                               1 - filter(("C1">=90 AND "C1"<=150))

                            Note
                            -----
                               - dynamic sampling used for this statement

                            Partial output from a 10046 at level 12 enabled at the same time:
                            =====================
                            PARSING IN CURSOR #12 len=56 dep=0 uid=63 oct=3 lid=63 tim=657812448763 hv=2642330621 ad='1af34a2c' sqlid='84bwrtyfrxhzx'
                            SELECT
                              C1
                            FROM
                              T1
                            WHERE
                              C1 BETWEEN 90 AND 150
                            END OF STMT
                            PARSE #12:c=46801,e=54061,p=0,cr=24,cu=0,mis=1,r=0,dep=0,og=1,tim=657812448759
                            BINDS #12:
                            EXEC #12:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=657812449038
                            WAIT #12: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657812449085
                            FETCH #12:c=0,e=768,p=0,cr=23,cu=0,mis=0,r=61,dep=0,og=1,tim=657812449899
                            STAT #12 id=1 cnt=61 pid=0 pos=1 obj=72628 op='TABLE ACCESS FULL T1 (cr=23 pr=0 pw=0 time=75 us cost=7 size=793 card=61)'
                            WAIT #12: nam='SQL*Net message from client' ela= 10282 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=657812460271
                            =====================
                            ...
                            PARSING IN CURSOR #11 len=80 dep=0 uid=63 oct=3 lid=63 tim=657812468416 hv=266232738 ad='22039234' sqlid='0nfjn6n7xwsx2'
                            SELECT
                              *
                            FROM
                              TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'))
                            END OF STMT
                            PARSE #11:c=0,e=7984,p=0,cr=111,cu=0,mis=1,r=0,dep=0,og=1,tim=657812468413
                            FETCH #11:c=62400,e=56650,p=0,cr=274,cu=0,mis=0,r=21,dep=0,og=1,tim=657812526032
                            STAT #11 id=1 cnt=21 pid=0 pos=1 obj=0 op='COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=274 pr=0 pw=0 time=86 us)'
                            I hope that clears things up for you. I tried the following, but was not able to reproduce DBMS_XPLAN returning the plan for DBMS_XPLAN, rather than the last plan executed:
                            ALTER SESSION SET STATISTICS_LEVEL='ALL';

                            ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

                            SET AUTOTRACE ON STATISTICS

                            SELECT
                              C1
                            FROM
                              T1
                            WHERE
                              C1 BETWEEN 90 AND 150;

                            Statistics
                            ---------------------------------------------------
                                      0  recursive calls
                                      0  db block gets
                                     28  consistent gets
                                      0  physical reads
                                      0  redo size
                                   1279  bytes sent via SQL*Net to client
                                    460  bytes received via SQL*Net from client
                                      6  SQL*Net roundtrips to/from client
                                      0  sorts (memory)
                                      0  sorts (disk)
                                     61  rows processed

                            select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
                            (Section Added)
                            ---------------------

                            SQL> SET AUTOTRACE ON STATISTICS
                            SQL> SET SERVEROUTPUT ON
                            SQL> SELECT /*+ gather_plan_statistics */
                              2    C1
                              3  FROM
                              4    T1
                              5  WHERE
                              6    C1 BETWEEN 90 AND 150;

                                    C1
                            ----------
                                    90
                                    91
                                    92
                            ...
                                   148
                                   149
                                   150

                            61 rows selected.

                            Statistics
                            ----------------------------------------------------------
                                      0  recursive calls
                                      0  db block gets
                                     28  consistent gets
                                      0  physical reads
                                      0  redo size
                                   1029  bytes sent via SQL*Net to client
                                    378  bytes received via SQL*Net from client
                                      6  SQL*Net roundtrips to/from client
                                      0  sorts (memory)
                                      0  sorts (disk)
                                     61  rows processed

                            SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

                            PLAN_TABLE_OUTPUT
                            --------------------------------------------------------------------------------

                            SQL_ID  9babjv8yq8ru3, child number 0

                            BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

                            NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
                                  Please verify value of SQL_ID and CHILD_NUMBER;
                                  It could also be that the plan is no longer in cursor cache (check v$sql_plan)
                            ---------------------
                            Charles Hooper
                            IT Manager/Oracle DBA
                            K&M Machine-Fabricating, Inc.

                            Included code to reproduce the DBMS_XPLAN problem returning the correct execution plan.
                            Message was edited by:
                            Charles Hooper
                            • 26. Re: performance(statspack) related problem
                              Aman....
                              Charles,
                              Please bear with me a little more.
                              Here are my doubts now
                              1) Why did you choose to have RBO kcking in first time and than CBO with the different optimizer modes ?
                              2)If I understood corretly than you mean to say that what Lockesh provided was that with the help of dbms_xplan, instead of this package showing the query plan for the original query,it was showing for the dbms_xplan call itslf?Is it correct?
                              3) Despite you did an excellent try to show me the difference, I am not able to differentiate.Sorry for my small brain.In both the parts there is a call to dbms_xplan, collection iteration pickeler fetch isn't it?Than how the query is different from the first one where 920 optimizer is used?
                              I am so sorry but I get things slowly, rather very slowly.
                              Thanks and regards
                              Aman....
                              • 27. Re: performance(statspack) related problem
                                Charles Hooper
                                Aman,
                                I think that you are understanding what I tried to explain.
                                1) Why did you choose to have RBO kcking in first
                                time and than CBO with the different optimizer modes
                                ?
                                I did not choose to use the RBO - Oracle made that decision. Let's see if we can find out why. With OPTIMIZER_FEATURES_ENABLE still set to 11.1.0.6:
                                ALTER SYSTEM SET OPTIMIZER_MODE='CHOOSE';

                                The DBMS_XPLAN:
                                ---------------------------------------------------------------------------
                                | Id  | Operation         | Name | Starts | A-Rows |   A-Time   | Buffers |
                                ---------------------------------------------------------------------------
                                |*  1 |  TABLE ACCESS FULL| T1   |      1 |     61 |00:00:00.01 |      23 |
                                ---------------------------------------------------------------------------

                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                   1 - filter(("C1"<=150 AND "C1">=90))

                                Note
                                -----
                                   - rule based optimizer used (consider using cbo)
                                So, it seems that OPTIMIZER_MODE='CHOOSE' causes this to happen, but why? In my sample setup, I did not collect statistics on the table, so let's see what happens when I collect statistics on the table (and then flush the shared pool so that the change in plan may happen):
                                EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1');

                                ALTER SYSTEM FLUSH SHARED_POOL;

                                The DBMS_XPLAN:
                                ------------------------------------------------------------------------------------
                                | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
                                ------------------------------------------------------------------------------------
                                |*  1 |  TABLE ACCESS FULL| T1   |      1 |     62 |     61 |00:00:00.01 |      23 |
                                ------------------------------------------------------------------------------------

                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                   1 - filter(("C1"<=150 AND "C1">=90))
                                Notice that the NOTE line is no longer included in the DBMS_XPLAN output.
                                2)If I understood corretly than you mean to say that
                                what Lockesh provided was that with the help of
                                dbms_xplan, instead of this package showing the query
                                plan for the original query,it was showing for the
                                dbms_xplan call itslf?Is it correct?
                                Yes, that is what I attempted to show.
                                3) Despite you did an excellent try to show me the
                                difference, I am not able to differentiate.Sorry for
                                my small brain.In both the parts there is a call to
                                dbms_xplan, collection iteration pickeler fetch isn't
                                it?Than how the query is different from the first
                                one where 920 optimizer is used?
                                Yes, there was a call to DBMS_XPLAN in both cases - the reason why I posted two cases was the unexpected rule base optimizer execution in the first test case. The COLLECTION ITERATOR PICKLER FETCH appeared in the 10046 trace in both cases - you will see that line in a 10046 trace when DBMS_XPLAN is called - I received the expected plan from the call to DBMS_XPLAN, rather than seeing COLLECTION ITERATOR PICKLER FETCH, like in the plan posted by Lockesh.

                                The use of OPTIMIZER_FEATURES_ENABLE=9.0.1 had little to do with this experiement - it was a left over setting from a test that I perform a couple weeks ago. I set it back to the original value when I noticed that Oracle was indicating that it was deciding to use the RBO for this and other simple queries - based on the above test, this parameter was not the cause of the warning about RBO.

                                As I am sure that you are finding, just because something is a little complicated, it does not mean that something is difficult or impossible to understand. Use the information in the "Optimizing Oracle Performance" book to help you understand what might be happening. You might need to search a little for the parameters that cause what is happening, but there are good books and blogs/websites that will help. 10053 trace files reveal a great deal of detail and shows the effects of changes to various parameters.

                                Good luck, and stay patient when trying to understand why something is happening.

                                Charles Hooper
                                IT Manager/Oracle DBA
                                K&M Machine-Fabricating, Inc.
                                • 28. Re: performance(statspack) related problem
                                  108476
                                  Hi,

                                  Let's format this to make it easy to read:

                                  *********************************************************
                                  STATSPACK report for

                                  DB Name DB Id Instance Inst Num Release Cluster Host
                                  ------------ ----------- ------------ -------- ----------- ------- ------------
                                  NEOSOFT 399087012 neosoft 1 9.2.0.7.0 NO APP

                                  Snap Id Snap Time Sessions Curs/Sess Comment
                                  --------- ------------------ -------- --------- -------------------
                                  Begin Snap: 1 16-May-08 10:20:15 435 2.2
                                  End Snap: 3 16-May-08 10:24:12 442 2.2
                                  Elapsed: 3.95 (mins)

                                  Cache Sizes (end)
                                  ~~~~~~~~~~~~~~~~~
                                  Buffer Cache: 352M Std Block Size: 8K
                                  Shared Pool Size: 200M Log Buffer: 1,024K

                                  Load Profile
                                  ~~~~~~~~~~~~ Per Second Per Transaction
                                  --------------- ---------------
                                  Redo size: 23,125.82 7,966.31
                                  Logical reads: 6,050.15 2,084.14
                                  Block changes: 96.53 33.25
                                  Physical reads: 1,103.40 380.10
                                  Physical writes: 16.60 5.72
                                  User calls: 189.40 65.24
                                  Parses: 29.92 10.31
                                  Hard parses: 8.69 2.99
                                  Sorts: 6.97 2.40
                                  Logons: 0.11 0.04
                                  Executes: 29.25 10.08
                                  Transactions: 2.90

                                  % Blocks changed per Read: 1.60 Recursive Call %: 31.70
                                  Rollback per transaction %: 0.29 Rows per Sort: 472.41

                                  Instance Efficiency Percentages (Target 100%)
                                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                  Buffer Nowait %: 100.00 Redo NoWait %: 100.00
                                  Buffer Hit %: 82.01 In-memory Sort %: 99.21
                                  Library Hit %: 89.76 Soft Parse %: 70.95
                                  Execute to Parse %: -2.29 Latch Hit %: 99.95
                                  Parse CPU to Parse Elapsd %: 100.93 % Non-Parse CPU: 84.62

                                  Shared Pool Statistics Begin End
                                  ------ ------
                                  Memory Usage %: 94.72 94.24
                                  % SQL with executions>1: 39.68 38.62
                                  % Memory for SQL w/exec>1: 39.34 38.76

                                  Top 5 Timed Events
                                  ~~~~~~~~~~~~~~~~~~ % Total
                                  Event Waits Time (s) Ela Time
                                  -------------------------------------------- ------------ ----------- --------
                                  db file scattered read 39,162 38 39.49
                                  CPU time 28 29.48
                                  db file sequential read 42,173 28 29.24
                                  direct path read 1,193 1 .59
                                  SQL*Net more data to client 31,629 1 .53
                                  -------------------------------------------------------------
                                  Wait Events for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> s - second
                                  -> cs - centisecond - 100th of a second
                                  -> ms - millisecond - 1000th of a second
                                  -> us - microsecond - 1000000th of a second
                                  -> ordered by wait time desc, waits desc (idle events last)

                                  Avg
                                  Total Wait wait Waits
                                  Event Waits Timeouts Time (s) (ms) /txn
                                  ---------------------------- ------------ ---------- ---------- ------ --------
                                  db file scattered read 39,162 0 38 1 56.9
                                  db file sequential read 42,173 0 28 1 61.3
                                  direct path read 1,193 0 1 0 1.7
                                  SQL*Net more data to client 31,629 0 1 0 46.0
                                  log file sync 683 0 0 0 1.0
                                  control file sequential read 184 0 0 1 0.3
                                  db file parallel write 61 0 0 1 0.1
                                  control file parallel write 78 0 0 0 0.1
                                  log file parallel write 732 0 0 0 1.1
                                  direct path write 30 0 0 0 0.0
                                  SQL*Net break/reset to clien 10 0 0 0 0.0
                                  LGWR wait for redo copy 3 0 0 0 0.0
                                  SQL*Net message from client 44,675 0 577 13 64.9
                                  virtual circuit status 47,524 4 145 3 69.1
                                  SQL*Net message to client 44,674 0 0 0 64.9
                                  SQL*Net more data from clien 260 0 0 0 0.4
                                  -------------------------------------------------------------
                                  Background Wait Events for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> ordered by wait time desc, waits desc (idle events last)

                                  Avg
                                  Total Wait wait Waits
                                  Event Waits Timeouts Time (s) (ms) /txn
                                  ---------------------------- ------------ ---------- ---------- ------ --------
                                  control file sequential read 80 0 0 2 0.1
                                  db file parallel write 61 0 0 1 0.1
                                  control file parallel write 78 0 0 0 0.1
                                  log file parallel write 732 0 0 0 1.1
                                  LGWR wait for redo copy 3 0 0 0 0.0
                                  rdbms ipc message 993 242 2,054 2069 1.4
                                  pmon timer 79 79 234 2962 0.1
                                  smon timer 13 0 228 17520 0.0
                                  -------------------------------------------------------------
                                  SQL ordered by Gets for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Buffer Gets Threshold: 10000
                                  -> Note that resources reported for PL/SQL includes the resources used by
                                  all SQL statements called within the PL/SQL code. As individual SQL
                                  statements are also reported, it is possible and valid for the summed
                                  total % to exceed 100

                                  CPU Elapsd
                                  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
                                  --------------- ------------ -------------- ------ -------- --------- ----------
                                  92,160 36 2,560.0 6.4 0.41 0.37 1007205397
                                  Module: Pharmacy 2.0.174.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  75,042 33 2,274.0 5.2 0.23 0.29 3052506495
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  68,310 27 2,530.0 4.8 0.27 0.27 2037438344
                                  Module: Pharmacy 2.0.174.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  48,617 31 1,568.3 3.4 0.16 0.22 1186386010
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  45,060 20 2,253.0 3.1 0.17 0.18 3400961035
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  42,976 17 2,528.0 3.0 0.14 0.17 3744553745
                                  Module: Pharmacy 2.0.174.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  36,114 78 463.0 2.5 0.13 0.11 4262998487
                                  Module: In Patient 2.1.152.exe
                                  SQL ordered by Gets for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Buffer Gets Threshold: 10000
                                  -> Note that resources reported for PL/SQL includes the resources used by
                                  all SQL statements called within the PL/SQL code. As individual SQL
                                  statements are also reported, it is possible and valid for the summed
                                  total % to exceed 100

                                  CPU Elapsd
                                  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
                                  --------------- ------------ -------------- ------ -------- --------- ----------
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'IP' escape '\' and ac.table_name like 'DOCTORVISIT' escape '

                                  25,102 11 2,282.0 1.8 0.06 0.10 812077776
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  24,984 36 694.0 1.7 0.09 0.07 3956499023
                                  Module: Pharmacy 2.0.174.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'INVENTORY' escape '\' and ac.table_name like 'DMEDMAST' esca

                                  23,213 1 23,213.0 1.6 1.59 2.83 2435229694
                                  Module: SQL*Plus
                                  BEGIN statspack.snap(i_snap_level=>7); END;

                                  22,930 10 2,293.0 1.6 0.14 0.09 3466778893
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  22,680 10 2,268.0 1.6 0.08 0.09 3786189544
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  20,340 9 2,260.0 1.4 0.13 0.08 3649693145
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  SQL ordered by Gets for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Buffer Gets Threshold: 10000
                                  -> Note that resources reported for PL/SQL includes the resources used by
                                  all SQL statements called within the PL/SQL code. As individual SQL
                                  statements are also reported, it is possible and valid for the summed
                                  total % to exceed 100

                                  CPU Elapsd
                                  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
                                  --------------- ------------ -------------- ------ -------- --------- ----------
                                  19,460 42 463.3 1.4 0.09 0.06 3858576624
                                  Module: In Patient 2.1.152.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=

                                  -------------------------------------------------------------
                                  SQL ordered by Reads for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Disk Reads Threshold: 1000

                                  CPU Elapsd
                                  Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
                                  --------------- ------------ -------------- ------ -------- --------- ----------
                                  8,084 4 2,021.0 3.1 0.14 1.08 4198744706
                                  Module: Out Patient 2.1.41.exe
                                  Select Max(BillNo) as Bno From PatientsRegistration Where regdt
                                  = to_date('01/04/2008','dd/mm/yyyy') and regdt <= to_date('31/0
                                  3/2009','dd/mm/yyyy')

                                  7,302 1 7,302.0 2.8 0.13 0.51 2116660478
                                  Module: In Patient 2.1.157.exe
                                  select discount,discamount from billpharmacy where patientid='PA
                                  T09212'

                                  7,302 1 7,302.0 2.8 0.06 0.41 3612969128
                                  Module: In Patient 2.1.152.exe
                                  select discount,discamount from billpharmacy where patientid='PA
                                  T09037'

                                  7,302 1 7,302.0 2.8 0.19 0.51 3981413559
                                  Module: In Patient 2.1.152.exe
                                  sELECT * FROM BillPharmacy WHERE BillPharmacy.pATIENtid= 'PAT090
                                  37' order by to_number((issueno))

                                  7,300 1 7,300.0 2.8 0.14 0.69 136284863
                                  Module: In Patient 2.1.152.exe
                                  delete from billpharmacy where patientid='PAT09037'

                                  7,299 1 7,299.0 2.8 0.20 0.77 2137420337
                                  Module: In Patient 2.1.152.exe
                                  delete from billpharmacy where patientid='PAT09154'

                                  7,298 1 7,298.0 2.8 0.13 0.43 1007690124
                                  Module: In Patient 2.1.152.exe
                                  select discount,discamount from billpharmacy where patientid='PA
                                  T08301'

                                  7,298 1 7,298.0 2.8 0.13 0.58 3087447381
                                  Module: In Patient 2.1.152.exe
                                  select discount,discamount from billpharmacy where patientid='PA
                                  T09154'

                                  7,061 1 7,061.0 2.7 0.13 0.97 2993688253
                                  Module: In Patient 2.1.152.exe
                                  select discount,discamount from billpharmacy where patientid='PA
                                  T09146'

                                  7,058 1 7,058.0 2.7 0.39 0.76 1202603169
                                  Module: In Patient 2.1.152.exe
                                  SELECT BILLHDR.BILLNO, BILLHDR.BILLDT, BILLHDR.BALANCE, BILLHDR.
                                  TOTAL, BILLHDR.NETAMOUNT,IPINFO.type as orgtype, BILLHDR.PREVAD
                                  V , IPInfo.IpNO, IPInfo.REGDT,IPInfo.REGTM, IPInfo.Name, IPInfo.
                                  ADDRESS1,IPINFO.relativenm ,Rooms.roomno , Beds.BEDNO, ROOMTYPES
                                  , decode(IPINFO.sex,1,'Male','Female')sex,IPINFO.AGE ||' '||dec

                                  6,640 1 6,640.0 2.5 0.13 1.06 796742944
                                  Module: In Patient 2.1.152.exe
                                  SQL ordered by Reads for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Disk Reads Threshold: 1000

                                  CPU Elapsd
                                  Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
                                  --------------- ------------ -------------- ------ -------- --------- ----------
                                  select discount,discamount from billpharmacy where patientid='PA
                                  T09123'

                                  4,617 4 1,154.3 1.8 0.19 0.94 2042905630
                                  Module: Out Patient 2.1.41.exe
                                  SELECT NVL(Max(MoneyReciept.mrBillNo),0) AS MaxOfBillNob FROM Mo
                                  neyReciept where generalopd = 0

                                  4,496 3 1,498.7 1.7 0.06 0.57 2718453880
                                  Module: Diagnotech 2.2.49.exe
                                  Select * from track where tablenm = 'FRMREPORTING' and primkeyid
                                  = 'R208970TS000002' order by trackdate,tracktime

                                  4,494 3 1,498.0 1.7 0.13 0.74 3290276624
                                  Module: Diagnotech 2.2.49.exe
                                  Select * from track where tablenm = 'FRMREPORTING' and primkeyid
                                  = 'R207032T0000351' order by trackdate,tracktime

                                  4,451 3 1,483.7 1.7 0.20 0.99 768943678
                                  Module: Diagnotech 2.2.49.exe
                                  Select * from track where tablenm = 'FRMREPORTING' and primkeyid
                                  = 'R208613TS000002' order by trackdate,tracktime

                                  4,091 4 1,022.8 1.6 0.19 1.62 266509601
                                  Module: In Patient 2.1.152.exe
                                  SELECT IPInfo.IPNo ,IPInfo.name,doctorvisit.date_r ,unithdr.drna
                                  me , doctorvisit.visitid FROM Doctorvisit , IPInfo,unithdr wher
                                  e Doctorvisit.Patid = IPInfo.PatID and Doctorvisit.doctorid=unit
                                  hdr.unitid and doctorvisit.date_r >=to_date('06/05/2008','fmdd/m
                                  m/yyyy') order by IPInfo.IPNo

                                  3,933 5 786.6 1.5 0.14 1.29 2546712787
                                  Module: Diagnotech 2.2.49.exe
                                  Select max(BillNo) as Bno From MoneyReciept where rcdt >= to_dat
                                  e('01/04/2008','dd/mm/yyyy') and rcdt <= to_date('31/03/2009','d
                                  d/mm/yyyy')

                                  3,439 3 1,146.3 1.3 0.25 1.89 2816864569
                                  Module: Diagnotech 2.2.49.exe
                                  Select * from track where tablenm = 'FRMREPORTING' and primkeyid
                                  = 'R207242T0000104' order by trackdate,tracktime

                                  3,129 2 1,564.5 1.2 0.06 0.88 1803349496
                                  Module: Diagnotech 2.2.49.exe
                                  Select * from track where tablenm = 'FRMREPORTING' and primkeyid
                                  = 'R208962TS000002' order by trackdate,tracktime

                                  3,065 4 766.3 1.2 0.17 1.30 54282346
                                  Module: In Patient 2.1.157.exe
                                  Select * from track where tablenm = 'FRMDOCTORVISIT' and primkey
                                  id = 'VI048354' order by trackdate,tracktime

                                  2,834 3 944.7 1.1 0.17 1.57 165470279
                                  Module: Diagnotech 2.2.49.exe
                                  SQL ordered by Reads for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Disk Reads Threshold: 1000

                                  CPU Elapsd
                                  Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
                                  --------------- ------------ -------------- ------ -------- --------- ----------
                                  Select * from track where tablenm = 'FRMREPORTING' and primkeyid
                                  = 'R208926TS000002' order by trackdate,tracktime

                                  2,769 3 923.0 1.1 0.13 1.28 3812904405
                                  Module: Diagnotech 2.2.49.exe
                                  Select * from track where tablenm = 'FRMREPORTING' and primkeyid
                                  = 'R208892TS000002' order by trackdate,tracktime

                                  2,730 3 910.0 1.0 0.19 1.21 680462328
                                  Module: In Patient 2.1.157.exe
                                  select * from DoctorVisit where Date_r >= to_date('06/05/2008','
                                  dd/mm/yyyy') and Date_r <= to_date('16/05/2008','dd/mm/yyyy')

                                  2,526 2 1,263.0 1.0 0.58 1.14 4043595143
                                  Module: SQL*Plus
                                  INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID , DBID , INSTANCE_NUMBER
                                  , TEXT_SUBSET , SHARABLE_MEM , SORTS , MODULE , LOADED_VERSIONS
                                  , FETCHES , EXECUTIONS , LOADS , INVALIDATIONS , PARSE_CALLS ,
                                  DISK_READS , BUFFER_GETS , ROWS_PROCESSED , COMMAND_TYPE , ADDRE
                                  SS , HASH_VALUE , VERSION_COUNT , CPU_TIME , ELAPSED_TIME , OUTL

                                  2,484 1 2,484.0 0.9 0.19 0.91 33028691
                                  Module: Diagnotech 2.2.49.exe
                                  select * from requisitions where upper(reqno) = '180800'

                                  2,483 1 2,483.0 0.9 0.13 0.93 2943168498
                                  Module: Diagnotech 2.2.49.exe
                                  Update Requisitions Set Collected = 0 Where TRIM(ReqID) ='R20987
                                  8'

                                  2,482 1 2,482.0 0.9 0.11 0.74 2336722822
                                  Module: Diagnotech 2.2.49.exe
                                  Update Requisitions Set Collected = 0 Where TRIM(ReqID) ='R20987
                                  7'

                                  2,480 1 2,480.0 0.9 0.06 0.67 650704065
                                  Module: In Patient 2.1.152.exe
                                  select sum(can.returnamt) refamt from diagnotech.cancellationhd
                                  r can,diagnotech.requisitions req where can.reqid = req.reqid an

                                  -------------------------------------------------------------
                                  SQL ordered by Executions for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Executions Threshold: 100

                                  CPU per Elap per
                                  Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
                                  ------------ --------------- ---------------- ----------- ---------- ----------
                                  283 283 1.0 0.00 0.00 1283498366
                                  Module: Diagnotech 2.2.49.exe
                                  Select to_char(sysdate,'fmdd/mm/yyyy HH24:mi:ss am') as abc from
                                  OPTIONS

                                  238 76 0.3 0.00 0.00 4274598960
                                  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
                                  estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
                                  density, col#, spare1, spare2, avgcln from hist_head$ where obj#
                                  =:1 and intcol#=:2

                                  126 126 1.0 0.00 0.00 2168100061
                                  Module: Diagnotech 2.2.49.exe
                                  select * from departments where deptid = 'DEP00004'

                                  114 0 0.0 0.00 0.00 4073347083
                                  select grantor#, grantee#, privilege#, sequence#, nvl(option$,0)
                                  from objauth$ where obj#=:1 and col#=:2

                                  112 112 1.0 0.00 0.00 3687727603
                                  insert into col$(obj#,name,intcol#,segcol#,type#,length,precisio
                                  n#,scale,null$,offset,fixedstorage,segcollength,deflength,defaul
                                  t$,col#,property,charsetid,charsetform,spare1,spare2,spare3)valu
                                  es(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,decode(:8,
                                  -127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,

                                  99 99 1.0 0.00 0.00 3468666020
                                  select text from view$ where rowid=:1

                                  84 84 1.0 0.00 0.00 2997854589
                                  insert into access$(d_obj#,order#,columns,types) values (:1,:2,:
                                  3,:4)

                                  84 84 1.0 0.00 0.00 3951809012
                                  insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_times
                                  tamp,d_owner#, property)values (:1,:2,:3,:4,:5,:6, :7)

                                  78 0 0.0 0.00 0.00 3867936055
                                  Module: Out Patient 2.1.41.exe
                                  commit

                                  78 78 1.0 0.00 0.00 4262998487
                                  Module: In Patient 2.1.152.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'IP' escape '\' and ac.table_name like 'DOCTORVISIT' escape '

                                  44 44 1.0 0.00 0.00 3216099004
                                  Module: Diagnotech 2.2.49.exe
                                  select * from options

                                  42 42 1.0 0.00 0.00 3858576624
                                  SQL ordered by Executions for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Executions Threshold: 100

                                  CPU per Elap per
                                  Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
                                  ------------ --------------- ---------------- ----------- ---------- ----------
                                  Module: In Patient 2.1.152.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'IP' escape '\' and ac.table_name like 'IPINFO' escape '\'

                                  36 540 15.0 0.01 0.01 1007205397
                                  Module: Pharmacy 2.0.174.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  36 36 1.0 0.00 0.00 3956499023
                                  Module: Pharmacy 2.0.174.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'INVENTORY' escape '\' and ac.table_name like 'DMEDMAST' esca

                                  34 34 1.0 0.00 0.00 857250417
                                  Module: Diagnotech 2.2.46.exe
                                  select * from departments where deptid = 'DEP00031'

                                  33 43,442 1,316.4 0.04 0.05 502973950
                                  Module: Pharmacy 2.0.174.exe
                                  SELECT substr(UPPER(MedMast.MedNm),1,50) MedNm , MedSubCat.MSCOD
                                  E ,MEDMAST.POTENCY ,substr(genericnm,1,50) genericnm, sum(dmeddt
                                  ls.currqty) stock, MedMast.medid FROM MedMast , MedSubCat,dmedd
                                  tls where UPPER(MEDMAST.MEDNM) LIKE '%%' and medmast.medid = d
                                  meddtls.medid and MEDMAST.SubCatID = MedSubCat.MSCatID and dmed

                                  33 132 4.0 0.01 0.01 3052506495
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  31 124 4.0 0.01 0.01 1186386010
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  31 31 1.0 0.00 0.00 3993731530
                                  Module: In Patient 2.1.152.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  SQL ordered by Executions for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Executions Threshold: 100

                                  CPU per Elap per
                                  Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
                                  ------------ --------------- ---------------- ----------- ---------- ----------
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'OTS1' escape '\' and ac.table_name like 'DOCTORINFO' escape

                                  28 28 1.0 0.00 0.00 737183288
                                  Module: Diagnotech 2.2.49.exe
                                  Select * From CReqno

                                  27 0 0.0 0.00 0.00 246231567
                                  Module: Pharmacy 2.0.174.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,

                                  -------------------------------------------------------------
                                  SQL ordered by Parse Calls for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Parse Calls Threshold: 1000

                                  % Total
                                  Parse Calls Executions Parses Hash Value
                                  ------------ ------------ -------- ----------
                                  283 283 3.99 1283498366
                                  Module: Diagnotech 2.2.49.exe
                                  Select to_char(sysdate,'fmdd/mm/yyyy HH24:mi:ss am') as abc from
                                  OPTIONS

                                  126 126 1.78 2168100061
                                  Module: Diagnotech 2.2.49.exe
                                  select * from departments where deptid = 'DEP00004'

                                  99 99 1.40 3468666020
                                  select text from view$ where rowid=:1

                                  78 78 1.10 3867936055
                                  Module: Out Patient 2.1.41.exe
                                  commit

                                  78 78 1.10 4262998487
                                  Module: In Patient 2.1.152.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'IP' escape '\' and ac.table_name like 'DOCTORVISIT' escape '

                                  44 44 0.62 3216099004
                                  Module: Diagnotech 2.2.49.exe
                                  select * from options

                                  42 42 0.59 3858576624
                                  Module: In Patient 2.1.152.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'IP' escape '\' and ac.table_name like 'IPINFO' escape '\'

                                  36 36 0.51 1007205397
                                  Module: Pharmacy 2.0.174.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  36 36 0.51 3956499023
                                  Module: Pharmacy 2.0.174.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'INVENTORY' escape '\' and ac.table_name like 'DMEDMAST' esca

                                  34 34 0.48 857250417
                                  Module: Diagnotech 2.2.46.exe
                                  SQL ordered by Parse Calls for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Parse Calls Threshold: 1000

                                  % Total
                                  Parse Calls Executions Parses Hash Value
                                  ------------ ------------ -------- ----------
                                  select * from departments where deptid = 'DEP00031'

                                  33 33 0.47 502973950
                                  Module: Pharmacy 2.0.174.exe
                                  SELECT substr(UPPER(MedMast.MedNm),1,50) MedNm , MedSubCat.MSCOD
                                  E ,MEDMAST.POTENCY ,substr(genericnm,1,50) genericnm, sum(dmeddt
                                  ls.currqty) stock, MedMast.medid FROM MedMast , MedSubCat,dmedd
                                  tls where UPPER(MEDMAST.MEDNM) LIKE '%%' and medmast.medid = d
                                  meddtls.medid and MEDMAST.SubCatID = MedSubCat.MSCatID and dmed

                                  33 33 0.47 3052506495
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  31 31 0.44 1186386010
                                  Module: In Patient 2.1.152.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  31 31 0.44 3993731530
                                  Module: In Patient 2.1.152.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'OTS1' escape '\' and ac.table_name like 'DOCTORINFO' escape

                                  30 15 0.42 3067006941
                                  delete from dependency$ where d_obj#=:1

                                  28 28 0.39 737183288
                                  Module: Diagnotech 2.2.49.exe
                                  Select * From CReqno

                                  27 27 0.38 246231567
                                  Module: Pharmacy 2.0.174.exe
                                  select to_char(null), ac.owner, ac.table_name, acc.column_name,
                                  acc.position, ac.constraint_name from all_constraints ac, all_co
                                  ns_columns acc where ac.owner=acc.owner and ac.constraint_type=
                                  'P' and ac.constraint_name=acc.constraint_name and ac.owner lik
                                  e 'INVENTORY' escape '\' and ac.table_name like 'DISSUEDTLSP' e

                                  27 27 0.38 445844524
                                  Module: In Patient 2.1.152.exe
                                  select nvl(tarrifflg,0) as tf from sourcehdr where sourceid = ''


                                  SQL ordered by Parse Calls for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Parse Calls Threshold: 1000

                                  % Total
                                  Parse Calls Executions Parses Hash Value
                                  ------------ ------------ -------- ----------
                                  27 27 0.38 2037438344
                                  Module: Pharmacy 2.0.174.exe
                                  select null, i.owner, i.table_name, decode (i.uniqueness,'UNIQUE
                                  ',0,1), null, i.index_name, 3, c.column_position, c.column_name,
                                  'A', i.distinct_keys, i.leaf_blocks, null from all_indexes i, a
                                  ll_ind_columns c where i.owner = c.index_owner and i.index_name
                                  = c.index_name and i.table_owner = c.table_owner and i.table_nam

                                  22 22 0.31 3073477137
                                  select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled
                                  from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.ob
                                  j#=o.obj# and o.owner#=u.user# order by o.obj#

                                  -------------------------------------------------------------
                                  Instance Activity Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

                                  Statistic Total per Second per Trans
                                  --------------------------------- ------------------ -------------- ------------
                                  CPU used by this session 2,821 11.9 4.1
                                  CPU used when call started 2,826 11.9 4.1
                                  CR blocks created 15 0.1 0.0
                                  DBWR buffers scanned 5,873 24.8 8.5
                                  DBWR checkpoint buffers written 167 0.7 0.2
                                  DBWR checkpoints 0 0.0 0.0
                                  DBWR free buffers found 5,653 23.9 8.2
                                  DBWR lru scans 17 0.1 0.0
                                  DBWR make free requests 17 0.1 0.0
                                  DBWR summed scan depth 5,873 24.8 8.5
                                  DBWR transaction table writes 1 0.0 0.0
                                  DBWR undo block writes 228 1.0 0.3
                                  SQL*Net roundtrips to/from client 89,026 375.6 129.4
                                  active txn count during cleanout 156 0.7 0.2
                                  background checkpoints completed 0 0.0 0.0
                                  background checkpoints started 0 0.0 0.0
                                  background timeouts 243 1.0 0.4
                                  branch node splits 0 0.0 0.0
                                  buffer is not pinned count 1,225,066 5,169.1 1,780.6
                                  buffer is pinned count 1,069,472 4,512.5 1,554.5
                                  bytes received via SQL*Net from c 4,334,045 18,287.1 6,299.5
                                  bytes sent via SQL*Net to client 79,310,906 334,645.2 115,277.5
                                  calls to get snapshot scn: kcmgss 14,976 63.2 21.8
                                  calls to kcmgas 942 4.0 1.4
                                  calls to kcmgcs 166 0.7 0.2
                                  change write time 19 0.1 0.0
                                  cleanout - number of ktugct calls 182 0.8 0.3
                                  cleanouts and rollbacks - consist 5 0.0 0.0
                                  cleanouts only - consistent read 18 0.1 0.0
                                  cluster key scan block gets 206,010 869.2 299.4
                                  cluster key scans 81,803 345.2 118.9
                                  commit cleanout failures: block l 0 0.0 0.0
                                  commit cleanout failures: callbac 11 0.1 0.0
                                  commit cleanout failures: cannot 0 0.0 0.0
                                  commit cleanouts 2,731 11.5 4.0
                                  commit cleanouts successfully com 2,720 11.5 4.0
                                  commit txn count during cleanout 87 0.4 0.1
                                  consistent changes 15 0.1 0.0
                                  consistent gets 1,412,564 5,960.2 2,053.2
                                  consistent gets - examination 572,440 2,415.4 832.0
                                  cursor authentications 531 2.2 0.8
                                  data blocks consistent reads - un 15 0.1 0.0
                                  db block changes 22,877 96.5 33.3
                                  db block gets 21,323 90.0 31.0
                                  deferred (CURRENT) block cleanout 1,548 6.5 2.3
                                  dirty buffers inspected 19 0.1 0.0
                                  enqueue conversions 52 0.2 0.1
                                  enqueue releases 4,639 19.6 6.7
                                  enqueue requests 4,640 19.6 6.7
                                  enqueue timeouts 0 0.0 0.0
                                  enqueue waits 0 0.0 0.0
                                  execute count 6,932 29.3 10.1
                                  free buffer inspected 19 0.1 0.0
                                  free buffer requested 258,870 1,092.3 376.3
                                  hot buffers moved to head of LRU 2,520 10.6 3.7
                                  immediate (CR) block cleanout app 23 0.1 0.0
                                  Instance Activity Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

                                  Statistic Total per Second per Trans
                                  --------------------------------- ------------------ -------------- ------------
                                  immediate (CURRENT) block cleanou 502 2.1 0.7
                                  index fast full scans (full) 163 0.7 0.2
                                  index fetch by key 380,673 1,606.2 553.3
                                  index scans kdiixs1 86,708 365.9 126.0
                                  leaf node 90-10 splits 22 0.1 0.0
                                  leaf node splits 64 0.3 0.1
                                  logons cumulative 25 0.1 0.0
                                  messages received 806 3.4 1.2
                                  messages sent 806 3.4 1.2
                                  no buffer to keep pinned count 0 0.0 0.0
                                  no work - consistent read gets 779,017 3,287.0 1,132.3
                                  opened cursors cumulative 7,669 32.4 11.2
                                  parse count (failures) 2 0.0 0.0
                                  parse count (hard) 2,060 8.7 3.0
                                  parse count (total) 7,091 29.9 10.3
                                  parse time cpu 434 1.8 0.6
                                  parse time elapsed 430 1.8 0.6
                                  physical reads 261,506 1,103.4 380.1
                                  physical reads direct 3,529 14.9 5.1
                                  physical writes 3,935 16.6 5.7
                                  physical writes direct 3,529 14.9 5.1
                                  physical writes non checkpoint 3,839 16.2 5.6
                                  pinned buffers inspected 0 0.0 0.0
                                  prefetched blocks 175,966 742.5 255.8
                                  prefetched blocks aged out before 0 0.0 0.0
                                  process last non-idle time 270 1.1 0.4
                                  recovery blocks read 0 0.0 0.0
                                  recursive calls 20,832 87.9 30.3
                                  recursive cpu usage 296 1.3 0.4
                                  redo blocks written 11,309 47.7 16.4
                                  redo buffer allocation retries 0 0.0 0.0
                                  redo entries 12,097 51.0 17.6
                                  redo log space requests 0 0.0 0.0
                                  redo log space wait time 0 0.0 0.0
                                  redo ordering marks 1 0.0 0.0
                                  redo size 5,480,820 23,125.8 7,966.3
                                  redo synch time 30 0.1 0.0
                                  redo synch writes 689 2.9 1.0
                                  redo wastage 160,348 676.6 233.1
                                  redo write time 29 0.1 0.0
                                  redo writer latching time 0 0.0 0.0
                                  redo writes 732 3.1 1.1
                                  rollback changes - undo records a 2 0.0 0.0
                                  rollbacks only - consistent read 10 0.0 0.0
                                  rows fetched via callback 235,269 992.7 342.0
                                  session connect time 0 0.0 0.0
                                  session logical reads 1,433,886 6,050.2 2,084.1
                                  session pga memory 45,074,044 190,185.8 65,514.6
                                  session pga memory max 49,946,024 210,742.7 72,596.0
                                  session uga memory max 3,455,708 14,581.1 5,022.8
                                  shared hash latch upgrades - no w 54,598 230.4 79.4
                                  shared hash latch upgrades - wait 0 0.0 0.0
                                  sorts (disk) 13 0.1 0.0
                                  sorts (memory) 1,639 6.9 2.4
                                  sorts (rows) 780,425 3,292.9 1,134.3
                                  summed dirty queue length 239 1.0 0.4
                                  Instance Activity Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

                                  Statistic Total per Second per Trans
                                  --------------------------------- ------------------ -------------- ------------
                                  switch current to new buffer 172 0.7 0.3
                                  table fetch by rowid 612,799 2,585.7 890.7
                                  table fetch continued row 626 2.6 0.9
                                  table scan blocks gotten 459,367 1,938.3 667.7
                                  table scan rows gotten 18,523,828 78,159.6 26,924.2
                                  table scans (long tables) 128 0.5 0.2
                                  table scans (short tables) 1,655 7.0 2.4
                                  transaction rollbacks 1 0.0 0.0
                                  transaction tables consistent rea 0 0.0 0.0
                                  transaction tables consistent rea 0 0.0 0.0
                                  user calls 44,888 189.4 65.2
                                  user commits 686 2.9 1.0
                                  user rollbacks 2 0.0 0.0
                                  workarea executions - multipass 0 0.0 0.0
                                  workarea executions - onepass 24 0.1 0.0
                                  workarea executions - optimal 2,433 10.3 3.5
                                  write clones created in foregroun 0 0.0 0.0
                                  -------------------------------------------------------------
                                  Tablespace IO Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  ->ordered by IOs (Reads + Writes) desc

                                  Tablespace
                                  ------------------------------
                                  Av Av Av Av Buffer Av Buf
                                  Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
                                  -------------- ------- ------ ------- ------------ -------- ---------- ------
                                  DIAGNOTECH
                                  61,945 261 0.7 1.9 64 0 0 0.0
                                  IP
                                  10,753 45 1.2 9.5 23 0 0 0.0
                                  SYSTEM
                                  5,668 24 1.2 3.2 22 0 0 0.0
                                  OTS1
                                  2,456 10 1.5 7.8 19 0 0 0.0
                                  TEMP
                                  1,429 6 0.6 2.5 739 3 0 0.0
                                  INVENTORY
                                  478 2 3.5 1.3 49 0 0 0.0
                                  UNDOTBS1
                                  0 0 0.0 229 1 0 0.0
                                  INDX
                                  15 0 2.0 1.0 0 0 0 0.0
                                  PAYROLL
                                  1 0 20.0 4.0 0 0 0 0.0
                                  -------------------------------------------------------------
                                  File IO Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  ->ordered by Tablespace, File

                                  Tablespace Filename
                                  ------------------------ ----------------------------------------------------
                                  Av Av Av Av Buffer Av Buf
                                  Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
                                  -------------- ------- ------ ------- ------------ -------- ---------- ------
                                  DIAGNOTECH F:\ORACLE\ORADATA\NEOSOFT\DIAGNOTECH1.ORA
                                  61,945 261 0.7 1.9 64 0 0

                                  INDX F:\ORACLE\ORADATA\NEOSOFT\INDX01.DBF
                                  15 0 2.0 1.0 0 0 0

                                  INVENTORY F:\ORACLE\ORADATA\NEOSOFT\INVENTORY1.ORA
                                  478 2 3.5 1.3 49 0 0

                                  IP F:\ORACLE\ORADATA\NEOSOFT\IP1.ORA
                                  10,753 45 1.2 9.5 23 0 0

                                  OTS1 F:\ORACLE\ORADATA\NEOSOFT\OTS11.ORA
                                  2,456 10 1.5 7.8 19 0 0

                                  PAYROLL F:\ORACLE\ORADATA\NEOSOFT\PAYROLL1.ORA
                                  1 0 20.0 4.0 0 0 0

                                  SYSTEM F:\ORACLE\ORADATA\NEOSOFT\SYSTEM01.DBF
                                  5,668 24 1.2 3.2 22 0 0

                                  TEMP F:\ORACLE\ORADATA\NEOSOFT\TEMP01.DBF
                                  1,429 6 0.6 2.5 739 3 0

                                  UNDOTBS1 F:\ORACLE\ORADATA\NEOSOFT\UNDOTBS01.DBF
                                  0 0 229 1 0

                                  -------------------------------------------------------------
                                  Buffer Pool Statistics for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> Standard block size Pools D: default, K: keep, R: recycle
                                  -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

                                  Free Write Buffer
                                  Number of Cache Buffer Physical Physical Buffer Complete Busy
                                  P Buffers Hit % Gets Reads Writes Waits Waits Waits
                                  --- ---------- ----- ----------- ----------- ---------- ------- -------- ------
                                  D 44,044 82.0 1,432,475 257,328 406 0 0 0
                                  -------------------------------------------------------------

                                  Instance Recovery Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> B: Begin snapshot, E: End snapshot

                                  Targt Estd Log File Log Ckpt Log Ckpt
                                  MTTR MTTR Recovery Actual Target Size Timeout Interval
                                  (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
                                  - ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
                                  B 87 19 3628 65684 64450 184320 64450
                                  E 87 20 4063 69805 69805 184320 69805
                                  -------------------------------------------------------------

                                  Buffer Pool Advisory for DB: NEOSOFT Instance: neosoft End Snap: 3
                                  -> Only rows with estimated physical reads >0 are displayed
                                  -> ordered by Block Size, Buffers For Estimate (default block size first)

                                  Size for Size Buffers for Est Physical Estimated
                                  P Estimate (M) Factr Estimate Read Factor Physical Reads
                                  --- ------------ ----- ---------------- ------------- ------------------
                                  D 32 .1 4,004 67.25 19,260,129,810
                                  D 64 .2 8,008 42.95 12,300,910,895
                                  D 96 .3 12,012 32.00 9,165,118,680
                                  D 128 .4 16,016 25.62 7,338,010,607
                                  D 160 .5 20,020 17.54 5,022,366,182
                                  D 192 .5 24,024 11.74 3,361,228,965
                                  D 224 .6 28,028 7.05 2,020,169,694
                                  D 256 .7 32,032 3.94 1,127,930,012
                                  D 288 .8 36,036 2.32 663,835,521
                                  D 320 .9 40,040 1.44 413,674,540
                                  D 352 1.0 44,044 1.00 286,377,525
                                  D 384 1.1 48,048 0.75 215,831,108
                                  D 416 1.2 52,052 0.60 171,670,371
                                  D 448 1.3 56,056 0.51 145,708,247
                                  D 480 1.4 60,060 0.45 128,439,943
                                  D 512 1.5 64,064 0.40 114,053,332
                                  D 544 1.5 68,068 0.37 104,584,674
                                  D 576 1.6 72,072 0.34 96,792,591
                                  D 608 1.7 76,076 0.32 92,596,531
                                  D 640 1.8 80,080 0.31 89,929,979
                                  -------------------------------------------------------------
                                  PGA Aggr Target Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> B: Begin snap E: End snap (rows dentified with B or E contain data
                                  which is absolute i.e. not diffed over the interval)
                                  -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
                                  -> Auto PGA Target - actual workarea memory target
                                  -> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
                                  -> %PGA W/A Mem - percentage of PGA memory allocated to workareas
                                  -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
                                  -> %Man W/A Mem - percentage of workarea memory under manual control

                                  PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
                                  --------------- ---------------- -------------------------
                                  88.2 213 29

                                  %PGA %Auto %Man
                                  PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
                                  Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
                                  - --------- --------- ---------- ---------- ------ ------ ------ ----------
                                  B 24 7 24.4 0.4 1.6 .0 100.0 1,228
                                  E 24 8 23.8 0.0 .0 .0 .0 1,228
                                  -------------------------------------------------------------

                                  PGA Aggr Target Histogram for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> Optimal Executions are purely in-memory operations

                                  Low High
                                  Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
                                  ------- ------- -------------- ------------- ------------ ------------
                                  8K 16K 2,000 2,000 0 0
                                  16K 32K 55 55 0 0
                                  32K 64K 28 28 0 0
                                  64K 128K 50 50 0 0
                                  128K 256K 110 110 0 0
                                  256K 512K 26 26 0 0
                                  512K 1024K 176 158 18 0
                                  1M 2M 8 4 4 0
                                  4M 8M 2 0 2 0
                                  -------------------------------------------------------------

                                  PGA Memory Advisory for DB: NEOSOFT Instance: neosoft End Snap: 3
                                  -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
                                  where Estd PGA Overalloc Count is 0

                                  Estd Extra Estd PGA Estd PGA
                                  PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
                                  Est (MB) Factr Processed Written to Disk Hit % Count
                                  ---------- ------- ---------------- ---------------- -------- ----------
                                  12 0.5 417.1 207.2 67.0 1
                                  18 0.8 417.1 89.3 82.0 0
                                  24 1.0 417.1 46.4 90.0 0
                                  29 1.2 417.1 46.4 90.0 0
                                  34 1.4 417.1 46.4 90.0 0
                                  38 1.6 417.1 13.2 97.0 0
                                  43 1.8 417.1 11.1 97.0 0
                                  48 2.0 417.1 11.1 97.0 0
                                  72 3.0 417.1 11.1 97.0 0
                                  96 4.0 417.1 11.1 97.0 0
                                  144 6.0 417.1 0.0 100.0 0
                                  192 8.0 417.1 0.0 100.0 0
                                  -------------------------------------------------------------
                                  Rollback Segment Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  ->A high value for "Pct Waits" suggests more rollback segments may be required
                                  ->RBS stats may not be accurate between begin and end snaps when using Auto Undo
                                  managment, as RBS may be dynamically created and dropped as needed

                                  Trans Table Pct Undo Bytes
                                  RBS No Gets Waits Written Wraps Shrinks Extends
                                  ------ -------------- ------- --------------- -------- -------- --------
                                  0 15.0 0.00 0 0 0 0
                                  1 412.0 0.00 831,990 1 0 1
                                  2 48.0 0.00 61,500 0 0 0
                                  3 87.0 0.00 132,404 0 0 0
                                  4 79.0 0.00 66,626 0 0 0
                                  5 71.0 0.00 91,284 0 0 0
                                  6 40.0 0.00 15,474 0 0 0
                                  7 660.0 0.00 547,536 1 0 1
                                  8 168.0 0.00 21,762 0 0 0
                                  9 86.0 0.00 10,108 0 0 0
                                  10 295.0 0.00 156,468 0 0 0
                                  -------------------------------------------------------------
                                  Rollback Segment Storage for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  ->Optimal Size should be larger than Avg Active

                                  RBS No Segment Size Avg Active Optimal Size Maximum Size
                                  ------ --------------- --------------- --------------- ---------------
                                  0 385,024 0 385,024
                                  1 9,625,600 806,999 16,900,096
                                  2 5,365,760 711,094 8,511,488
                                  3 5,365,760 664,381 9,560,064
                                  4 5,365,760 763,735 8,511,488
                                  5 5,365,760 672,648 9,625,600
                                  6 5,365,760 677,906 9,560,064
                                  7 6,414,336 647,093 8,511,488
                                  8 5,365,760 628,652 10,608,640
                                  9 5,365,760 769,325 9,560,064
                                  10 5,365,760 622,015 9,560,064
                                  -------------------------------------------------------------
                                  Undo Segment Summary for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> Undo segment block stats:
                                  -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
                                  -> eS - expired Stolen, eR - expired Released, eU - expired reUsed

                                  Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
                                  TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
                                  ---- -------------- ---------- -------- ---------- -------- ------ -------------
                                  1 695 1,032,965 12 1 0 0 0/0/0/0/0/0
                                  -------------------------------------------------------------


                                  Undo Segment Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> ordered by Time desc

                                  Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
                                  End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
                                  ------------ ------------ -------- ------- -------- ------- ------ -------------
                                  16-May 10:22 695 ######## 12 1 0 0 0/0/0/0/0/0
                                  -------------------------------------------------------------
                                  Latch Activity for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
                                  willing-to-wait latch get requests
                                  ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
                                  ->"Pct Misses" for both should be very close to 0.0

                                  Pct Avg Wait Pct
                                  Get Get Slps Time NoWait NoWait
                                  Latch Requests Miss /Miss (s) Requests Miss
                                  ------------------------ -------------- ------ ------ ------ ------------ ------
                                  Consistent RBA 732 0.0 0 0
                                  FAL request queue 1 0.0 0 0
                                  FIB s.o chain latch 12 0.0 0 0
                                  FOB s.o list latch 27 0.0 0 0
                                  SQL memory manager latch 2 0.0 0 79 0.0
                                  SQL memory manager worka 7,245 0.0 0 0
                                  active checkpoint queue 169 0.0 0 0
                                  archive control 10 0.0 0 0
                                  archive process latch 7 0.0 0 0
                                  cache buffer handles 12,078 0.0 0 0
                                  cache buffers chains 2,602,999 0.0 0.0 0 506,781 0.0
                                  cache buffers lru chain 858 0.0 0 615,593 0.0
                                  channel handle pool latc 37 0.0 0 0
                                  channel operations paren 265 0.0 0 0
                                  checkpoint queue latch 30,545 0.0 0 893 0.0
                                  child cursor hash table 16,079 0.0 0 0
                                  dml lock allocation 2,674 0.0 0 0
                                  dummy allocation 43 0.0 0 0
                                  enqueue hash chains 9,313 0.0 0 0
                                  enqueues 9,405 0.0 0 0
                                  event group latch 5 0.0 0 0
                                  hash table column usage 0 0 55,565 0.0
                                  hash table modification 1 0.0 0 0
                                  kmcptab latch 78 0.0 0 0
                                  kmcpvec latch 0 0 78 0.0
                                  ktm global data 13 0.0 0 0
                                  lgwr LWN SCN 742 0.0 0 0
                                  library cache 193,957 0.0 0.0 0 6,959 0.4
                                  library cache load lock 110 0.0 0 0
                                  library cache pin 63,420 0.0 0 0
                                  library cache pin alloca 43,208 0.0 0 0
                                  list of block allocation 49 0.0 0 0
                                  loader state object free 34 0.0 0 0
                                  message pool operations 26 0.0 0 0
                                  messages 2,866 0.0 0 0
                                  mostly latch-free SCN 742 0.0 0 0
                                  multiblock read objects 164,572 0.0 0.0 0 0
                                  ncodef allocation latch 3 0.0 0 0
                                  object stats modificatio 1,669 0.0 0 0
                                  post/wait queue 1,029 0.0 0 683 0.0
                                  process allocation 11 0.0 0 5 0.0
                                  process group creation 11 0.0 0 0
                                  redo allocation 13,617 0.0 0.0 0 0
                                  redo copy 0 0 12,145 0.0
                                  redo writing 2,533 0.0 0 0
                                  row cache enqueue latch 215,027 0.0 0.0 0 0
                                  row cache objects 218,418 0.0 0.0 0 472 0.0
                                  sequence cache 99 0.0 0 0
                                  session allocation 8,683 0.0 0 0
                                  session idle bit 94,091 0.0 0 0
                                  Latch Activity for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
                                  willing-to-wait latch get requests
                                  ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
                                  ->"Pct Misses" for both should be very close to 0.0

                                  Pct Avg Wait Pct
                                  Get Get Slps Time NoWait NoWait
                                  Latch Requests Miss /Miss (s) Requests Miss
                                  ------------------------ -------------- ------ ------ ------ ------------ ------
                                  session switching 3 0.0 0 0
                                  session timer 80 0.0 0 0
                                  shared pool 160,825 0.0 0.0 0 0
                                  sim partition latch 0 0 35 0.0
                                  simulator hash latch 63,337 0.0 0 0
                                  simulator lru latch 4,437 0.0 0 16,447 0.0
                                  sort extent pool 129 0.0 0 0
                                  transaction allocation 47 0.0 0 0
                                  transaction branch alloc 3 0.0 0 0
                                  undo global data 3,091 0.0 0 0
                                  user lock 114 0.0 0 0
                                  virtual circuit buffers 540,157 0.4 0.0 0 0
                                  virtual circuit queues 256,196 0.0 0.0 0 0
                                  virtual circuits 89,002 0.0 0 0
                                  -------------------------------------------------------------
                                  Top 5 Logical Reads per Segment for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Segment Logical Reads Threshold: 10000

                                  Subobject Obj. Logical
                                  Owner Tablespace Object Name Name Type Reads %Total
                                  ---------- ---------- -------------------- ---------- ----- ------------ -------
                                  DIAGNOTECH DIAGNOTECH REQUISITIONS TABLE 162,064 11.66
                                  SYS SYSTEM I_OBJ1 INDEX 115,328 8.30
                                  SYS SYSTEM OBJ$ TABLE 113,328 8.15
                                  SYS SYSTEM I_IND1 INDEX 109,584 7.88
                                  IP IP BILLPHARMACY TABLE 89,328 6.43
                                  -------------------------------------------------------------


                                  Top 5 Physical Reads per Segment for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Segment Physical Reads Threshold: 1000

                                  Subobject Obj. Physical
                                  Owner Tablespace Object Name Name Type Reads %Total
                                  ---------- ---------- -------------------- ---------- ----- ------------ -------
                                  IP IP BILLPHARMACY TABLE 86,212 34.43
                                  DIAGNOTECH DIAGNOTECH REQUISITIONS TABLE 79,487 31.74
                                  DIAGNOTECH DIAGNOTECH TRACK TABLE 33,193 13.25
                                  IP SYSTEM TRACK TABLE 17,926 7.16
                                  OTS1 OTS1 MONEYRECIEPT TABLE 10,036 4.01
                                  -------------------------------------------------------------


                                  Top 5 Row Lock Waits per Segment for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  -> End Segment Row Lock Waits Threshold: 100

                                  Row
                                  Subobject Obj. Lock
                                  Owner Tablespace Object Name Name Type Waits %Total
                                  ---------- ---------- -------------------- ---------- ----- ------------ -------
                                  IP IP BILLID4064 INDEX 1 100.00
                                  -------------------------------------------------------------
                                  Dictionary Cache Stats for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  ->"Pct Misses" should be very low (< 2% in most cases)
                                  ->"Cache Usage" is the number of cache entries being used
                                  ->"Pct SGA" is the ratio of usage to allocated size for that cache

                                  Get Pct Scan Pct Mod Final
                                  Cache Requests Miss Reqs Miss Reqs Usage
                                  ------------------------- ------------ ------ ------- ----- -------- ----------
                                  dc_histogram_defs 22,135 1.1 0 0 3,348
                                  dc_object_ids 26,251 0.0 0 0 625
                                  dc_objects 4,895 0.1 0 15 960
                                  dc_profiles 21 0.0 0 0 1
                                  dc_rollback_segments 273 0.0 0 0 12
                                  dc_segments 11,239 0.1 0 0 604
                                  dc_sequences 3 33.3 0 3 6
                                  dc_tablespaces 19,449 0.0 0 0 5
                                  dc_user_grants 2,814 0.0 0 0 17
                                  dc_usernames 886 0.0 0 0 15
                                  dc_users 22,542 0.0 0 0 20
                                  -------------------------------------------------------------


                                  Library Cache Activity for DB: NEOSOFT Instance: neosoft Snaps: 1 -3
                                  ->"Pct Misses" should be very low

                                  Get Pct Pin Pct Invali-
                                  Namespace Requests Miss Requests Miss Reloads dations
                                  --------------- ------------ ------ -------------- ------ ---------- --------
                                  BODY 445 0.0 445 0.0 0 0
                                  CLUSTER 397 0.0 121 0.0 0 0
                                  SQL AREA 7,041 28.0 24,030 16.9 55 23
                                  TABLE/PROCEDURE 8,324 0.0 15,082 0.6 18 0
                                  TRIGGER 953 0.0 953 0.0 0 0
                                  -------------------------------------------------------------
                                  Shared Pool Advisory for DB: NEOSOFT Instance: neosoft End Snap: 3
                                  -> Note there is often a 1:Many correlation between a single logical object
                                  in the Library Cache, and the physical number of memory objects associated
                                  with it. Therefore comparing the number of Lib Cache objects (e.g. in
                                  v$librarycache), with the number of Lib Cache Memory Objects is invalid

                                  Estd
                                  Shared Pool SP Estd Estd Estd Lib LC Time
                                  Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache
                                  Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits
                                  ----------- ----- ---------- ------------ ------------ ------- ---------------
                                  104 .5 97 21,986 47,321 1.0 22,910,514
                                  128 .6 120 27,657 47,381 1.0 22,988,994
                                  152 .8 143 32,965 47,422 1.0 23,048,924
                                  176 .9 166 38,101 47,451 1.0 23,094,145
                                  200 1.0 189 42,541 47,474 1.0 23,126,910
                                  224 1.1 212 47,258 47,492 1.0 23,155,651
                                  248 1.2 235 52,101 47,508 1.0 23,180,673
                                  272 1.4 258 56,800 47,521 1.0 23,202,200
                                  296 1.5 283 61,512 47,532 1.0 23,220,564
                                  320 1.6 306 65,892 47,540 1.0 23,235,720
                                  344 1.7 329 70,532 47,547 1.0 23,246,826
                                  368 1.8 352 75,208 47,553 1.0 23,257,346
                                  392 2.0 375 81,494 47,559 1.0 23,268,801
                                  416 2.1 411 88,685 47,565 1.0 23,279,344
                                  -------------------------------------------------------------
                                  SGA Memory Summary for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

                                  SGA regions Size in Bytes
                                  ------------------------------ ----------------
                                  Database Buffers 369,098,752
                                  Fixed Size 455,784
                                  Redo Buffers 1,191,936
                                  Variable Size 436,207,616
                                  ----------------
                                  sum 806,954,088
                                  -------------------------------------------------------------


                                  SGA breakdown difference for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

                                  Pool Name Begin value End value % Diff
                                  ------ ------------------------------ ---------------- ---------------- -------
                                  java free memory 83,886,080 83,886,080 0.00
                                  large free memory 63,891,136 64,350,992 0.72
                                  large session heap 19,994,944 19,535,088 -2.30
                                  shared 1M buffer 2,098,176 2,098,176 0.00
                                  shared Checkpoint queue 1,129,216 1,129,216 0.00
                                  shared DML lock 1,028,764 1,028,764 0.00
                                  shared FileOpenBlock 6,564,072 6,564,072 0.00
                                  shared KGK heap 3,756 3,756 0.00
                                  shared KGLS heap 2,584,540 2,431,584 -5.92
                                  shared KQR M PO 2,844,672 2,768,896 -2.66
                                  shared KQR S PO 249,880 250,652 0.31
                                  shared KQR S SO 7,936 7,936 0.00
                                  shared KSXR pending messages que 841,036 841,036 0.00
                                  shared KSXR receive buffers 1,033,000 1,033,000 0.00
                                  shared MTTR advisory 59,288 59,288 0.00
                                  shared PL/SQL DIANA 3,179,412 3,015,708 -5.15
                                  shared PL/SQL MPCODE 388,924 241,096 -38.01
                                  shared PLS non-lib hp 3,688 3,688 0.00
                                  shared VIRTUAL CIRCUITS 2,564,620 2,564,620 0.00
                                  shared db_handles 1,080,000 1,080,000 0.00
                                  shared dictionary cache 2,137,216 2,137,216 0.00
                                  shared enqueue 1,676,824 1,676,824 0.00
                                  shared event statistics per sess 18,675,020 18,675,020 0.00
                                  shared fixed allocation callback 388 388 0.00
                                  shared free memory 14,167,016 15,470,156 9.20
                                  shared joxs heap init 4,220 4,220 0.00
                                  shared ktlbk state objects 778,960 778,960 0.00
                                  shared library cache 43,962,768 43,051,344 -2.07
                                  shared message pool freequeue 665,792 665,792 0.00
                                  shared miscellaneous 41,742,904 42,080,748 0.81
                                  shared parameters 3,224 9,272 187.59
                                  shared processes 1,500,000 1,500,000 0.00
                                  shared sessions 3,998,480 3,998,480 0.00
                                  shared sim memory hea 195,212 195,212 0.00
                                  shared sql area 111,345,832 111,177,960 -0.15
                                  shared table definiti 16,576 15,240 -8.06
                                  shared transaction 1,852,092 1,852,092 0.00
                                  shared trigger defini 26,236 24,148 -7.96
                                  shared trigger inform 592 592 0.00
                                  shared trigger source 304 304 0.00
                                  buffer_cache 369,098,752 369,098,752 0.00
                                  fixed_sga 455,784 455,784 0.00
                                  log_buffer 1,180,672 1,180,672 0.00
                                  -------------------------------------------------------------
                                  init.ora Parameters for DB: NEOSOFT Instance: neosoft Snaps: 1 -3

                                  End value
                                  Parameter Name Begin value (if different)
                                  ----------------------------- --------------------------------- --------------
                                  background_dump_dest F:\oracle\admin\neosoft\bdump
                                  compatible 9.2.0.0.0
                                  control_files F:\oracle\oradata\neosoft\control
                                  core_dump_dest F:\oracle\admin\neosoft\cdump
                                  db_block_size 8192
                                  db_cache_size 369098752
                                  db_domain
                                  db_file_multiblock_read_count 16
                                  db_name neosoft
                                  dispatchers (PROTOCOL=TCP)
                                  fast_start_mttr_target 300
                                  hash_join_enabled TRUE
                                  instance_name neosoft
                                  java_pool_size 83886080
                                  large_pool_size 83886080
                                  open_cursors 300
                                  pga_aggregate_target 25165824
                                  processes 1500
                                  query_rewrite_enabled FALSE
                                  remote_login_passwordfile EXCLUSIVE
                                  shared_pool_size 209715200
                                  sort_area_size 524288
                                  star_transformation_enabled FALSE
                                  timed_statistics TRUE
                                  undo_management AUTO
                                  undo_retention 10800
                                  undo_tablespace UNDOTBS1
                                  user_dump_dest F:\oracle\admin\neosoft\udump
                                  -------------------------------------------------------------

                                  End of Report
                                  • 29. Re: performance(statspack) related problem
                                    Aman....
                                    Charles,
                                    I did not choose to use the RBO - Oracle made that decision
                                    I understand.Today morning when I read the post again,it was obvious.My question was incorrect.You didnt ask for RBO to kick in.It was "CHOOSE" mode who did that with the lack of statistics.But I was wondering what was the need to set the optmizer_featue to different versions.It should not have anything to do with the pickeler fetch.This also you confirmed by saying this
                                    The use of OPTIMIZER_FEATURES_ENABLE=9.0.1 had little to do with this experiement - it was a left over setting from a test that I perform a couple weeks ago. I set it back to the original value when I noticed that Oracle was indicating that it was deciding to use the RBO for this and other simple queries - based on the above test, this parameter was not the cause of the warning about RBO.

                                    2)
                                    You said,
                                    Yes, there was a call to DBMS_XPLAN in both cases - the reason why I posted two cases was the unexpected rule base optimizer execution in the first test case. The COLLECTION ITERATOR PICKLER FETCH appeared in the 10046 trace in both cases - you will see that line in a 10046 trace when DBMS_XPLAN is called - I received the expected plan from the call to DBMS_XPLAN, rather than seeing COLLECTION ITERATOR PICKLER FETCH, like in the plan posted by Lockesh.

                                    The reason I got confused while reading the output was this only that RBO/CBO were there and also the same Pickler Fetch part than what's the difference.But I understood this that with the dbms_xplan and autotrace, the pickeler fetch part is kicking or the call to the dbms_xplan query itself but not the real query which JL wanted to see.This pickler fetch in itself is a troublesome thing.About 4 months back, I searched out for this and finally found its definition on Asktom.

                                    As I am sure that you are finding, just because something is a little complicated, it does not mean that something is difficult or impossible to understand. Use the information in the "Optimizing Oracle Performance" book to help you understand what might be happening. You might need to search a little for the parameters that cause what is happening, but there are good books and blogs/websites that will help. 10053 trace files reveal a great deal of detail and shows the effects of changes to various parameters.

                                    Thanks a bunch for your encouraging words and so much precious advice.Thanks a ton Charles for taking out time and preparing the demo to make me understand.Yes I never think that some thing is impossible tounderstand.When another human can "code" all this,I don't think that its too hard to just "understand" it.Yes I am having almost all the books which are showing the proof with the explanation and in my list of sites, immediately that site is added which is really good :-).
                                    Good luck, and stay patient when trying to understand why something is happening.

                                    Thanks a ton for the encouragement.Yes I am going to stay put.That's what was told to me about 5 years back by some one who is known as the best in Oracle community(no prize for guessing who) when I asked him when I will be like him,that it took him 15 years to be where he is at the time.So don't think that you will learn in few months or years.It wont happen and its 101% true.So for sure will try out that I understand things completely and in this process will surely disturb you and lot of other guys a little too much :-).
                                    Thanks and best regards
                                    Aman....