Expensive SQL statements in PI 7.3 For The Object SWWWIHEAD

[Former Member]

Hi All,

I am receiving high expensive SQL statements alerts for the object SWWWIHEAD in EWA report continuously in one of our PI 7.3 box,

I have  checked the SQL statement which is  using more I/0 (40 %) and CPU (2%), below is the select statement which it's using. Can you please let me know me know the possible root cause for  the issue and also any issue in the below  query ? I have gone through few notes 881187 & 1024505 but those are not helping to tune this performance issue,  as per the note 881187  the indexes for the table already exist in our system.

Please let me know if you need more logs or information on the same.

SQL Statemet:

SELECT

"WI_ID"

FROM

"SWWWIHEAD"

WHERE

"CLIENT"= :A0 AND "WI_TYPE"= :A1 AND "WI_STAT" IN ( :A2, :A3) AND "WF_TYPE"= :A4

Execution Plan From: V$SQL_PLAN sql_id: dqsaj4vn26r31

SELECT STATEMENT Estimated Costs= 19.803 Estimated Rows= 0

Optimizer: ALL_ROWS

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID SWWWIHEAD

Estimated Costs= 19.803 Estimated Rows= 288.498

Filter predicates: "WF_TYPE"=:A4

Estim. Bytes: 9.808.932

Estim. CPU-Costs = 214.099.156 Estim. IO-Costs = 19.768

1 INDEX RANGE SCAN SWWWIHEAD~C

Estimated Costs= 529 Estimated Rows= 288.498

Access predicates:

"CLIENT"=:A0 AND "WI_TYPE"=:A1 AND (("WI_STAT"=:A2 OR "WI_STAT"=:A3))

Search Columns: 3

Estim. CPU-Costs = 15.602.140 Estim. IO-Costs = 527

[Former Member · Accepted solution]

Hi Pavan,

The notes you indicated seems pretty old.

Did you have a look at this one?

2019259 - SWPC: Poor performance

I am not sure what is your issue. I would suggest to run some traces on the program indicated by your EWA.

You can also try to look for correction related to that program or the table.

You should use STAD (with table activated in ST03) or ST12.

Hope it helps.

Regards,

Adel

[Former Member · Accepted solution]

Hi Adel,

Thanks for your inputs, let me clear you on what exactly my issue, we are reeving weekly EWA report  for PI box, in that report we are reeving the continuous high expensive SQL statements, I have checked all listed objects and queries which I observed only SWWIHEAD objet we are getting more I/O, below are statements which EWA pointing, can you please help me on the any issues with select statements in the below query for SWWWIHEAD,  and one more thing which you replied I checked that note I think its intended for  the field WI_CHCKWI in the WHERE condition, but in my case the SQL select statement is using the filed  WI_ID for select statement, can you please check the below query for SWWWIHEAD and can you suggest still the above note is applicable and any issue with statements.

High Expensive SQL statements:

Load From Expensive Statements
Rating	CPU Load [%]	I/O Load [%]	Elapsed Time [%]
	45,72	47,28	42,00

The table above shows the cumulative load of the top statements from cache based on elapsed database time. If the database was inactive for more than one day before the analysis was performed, the information provided may not be entirely accurate.

Note: The rating is unrated since the system has not yet been very active. The overall section rating is linked to the above table rating; the ratings are described in SAP Note 551646.

If the table rating is RED, there are SQL statements that cause a high percentage of the overall load on your SAP system.
If the table rating is YELLOW, there are SQL statements that cause a considerable percentage of the overall load on your SAP system.
If the table rating is GREEN, your system SQL statement cache contains no significant problems.
If the table rating is UNRATED, the cache utilization, system load (dialog steps or total reads) was too low, or some analysis data was unavailable.

The following table lists the load of each SQL statement individually. The load of the statement is evaluated against the total load since database startup. If an object name in this table contains the character "/", it may indicate a join. If an object is not in the ABAP Dictionary (transaction SE12) with the object name listed, check for each part of the join (items separated by "/").

1. 18.1 Cache Analysis On 05.01.2015

Expensive Statements Overview
Object Name	CPU Load [%]	I/O Load [%]	Elapsed Time [%]	Total Executions	Records Processed
SWWWIHEAD	1,00	41,16	10,00	32	23.906.391
SWWWIHEAD	9,20	0,11	7,00	32.612.651 32.612.651
SWW_WI2OBJ	11,68	0,16	5,00	32.651.201 64.976.269
SWFRXIHDR	9,21	0,07	4,00	32.651.201 32.651.201
SWWWIHEAD	5,84	4,88	3,00	4.781.262	0
SWWWIHEAD	8,60	0,02	3,00	30.517.271 30.517.265
V$SYSTEM_EVENT	0,00	0,00	3,00	46.224 5.032.405
V$ARCHIVED_LOG	0,00	0,00	3,00	4.625 4.625
SXMSCLUR	0,19	0,88	2,00	487.548 520.644
V$ARCHIVED_LOG	0,00	0,00	2,00	4.625 4.625 18.1.1 Access on SWWWIHEAD

Load Statistics Total
Analysis Date	Total Executions	Total Physical Reads	Elapsed Time (ms)	Total Buffer Gets	Records Processed
05.01.2015	32	9.814.257 4.741.230 14.159.474 23.906.391

SELECT
"WI_ID"

FROM

"SWWWIHEAD"

WHERE

"CLIENT"= :A0 AND "WI_TYPE"= :A1 AND "WI_STAT" IN ( :A2, :A3) AND "WF_TYPE"= :A4

Execution Plan From: V$SQL_PLAN sql_id: dqsaj4vn26r31

SELECT STATEMENT Estimated Costs= 19.803 Estimated Rows= 0

Optimizer: ALL_ROWS

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID SWWWIHEAD

Estimated Costs= 19.803 Estimated Rows= 288.498

Filter predicates: "WF_TYPE"=:A4

1. Estim. Bytes: 9.808.932
2. Estim. CPU-Costs = 214.099.156 Estim. IO-Costs = 19.768

1 INDEX RANGE SCAN SWWWIHEAD~C

Estimated Costs= 529 Estimated Rows= 288.498

Access predicates:

"CLIENT"=:A0 AND "WI_TYPE"=:A1 AND (("WI_STAT"=:A2 OR "WI_STAT"=:A3))

Search Columns: 3

1. Estim. CPU-Costs = 15.602.140 Estim. IO-Costs = 527

Program Name	Line	Created By	Last Changed By	Last Changed On
CL_SWF_RUN_CCMS_WF_CONSISTENCYCM005	42	SAP	SAP	11.09.2006

000040
000041 *---- get process instances in final state (including subflows)
000042 SELECT wi_id FROM swwwihead CLIENT SPECIFIED
000043 INTO CORRESPONDING FIELDS OF TABLE lt_processes_final
000044 WHERE client = m_client
000045 AND wi_type = swfco_wi_flow
000046 AND wi_stat IN lr_wi_stat
000047 AND wf_type = c_wf_type_bpm.

1. 18.1.2 Access on SWWWIHEAD

Load Statistics Total
Analysis Date	Total Executions	Total Physical Reads	Elapsed Time (ms)	Total Buffer Gets	Records Processed
05.01.2015 32.612.651 25.096 3.384.604 130.450.604 32.612.651

SELECT

FROM "SWWWIHEAD"

WHERE

"CLIENT"= :A0 AND "WI_ID"= :A1 AND "WI_TYPE" IN ( :A2, :A3, :A4, :A5, :A6, :A7, :A8, :A9, :A10, :A11) AND "WI_STAT" IN ( :A12, :A13, :A14, :A15, :A16, :A17, :A18, :A19, :A20, :A21, :A22)

Execution Plan From: V$SQL_PLAN sql_id: 5m9c7vwbg047r

SELECT STATEMENT Estimated Costs= 1 Estimated Rows= 0

Optimizer: ALL_ROWS

2 TABLE ACCESS BY INDEX ROWID SWWWIHEAD

Estimated Costs= 1 Estimated Rows= 1

Filter predicates:

(INTERNAL_FUNCTION("WI_TYPE") AND INTERNAL_FUNCTION("WI_STAT"))

1. Estim. Bytes: 435
2. Estim. CPU-Costs = 4.852 Estim. IO-Costs = 1

1 INDEX UNIQUE SCAN SWWWIHEAD~0

Estimated Costs= 0 Estimated Rows= 1

Access predicates: "CLIENT"=:A0 AND "WI_ID"=:A1

Search Columns: 2

1. Estim. CPU-Costs = 3.059 Estim. IO-Costs = 0

Program Name	Line	Created By	Last Changed By	Last Changed On
CL_SWF_RUN_P_HEAD=============CM003	69	SAP	SAP	18.05.2004

000069 SELECT * FROM (tablename) INTO TABLE lt_workitems_header
000070 where
000071 wi_id in lt_rangetab_for_id[] and
000072 wi_type in lrt_type and
000073 wi_stat in lrt_state and
000074 wi_cd in me->if_swf_rep_header_selection~rangetab_for_creation_date[] and
000075 wi_ct in me->if_swf_rep_header_selection~rangetab_for_creation_time[] and
000076 wi_rh_task in me->if_swf_rep_header_selection~rangetab_for_task[] and
000077 wi_prio in me->if_swf_rep_header_selection~rangetab_for_priority[] and
000078 wi_dh_stat in me->if_swf_rep_header_selection~rangetab_for_dhsta[] and
000079 crea_tmp in me->if_swf_rep_header_selection~rangetab_for_creation_ts[].

1. 18.1.3 Access on SWW_WI2OBJ

Load Statistics Total
Analysis Date	Total Executions	Total Physical Reads	Elapsed Time (ms)	Total Buffer Gets	Records Processed
05.01.2015 32.651.201 37.204 2.517.877 165.697.985 64.976.269

SELECT
"WI_ID","TOP_WI_ID","WI_RH_TASK","WI_RELEASE","CREA_TMP","CATID","INSTID","TYPEID","WI_RELTYPE"

FROM

"SWW_WI2OBJ"

WHERE

"CLIENT"= :A0 AND "CATID"= :A1 AND "INSTID"= :A2 AND "TYPEID"= :A3

Execution Plan From: V$SQL_PLAN sql_id: 0hm5dv691wsdk

SELECT STATEMENT Estimated Costs= 2 Estimated Rows= 0

Optimizer: ALL_ROWS

2 TABLE ACCESS BY INDEX ROWID SWW_WI2OBJ

Estimated Costs= 1 Estimated Rows= 2

1. Estim. Bytes: 234
2. Estim. CPU-Costs = 7.341 Estim. IO-Costs = 1

1 INDEX RANGE SCAN SWW_WI2OBJ~001

Estimated Costs= 1 Estimated Rows= 2

Access predicates:

"CLIENT"=:A0 AND "CATID"=:A1 AND "TYPEID"=:A3 AND "INSTID"=:A2

Search Columns: 4

1. Estim. CPU-Costs = 5.777 Estim. IO-Costs = 1

Program Name	Line	Created By	Last Changed By	Last Changed On
CL_SWF_REP_ITEM2OBJECT========CM00O	53	SAP	SAP	10.11.2012

000053 select * from sww_wi2obj appending corresponding fields of
000054 table m_swwwi2obj
000055 where crea_tmp in lt_rangetab_for_time
000056 and catid eq i_object-catid
000057 and instid in lr_instid
000058 and typeid eq i_object-typeid
000059 and wi_reltype in lr_reltype
000060 and wi_rh_task in im_rangetab_for_tasks.

1. 18.1.4 Access on SWFRXIHDR

Load Statistics Total
Analysis Date	Total Executions	Total Physical Reads	Elapsed Time (ms)	Total Buffer Gets	Records Processed
05.01.2015 32.651.201 17.855 1.863.884 130.605.015 32.651.201

SELECT

FROM "SWFRXIHDR"

WHERE

"CLIENT"= :A0 AND "GUID"= :A1

Execution Plan From: V$SQL_PLAN sql_id: 6cnqxjunv5nk6

SELECT STATEMENT Estimated Costs= 1 Estimated Rows= 0

Optimizer: ALL_ROWS

2 TABLE ACCESS BY INDEX ROWID SWFRXIHDR

Estimated Costs= 1 Estimated Rows= 1

1. Estim. Bytes: 130
2. Estim. CPU-Costs = 4.561 Estim. IO-Costs = 1

1 INDEX UNIQUE SCAN SWFRXIHDR~0

Estimated Costs= 0 Estimated Rows= 1

Access predicates: "CLIENT"=:A0 AND "GUID"=:A1

Search Columns: 2

1. Estim. CPU-Costs = 3.059 Estim. IO-Costs = 0

Program Name	Line	Created By	Last Changed By	Last Changed On
CL_SWF_XI_INSTANCE============CM00S	12	SAP	SAP	30.10.2007

000011 *- select
000012 SELECT SINGLE * FROM swfrxihdr
000013 INTO l_xi_header
000014 WHERE guid = im_message_guid.

1. 18.1.5 Access on SWWWIHEAD

Load Statistics Total
Analysis Date	Total Executions	Total Physical Reads	Elapsed Time (ms)	Total Buffer Gets	Records Processed
05.01.2015 4.781.262 1.163.620 1.677.997 82.896.552	0

SELECT
"WI_ID","WI_CHCKWI"

FROM

"SWWWIHEAD"

WHERE

"CLIENT"= :A0AND "WI_CHCKWI" IN ( :A1, :A2, :A3, :A4, :A5) AND "WI_STAT" IN ( :A6, :A7, :A8, :A9, :A10, :A11, :A12)

Execution Plan From: V$SQL_PLAN sql_id: 13knr36sgyax0

SELECT STATEMENT Estimated Costs= 8 Estimated Rows= 0

Optimizer: ALL_ROWS

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID SWWWIHEAD

Estimated Costs= 8 Estimated Rows= 303

1. Estim. Bytes: 12.120
2. Estim. CPU-Costs = 85.034 Estim. IO-Costs = 8

1 INDEX RANGE SCAN SWWWIHEAD~B

Estimated Costs= 2 Estimated Rows= 85

Filter predicates:

("WI_STAT"=:A6 OR "WI_STAT"=:A7 OR "WI_STAT"=:A8 OR "WI_STAT"=:A9 OR "WI_ST

AT"=:A10 OR "WI_STAT"=:A11 OR "WI_STAT"=:A12)

Access predicates:

"CLIENT"=:A0 AND (("WI_CHCKWI"=:A1 OR "WI_CHCKWI"=:A2 OR "WI_CHCKWI"=:A3 OR

"WI_CHCKWI"=:A4 OR "WI_CHCKWI"=:A5))

Search Columns: 2

1. Estim. CPU-Costs = 31.846 Estim. IO-Costs = 2

Program Name	Line	Created By	Last Changed By	Last Changed On
CL_SWF_RUN_CCMS_WF_CONSISTENCYCM005	68	SAP	SAP	11.09.2006

000067
000068 SELECT wi_id wi_chckwi FROM swwwihead CLIENT SPECIFIED
000069 INTO CORRESPONDING FIELDS OF TABLE lt_workitems_not_final
000070 FOR ALL ENTRIES IN lt_processes_final
000071 WHERE client = m_client
000072 AND wi_chckwi = lt_processes_final-wi_id
000073 AND wi_stat IN lr_wi_stat.

1. 18.1.6 Access on SWWWIHEAD

Load Statistics Total
Analysis Date	Total Executions	Total Physical Reads	Elapsed Time (ms)	Total Buffer Gets	Records Processed
05.01.2015 30.517.271 4.579 1.659.409 122.069.044 30.517.265

SELECT

FROM "SWWWIHEAD"

WHERE

"CLIENT"= :A0 AND "WI_ID"= :A1

Execution Plan From: V$SQL_PLAN sql_id: 3g15sr2ama68f

SELECT STATEMENT Estimated Costs= 1 Estimated Rows= 0

Optimizer: ALL_ROWS

2 TABLE ACCESS BY INDEX ROWID SWWWIHEAD

Estimated Costs= 1 Estimated Rows= 1

1. Estim. Bytes: 435
2. Estim. CPU-Costs = 4.749 Estim. IO-Costs = 1

1 INDEX UNIQUE SCAN SWWWIHEAD~0

Estimated Costs= 0 Estimated Rows= 1

Access predicates: "CLIENT"=:A0 AND "WI_ID"=:A1

Search Columns: 2

1. Estim. CPU-Costs = 3.059 Estim. IO-Costs = 0

Program Name	Line	Created By	Last Changed By	Last Changed On
LSWW_SRVU02	33	SAP	SAP	18.05.2004

000033 SELECT SINGLE * INTO wi_header FROM swwwihead
000034 WHERE wi_id = wi_id.

Thanks in Advance..

[Former Member · Accepted solution]

Hi Pavan,

It is not an easy job to find out where is coming a performance issue.

In 18.1 the first occurence of SWWWIHEAD has very high I/O but it has been executed only 32 times.

It may be a job. Very probably you have too much entries on that table which is not normal or there is a bug in the program and it is now using the best path to access the data.

How Many entries you have in this table?

Did you check in Se11 that SWWWIHEAD has an index with CLIENT WI_TYPE WI_STAT WF_TYPE?

If not try to use DB05 to see what field should be enough to have a good index.

Can you confirm your DB and PI version?

Could you confirm the different indexes on that table?

A screenshot will be better.

Specially Index C as per note  881187.

Note   1024505 is talking about report RSWWCOND (background job SWWCOND).

Where you able to drill down the issue to that program?

The note is pretty old.

Are you familiar with STAD?

Can you run it just after the job ends?

Let s say the job finished at 11:06.

You run STAD on the user and program at 11:00 for 10mns

Regards,

Adel