OraFAQ Forum: Performance Tuning » Need help to tune this query

Home » RDBMS Server » Performance Tuning » Need help to tune this query (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need help to tune this query [message #594378]

Wed, 28 August 2013 01:30

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi Experts,

Please help me to improve the performance of the below query.

SELECT   DISTINCT       100,
                        200,
                         'America',
                         si.sid,
                         si.logname,
                         ri.rname,
                         ui.gname,
                         ui.gid,
                         si.mail,
                        sdi.d_id,
                        si.salesch
        FROM   salesinfo si,
               userinfo ui,
               roleinfo ri,
               userlist uil,
               accountability ac,
               deptsalesinfo sdi
       WHERE       si.sid = uil.u_id
               AND uil.r_id = ri.r_id
               AND uil.gid = ui.gid
               AND si.res_id = ac.res_id
               AND ac.rkey LIKE 'MNR%'
               AND UPPER (ri.rname) = 'MNR_SHR'
               AND si.sid = sdi.u_id(+);

6292 rows selected.

I am posting execution plan , number of records in each table and indexes.

Execution Plan
----------------------------------------------------------
Plan hash value: 2083464354

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     2 |   310 |   201   (2)| 00:00:03 |
|   1 |  HASH UNIQUE                        |                         |     2 |   310 |   201   (2)| 00:00:03 |
|   2 |   NESTED LOOPS                      |                         |       |       |            |       |
|   3 |    NESTED LOOPS                     |                         |     2 |   310 |   200   (2)| 00:00:03 |
|*  4 |     HASH JOIN OUTER                 |                         |     2 |   256 |   198   (2)| 00:00:03 |
|*  5 |      HASH JOIN                      |                         |     2 |   240 |   196   (2)| 00:00:03 |
|   6 |       TABLE ACCESS BY INDEX ROWID   | accountability          |     1 |    29 |     2   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN             | accountability_N1       |     1 |       |     1   (0)| 00:00:01 |
|   8 |       NESTED LOOPS                  |                         |       |       |            |       |
|   9 |        NESTED LOOPS                 |                         |   225 | 20475 |   194   (2)| 00:00:03 |
|  10 |         MERGE JOIN                  |                         |    88 |  3168 |    17   (6)| 00:00:01 |
|* 11 |          TABLE ACCESS BY INDEX ROWID| roleinfo                |     1 |    18 |     3   (0)| 00:00:01 |
|  12 |           INDEX FULL SCAN           | roleinfo_PK             |     2 |       |     1   (0)| 00:00:01 |
|* 13 |          SORT JOIN                  |                         |  8766 |   154K|    14   (8)| 00:00:01 |
|  14 |           TABLE ACCESS FULL         | userlist                |  8766 |   154K|    13   (0)| 00:00:01 |
|* 15 |         INDEX RANGE SCAN            | salesinfo_N1            |     3 |       |     1   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID  | salesinfo               |     3 |   165 |     2   (0)| 00:00:01 |
|  17 |      INDEX FULL SCAN                | UK_BU_DEPARTMENT_SALPER |    46 |   368 |     1   (0)| 00:00:01 |
|* 18 |     INDEX UNIQUE SCAN               | userinfo_PK             |     1 |       |     0   (0)| 00:00:01 |
|  19 |    TABLE ACCESS BY INDEX ROWID      | userinfo                |     1 |    27 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("si"."sid"="sdi"."u_id"(+))
   5 - access("si"."res_id"="ac"."res_id")
   7 - access("ac"."rkey" LIKE 'ORL%')
       filter("ac"."rkey" LIKE 'ORL%')
  11 - filter(UPPER("ri"."rname")='ORL_MEMBER')
  13 - access("uil"."r_id"="ri"."r_id")
       filter("uil"."r_id"="ri"."r_id")
  15 - access("si"."sid"="uil"."u_id")
  18 - access("uil"."gid"="ui"."gid")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      23722  consistent gets
          0  physical reads
          0  redo size
     470026  bytes sent via SQL*Net to client
       4973  bytes received via SQL*Net from client
        421  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       6292  rows processed

Number of records in each table.

SELECT COUNT(*) FROM salesinfo--119913 
SELECT COUNT(*) FROM userlist--7060                                
SELECT COUNT(*) FROM userinfo--124                
SELECT COUNT(*) FROM roleinfo--2                
SELECT COUNT(*) FROM accountability--100                
SELECT COUNT(*) FROM deptsalesinfo--46

--Indexes on salesinfo
CREATE INDEX salesinfo_N1 ON salesinfo(sid);
CREATE INDEX salesinfo_N2 ON salesinfo(res_id,salesch);

--Indexes on userinfo
CREATE UNIQUE INDEX userinfo_PK ON userinfo(gid);

--Indexes on roleinfo
CREATE UNIQUE INDEX roleinfo_PK ON roleinfo(r_id);

--Indexes on userlist
CREATE INDEX userlist_IX_GROUP ON userlist(gid);
CREATE UNIQUE INDEX userlist_PK ON userlist(u_id, gid, r_id);

--Indexes on accountability
CREATE INDEX accountability_N1 ON accountability(rkey);
CREATE UNIQUE INDEX PK_accountability ON accountability(res_id);

--Indexes on deptsalesinfo
CREATE UNIQUE INDEX PK_deptsalesinfo ON deptsalesinfo(ID);
CREATE UNIQUE INDEX UK_BU_DEPARTMENT_SALPER ON deptsalesinfo(d_id, u_id);

Please help me.

Thanks in advance.

Report message to a moderator

Re: Need help to tune this query [message #594388 is a reply to message #594378]

Wed, 28 August 2013 03:00

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Thanks for posting the explain plan and index details.
The explain plan suggests the query is fairly quick. How long does it actually take and how long do you need it to take?

Report message to a moderator

Re: Need help to tune this query [message #594396 is a reply to message #594378]

Wed, 28 August 2013 03:28

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Some observations, possibly of little value:
The CBO expects 2 rows back, but is getting 6292. So there is a problem with cardinality estimates. The only mistake I can see is at id 14, where the CBO expects 8766 rows but it should be 7060. I wouldn't have thought it would make much difference, but even so, I would gather stats on userlist.
Are ids 11 and 12 bit odd? The cardinality estimate is perfect, but why do it that way? I can't see the point of scanning the entire index to retrieve two rowids at 12 and then retrieving both rows and filtering one away at 11. The CBO must have a reason for doing this, but I would hint a full scan of roleinfo (or perhaps make roleinfo_pk invisible) and see what happens.

Report message to a moderator

Re: Need help to tune this query [message #594398 is a reply to message #594378]

Wed, 28 August 2013 03:35

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I have just noticed that you are lying. Your query has this predicate,

AND ac.rkey LIKE 'MNR%'
AND UPPER (ri.rname) = 'MNR_SHR'

but the plan has this,

7 - access("ac"."rkey" LIKE 'ORL%')
filter("ac"."rkey" LIKE 'ORL%')
11 - filter(UPPER("ri"."rname")='ORL_MEMBER')

Report message to a moderator

Re: Need help to tune this query [message #594406 is a reply to message #594378]

Wed, 28 August 2013 04:36

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

ramya_162 wrote on Wed, 28 August 2013 01:30

Hi Experts,

Please help me to improve the performance of the below query.

SELECT   DISTINCT       100,
                        200,
                         'America',
                         si.sid,
                         si.logname,
                         ri.rname,
                         ui.gname,
                         ui.gid,
                         si.mail,
                        sdi.d_id,
                        si.salesch
        FROM   salesinfo si,
               userinfo ui,
               roleinfo ri,
               userlist uil,
               accountability ac,
               deptsalesinfo sdi
       WHERE       si.sid = uil.u_id
               AND uil.r_id = ri.r_id
               AND uil.gid = ui.gid
               AND si.res_id = ac.res_id
               AND ac.rkey LIKE 'MNR%'
               AND UPPER (ri.rname) = 'MNR_SHR'
               AND si.sid = sdi.u_id(+);

6292 rows selected.

I am posting execution plan , number of records in each table and indexes.

Execution Plan
----------------------------------------------------------
Plan hash value: 2083464354

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     2 |   310 |   201   (2)| 00:00:03 |
|   1 |  HASH UNIQUE                        |                         |     2 |   310 |   201   (2)| 00:00:03 |
|   2 |   NESTED LOOPS                      |                         |       |       |            |       |
|   3 |    NESTED LOOPS                     |                         |     2 |   310 |   200   (2)| 00:00:03 |
|*  4 |     HASH JOIN OUTER                 |                         |     2 |   256 |   198   (2)| 00:00:03 |
|*  5 |      HASH JOIN                      |                         |     2 |   240 |   196   (2)| 00:00:03 |
|   6 |       TABLE ACCESS BY INDEX ROWID   | accountability          |     1 |    29 |     2   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN             | accountability_N1       |     1 |       |     1   (0)| 00:00:01 |
|   8 |       NESTED LOOPS                  |                         |       |       |            |       |
|   9 |        NESTED LOOPS                 |                         |   225 | 20475 |   194   (2)| 00:00:03 |
|  10 |         MERGE JOIN                  |                         |    88 |  3168 |    17   (6)| 00:00:01 |
|* 11 |          TABLE ACCESS BY INDEX ROWID| roleinfo                |     1 |    18 |     3   (0)| 00:00:01 |
|  12 |           INDEX FULL SCAN           | roleinfo_PK             |     2 |       |     1   (0)| 00:00:01 |
|* 13 |          SORT JOIN                  |                         |  8766 |   154K|    14   (8)| 00:00:01 |
|  14 |           TABLE ACCESS FULL         | userlist                |  8766 |   154K|    13   (0)| 00:00:01 |
|* 15 |         INDEX RANGE SCAN            | salesinfo_N1            |     3 |       |     1   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID  | salesinfo               |     3 |   165 |     2   (0)| 00:00:01 |
|  17 |      INDEX FULL SCAN                | UK_BU_DEPARTMENT_SALPER |    46 |   368 |     1   (0)| 00:00:01 |
|* 18 |     INDEX UNIQUE SCAN               | userinfo_PK             |     1 |       |     0   (0)| 00:00:01 |
|  19 |    TABLE ACCESS BY INDEX ROWID      | userinfo                |     1 |    27 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("si"."sid"="sdi"."u_id"(+))
   5 - access("si"."res_id"="ac"."res_id")
   7 - access("ac"."rkey" LIKE 'MNR%')
       filter("ac"."rkey" LIKE 'MNR%')
  11 - filter(UPPER("ri"."rname")='MNR_SHR')
  13 - access("uil"."r_id"="ri"."r_id")
       filter("uil"."r_id"="ri"."r_id")
  15 - access("si"."sid"="uil"."u_id")
  18 - access("uil"."gid"="ui"."gid")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      23722  consistent gets
          0  physical reads
          0  redo size
     470026  bytes sent via SQL*Net to client
       4973  bytes received via SQL*Net from client
        421  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       6292  rows processed

Number of records in each table.

SELECT COUNT(*) FROM salesinfo--119913 
SELECT COUNT(*) FROM userlist--7060                                
SELECT COUNT(*) FROM userinfo--124                
SELECT COUNT(*) FROM roleinfo--2                
SELECT COUNT(*) FROM accountability--100                
SELECT COUNT(*) FROM deptsalesinfo--46

--Indexes on salesinfo
CREATE INDEX salesinfo_N1 ON salesinfo(sid);
CREATE INDEX salesinfo_N2 ON salesinfo(res_id,salesch);

--Indexes on userinfo
CREATE UNIQUE INDEX userinfo_PK ON userinfo(gid);

--Indexes on roleinfo
CREATE UNIQUE INDEX roleinfo_PK ON roleinfo(r_id);

--Indexes on userlist
CREATE INDEX userlist_IX_GROUP ON userlist(gid);
CREATE UNIQUE INDEX userlist_PK ON userlist(u_id, gid, r_id);

--Indexes on accountability
CREATE INDEX accountability_N1 ON accountability(rkey);
CREATE UNIQUE INDEX PK_accountability ON accountability(res_id);

--Indexes on deptsalesinfo
CREATE UNIQUE INDEX PK_deptsalesinfo ON deptsalesinfo(ID);
CREATE UNIQUE INDEX UK_BU_DEPARTMENT_SALPER ON deptsalesinfo(d_id, u_id);

Please help me.

Thanks in advance.

Report message to a moderator

Re: Need help to tune this query [message #594407 is a reply to message #594406]

Wed, 28 August 2013 04:42

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

What was the point of quoting your own message?

Report message to a moderator

Re: Need help to tune this query [message #594419 is a reply to message #594407]

Wed, 28 August 2013 05:05

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

He has changed the predicate info in the execution plan as per JW's post.

Report message to a moderator

Re: Need help to tune this query [message #594420 is a reply to message #594419]

Wed, 28 August 2013 05:07

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

pablolee wrote on Wed, 28 August 2013 11:05

He has changed the predicate info in the execution plan as per JW's post.

Ah, yes. So now one has no idea what the execution statistics refer to. Two rows or 6292 rows?

Report message to a moderator

Re: Need help to tune this query [message #594448 is a reply to message #594420]

Wed, 28 August 2013 09:46

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Surely you can just use 'the force' John. Very Happy

[Updated on: Wed, 28 August 2013 09:47]

Report message to a moderator

Re: Need help to tune this query [message #595064 is a reply to message #594448]

Thu, 05 September 2013 22:43

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Time to use the SCHWARTZ.

This kind of query is generally quite easy to tune. We will today use a variation of the technique called QUERY DECOMPOSITION AND RECONSTRUCTION.

Given your Query Plan and Predicate Information, it is easy to see the first queries you need to run in order to validate the plan. Since cardinality is the number one thing in a query plan and you seem to understand this given your other statements, decompose the main query into smaller queries to see how well the optimizer did in getting the right cardinalities. You should be able to make the association between these four sql statements and your predicate info and query plan. Run them and then post your results.

select count(*) from accountability ac where "ac"."rkey" LIKE 'MNR%';

select count(*) from roleinfo ri where (UPPER("ri"."rname")='MNR_SHR';

select count(*) from userlist;

select count(*) from deptsalesinfo;

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     2 |   310 |   201   (2)| 00:00:03 |
|   1 |  HASH UNIQUE                        |                         |     2 |   310 |   201   (2)| 00:00:03 |
|   2 |   NESTED LOOPS                      |                         |       |       |            |       |
|   3 |    NESTED LOOPS                     |                         |     2 |   310 |   200   (2)| 00:00:03 |
|*  4 |     HASH JOIN OUTER                 |                         |     2 |   256 |   198   (2)| 00:00:03 |
|*  5 |      HASH JOIN                      |                         |     2 |   240 |   196   (2)| 00:00:03 |
|   6 |       TABLE ACCESS BY INDEX ROWID   | accountability          |     1 |    29 |     2   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN             | accountability_N1       |     1 |       |     1   (0)| 00:00:01 |
|   8 |       NESTED LOOPS                  |                         |       |       |            |       |
|   9 |        NESTED LOOPS                 |                         |   225 | 20475 |   194   (2)| 00:00:03 |
|  10 |         MERGE JOIN                  |                         |    88 |  3168 |    17   (6)| 00:00:01 |
|* 11 |          TABLE ACCESS BY INDEX ROWID| roleinfo                |     1 |    18 |     3   (0)| 00:00:01 |
|  12 |           INDEX FULL SCAN           | roleinfo_PK             |     2 |       |     1   (0)| 00:00:01 |
|* 13 |          SORT JOIN                  |                         |  8766 |   154K|    14   (8)| 00:00:01 |
|  14 |           TABLE ACCESS FULL         | userlist                |  8766 |   154K|    13   (0)| 00:00:01 |
|* 15 |         INDEX RANGE SCAN            | salesinfo_N1            |     3 |       |     1   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID  | salesinfo               |     3 |   165 |     2   (0)| 00:00:01 |
|  17 |      INDEX FULL SCAN                | UK_BU_DEPARTMENT_SALPER |    46 |   368 |     1   (0)| 00:00:01 |
|* 18 |     INDEX UNIQUE SCAN               | userinfo_PK             |     1 |       |     0   (0)| 00:00:01 |
|  19 |    TABLE ACCESS BY INDEX ROWID      | userinfo                |     1 |    27 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("si"."sid"="sdi"."u_id"(+))
   5 - access("si"."res_id"="ac"."res_id")
   7 - access("ac"."rkey" LIKE 'MNR%')
       filter("ac"."rkey" LIKE 'MNR%')
  11 - filter(UPPER("ri"."rname")='MNR_SHR')
  13 - access("uil"."r_id"="ri"."r_id")
       filter("uil"."r_id"="ri"."r_id")
  15 - access("si"."sid"="uil"."u_id")
  18 - access("uil"."gid"="ui"."gid")

You do understand why these four sql statements would be informative right?

Kevin

Report message to a moderator

Re: Need help to tune this query [message #597376 is a reply to message #595064]

Fri, 04 October 2013 01:50

8939513598$
Messages: 103
Registered: July 2013
Location: chennai

Senior Member

Hello Ramya Ma'am, Did you tuned your sql query, atlast ?, what solution did you implement? it's been a month after your posting of the sql query, i need to learn from yourside, can you help me?

Report message to a moderator

Previous Topic:	Buffer Busy Wait DBMS_ALERT_INFO
Next Topic:	Please advise on configuring memory

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 18:44:16 CDT 2024