Lot of I/O Wait Time

[prateek47807]

Hi All,

Below sql is a db file sequential read and read by other session with sequential I/O Wait of 100%(oracle 10g db). This is sql is executed through an application in Peoplesoft by many concurrent users working on the system in day.

I have queried the V$ACTIVE_SESSION_HISTORY,V$EVENT_NAME, V$SQLAREA to find that the top 10 out of 25 queries which are of maximum wait time is the below query.

I have inclcued the explain plan

SELECT 'X'
FROM ps_installation
WHERE EXISTS (
SELECT 'X'
FROM ps_combo_data_tbl
WHERE setid = 'TBA'
AND process_group = 'FAC'
AND combination IN ('PNV', 'PPT', 'PEQ')
AND valid_code = 'R'
AND TO_DATE ('2007-08-04', 'YYYY-MM-DD') BETWEEN effdt_from
AND effdt_to
AND fund_code = '01');


Explain_plan.txt

I looked at the v$sysstat and v$sys_time_model table to get the cpu time and compared it with results of db file sequential read and db file scattered read of v$active_session_history and found cpu just greater that wait time

I am not sure if my analysis is correct. could you please suggest as to what can be done?


Your responses are highly appreciated.

[BV1963]

If I remember correctly PS_INSTALLATION has just one row , how many rows are in the other table?

[prateek47807]

ps_combo_data_tbl has no rows for the above said condition.

[mike9]

could you please describe the index used in the explain plan

[mike9]

how many distinct values do you have for the following columns:
setid
process_group
combination
valid_code
fund_code

[malay_biswal]

"read by other session" is similar to buffer busy waits. THis is I/O related problem, means if your db file sequential/scattered read are high this could indirectly contribute to the above mentioned wait.

You can try adding freelists to the table and then check.

[prateek47807]

Mike,

Below are the indexes on the table and is using INDEX PS_COMBO_DATA_TBL.

CREATE UNIQUE INDEX PS_COMBO_DATA_TBL ON PS_COMBO_DATA_TBL (SETID,
PROCESS_GROUP,
COMBINATION,
ACCOUNT,
ALTACCT,
DEPTID,
OPERATING_UNIT,
PRODUCT,
FUND_CODE,
AFFILIATE,
AFFILIATE_INTRA1,
AFFILIATE_INTRA2,
CHARTFIELD1,
CHARTFIELD3,
PROJECT_ID,
BOOK_CODE,
CURRENCY_CD,
STATISTICS_CODE) TABLESPACE PSINDEX STORAGE (INITIAL 3863552 NEXT
386355 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10
/
CREATE INDEX PSACOMBO_DATA_TBL ON PS_COMBO_DATA_TBL (SETID,
PROCESS_GROUP,
COMBINATION,
VALID_CODE,
EFFDT_FROM,
EFFDT_TO) TABLESPACE PSINDEX STORAGE (INITIAL 2038784 NEXT 203878
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10
/
CREATE INDEX PSBCOMBO_DATA_TBL ON PS_COMBO_DATA_TBL (SETID,
PROCESS_GROUP,
COMBINATION,
VALID_CODE,
FUND_CODE,
EFFDT_FROM,
EFFDT_TO) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10
/
CREATE INDEX PSCCOMBO_DATA_TBL ON PS_COMBO_DATA_TBL (VALID_CODE,
EFFDT_FROM,
EFFDT_TO,
PROCESS_GROUP,
COMBINATION) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10
/


coming to the distinct values if I remove the fund_code criteria the number of rows are 2244 by adding the fund_code criteria it returns zero rows.

Thanks