| query fast in one session slow in another [message #218191] |
Wed, 07 February 2007 03:31  |
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  |
skooman
Messages: 913
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
