Home » RDBMS Server » Performance Tuning » query fast in one session slow in another
query fast in one session slow in another [message #218191] Wed, 07 February 2007 03:31 Go to next message
henriec
Messages: 3
Registered: June 2006
Junior Member
I have this query. Its running very slow in a batch-job (3000+ secs per exec+fetch). When i logon to the same database with SQL-plus and execute the query by hand it returns witin 30milliSec.

There is no difference in speed between CBO en RBO.

When running, the query doesn't show up in long-ops. In session-wait theres only a db-file-sequential-read visible.

Can someone give me a hint where to look?

The query:

SELECT 1
FROM GPN, CLT
WHERE GPN.CLT_PSN_PERSOONSNUMMER = CLT.PSN_PERSOONSNUMMER
AND GPN.CLT_CLIENTNUMMER = CLT.CLIENTNUMMER
AND GPN.CLT_ISG_ID = CLT.ISG_ID
AND CLT.PSN_PERSOONSNUMMER = :B2
AND GPN.JAAR = :B1
AND GPN.CODE_AA IS NULL
AND GPN.PDT_NR IN (6, 7)
execution CBO:

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=1 Bytes=41)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'GPN'
           (Cost=54 Card=1 Bytes=25)

   2    1     NESTED LOOPS (Cost=54 Card=1 Bytes=41)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CLT' (Cost=
          5 Card=2 Bytes=32)

   4    3         INDEX (RANGE SCAN) OF 'THI_CLT_PSN_FK1' (NON-UNIQUE)
           (Cost=3 Card=2)

   5    2       BITMAP CONVERSION (TO ROWIDS)
   6    5         BITMAP AND
   7    6           BITMAP CONVERSION (FROM ROWIDS)
   8    7             INDEX (RANGE SCAN) OF 'THI_GPN_CLT_FK1' (NON-UNI
          QUE) (Cost=2 Card=1)

   9    6           BITMAP OR
  10    9             BITMAP CONVERSION (FROM ROWIDS)
  11   10               INDEX (RANGE SCAN) OF 'THI_GPN_PDT_FK1' (NON-U
          NIQUE) (Cost=2 Card=1)

  12    9             BITMAP CONVERSION (FROM ROWIDS)
  13   12               INDEX (RANGE SCAN) OF 'THI_GPN_PDT_FK1' (NON-U
          NIQUE) (Cost=2 Card=1)

Statistics
----------------------------------------------------------
         49  recursive calls
          0  db block gets
         17  consistent gets
          4  physical reads
          0  redo size
        214  bytes sent via SQL*Net to client
        328  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

execution RBO:

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'GPN'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CLT'
   4    3         INDEX (RANGE SCAN) OF 'THI_CLT_PSN_FK1' (NON-UNIQUE)
   5    2       INDEX (RANGE SCAN) OF 'THI_GPN_CLT_FK1' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        214  bytes sent via SQL*Net to client
        328  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed



[Updated on: Wed, 07 February 2007 03:33]

Report message to a moderator

Re: query fast in one session slow in another [message #218324 is a reply to message #218191] Wed, 07 February 2007 14:46 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Could it be that while running it in SQL*Plus, you enter values for the :B1 and :b2?
If so, than that's effecting the execution plan, because in SQL*Plus, the optimizer has a more accurate knowledge of the number of rows which have this value. When using variables, the optimizer doesn't know (except by bind variable peeking - but this might not be accurate next time the query runs), so it will use the default expected result of 5%, which might of course be far beside the truth and result in a completely different plan.

Thus, to compare the two ways of running the query "fairly" either use values or bind variables for both tests.

And BTW what do CBO and RBO have to do with it? Don't the batch and SQL*Plus test both run in the same optimizer mode?

[Updated on: Wed, 07 February 2007 14:49]

Report message to a moderator

Previous Topic: data guard query
Next Topic: Database Triggers performance issues
Goto Forum:
  


Current Time: Sat Dec 03 13:52:45 CST 2016

Total time taken to generate the page: 0.07743 seconds