| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: oracle full table scan
I got the attachments...
> 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 Postmaster_at_fatcity.com 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 Postmaster_at_fatcity.com 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 Postmaster_at_fatcity.com 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 Postmaster_at_fatcity.com 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 Postmaster_at_fatcity.com for clarification.
>
> ATTACHMENT part 13 application/octet-stream name=defaults.sql
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: wisernet100_at_yahoo.com 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: ListGuru_at_fatcity.com (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).Received on Thu Apr 03 2003 - 15:08:38 CST
 |
 |