-
Lot of I/O Wait Time
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.
Last edited by prateek47807; 09-06-2007 at 12:09 PM.
Reason: To include the database details
-
If I remember correctly PS_INSTALLATION has just one row , how many rows are in the other table?
One, who thinks that the other one who thinks that know and does not know, does not know either!
-
ps_combo_data_tbl has no rows for the above said condition.
-
could you please describe the index used in the explain plan
-
how many distinct values do you have for the following columns:
setid
process_group
combination
valid_code
fund_code
-
"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.
-
Regarding Index
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|