Home » RDBMS Server » Performance Tuning » Need help to tune this query (Oracle 11g)
Need help to tune this query [message #594378] Wed, 28 August 2013 01:30 Go to next message
ramya_162
Messages: 80
Registered: August 2013
Location: Banglore
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.
Re: Need help to tune this query [message #594388 is a reply to message #594378] Wed, 28 August 2013 03:00 Go to previous messageGo to next message
cookiemonster
Messages: 10593
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?
Re: Need help to tune this query [message #594396 is a reply to message #594378] Wed, 28 August 2013 03:28 Go to previous messageGo to next message
John Watson
Messages: 4102
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.
Re: Need help to tune this query [message #594398 is a reply to message #594378] Wed, 28 August 2013 03:35 Go to previous messageGo to next message
John Watson
Messages: 4102
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')


Re: Need help to tune this query [message #594406 is a reply to message #594378] Wed, 28 August 2013 04:36 Go to previous messageGo to next message
ramya_162
Messages: 80
Registered: August 2013
Location: Banglore
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.

Re: Need help to tune this query [message #594407 is a reply to message #594406] Wed, 28 August 2013 04:42 Go to previous messageGo to next message
cookiemonster
Messages: 10593
Registered: September 2008
Location: Rainy Manchester
Senior Member
What was the point of quoting your own message?
Re: Need help to tune this query [message #594419 is a reply to message #594407] Wed, 28 August 2013 05:05 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
He has changed the predicate info in the execution plan as per JW's post.
Re: Need help to tune this query [message #594420 is a reply to message #594419] Wed, 28 August 2013 05:07 Go to previous messageGo to next message
John Watson
Messages: 4102
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?
Re: Need help to tune this query [message #594448 is a reply to message #594420] Wed, 28 August 2013 09:46 Go to previous messageGo to next message
pablolee
Messages: 2590
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 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
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
Re: Need help to tune this query [message #597376 is a reply to message #595064] Fri, 04 October 2013 01:50 Go to previous message
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?
Previous Topic: Buffer Busy Wait DBMS_ALERT_INFO
Next Topic: Please advise on configuring memory
Goto Forum:
  


Current Time: Thu Apr 24 20:04:37 CDT 2014

Total time taken to generate the page: 0.10204 seconds