Home » RDBMS Server » Performance Tuning » The same sql runs very slowly in jboss-cmp,very quickly in native jdbc.
The same sql runs very slowly in jboss-cmp,very quickly in native jdbc. [message #121823] Wed, 01 June 2005 04:09 Go to next message
peixubin
Messages: 2
Registered: May 2005
Junior Member
hi all.
I have this query:

SELECT t0_o.YSID
FROM
YD.HS_DFLSZ t0_o WHERE (t0_o.SFRQ BETWEEN :1 AND :2 AND t0_o.DFZT = 20 AND
t0_o.ZFFS = 100 AND t0_o.YHDM = :3)

the table HS_DFLSZ structure:
YSID varchar2(20) not null ,primary key,
sfrq date ,local index,
rq varchar2(6) ,partitioned field.



If it runs in jboss-cmp,the oracle's trace file show:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 298 0.00 0.00 165440 167911 357 2978
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 300 0.00 0.00 165440 167911 357 2978

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 127

Rows Row Source Operation
------- ---------------------------------------------------
2978 PARTITION RANGE ALL PARTITION: START=1 STOP=85
2978 TABLE ACCESS FULL HS_DFLSZ PARTITION: START=1 STOP=85



If it runs in native jdbc,the oracle's trace file show:


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 171 0.00 0.00 0 764 0 1707
total 173 0.00 0.00 0 764 0 1707

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 127

Rows Row Source Operation
------- ---------------------------------------------------
1707 PARTITION RANGE ALL PARTITION: START=1 STOP=85
1707 TABLE ACCESS BY LOCAL INDEX ROWID HS_DFLSZ PARTITION: START=1 STOP=85
10968 INDEX RANGE SCAN PARTITION: START=1 STOP=85 (object id 362173)

why the execution plans is different?








Re: The same sql runs very slowly in jboss-cmp,very quickly in native jdbc. [message #122109 is a reply to message #121823] Thu, 02 June 2005 21:01 Go to previous message
peixubin
Messages: 2
Registered: May 2005
Junior Member
up
Previous Topic: Oracle Server Information
Next Topic: Index Partition rebuild Query
Goto Forum:
  


Current Time: Mon Mar 18 22:17:20 CDT 2024