Running queries

From: HighLander <st860029_at_educ.di.unito.it>
Date: Fri, 15 Jan 1999 12:56:54 +0100
Message-ID: <Pine.SOL.3.95.990115125525.28264C-100000@speedy>

I've got some problems running some heavy queries that imply 7 tables used with anti-join. The tables aren't big at all, no more than 30000 rows.

The problems raise up when the parameter ALWAYS_ANTI_JOIN is set to HASH in the init.ora.

We can divide the queries in 2 groups:

• employee
• branch manager

For each query of the first group there's one similar in the second group; They are different in the WHERE clause ( the manager can see data for all the employees while the single employee can see only his own data ).

If I choose an employee ( MKD_NUCLEO = 20101 ) the query works fine, while the manager's one doesn't.

I'm waiting for an answer from the Oracle Support but they seem to have no good ideas about my case.

I hope that someone of you will be able to help me in anyway, even only a suggestion. I've tried almost everything for a month with other Oracle consultants but we came to nothing.

Here follow the init.ora, the query, some info about the env and my e-mail address.

############# init.ora

db_name = TAIF_DB
db_files = 20
control_files = (C:\Bav\Datafiles\ctl1.ora, C:\Bav\Datafiles\ctl2.ora)

rollback_segments = (r0001, r0002, r0003)

compatible = 7.3.3.0.0

db_block_buffers = 3000
shared_pool_size = 12000000

db_file_multiblock_read_count = 32

sort_area_size = 3145728
sort_area_retained_size = 3145728

hash_area_size = 3145728                                            
hash_multiblock_io_count = 8                                       

always_anti_join = HASH

log_checkpoint_interval = 100000

processes = 50
dml_locks = 100
sessions = 50
open_cursors = 200

log_buffer = 65536

sequence_cache_entries = 10
sequence_cache_hash_buckets = 10

timed_statistics = true       # if you want timed statistics
max_dump_file_size = 20480      

# define directories to store trace and alert files background_dump_dest=C:\Bav\Datafiles
user_dump_dest=C:\Bav\Datafiles

db_block_size = 4096

remote_login_passwordfile = shared

############# query

SELECT SMTOCLI1.MKD_CODANA, SMTOCLI1.MKD_INTEST, SMTSKPRS1.MDC_DSC_PRIMO,        SMTSKSEG1.DES_CLASSE, SMTSKSIT1.DES_SITUAZ, SMTSKRET1.MDC_DESGERRETE

FROM SSV_OWN.SMTOCLI SMTOCLI1, SSV_OWN.SMTSKPRS SMTSKPRS1, 
     SSV_OWN.SMTSKNUC SMTSKNUC1, SSV_OWN.SMTSKSEG SMTSKSEG1, 
     SSV_OWN.SMTSKSIT SMTSKSIT1, SSV_OWN.SMTSKRET SMTSKRET1
WHERE ( ( SMTSKPRS1.MDC_ANPRSE = SMTOCLI1.MDC_ANPRSE ) AND 
        ( SMTSKNUC1.MKD_NUCLEO = SMTOCLI1.MKD_NUCLEO ) AND 
        ( SMTSKSEG1.SM_FAETN = SMTOCLI1.SMC_FAETN AND 
          SMTSKSEG1.MKD_SEGCLI = SMTOCLI1.MKC_SEGCLI ) AND 
        ( SMTSKSIT1.COD_STATUS = SMTOCLI1.MDC_ANSIT ) AND 
        ( SMTSKRET1.MDC_ANRETE = SMTOCLI1.MDC_ANRETE ) ) AND 
      ( SMTOCLI1.MKD_CODANA NOT IN 
      (( SELECT SMTOCLI.MKD_CODANA
           FROM SSV_OWN.SMTOCLI SMTOCLI, SSV_OWN.SMTEVCCF SMTEVCCF, 
                SSV_OWN.SMTSKPRS SMTSKPRS, SSV_OWN.SMTSKNUC SMTSKNUC, 
                SSV_OWN.SMTSKSEG SMTSKSEG, SSV_OWN.SMTSKSIT SMTSKSIT, 
                SSV_OWN.SMTSKRET SMTSKRET
          WHERE ( ( SMTOCLI.MKD_CODANA = SMTEVCCF.CDANA ) AND 


                  ( SMTSKPRS.MDC_ANPRSE = SMTOCLI.MDC_ANPRSE ) AND 


                  ( SMTSKNUC.MKD_NUCLEO = SMTOCLI.MKD_NUCLEO ) AND 


                  ( SMTSKSEG.SM_FAETN = SMTOCLI.SMC_FAETN AND 


                    SMTSKSEG.MKD_SEGCLI = SMTOCLI.MKC_SEGCLI ) AND 


                  ( SMTSKSIT.COD_STATUS = SMTOCLI.MDC_ANSIT ) AND 


                  ( SMTSKRET.MDC_ANRETE = SMTOCLI.MDC_ANRETE ) ) AND 


                ( SMTSKNUC.MKD_NUCLEO = '20101' ) )) ) AND 
      ( SMTSKNUC1.MKD_NUCLEO = '20101' )

ORDER BY SMTOCLI1.MKD_INTEST ASC ############# environment

WINDOWS NT 4.0 SERVER
ORACLE 7.3.3.6 ############# e-mail

pdesalvo_at_hotmail.com

Thanks a lot.

Paolo Received on Fri Jan 15 1999 - 05:56:54 CST