Home » RDBMS Server » Performance Tuning » sql tunning help required (11.1.0.7)
sql tunning help required [message #522615] Mon, 12 September 2011 15:57 Go to next message
nut_shut
Messages: 1
Registered: August 2011
Location: South
Junior Member
SELECT PARTICIPANT.*,
       EMPLOYEE_GROUPS.*
       FROM PARTICIPANT, 
       SRVC_HIST,
       EMPLOYEE_GROUPS 
       WHERE CLONE_TYPE IS NULL 
       AND PARTICIPANT.PART_ID=SRVC_HIST.PART_ID 
       AND SRVC_HIST.EMP_GRP_ID=EMPLOYEE_GROUPS.EMP_GRP_ID 
       AND (PART_SSN =:"SYS_B_0" OR PARTNER_PART_ID = :"SYS_B_1")


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  ajbsrh2phsgbw, child number 0
-------------------------------------
SELECT PARTICIPANT.*, EMPLOYEE_GROUPS.* FROM PARTICIPANT, SRVC_HIST,
EMPLOYEE_GROUPS WHERE CLONE_TYPE IS NULL AND
PARTICIPANT.PART_ID=SRVC_HIST.PART_ID AND
SRVC_HIST.EMP_GRP_ID=EMPLOYEE_GROUPS.EMP_GRP_ID AND (PART_SSN =
:"SYS_B_0" OR PARTNER_PART_ID = :"SYS_B_1")

Plan hash value: 2097804779

--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |       |       |  1181 (100)|          |
|*  1 |  HASH JOIN                    |                  |  1986 |   453K|  1181   (2)| 00:00:15 |
|*  2 |   TABLE ACCESS FULL           | PARTICIPANT      |   116 | 15080 |   946   (2)| 00:00:12 |
|   3 |   NESTED LOOPS                |                  |       |       |            |          |
|   4 |    NESTED LOOPS               |                  |  2003 |   203K|   234   (1)| 00:00:03 |
|   5 |     MERGE JOIN CARTESIAN      |                  |    16 |  1520 |     9   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL        | EMPLOYEE_GROUPS  |     4 |    24 |     3   (0)| 00:00:01 |
|   7 |      BUFFER SORT              |                  |     4 |   356 |     6   (0)| 00:00:01 |
|*  8 |       TABLE ACCESS FULL       | EMPLOYEE_GROUPS  |     4 |   356 |     2   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN          | SH_EMP_GRP_ID_IX |    70 |       |    12   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID| SRVC_HIST        |   125 |  1125 |    14   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - access("PART_ID"="PART_ID")
   2 - filter(("CLIENT_ID"=6 AND ("PARTNER_PART_ID"=:SYS_B_1 OR "PART_SSN"=:SYS_B_0) AND
              "CLONE_TYPE" IS NULL))
   6 - filter("CLIENT_ID"=6)
   8 - filter("CLIENT_ID"=6)
   9 - access("EMP_GRP_ID"="EMP_GRP_ID")
       filter("EMP_GRP_ID"="EMP_GRP_ID")


* {code tags} added by BlackSwan; please do so yourself in the future

[Updated on: Mon, 12 September 2011 23:16] by Moderator

Report message to a moderator

Re: sql tunning help required [message #522624 is a reply to message #522615] Mon, 12 September 2011 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read How to use [code] tags and make your code easier to read.

What make you think there is something to do?
Post the usual information: indexes, cardinality (global and for the important columns).

Regards
Michel
Re: sql tunning help required [message #529414 is a reply to message #522624] Mon, 31 October 2011 13:58 Go to previous messageGo to next message
singh09
Messages: 8
Registered: October 2011
Junior Member
See the explain plan you know the culprits. Try to add some indexes on the columns to avoid full table scans. You can also check if you are using right order while fetching data from tables.




"Teamwork is an individual skill."
Re: sql tunning help required [message #529676 is a reply to message #529414] Wed, 02 November 2011 08:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The right order of what exactly?
Re: sql tunning help required [message #529733 is a reply to message #529676] Wed, 02 November 2011 13:12 Go to previous message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
ps: also remove the SELECT PARTICIPANT.*, EMPLOYEE_GROUPS.* and replace them with the actual columnnames
Is there an index on PART_SSN or PARTNER_PART_ID ?
Previous Topic: Compare current row values with previous one until mismatch
Next Topic: Doubt about Tuning SQL
Goto Forum:
  


Current Time: Thu Mar 28 09:35:59 CDT 2024