不仅仅是时间的问题还有其它的消耗也要考虑, 可能是你做多表连接的sql没有优化的很好
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),
namevarchar2(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))
|
