Skip to Content
avatar image
Former Member

Expensive SQL on swequeue table

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 16, 2012 at 07:48 AM

    Solution:

    Delete old events via report RSWEQDELETE. This will cleanup SWEQUEUE to contain fewer entries and the number of reads by REFRESH ADAPTER jobs will come down.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 16, 2012 at 09:25 AM

    I am not having the impression this is a real problem. SWEQUEUE~C is the index for OBJTYPE, how many distinct OBJTYPE do you have? How many rows for the value you are querying (the job is probably querying each one of them seperatly)?

    How long does the job SXMS_REFRESH_ADAPTER_STATUS run?

    Cheers Michael

    Edited by: mho on Jan 16, 2012 10:26 AM

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      if the problem is the number of executions, ther eis not much to be done at Oracle level.

      I'd create an index to minimize the time at DB level with the 4 fields of the where condition:

      CLIENT

      STATUS

      OBJTYPE

      OBJKEY

      (or modify the curretnly used index SWEQUEUE~C) you can use the clause COMPRESS 3 to minize the index size

  • Jan 16, 2012 at 06:02 PM

    Hi,

    I see several "not" normal things here.

    (note, currently I do not have in front of me the definition of the indexes for this table, so it would be very good if you put the definition and statistics of all of them to double check)

    Michael mentions that the index SWEQUEUE~C has OBJTYPE and I assume it also have MANDT (please check)

    It access this index, but only uses one of the columns provided and I assume it is MANDT:

    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

    therefore, it reads the complete index and then the complete table, which is bad.

    it sounds like a bug (I know that there are several bugs related to FIRST_ROWS, may be this is one of them)

    a quick "workaround" would be to have an index with all fields from the where clause, that way nly the index is read. Probably can be compressed as I tink STATUS is not very selective (same as MANDT) and perhaps OBJTYPE.

    So

    1.- check if you have an index already with MANDT, OBJTYPE, STATUS and OBJKEY (if so, why is not used)

    2.- Check what version of Orcle and Optimizer merge fix do you have (is it current?)

    3.- Check the table/index statistics to see if it make sense this or other index

    Add comment
    10|10000 characters needed characters exceeded