Did you look at them...
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan
[EMAIL PROTECTED]
.com To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: oracle full table scan
04/03/2003 02:08
PM
Please respond to
ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
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).
