Expensive SQL on swequeue table

[Former Member]

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

[Former Member · Accepted solution]

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.

[fidel_vales]

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

[Former Member]

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

[Former Member]

Hi Michael/Fidel:

Yes, you are right that the statement itself is not the problem. I'm going by the cost (13). The real issue is that this statement is triggered way too often by a function-module called by the job SXMS_REFRESH_ADAPTER_STATUS. I haven't been able to establish a where-used list back from the FM to the program, but I see it clearly by correlating PID of the job and Client ID in ST04. The repeated executions seem to set an alarm off as expensive statement in EWA--information from the table is small but it is accessed repeatedly.

The job SXMS_REFRESH_ADAPTER_STATUS runs every hour and the runtime has been between 0.5 to 1 hr. The job updates XI message status based on message acknowledgements and workflow-specific events (stored in SWEQUEUE). I have noticed a lot of old XI messages (stored in another set of tables) that need to be cleared off from the database, which might be causing the table to be looked up so often. While I am cleaning them up from Dev, QA and Prod, I am looking to see if anything else can be done in addition.

SWEQUEUE~C only indexes OBJTYPE, so the only column accessed is OBJTYPE.

There is no index on OBJKEY.

Here is ST05 analysis:

Rows per      Distinct              1           11          101        1,001       10,001      100,001    more than
   generic key     values             - 10        - 100      - 1,000     - 10,000    - 100,000   -1,000,000    1,000,000

      Key fields               Number of areas that are specified by the generic key and contain the given number of rows
  1   CLIENT                1            0            0            0            1
  2   OBJTYPE               6            1            4            0            1
  3   OBJKEY            1,132        1,132
  4   STATUS            1,141        1,141

Oracle upgrade to 11g is being planned and statistics are up-to-date.

[fidel_vales]

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