I got the attachments...
--- [EMAIL PROTECTED] wrote: > Sorry, the attachments didn't make it, though they were only text. > > I can put them some accessible via the web if anyone wants them. > > Jared > > > > > > [EMAIL PROTECTED] > Sent by: [EMAIL PROTECTED] > 04/03/2003 11:34 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: RE: oracle full table scan > > > If you can change it to an IOT, it may be beneficial. > > There's no blanket clause to be used that says 'Always do this'. > > I higly encourage folks on this list to setup and use the run_stats > method of comparing different access methods. This is something > Tom Kyte put together. It is very simple to use. > > URL: http://osi.oracle.com/~tkyte/runstats.html > > I've attached my versions of the scripts for your convenience. > > You can use these to easily compare unindexed vs indexed > reads on small tables, indexed vs IOT, etc. > > Jared > > > > > > > > "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 04/03/2003 10:05 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: RE: oracle full table scan > > > Thanks Jared, > What if my developer is selecting all or most of the records from the > > table and not all the columns in the select list are in the index > that > should have been used? > I understand your point, in fact to use Jonathan's words .. "should a > > small lookup table BE an index (IOT)?" ... I am testing this approach > here > > and have found some performance benefit out of it. > Cheers > Raj > -------------------------------------------------------------------------------- > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > > -----Original Message----- > Sent: Thursday, April 03, 2003 1:01 PM > To: [EMAIL PROTECTED] > Cc: Jamadagni, Rajendra > Importance: High > > Raj, > Indexing small tables is a good thing if you are doing single row > lookups. > > An index read and lookup by rowid is much more scalable than > doing an FTS, even if the table is only 2 blocks. > Jared > > > > > > > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > > > > ATTACHMENT part 2 application/octet-stream name=view.sql > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > > ATTACHMENT part 4 application/octet-stream name=grants.sql > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > > ATTACHMENT part 6 application/octet-stream name=run_stats.sql > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > > > Runstats.sql This is the test harness I use to try out different > ideas. It > shows two vital sets of statistics for me The elapsed time difference > between > two approaches. It very simply shows me which approach is faster by > the wall > clock How many resources each approach takes. This can be more > meaningful then > even the wall clock timings. For example, if one approach is faster > then the > other but it takes thousands of latches (locks), I might avoid it > simply > because it will not scale as well. The way this test harness works is > by saving > the system statistics and latch information into a temporary table. > We then run > a test and take another snapshot. We run the second test and take yet > another > snapshot. Now we can show the amount of resources used by approach 1 > and > approach 2. > > Requirements > > In order to run this test harness you must at a minimum have: Access > to > V$STATNAME, V$MYSTAT, and V$LATCH If you want to use the view as I > have, you > must be granted select DIRECTLY on SYS.V_$STATNAME, SYS.V_$MYSTAT, > and > SYS.V_$LATCH. It will not work to have select on these via a ROLE. > You can > still run the test harness, you just will not be using the view > "STATS" I have > below (substitute in the query text in the PLSQL block where I > reference the > view STATS). The ability to create a table -- run_stats -- to hold > the before, > during and after information. You should note also that the LATCH > information > is collected on a SYSTEM WIDE basis. If you run this on a multi-user > system, > the latch information may be technically "incorrect" as you will > count the > latching information for other sessions - not just your session. This > test > harness works best in a simple, controlled test environment. > > The table we need is very simple: > create table run_stats > ( runid varchar2(15), > name varchar2(80), > value int > ); > and if you can get direct grants on the V$ tables necessary (or have > your DBA > create this view and grant SELECT on it to you), you can create this > view: > create or replace view stats > as select 'STAT...' || a.name name, b.value > from v$statname a, v$mystat b > where a.statistic# = b.statistic# > union all > select 'LATCH.' || name, gets > from v$latch; > Now the test harness itself is very simple. Here it is: > > declare > l_start number; > add any other variables you need here for the test... > begin > delete from run_stats; > commit; > -- start by getting a snapshot of the v$ tables > insert into run_stats select 'before', stats.* from stats; > > -- and start timing... > l_start := dbms_utility.get_time; > > -- for things that take a very small amount of time, I like to > -- loop over it time and time again, to measure something "big" > -- if what you are testing takes a long time, loop less or maybe > -- not at all > for i in 1 .. 1000 > loop > -- your code here for approach #1 > end loop; > > dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' > ); > > -- get another snapshot and start timing again... > insert into run_stats select 'after 1', stats.* from stats; > > l_start := dbms_utility.get_time; > > for i in 1 .. 1000 > loop > -- your code here for approach #2 > end loop; > > dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' > ); > insert into run_stats select 'after 2', stats.* from stats; > end; > / > and thats it, now after that block runs, you'll see the wall clock > difference > between the two approaches. You can see the really important stuff > using this > query: > select a.name, b.value-a.value run1, c.value-b.value run2, > ( (c.value-b.value)-(b.value-a.value)) diff > from run_stats a, run_stats b, run_stats c > where a.name = b.name > and b.name = c.name > and a.runid = 'before' > and b.runid = 'after 1' > and c.runid = 'after 2' > and (c.value-a.value) > 0 > and (c.value-b.value) <> (b.value-a.value) > order by abs( (c.value-b.value)-(b.value-a.value)) > / > > > > ATTACHMENT part 9 application/octet-stream name=table.sql > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > > ATTACHMENT part 11 application/octet-stream name=test_harness.sql > The previous attachment was filtered out by the ListGuru mailing > software at fatcity.com because binary attachments are not > appropriate > for mailing lists. If you want a copy of the attachment which was > removed, contact the sender directly and ask for it to be sent to > you by private E-mail. > > This warning is inserted into all messages containing binary > attachments which have been removed by ListGuru. If you have > questions > about this message, contact [EMAIL PROTECTED] for clarification. > > ATTACHMENT part 13 application/octet-stream name=defaults.sql __________________________________________________ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
