on 01-16-2012 7:36 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
User | Count |
---|---|
78 | |
9 | |
9 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.