Skip to Content
0
Former Member
Jan 16, 2012 at 07:36 AM

Expensive SQL on swequeue table

182 Views

We have an expensive SQL statement reported in EWA. The sql is

SELECT

/*+ FIRST_ROWS (2147483647) */

COUNT(*)

FROM

"SWEQUEUE"

WHERE

"CLIENT" = :A0 AND "OBJTYPE" = :A1 AND "OBJKEY" = :A2 AND

"STATUS" = :A3 AND ROWNUM <= :A4&

originating from LSWEQBROWSERU02 program with the following program code:

000115 SELECT COUNT( * ) FROM swequeue

000116 UP TO max_rows ROWS

000117 INTO counter

000118 WHERE evt_guid IN lt_evt_guid_sel "it_evt_guid

000119 AND event_id IN it_event_id

000120 AND tstamp IN it_timestamp

000121 AND event IN it_events

000122 AND objtype IN it_objtypes

000123 AND objkey IN it_objkey

000124 AND rectype IN it_rectypes

000125 AND retried IN it_retried

000126 AND crea_date IN it_crea_date

000127 AND crea_time IN it_crea_time

000128 AND status IN lt_status

000129 AND deliv_stmp IN it_deliver_tstmp.

The table swequeue has about 4K entries. Is the hint /*+ FIRST_ROWS (2147483647) */ causing SQL to be expensive.

EWA says it marked the SQL as expensive because the SQL has "Many executions - few records per execution". The SQL is triggered indirectly from an XI job: SXMS_REFRESH_ADAPTER_STATUS, which is a standard job that has to run.

Execution Plan:

SELECT STATEMENT ( Estimated Costs = 13 , Estimated #Rows = 1 )

4 SORT AGGREGATE

3 COUNT STOPKEY
Filter Predicates

2 TABLE ACCESS BY INDEX ROWID SWEQUEUE
( Estim. Costs = 12 , Estim. #Rows = 1 )
Estim. CPU-Costs = 170,768 Estim. IO-Costs = 12
Filter Predicates

1 INDEX RANGE SCAN SWEQUEUE~C
( Estim. Costs = 1 , Estim. #Rows = 608 )
Search Columns: 1
Estim. CPU-Costs = 33,036 Estim. IO-Costs = 1
Access Predicates