Select statement execution takes longer time for First time.

[Former Member]

Hi Experts,

I am facing the following issue. I am using one select statement to retrieve all the contracts from the table CACS_CTRTBU according to FOR ALL ENTRIES restriction.

if p_lt_zcacs[] is not initial.

SELECT

appl ctrtbu_id version gpart

busi_begin busi_end tech_begin tech_end

flg_cancel_obj flg_cancel_vers int_title

FROM cacs_ctrtbu INTO TABLE lt_cacs FOR ALL ENTRIES IN p_lt_zcacs

WHERE

appl EQ gv_appl

AND ctrtbu_id EQ p_lt_zcacs-ctrtbu_id

AND ( flg_cancel_vers EQ '' OR version EQ '000000' )

AND flg_cancel_obj EQ ''

AND busi_begin LE p_busbegin

AND busi_end GT p_busbegin.

endif.

The WHERE condition is in order with the available Index. The index has APPL,CTRTBU_ID,FLG_CANCEL_VERS and FLG_CANCEL_OBJ.

The technical settings of table CACS_CTRTBU says that the "Buffering is not allowed"

Now the problem is , for the first time execution of this select statement, with 1.5 lakh entries in P_LT_ZCACS table, the select statement takes 3 minutes.

If I execute this select statement again, in another run with Exactly the same parameter values and number of entries in P_LT_ZCACS ( i.e 1.5 lakh entries), it gets executed in 3-4 seconds.

What can be the issue in this case? Why first execution takes longer time?.. Or is there any way to modify the Select statemnt to get better performance.

Thanks in advance

Sreejith A P

[debasissahoo]

Sree,

"Buffering is not allowed" controls the buffering at application level.

But you are getting the difference in time because the data you are fetching through the select statement are being put in the oracle/ database buffer cache. Any data operation at database level is first fetched to the buffer cache and then processed.

So the first time you fetch it'll take time as the data is not there in the buffer cache. but next time onwards, it may take very less time, as most chances are there to get these data in the cache.

But please note that, you should try every possible option to tune the select statement or find an better alternative to tackle the heavy search. Because this may have performance impact on the system. as fetching heavy number of data into cache means other cached data may have to thrown out of the database buffer cache.

Thanks,

Debasis.

[markus_doehr2]

> Now the problem is , for the first time execution of this select statement, with 1.5 lakh entries in P_LT_ZCACS table, the select statement takes 3 minutes.

>

> If I execute this select statement again, in another run with Exactly the same parameter values and number of entries in P_LT_ZCACS ( i.e 1.5 lakh entries), it gets executed in 3-4 seconds.

This is normal behaviour.

The first time you execute the statement the database needs to read all the data into the database cache/buffer which takes time. The second time you execute that statement, the data is already in the cache of the database and hence the DB doesn't need to read anything from disk but all from memory - which is up to 100 times faster than disk I/O.

Tuning SQL statements is database specific. You'd need to do an SQL trace and check which execution plan is used.

Markus