Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Physics of the FILTER operation within SQL_PLANE.

Re: Physics of the FILTER operation within SQL_PLANE.

From: <J.Velikanovs_at_alise.lv>
Date: Tue, 29 Jun 2004 12:45:42 +0300
Message-ID: <OF450003AC.9229B286-ONC2256EC2.00315831-C2256EC2.00364BD7@alise.lv> 





One important conceptual personal conclusion I got from this 
investigation:

vvvvvvvvvvvvvvvv




We can't compare two SQL executions plans (for one query) efficiency by 

BUFFERs gotten (LIO) executing query.
^^^^^^^^^^^^^^^^






I am 7 years working as Oracle DBA. 


Before this day I have told Developers that the main SQL efficiency 
indicator is buffer gotten (LIO) during execution of particular SQL. 
Fastest from two SQL will be SQL with lower LIO indicator. "Plus we need 
to evaluate sorting operations".




HA HA HA !!!


Take a look on TESTCASE Below!



SQL1 



Query=1201856


Elapsed=46.17


Query/sec=26031 blocs/sec



SQL2



Query=5616


Elapsed=42.98


Query/sec=130 blocs/sec



COMMENTS: 



- Approximately the same execution time, but 200 times different LIO count
- Waits for disks in this case insignificant comparing to elapsed time, 
can be 


ignored


- Real SQL execution time (set timing on) is nearly the same as elapsed



!!!Conclusion!!!:


SQL1 execute LIO 200 times effective then SQL2. 
If 

vvvvvvvvvvvvvvvv
SQL1 LIO = SQL2 LIO = 100 000 ,then 
^^^^^^^^^^^^^^^^




SQL1 cpu time = 38 sec, 


SQL2 cpu time = 7692 sec, 

vvvvvvvvvvvvvvvv




SQL1 cpu time!= SQL2 cpu time

SQL1 cpu time * 200 = SQL2 cpu time
^^^^^^^^^^^^^^^^




LIO doesn't indicate effectiveness.


I need to recall my recommendations to developers that I have given during 
7 years ;)


I can't effectively use statspack reports (TOPs by Buffers) for 
identifying ineffective SQL-s.




It is looks like; I am going to tell Developers to go back to old method 
for SQL execution plans effectiveness evaluation:
“Take your watch and just measure the execution time”



No No No! Am in prostration ;(


I don’t know that to tell!


I don’t see any appropriate method how to compare tow SQL execution plans.
Any ieas?



Thanks in advance,


Jurijs




Jurijs


9268222




http://otn.oracle.com/ocm/jvelikanovs.html




TEST Case text




SELECT /*+ RULE */ count(m.v) from main_tab m
where

        m.n in
        (select f.n from 




  filter_tab f where f.v='a')




call     count       cpu    elapsed       disk      query    current  rows
------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        2      0.00       0.00          0          0          0     0
Execute      4      0.00       0.00          0          0          0     0
Fetch        4     44.48      46.17        562    1201856          0     4


------- ------  -------- ---------- ---------- ---------- ---------- 




total       10     44.48      46.17        562    1201856          0     4



Misses in library cache during parse: 1


Optimizer mode: RULE


Parsing user id: SYS   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------
      2  SORT AGGREGATE (cr=801234 r=562 w=0 time=31244261 us)


 800000   NESTED LOOPS  (cr=801234 r=562 w=0 time=28962918 us)
 800000    TABLE ACCESS FULL MAIN_TAB (cr=1230 r=562 w=0 time=3309977 us)
 800000    TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=800004 r=0 w=0 
time=16177875 us)


 800000     INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=4 r=0 w=0 time=5226711 
us)(object id 9699)




Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total 


Waited




SELECT /*+ RULE */ count(m.v) from main_tab m
where

        exists
        (select v from 




  filter_tab f where f.n=m.n and f.v='a')




call     count       cpu    elapsed       disk      query    current  rows
------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        2      0.00       0.00          0          0          0     0
Execute     12      0.01       0.00          0          0          0     0
Fetch       12     42.38      42.97       1686       5616          0    12


------- ------  -------- ---------- ---------- ---------- ---------- 




total       26     42.39      42.98       1686       5616          0    12



Misses in library cache during parse: 1


Optimizer mode: RULE


Parsing user id: SYS   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------
      6  SORT AGGREGATE (cr=3738 r=1686 w=0 time=28675198 us)


2400000   FILTER  (cr=3738 r=1686 w=0 time=22306438 us)
2400000    TABLE ACCESS FULL MAIN_TAB (cr=3690 r=1686 w=0 time=7478255 us)


     24    TABLE ACCESS BY INDEX ROWID FILTER_TAB (cr=48 r=0 w=0 time=524 
us)


     24     INDEX UNIQUE SCAN FILTER_TAB_I1 (cr=24 r=0 w=0 time=242 
us)(object id 9699)




Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total 


Waited




"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: oracle-l-bounce_at_freelists.org


29.06.2004 11:44


Please respond to oracle-l
 

        To:     <oracle-l_at_freelists.org>
        cc: 
        Subject:        Re: Physics of the FILTER operation within 


SQL_PLANE.




I've just done a little more work based on your test case,
checking which rows in the filtering table get hit. Based
on these observations, I think Oracle creates a hash table
for the results of the FILTERing probe, using the probe
"key" as the basis for the hashkey.  On each probe,
Oracle saves the result in the hash table, so long as 
there are no hash collisions.  If a new probe key collides
with an existing probe key, then the result is not saved.
I also infer that in your test case, the size of the hash table
is 128.



I would guess, but have not yet confirmed, that the size 
of the hash table is set as the query starts, - but only 
when running the cost based optimizer, so that Oracle
has an estimate of the number of probe keys that need
to be stored.





Regards



Jonathan Lewis



http://www.jlcomp.demon.co.uk



http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ



http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st







FILTER operation effectiveness depends on how (in which order) rows are 
inserted into driving table. Looks similar to clustering factor in index 
range scans ;)


Take a look on simple TESTCASE I have made on 9.2.0.4 Win2000.
As you can see LIO count defers by 17 times (First case 63 LIO, second 
1061) depending on order how rows have been inserted.
One more effect, if we reduce row count in filter table (third test), then 



Oracle execute filter operation more effective (LIO=43) independing of 
inserting order, due to "remembering results of previous probes" probably.



    
  
  1.   Any comments?
>> [Jonathan Lewis] However, FILTER can be much more efficient than nested 






loop, because it can remember results of previous probes into the second 
table - effectively making the probe an in-memory lookup.
2. Any ideas how many "results of previous probes" Oracle can "remember" 
for next comparison?



Best regards,


Jurijs






Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------





----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------



Received on Tue Jun 29 2004 - 04:50:03 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US