Home » RDBMS Server » Performance Tuning » Tuning a query with an EXISTS clause (Oracle 10.2.0.4 on Solaris 10)
Tuning a query with an EXISTS clause [message #586924] Tue, 11 June 2013 12:54 Go to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
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:


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);

GRANT DELETE, INSERT, UPDATE ON MSG_INSTANCE TO POD_MOVER;

GRANT SELECT ON MSG_INSTANCE TO PUBLIC;

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:='G3T2T99999999E7F'; this client has more data And below is a prod client for prt03y
--
exec :SYS_B_0:='036X199999999BRB';
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.

The question is how to tune the sql to bring it back to complete it in less then 3 seconds.
Thanks,

[Updated on: Tue, 11 June 2013 13:17]

Report message to a moderator

Re: Tuning a query with an EXISTS clause [message #586944 is a reply to message #586924] Tue, 11 June 2013 13:36 Go to previous messageGo to next message
Flyby
Messages: 143
Registered: March 2011
Location: Belgium
Senior Member
Suggestions: Create covering indexes for msg_instance (clnt_oid+msg_instance_id) and msg_user (msg_instance_id+msg_status) as they are used for joins. Update statistics. Is it possible to post the actual query plan instead of explain plan?
Re: Tuning a query with an EXISTS clause [message #586946 is a reply to message #586944] Tue, 11 June 2013 13:58 Go to previous messageGo to next message
orausern
Messages: 726
Registered: December 2005
Location: Hyderabad
Senior Member
Quote:

Is it possible to post the actual query plan instead of explain plan?

The plan I mentioned earlier is output of:
select * from table(dbms_xplan.display_cursor());

So it is the actual plan and not estimated. Also adding the two indexes is not also helping out unluckily.

Thanks,
Re: Tuning a query with an EXISTS clause [message #586954 is a reply to message #586946] Tue, 11 June 2013 18:36 Go to previous messageGo to next message
bobbydurrettdba
Messages: 15
Registered: April 2012
Location: Phoenix, Arizona
Junior Member
I would run these queries to figure out how selective the different predicates are:

select count(*) from msg_instance mi
WHERE mi.clnt_oid = :SYS_B_0;

select count(*) FROM msg_user mu
where mu.msg_status IN (:SYS_B_2, :SYS_B_3);

Also, just figure out how big the tables are:

select count(*) from msg_instance mi;

select count(*) FROM msg_user mu;

Depending on whether the condition on mi.clnt_oid or mu.msg_status are selective you will make different choices.

For example, if neither are selective just do full scans and hash joins.
If mu.msg_status is selective maybe you need an index where mu.msg_status is first and you might need a hint to force its use.

My SQL tuning paper if you want to spend the time to read it: http://www.bobbydurrettdba.com/uploads/Introtosqltuning.pdf
Re: Tuning a query with an EXISTS clause [message #587451 is a reply to message #586954] Fri, 14 June 2013 17:42 Go to previous message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
where exactly did you see this query running in 3 seconds?

are you on a version of Oracle that allows for use of the GATHER_PLAN_STATISTICS hint? if so, use it and post results that show the actual plan step cardinalities with the estimates. If you don't know this hint, read up on it.

Once you get estimates vs. actuals, pay attention to what Bobby said.

if you have a place where it runs in 3 seconds, get a plan from there too so we can compare them.

[Updated on: Fri, 14 June 2013 17:43]

Report message to a moderator

Previous Topic: Performance tuning made easy
Next Topic: DB designing from performance point of view.
Goto Forum:
  


Current Time: Tue Jul 29 15:02:03 CDT 2014

Total time taken to generate the page: 0.11411 seconds