Skip to Content
avatar image
Former Member

Expensive SQL statements in PI 7.3 For The Object SWWWIHEAD

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jan 05, 2015 at 11:20 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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