Hi Experts,
We are oracle 10.2.0.4 on Sun Solairs 10. There is one sql that is taking a long time (more than 40 seconds in production). I am copying below the create table scripts and the query. I will be very thankful for any suggestions on tuning it:
{code}
CREATE TABLE MSG_USER
(
MSG_USER_ID NUMBER(12) NOT NULL,
MSG_INSTANCE_ID NUMBER(12) NOT NULL,
CLNT_OID VARCHAR2(16 BYTE) NOT NULL,
PROCESS_STEP_ID NUMBER(12),
USER_OID VARCHAR2(16 BYTE) NOT NULL,
MSG_STATUS VARCHAR2(3 BYTE) NOT NULL,
SEND_REMINDER_DATE DATE,
SEND_REMINDER_COUNT NUMBER(2),
HIDE_DETAILS_LINK NUMBER(1),
ARCHIVED NUMBER(1) NOT NULL,
READ NUMBER(1) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE,
MSG_TYPE_NAME VARCHAR2(80 BYTE) NOT NULL,
BASE_TYPE CHAR(3 CHAR) NOT NULL,
GROUP_OID NUMBER(12),
DUE_DATE DATE,
REMINDER_TYPE NUMBER(1)
);
CREATE UNIQUE INDEX PK_MSG_USER ON MSG_USER
(MSG_USER_ID);
CREATE INDEX MSG_USER_IDX01 ON MSG_USER
(USER_OID, MSG_STATUS, CLNT_OID);
CREATE INDEX MSG_USER_IDX02 ON MSG_USER
(SEND_REMINDER_DATE);
CREATE INDEX MSG_USER_IDX03 ON MSG_USER
(PROCESS_STEP_ID);
CREATE INDEX MSG_USER_IDX04 ON MSG_USER
(CLNT_OID);
CREATE INDEX MSG_USER_IDX05 ON MSG_USER
(MSG_TYPE_NAME);
CREATE INDEX MSG_USER_IDX06 ON MSG_USER
(MSG_INSTANCE_ID);
ALTER TABLE MSG_USER ADD (
CONSTRAINT PK_MSG_USER
PRIMARY KEY
(MSG_USER_ID)
USING INDEX PK_MSG_USER
ENABLE VALIDATE);
CREATE TABLE MSG_INSTANCE
(
MSG_INSTANCE_ID NUMBER(12) NOT NULL,
CLNT_OID VARCHAR2(16 BYTE) NOT NULL,
SUBMITTER_USER_OID VARCHAR2(16 BYTE),
SUBMITTER_PROCESS_ID VARCHAR2(255 BYTE),
SUBMITTER_LASTNAME VARCHAR2(64 BYTE),
SUBMITTER_FIRSTNAME VARCHAR2(64 BYTE),
AFFECTED_USER_OID VARCHAR2(16 BYTE),
AFFECTED_LASTNAME VARCHAR2(64 BYTE),
AFFECTED_FIRSTNAME VARCHAR2(64 BYTE),
AFFECTED_USER_EMAIL VARCHAR2(256 BYTE),
NOTIFY_AFFECTED_USER NUMBER(1) NOT NULL,
REMOTE_ID NUMBER(15),
SRC_APP VARCHAR2(80 BYTE) NOT NULL,
SUBJECT VARCHAR2(80 BYTE),
SUMMARY VARCHAR2(4000 BYTE),
CREATED_DATE DATE NOT NULL,
MODIFIED_DATE DATE,
PRIORITY CHAR(3 BYTE) NOT NULL,
ACTION_URL_EXTENSION VARCHAR2(100 BYTE),
SUBJECT_LANG_ID NUMBER(12),
SUMMARY_LANG_ID NUMBER(12),
SUBMITTER_PROCESS_LANG_ID NUMBER(12)
);
CREATE UNIQUE INDEX PK_MSG_INSTANCE ON MSG_INSTANCE
(MSG_INSTANCE_ID);
CREATE INDEX MSG_INSTANCE_IDX01 ON MSG_INSTANCE
(CLNT_OID);
CREATE INDEX MSG_INSTANCE_IDX02 ON MSG_INSTANCE
(REMOTE_ID, CLNT_OID);
ALTER TABLE MSG_INSTANCE ADD (
CONSTRAINT PK_MSG_INSTANCE
PRIMARY KEY
(MSG_INSTANCE_ID)
USING INDEX PK_MSG_INSTANCE
ENABLE VALIDATE);
The table row counts are as folows -to know how large the table is:
SQL> select count(*) from msg_user;
COUNT(*)
----------
2299677
0:07.71
SQL> select count(*) from msg_instance;
COUNT(*)
----------
684168
Query:
variable SYS_B_0 varchar2(16);
variable SYS_B_1 NUMBER;
variable SYS_B_2 varchar2(16);
variable SYS_B_3 varchar2(16);
--exec :SYS_B_0:='G3T2TEG5JG15EE7F'; this client has more data And below is a prod client for prt03y
--
exec :SYS_B_0:='036X1Z6T83700BRB';
exec :SYS_B_1:=1;
exec :SYS_B_2:='NEW';
exec :SYS_B_3:='INP';
SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM ( SELECT *
FROM msg_instance mi
WHERE mi.clnt_oid = :SYS_B_0
AND EXISTS
(SELECT :SYS_B_1
FROM msg_user mu
WHERE mu.msg_instance_id = mi.msg_instance_id
AND mu.msg_status IN
(:SYS_B_2, :SYS_B_3))
ORDER BY created_date DESC) A)
WHERE rn >= 1 AND rn < 11;
Query plan:
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2296 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | VIEW | | 766 | 2002K| 2296 (1)| 00:00:28 |
| 3 | COUNT | | | | | |
| 4 | VIEW | | 766 | 1992K| 2296 (1)| 00:00:28 |
| 5 | SORT ORDER BY | | 766 | 227K| 2296 (1)| 00:00:28 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
| 6 | NESTED LOOPS SEMI | | 766 | 227K| 2295 (1)| 00:00:28 |
| 7 | TABLE ACCESS BY INDEX ROWID| MSG_INSTANCE | 766 | 219K| 384 (0)| 00:00:05 |
|* 8 | INDEX RANGE SCAN | MSG_INSTANCE_IDX01 | 766 | | 7 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| MSG_USER | 1533K| 16M| 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | MSG_USER_IDX06 | 2 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:B1>:B0 AND :B0<:B1))
2 - filter(("RN">=:B0 AND "RN"<:B1))
8 - access("MI"."CLNT_OID"=:SYS_B_0)
9 - filter(("MU"."MSG_STATUS"=:SYS_B_2 OR "MU"."MSG_STATUS"=:SYS_B_3))
10 - access("MU"."MSG_INSTANCE_ID"="MI"."MSG_INSTANCE_ID")
37 rows selected.
{code}
The question is how to tune the sql to bring it back to complete it in less then 3 seconds.
Thanks,
OrauserN