I get more info on those beta releases.When excatly was it released.

Thanx for your quick response.

I tried to find under products and downloads sections, and I could not find any information on release 10i. Is there any place where I can find more information about features and highlights of 10i Beta version of Oracle ?

Thanks.

Tom can confirm, but I'm pretty certain that 10i beta program
participants sign NDAs. (That's Non-Disclosure Agreements.)

So, you won't hear anything about the details of 10i or it's
features till the official release happens. (Perhaps at
OpenWorld??)

-Mark

Thanks for the details.

Any approximate beta release date for general public(not NDA)?

HI Tom
I was trying to use your runstats utility on Oracle
9.0.1.0.0 production (Enterprise Edition) - but I am getting the following error


09:13:17 oracle@ORA901> exec runstats_pkg.rs_start;
BEGIN runstats_pkg.rs_start; END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


09:13:26 oracle@ORA901> spool off

I then ran the select * from stats view in the script
and found it was not able to run to completion - following is the output..Any ideas? Thank you so much!!

09:12:04 oracle@ORA901> select * from stats;

STAT...logons cumulative
1

STAT...logons current
1

STAT...opened cursors cumulative
19

STAT...opened cursors current
7

.... some more rows here
......

STAT...OS All other sleep time
0

ERROR:
ORA-03113: end-of-file on communication channel



240 rows selected.

09:12:12 oracle@ORA901> spool off

Thanx Tom - For your lightening response (How HOW do
you do it!!!?))

Anyways, I am not sure how to dig further. It may be a bug with my 9.0.1.0 - I have run into some other
problems as well and I think I need to move to 9.0.1.4...

Tom
Read the note about you being out of country - please
respond when you like (it goes without saying)

the runstats utility - is there a version that compares
more than 2 approaches?

Could not find it on apress download section though
may be they don't have it yet..

So this book is by our own Conor, eh? Tempted to ask your
opinion - will it give us as much value as your
chapter in "effective oracle by design"
gave us (not to put you down, Conor)?
thanx!

May be I will be the first reviewer on amazon then?;)

I can say without bias that its a great book :-)

Seriously though, the emphasis for the book is not syntax, but tools and techniques for maximising the benefits from PL/SQL. From the back cover:

<marketing mode on>

"If you’re brand new to PL/SQL, then you’ll want to take some time to get familiar with the language before tackling this book. It’s not for the total beginner. But once you’re up and running, we believe you’ll find our book an invaluable guide for ensuring that the PL/SQL solutions you build are robust, perform well, and are easy to maintain."

</marketing mode on>

Cheers
Connor

based on your feedbacks on asktom site and the fact that
Tom tech-edited it:) Also ordered "Scaling Oracle8i: Building Highly Scalable OLTP System Architectures "
based on your web site recommendations...
Went through your presentation on oow from your
web site...Looks great....

do you recommend it to be shipped with the product
(assuming we can use it) - I dont see any reasons
to do that since the benchmarks are to be run on an
isolated system (which would be a dev environment.)

Also, in a connection pool env., I guess the table
run_stats would retain the data till a repository bounce
since sessions are maintained in a pool (and don't expire),
correct?

The stats get deleted each time you run it
per the implementation

Congrats Connor!!!

Good to see your book, is this your first book.

Thanks.

where can one get additional information about possible causes/solutions of problems reported by those runstats summaries?

maybe for latches something similar to the information given for wait events at: </code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/instance_tune.htm#33815 <code>

Hi Tom,

Can you tell me or provide me url from where I can download runstats utility ? I want to use it in oracle 9.2

regards & thanks
pjp

Tom,

I've tweaked your runstats code in a couple of ways that seem useful
to me.  So I figured I might as well pass them back to you.

1. I've broken the distinction between latches and stats out into a
separate column called stat_type.

2. I've added a third query to the stats view, which also generates a
third type of statistic in addition to latches and stats:

 UNION ALL
 SELECT 'TIME',
        'Elapsed Time',
        hsecs
   FROM v$timer;

3. I've changed the package signature such that there are two main
APIs: 1) reset, which clears out the temp table and adds a current
stat data, and 2) snap, which appends the current stat data:

 PROCEDURE reset
 IS
 BEGIN
   DELETE FROM run_stats;

   g_runid := 0;
   insert_stats ();
 END reset;

 PROCEDURE snap (p_run_name IN VARCHAR2 DEFAULT NULL)
 IS
 BEGIN
   g_runid := g_runid + 1;
   insert_stats (p_run_name);
 END snap;

Where the actual stats are recorded as you'd expect, though the column
"runid" is now numeric for reasons that will soon be apparent:

 PROCEDURE insert_stats (p_run_name IN VARCHAR2 DEFAULT NULL)
 IS
 BEGIN
   INSERT INTO run_stats
               (runid,
                run_name,
                stat_type,
                NAME,
                VALUE)
     SELECT g_runid,
            NVL (p_run_name, 'Run ' || g_runid),
            s.stat_type,
            s.NAME,
            s.VALUE
       FROM stats s;
 END insert_stats;

From here I create a reporting view called "snaps" that presents the
various stats for up to six calls to the snap() API:

CREATE OR REPLACE VIEW snaps AS
SELECT s.*,
      avg(max_stat) over (partition by stat_type) as avg_stat,
      avg(max_stat-min_stat) over (partition by stat_type) as avg_stat_delta
 FROM (
SELECT stat_type,
      name,
      max(decode(runid, 1, stat)) AS snap1,
      max(decode(runid, 2, stat)) AS snap2,
      max(decode(runid, 3, stat)) AS snap3,
      max(decode(runid, 4, stat)) AS snap4,
      max(decode(runid, 5, stat)) AS snap5,
      max(decode(runid, 6, stat)) AS snap6,
      min(stat) AS min_stat,
      max(stat) AS max_stat
 FROM (
SELECT stat_type,
      name,
      runid,
      run_name,
      value -
        LAG(value) OVER (PARTITION BY stat_type, name ORDER BY runid) AS stat
 FROM run_stats
      )
 WHERE runid > 0
 GROUP BY
      stat_type,
      name
HAVING max(stat) > 0
      )  s;

Then I present this data in another view that is focused on only 2
snaps (the most common scenario).  I further only show the stats that
are contributing above average -- i.e. the reporting threshold for the
statistics is handled for me.

CREATE OR REPLACE VIEW snaps2
AS
 SELECT stat_type,
        name,
        snap1,
        snap2,
        ROUND (CASE
                 WHEN snap1 > 0
                   THEN snap2 / snap1
                 WHEN snap2 > 0
                   THEN NULL
                 ELSE 0
               END,
               2) AS ratio,
        snap2 - snap1 AS diff
   FROM snaps
  WHERE max_stat >= NVL (runstats_pkg.stat_threshold (), avg_stat)
     OR (max_stat - min_stat) >=
                   NVL (runstats_pkg.stat_delta_threshold (), avg_stat_delta);

So this all works as follows (rs is a global synonym for the runstats_pkg):

SQL> begin
 2    rs.reset();
 3    update big_table
 4       set object_id = -1*object_id
 5     where rownum <= 50;
 6    commit;
 7    rs.snap();
 8    for x in (select rowid as rid, object_id from big_table where
rownum <= 50)
 9    loop
 10      update big_table  b
 11         set object_id = -1*x.object_id
 12       where b.rowid = x.rid;
 13    end loop;
 14    commit;
 15    rs.snap();
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL> col name for a30
SQL> select * from snaps2
 2  order by stat_type, diff desc;

STAT_ NAME                                SNAP1      SNAP2      RATIO       DIFF
----- ------------------------------ ---------- ---------- ---------- ----------
LATCH cache buffers chains                 9932      10690       1.08        758
LATCH checkpoint queue latch                  1        306        306        305
LATCH library cache                          13        116       8.92        103
LATCH library cache pin                      10        112       11.2        102
STAT  redo size                           15208      15760       1.04        552
STAT  recursive calls                         6        107      17.83        101
STAT  session logical reads                  59        131       2.22         72
STAT  consistent gets                         5         62       12.4         57
STAT  calls to get snapshot scn: kcm          6         58       9.67         52
     gss

STAT  execute count                           3         53      17.67         50
STAT  buffer is not pinned count              1         50         50         49
STAT  table scan blocks gotten                1         50         50         49
STAT  no work - consistent read gets          1         49         49         48
STAT  sorts (rows)                         2259       2259          1          0
TIME  Elapsed Time                            7          8       1.14          1

15 rows selected.

SQL>

Cheers,
Scott
 

Tom,

I am a new but increasingly avid fan reading your books and learning to use the performance tools you recommend. I use v8.1.7 in a shop that is about to upgrade to 10g.

As an exercise, I found a package that uses dynamic SQL in a loop to insert records into a table. I created a new version to do exactly the same thing, eliminating the dynamic SQL. TKPROF shows a huge improvement in parsing. Runstats shows that most of the latch statistics are much improved.

However, two latch statistics are terrible for the new version: cache buffers chains and cache buffers lru chain. Where the new code is Run1 and the dynamic SQL code is Run2, the runstat lines that concern me are:

Name Run1 Run2 Diff
LATCH.cache buffers lru chain 353,676 8,736 -344,940
LATCH.cache buffers chains 76,919,934 5,887,175 -71,032,759

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
78,371,229 48,746,209 -29,625,020 160.77%

1) Does that mean that the dynamic SQL version is actually better in spite of the improvement in other latch stats?

From the One-on-One CD that came with Expert Oracle (what a great perk!) I see that you say that stat may indicate that there is a "hot block" with lots of contention.

2)Since the two versions create near identical inserts into the output table, is there any reason the hard-coded SQL should have less contention for a hot block than the new code?

3)I tried running the same scenario twice and got near identical results from runstats. Do you see anything fishy about that?

Tom,

Further searches on Ask Tom may have me on the path to finding the answers to the questions I asked in the previous post. The following streams have led me to the conclusion that I have to reduce logical I/Os in a select query or yield to the superiority of the dynamic SQL:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1229436447262, http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6643159615303, <code>

I'm sure the answers to most of life's questions are out there in Ask Tom, if only you can come up with the right search criteria and gain enough experience to recognize the light when shines down. In the words of another of your readers, much of what I read is "over my mind," but I've picked up a bucket of pearls on this site.

Thank you!

Tom if I want to include your package, in a sql script (not commercial), can I do?, I have to put something?

Thanks Tom

I just set up Runstats on 10g and ran it against a query I am rewriting. I am trying to make sense of all the latch and stat info that came out. It is a little mind-boggling! Any suggestions on how to interpret what is important in the list? Thanks in advance.

ORIGINAL QUERY VS. NEW QUERY USING OLD VIEW

Run1 ran in 49922 hsecs
Run2 ran in 56342 hsecs
Run1 ran in 88.61 % of the time

Name Run1 Run2 Diff
LATCH.undo global data 1,544 460 -1,084
LATCH.simulator hash latch 14,476 13,386 -1,090
LATCH.cache buffers lru chain 1,408 2,725 1,317
STAT...bytes received via SQL* 784 2,426 1,642
LATCH.checkpoint queue latch 4,061 6,051 1,990
LATCH.library cache pin 29,875 27,180 -2,695
LATCH.library cache lock 16,656 13,684 -2,972
STAT...buffer is pinned count 132,308 128,070 -4,238
LATCH.library cache 50,825 46,288 -4,537
LATCH.redo allocation 6,963 2,210 -4,753
LATCH.row cache objects 37,580 32,442 -5,138
LATCH.SQL memory manager worka 24,183 29,813 5,630
STAT...Elapsed Time 49,932 56,350 6,418
LATCH.shared pool 17,352 9,761 -7,591
STAT...rows fetched via callba 8,622 0 -8,622
STAT...index fetch by key 12,998 4,326 -8,672
STAT...table fetch by rowid 135,249 126,503 -8,746
LATCH.session allocation 13,938 3,603 -10,335
STAT...consistent gets - exami 25,991 12,983 -13,008
LATCH.AWR Alerted Metric Eleme 13,188 0 -13,188
STAT...bytes sent via SQL*Net 374,350 388,872 14,522
STAT...buffer is not pinned co 168,256 142,087 -26,169
STAT...table scan blocks gotte 39,804 259 -39,545
STAT...no work - consistent re 187,215 138,902 -48,313
STAT...consistent gets 222,001 160,553 -61,448
STAT...consistent gets from ca 222,001 160,553 -61,448
STAT...session logical reads 222,041 160,581 -61,460
STAT...session pga memory -65,536 0 65,536
STAT...physical read bytes 81,920 0 -81,920
STAT...physical read total byt 81,920 0 -81,920
LATCH.cache buffers chains 499,307 398,962 -100,345
STAT...sorts (rows) 307,140 2,469 -304,671
STAT...session uga memory max 196,224 967,112 770,888
STAT...table scan rows gotten 1,884,300 8,776 -1,875,524

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
797,006 652,111 -144,895 122.22%

NEW QUERY USING OLD VIEW VS. NEW QUERY USING NEW VIEW

Run1 ran in 47324 hsecs
Run2 ran in 44564 hsecs
Run1 ran in 106.19 % of the time

Name Run1 Run2 Diff
STAT...Elapsed Time 47,328 44,569 -2,759
LATCH.library cache lock 12,458 9,090 -3,368
STAT...index scans kdiixs1 8,653 4,327 -4,326
STAT...index fetch by key 4,326 0 -4,326
STAT...shared hash latch upgra 8,653 4,327 -4,326
LATCH.library cache pin 24,184 18,560 -5,624
LATCH.shared pool 9,930 4,205 -5,725
STAT...table scan blocks gotte 259 6,803 6,544
STAT...consistent gets - exami 12,986 4,334 -8,652
LATCH.simulator lru latch 13,425 2,287 -11,138
LATCH.simulator hash latch 13,435 2,287 -11,148
LATCH.library cache 42,365 30,275 -12,090
LATCH.row cache objects 49,130 17,285 -31,845
STAT...table fetch by rowid 126,493 4 -126,489
STAT...no work - consistent re 138,892 11,129 -127,763
STAT...buffer is pinned count 128,070 6 -128,064
STAT...buffer is not pinned co 142,067 4,327 -137,740
STAT...session logical reads 160,563 19,837 -140,726
STAT...consistent gets from ca 160,545 19,813 -140,732
STAT...consistent gets 160,545 19,813 -140,732
STAT...table scan rows gotten 8,776 320,055 311,279
LATCH.cache buffers chains 466,022 70,153 -395,869
STAT...session pga memory max 0 458,752 458,752
STAT...session uga memory max 160 1,242,752 1,242,592

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
699,411 217,725 -481,686 321.24%

I have a question about the "snaps2" view where a reference is made to two procedures as follows:
...
WHERE max_stat >= NVL (runstats_pkg.stat_threshold (), avg_stat)
OR (max_stat - min_stat) >=
NVL (runstats_pkg.stat_delta_threshold (), avg_stat_delta);

Where or how were the procedures (runstats_pkg.stat_threshold and runstats_pkg.stat_delta_threshold )defined??

Thanks for the info. Could you please tell me what things jumped out at you that you liked in comparing the runs? And where are all of these entities defined? I would like to have a better grasp of what these things are in particular. Thanks in advance.

Thanks loads! This is great! One more thing. Is it OK to combine the Runstats run with writing to the trace file for TKPROF output? My queries produce a fair amount of data and it is tedious to run them twice but I do not want to introduce any spurious data.

I have been tasked to tune some queries at the same time as another group has been tasked to look into the same set of queries. I have been using autotrace, tkprof and runstats for my evaluations. The other group has taken a totally different approach. They run 200 versions of the query using different views, different filters and different sort orders and collect only elapsed time. They put this info into a spreadsheet and then run a statistical regression model to identify where significant differences lie, supposedly to identify which of their hypotheses shows a change. This does not seem to have any inherent validity to me but can you please comment on this approach? Management seems to like their approach and I would like to be able to say something other than something censored! Maybe I am nuts? Thanks in advance.

I have no idea how they come up with 200 versions. Probably 3 or 4 different views, several different filters, several different sorts all multiplied together makes 200? :) They are doing a regression because one of the people has a statistical background but no Oracle and as they sing "if I had a hammer...". I have a statistical background too but it would never have occurred to me to tune this way.

Tom,

There appears to be (obviously, duh) some overhead associated with runstats which skews the results.

For example I am seeing a 'redo size' of around 2000 when I am doing 'select * from dual'....

I guess what I am wanting to confirm 'officially' is that RUNSTATS is best used on 'larger' tests where the minimal 'overhead' of the utility itself can be safely obsorbed into the totals.

Am I correct?

Thanks,

Robert.

I have been regularly using runstats. What a great tool! But I have a situation that I am not sure how to handle. I need to benchmark two methods of loading data and I want to look at the runstats output for the two methods. I have a test case of data that I want to use for both methods so that the scenarios are exactly the same. To get the tracing info all I did was run method A, then run my script that undoes all the inserts and updates in the various tables, and then run method B. In the TKprof output I can just ignore the info for the undo script. But if I use runstats then the undo will be included in either the run for method A or the run for method B depending upon where I execute rs_middle. Do you have any suggestions on how to handle this? Help on this would be greatly appreciated. Thank you.

Has the </code> http://asktom.oracle.com/~tkyte/runstats.html <code>page been moved? All references to it links to that page, but it doesn't seem to be there anymore.

After submitting that I found another link that showed that the tidlde has been dropped.

The link is now </code> http://asktom.oracle.com/tkyte/runstats.html <code>

The link in the previous posts http://asktom.oracle.com/tkyte/runstats.html is no longer available (maybe after the move to apex?). Is it still out there somewhere?

Thanks,

Gunther

Hi Tom

I tried to create the runstats_pkg tool in a database but in vain. I am not able to figure out the missing privileges. Following script should give enough detail. Please help me.

SQL> select count(*) from v$statname;

  COUNT(*)
----------
       628

SQL> declare
  2    n number;
  3  begin
  4    select count(*)
  5      into n
  6      from v$statname;
  7    dbms_output.put_line('Count(*) - '||n);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> create or replace
  2  function parthi_test_fun return number
  3  is
  4    n number;
  5  begin
  6    select count(*)
  7      into n
  8      from v$statname;
  9    return n;
 10  end;
 11  /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION PARTHI_TEST_FUN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
7/10     PL/SQL: ORA-00942: table or view does not exist

SQL> create or replace view parthi_test_vw as
  2  select count(*) cnt from v$statname;
select count(*) cnt from v$statname
                         *
ERROR at line 2:
ORA-01031: insufficient privileges

SQL> 

Tom,
Is there an upgraded version of RUNSTATS for 11G?
Given 11G has mutexes (MUTEX_SLEEP, MUTEX_SLEEP_HISTORY)and all?

The link is no longer active

http://asktom.oracle.com/~tkyte/runstats.html

Do you have a recent one?

Thanks