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.
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)?
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
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?
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!
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 youre brand new to PL/SQL, then youll want to take some time to get familiar with the language before tackling this book. Its not for the total beginner. But once youre up and running, we believe youll 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
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
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?
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
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?
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.
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%
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??
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.
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.
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.
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.
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.
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.
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
Let me answer my own question...
Mathias, December 03, 2006 - 11:13 am UTC
runstats.html moved
Gunther, September 23, 2009 - 6:47 am UTC
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>
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
April 22, 2013 - 1:49 pm UTC
look on the homepage where for the new link....