Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: May 05, 2003 - 2:11 pm UTC

Last updated: April 22, 2013 - 1:49 pm UTC

Version: 9.2

Viewed 1000+ times

You Asked

Hi,
Just a quick one here.
1) Is your RUNSTATS routine also valid for 9.2.If not what changes need to be done.
2) Is is true Oracle does not use RBS, redo on 9i DB instead UNDO is used.
2) When is Oracle releasing 10i beta version.

Thanx

and Tom said...

1) yes it is.

2) undo = rollback, rollback = undo. There is system managed UNDO accomplished by creating an undo tablespace. Here Oracle figures out how many rollback segments to create, how big they should be. There is manually managed UNDO which is accomplished by you doing rollback segment creation as you used to have to.

3) it already has.

Rating

  (36 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Where can ?

A reader, May 05, 2003 - 3:43 pm UTC

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

Thanx for your quick response.

Where can I find information on Oracle Database release 10i ?

Sanjay, May 05, 2003 - 3:44 pm UTC

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 Kyte
May 05, 2003 - 8:27 pm UTC

exactly - 10i has not yet been "announced". Information about it is officially available only under NDA at this point

Beta released under NDA

Mark J. Bobak, May 05, 2003 - 6:37 pm UTC

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

A reader, May 05, 2003 - 10:22 pm UTC

Thanks for the details.

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

Tom Kyte
May 06, 2003 - 7:39 am UTC

it'll be out by the end of the year.

runstats problem

A reader, June 26, 2003 - 12:18 pm UTC

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




Tom Kyte
June 26, 2003 - 12:30 pm UTC

can you dig further and see if there are issues with the underlying v$ views themselves -- can you run the query in the view, or is it the view

you have filed a tar with support right?

Re: runstats problem

A reader, June 27, 2003 - 11:09 am UTC

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...



runstats

A reader, January 14, 2004 - 5:16 pm UTC

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?

Tom Kyte
January 14, 2004 - 6:09 pm UTC

not that I have but ....



</code> http://www.amazon.com/exec/obidos/tg/detail/-/1590592174/ <code>

does...... (i tech edited that book -- they took runstats and made it "better", should have code on apress.com)

cool- thanx!

A reader, January 14, 2004 - 6:20 pm UTC

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!

Tom Kyte
January 14, 2004 - 6:35 pm UTC

sitting next to the guy who is responsible for the downloads for this book -- says they will be there "real soon" ;)


he does plsql in depth -- with others (chris beck, works for me contributed as well)... if you want plsql info -- much more than I give in effective oracle.

thanx Tom!

A reader, January 14, 2004 - 6:37 pm UTC

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


Certainly a very good book...

Connor McDonald, January 15, 2004 - 9:04 am UTC

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

well, i ordered it yesterday , Conor

A reader, January 15, 2004 - 10:16 am UTC

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....



runstats

A reader, January 15, 2004 - 4:52 pm UTC

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?

ok - no need to answer the second q

A reader, January 15, 2004 - 5:08 pm UTC

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

A reader, January 15, 2004 - 6:05 pm UTC

Congrats Connor!!!

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

Thanks.

but how to use runstats reports?

A reader, May 07, 2005 - 9:31 am UTC

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>

Tom Kyte
May 07, 2005 - 9:45 am UTC

metalink has much information, many notes on them.


And -- it is not so much "causes/solutions of problems" you are looking at here.

It is "ok, which one uses less of some resource (statistics are good for that) and has the chance to scale better (less latch utilization)"

so it is not identifying a problem so much as showing you facts about resource usage.

From where I can download runstats ?

Parag Jayant Patankar, June 03, 2005 - 10:32 am UTC

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 Kyte
June 03, 2005 - 11:07 am UTC

A tweak to runstats

Scott Swank, October 17, 2005 - 4:20 pm UTC

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
 

Are all latches created equal?

cmj, November 04, 2005 - 3:12 pm UTC

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 Kyte
November 04, 2005 - 5:48 pm UTC

... TKPROF shows a huge improvement in parsing. ...

that could be predicted, yes.


are the plans different or the same. give us an example of what you did - something is different here.

Latches created equal

cmj, November 04, 2005 - 4:32 pm UTC

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!

A reader, November 07, 2005 - 4:14 pm UTC

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

Tom Kyte
November 08, 2005 - 9:42 pm UTC

just use it. be nice to attribute it (especially if you publish it in an article or something) but just use it.

A reader, November 09, 2005 - 8:00 am UTC

Thanks Tom

Runstats

A reader, May 31, 2006 - 8:14 am UTC

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.

Tom Kyte
May 31, 2006 - 10:26 am UTC

show us the information - using runstats_pkg.rs_stop( 10000 )

(only things that differ by 10,000 or more) to start with...

Runstats Comparisons

A reader, May 31, 2006 - 10:55 am UTC

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%


Tom Kyte
May 31, 2006 - 3:14 pm UTC

I like the last run2.

less latching.
runs fast.

looks like it is using a nice bulk operation (full scan - table scan rows gotten).


You look at the latching activity - in general "the less latching, the more scalable an implemenation will be (more people can do this at the same time with less bumping into eachother)". Remember - latches are a type of lock, locks are serialization devices, serialization devices inhibit our scalability.

You look at the raw statistics - gives you an understanding of what is going on.

You look at CPU and elapsed times.




Regarding "A tweak to runstats" a couple posts back....

Jmv, May 31, 2006 - 4:52 pm UTC

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??

Tom Kyte
June 01, 2006 - 9:17 am UTC

that is not my implementation :) sorry - I didn't do that.

Runstats

A reader, June 01, 2006 - 5:28 am UTC

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.

Tom Kyte
June 01, 2006 - 10:41 am UTC

the aggregated latching information draws my eyes (in general, less latching -> I like it better)

The elapsed times jumps right out.

Then, I look at the statistics - they are documented in the reference guide (metalink searches are good as well)

The latching stuff, the big ones pop right out and are somewhat "obvious" - shared pool, library cache. Less obvious ones - metalink is a good resource.

Runstats

A reader, June 01, 2006 - 12:41 pm UTC

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.

Tom Kyte
June 01, 2006 - 1:14 pm UTC

turning on tracing will completely change the execution profile of many things. I would not recommend it generally.

Runstats vs. Elapsed Time

A reader, June 07, 2006 - 8:42 am UTC

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.

Tom Kyte
June 07, 2006 - 2:46 pm UTC

How did they come up with 200 versions of the query??

If it is 200 version of the same query, one wonders why they need a "statistical regression model", wouldn't you just "sort" and take the one with the smallest runtime?

I don't follow what they are doing - many "50 cent words" but I don't see how it works?

Runstats

A reader, June 08, 2006 - 6:19 am UTC

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 Kyte
June 08, 2006 - 9:17 am UTC

I fail to see how statistical analysis comes into play here.

If you have 200 versions of the same thing, you run them, time them. Ok, now how do you "regress" them

Overhead with RUNSTATS (skewing results)

Robert, September 13, 2006 - 12:28 pm UTC

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.

Tom Kyte
September 13, 2006 - 3:06 pm UTC

or just ignore 2,000 measely bytes of redo which is just "noise".



Using Runstats

A reader, September 20, 2006 - 6:59 am UTC

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.

Tom Kyte
September 20, 2006 - 3:07 pm UTC

you might consider statspack for this - which will get everything runstats does plus "way more"

statspack.snap
method a
statspack.snap
undo whatever
statspack.snap
method b
statspack.snap

and compare method a/b at that level (else we have to rewrite runstats :)

runstats.html moved?

Mathias, December 03, 2006 - 11:06 am UTC

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.

Tom Kyte
December 03, 2006 - 1:18 pm UTC

</code> http://asktom.oracle.com/tkyte/runstats.html <code>

had to turn off user directory indexing :(

Let me answer my own question...

Mathias, December 03, 2006 - 11:13 am UTC

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>

runstats.html moved

Gunther, September 23, 2009 - 6:47 am UTC

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
Tom Kyte
September 28, 2009 - 2:19 pm UTC

I'm going to have to post those in my "files" area - working on that...

Runstats - Not able to create

Parthiban Nagarajan, May 02, 2011 - 5:03 am UTC

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 Kyte
May 04, 2011 - 12:08 pm UTC

RunStats for 11G

StanR, January 23, 2012 - 11:23 am UTC

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



runstats.html is inactive

-praveen-, April 03, 2013 - 3:40 pm UTC

The link is no longer active

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

Do you have a recent one?

Thanks
Tom Kyte
April 22, 2013 - 1:49 pm UTC

look on the homepage where for the new link....