We have an expensive SQL statement reported in EWA. The sql is
/*+ FIRST_ROWS (2147483647) */
"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.
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