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: Always_semi_join

Re: Always_semi_join

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Tue, 20 May 2003 21:21:41 -0800
Message-ID: <F001.0059E8A3.20030520212141@fatcity.com>


Hash search is a fast search algorithm which works on sets. If we have set A with elements a1,...,aN, then hashing algorithm would work roughly as follows:

  1. An array H of size M, M<N would be allocated. That array is called hash table.
  2. A function f:A--> [1,..,M] would be defined. This function is called a hash function.
  3. Each element a of the set A would be queued up to H[f(a)] , with H[f(a)] serving as a queue head.

If we wanted to search for an element a, we would compute f(a), go to H[f(a)] and compare a with all elements in the queue, instead of comparing it against all elements of the set A. I will not delve into collisions and perfect hashing functions. This description suffices for my purpose. A simple, but very good implementation of hash algorithm can be found in the Bible (Kernighan & Ritchie, The C Programming Language)

>From the above description we can read the following: the whole set A is used in
constructing hash table. Database tables are sets as well (as a matter of fact, set theory was the mathematical theory after which Codd & Date modeled their relational theory) which means that the whole table must be read to construct a hash table. Bigger the database table, bigger gets the hash table. If you have monstrous tables with 100M records, hash table can get rather substantial. In case of a dedicated connection, hash table is stored in PGA (shared connection stores it in LARGE_POOL) but if the table is really big, parts of the table are swapped to temporary extents in the temp tablespace. That means that for hash join, you'll not only be reading the two involved tables, but also the hash table. Add paging to adequately increase the address space and you'll see that the whole thing will slow down drastically. A corollary to what was said above is that you don't want the hash table to be swapped to disk. That, in turn, means that you dont want to hash really big tables. That is what sort/merge is for. The maximum size of an in-memory hash table is HASH_AREA_SIZE. For sorts oracle has decent statistics like the ones below.

SQL> select name, value from v$sysstat where name like '%sort%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
sorts (memory)                                                          846
sorts (disk)                                                              0
sorts (rows)                                                           2339


For hashing algorithm, no such statistics is available. The only ones that we get are the incomprehensible ones listed below:

SQL> select name, value from v$sysstat where name like '%hash%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
shared hash latch upgrades - no wait                                   2024
shared hash latch upgrades - wait                                         0
native hash arithmetic execute                                            0
native hash arithmetic fail                                               0


Cary might know some other way to monitor he efficiency of our hash setup in the database. I'm just a mere mortal so I have to use top and physical reads statistics in order to detect processes growing out of control. This is frequently caused by hashing.

On 2003.05.20 13:38 Ramon E. Estevez wrote:
> Tks,
>
> So it is only advisable when there's a small table involve in the query.
>
>
>
> Ramon E. Estevez
> restevez_at_blh.com.do
> 809-535-8994
>
>
>
> -----Original Message-----
> rgaffuri_at_cox.net
> Sent: Tuesday, May 20, 2003 11:47 AM
> To: Multiple recipients of list ORACLE-L
>
>
> hash always requires a full table scan. So you use hash's when you need
> to return large amounts of data from each and/or there are large
> differences in sizes of the two tables.
>
> you can set always_semi_join with alter session and from EXECUTE
> IMMEDIATE.
>
> chage it as needed.
> >
> > From: "Ramon E. Estevez" <restevez_at_blh.com.do>
> > Date: 2003/05/20 Tue AM 11:06:41 EDT
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Subject: Always_semi_join
> >
> > Hi List,
> >
> > I change the parameter ALWAYS_SEMI_JOIN to HASH in a DB and ran a
> > report and last forever, In another DB, without the parameter, ran in
> > 12 seconds. This was the only process that was
> > Affected with the parameter.
> >
> > Why a FTS if I am using a index in the where clause, will the DB
> > ALWAYS use that method.
> >
> > Kind of confused.
> >
> > TIA
> >
> > **---
> > BD WITH ALWAYS_SEMI_JOIN=STANDARD
> > **---
> > QUERY_PLAN
> > ----------------------------------------------------------------------
> > --
> > --
> > SORT ORDER BY
> > FILTER
> > NESTED LOOPS
> > NESTED LOOPS
> > TABLE ACCESS BY INDEX ROWID TPLA_ACCRUAL
> > INDEX RANGE SCAN CP01CAP_ACC
> > TABLE ACCESS BY INDEX ROWID TPLA_CUENTA
> > INDEX UNIQUE SCAN CP01PLA_PDA
> > TABLE ACCESS BY INDEX ROWID TCLI_PERSONA
> > INDEX UNIQUE SCAN CP01CLI_CLI
> > FILTER
> > INDEX RANGE SCAN CP01CAP_ACC
> >
> > 12 rows selected.
> >
> > ************************************************
> > **---
> > BD WITH ALWAYS_SEMI_JOIN=HASH
> > **---
> > QUERY_PLAN
> > ----------------------------------------------------------------------
> > --
> > ---
> > SORT ORDER BY
> > MERGE JOIN CARTESIAN
> > HASH JOIN
> > TABLE ACCESS BY LOCAL INDEX ROWID TPLA_ACCRUAL
> > INDEX RANGE SCAN CP01CAP_ACC
> > HASH JOIN
> > TABLE ACCESS FULL TPLA_CUENTA
> > TABLE ACCESS FULL TCLI_PERSONA
> > SORT JOIN
> > INDEX RANGE SCAN CP01CAP_ACC
> >
> > 10 rows selected.
> >
> >
> >
> > Ramon E. Estevez
> > restevez_at_blh.com.do
> > 809-535-8994
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Ramon E. Estevez
> > INET: restevez_at_blh.com.do
> >
> > 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).
> >
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> 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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Ramon E. Estevez
> INET: restevez_at_blh.com.do
>
> 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).
>

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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 Wed May 21 2003 - 00:21:41 CDT

Original text of this message

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