Home » RDBMS Server » Performance Tuning » Poorly performing SQL query (Oracle 9.2.0.6 on Solaris)
Poorly performing SQL query [message #286312] Fri, 07 December 2007 02:46 Go to next message
rdoggart
Messages: 10
Registered: December 2007
Location: Belfast
Junior Member
Hi All,

I have a problem with a customer database and performance. I have ran statspack and identified the worst performing SQL statement, but have run out of ideas on how to get the SQL to perform better. The query was not a problem a month ago, but has only recently started to cause problems. I do not have an old explain plan to compare it to. The query is from an application and so cannot be changed. Why are we doing so many ‘DB File Sequential Reads’ when the hash join is doing full table accesses ? And why am I reading 20,000+ blocks to get two rows.


TKprof output:

select * from lgncc_commoncaseview where
clientid = :b1 and
clienttype = :b2 and (1=1)
union
select * from lgncc_commoncaseview where
xref1 = :b3 and
objecttype = :b4 and (1=1)

call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      4.49      34.89       1787      21664          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      4.49      34.90       1787      21664          0           2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 64

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  SORT UNIQUE
      2   UNION-ALL
      1    NESTED LOOPS
      1     HASH JOIN
      4      TABLE ACCESS BY INDEX ROWID OBJ#(35867)
      4       INDEX RANGE SCAN OBJ#(38739) (object id 38739)
 117901      HASH JOIN OUTER
 117901       TABLE ACCESS FULL OBJ#(35878) (lgncc_enquiry)
1475502       TABLE ACCESS FULL OBJ#(35880)  (lgncc_enquiryrelation)
      1     TABLE ACCESS BY INDEX ROWID OBJ#(35873)
      1      INDEX UNIQUE SCAN OBJ#(35874) (object id 35874)
      1    NESTED LOOPS OUTER
      1     NESTED LOOPS OUTER
      1      NESTED LOOPS
      1       TABLE ACCESS BY INDEX ROWID OBJ#(35878)
      1        INDEX RANGE SCAN OBJ#(38738) (object id 38738)
      1       TABLE ACCESS BY INDEX ROWID OBJ#(35873)
      1        INDEX UNIQUE SCAN OBJ#(35874) (object id 35874)
      1      INDEX RANGE SCAN OBJ#(38516) (object id 38516)
      1     TABLE ACCESS BY INDEX ROWID OBJ#(35867)
      1      INDEX UNIQUE SCAN OBJ#(35868) (object id 35868)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00           0.00
  db file sequential read                      1382        0.17          16.42
  db file scattered read                        175        0.10           3.45
  latch free                                      6        0.01           0.01
  buffer busy waits                            1137        0.08          11.15
  SQL*Net more data to client                     1        0.00           0.00
  SQL*Net message from client                     2        6.89           6.89


Lgncc_CommonCaseview Definition

 

SELECT
-- [ID]
  ENQUIRY.ID                AS REFERENCE,
  0                         AS TYPE,
-- [DESCRIPTION]
  ENQUIRY.TITLE             AS LINE1,
  TYPE.NAME                   AS LINE2,
-- [CASE]
  ENQUIRY.CASEID            AS CASEID,
  ENQUIRY.CASEREF           AS RELATEDCASE,
  ENQUIRY.ENQUIRYTYPE       AS ENQUIRYTYPE,
  ENQUIRY.OBJECTTYPE        AS OBJECTTYPE,
  ENQUIRY.XREF1             AS XREF1,
  ENQUIRY.XREF2             AS XREF2,
  ENQUIRY.XREF3             AS XREF3,
  ENQUIRY.OBJECTDESC        AS OBJECTDESC,
-- [INTERACTION]
  INT.CLIENTTYPE            AS CLIENTTYPE,
  INT.CLIENTID              AS CLIENTID,
  INT.CLIENTNAME            AS CLIENTNAME,
  INT.LOGID                 AS INTERACTIONID,
  INT.INTREF                AS INTERACTIONREF,
  INT.VERIFIED              AS INTERACTIONVERIFIED,
  nvl(INT.INITCHANNEL, -1)  AS INTERACTIONCHANNEL,
  INT.REFERENCE             AS INTERACTIONREFERENCE,
  INT.STARTTIME             AS INTERACTIONDATE,
-- [STATUS]
  NULL                          AS TARGETDATE,
  ENQUIRY.STATUS            AS STATUS,
-- [AUDIT]
  ENQUIRY.CREATIONDATE      AS CREATIONDATE,
  ENQUIRY.SOURCEID          AS CREATEDBY,
  ENQUIRY.CREATIONDATE      AS MODIFIEDDATE,
  NULL                      AS MODIFIEDBY
FROM
    LGNCC_ENQUIRY ENQUIRY
    INNER JOIN LGNCC_ENQUIRYTYPE TYPE ON 
       ENQUIRY.ENQUIRYTYPE = TYPE.ID
    LEFT OUTER JOIN LGNCC_ENQUIRYRELATION REL ON 
       ENQUIRY.ID = REL.ENQUIRYID AND REL.RELATION = 1
    LEFT OUTER JOIN LGNCC_INTLOGHDR INT ON 
       INT.LOGID = REL.INTERACTIONID
    WHERE 
    ENQUIRY.CASEREF IS NOT NULL AND ENQUIRY.DELETEDDATE IS NULL



Ronnie Doggart
Re: Poorly performing SQL query [message #286463 is a reply to message #286312] Fri, 07 December 2007 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Are statistics current on all object touched by this SQL statement?
Re: Poorly performing SQL query [message #286466 is a reply to message #286312] Fri, 07 December 2007 09:22 Go to previous messageGo to next message
rdoggart
Messages: 10
Registered: December 2007
Location: Belfast
Junior Member
Yes statistics are all up to date on tables and indexes.
Re: Poorly performing SQL query [message #286585 is a reply to message #286312] Sat, 08 December 2007 08:47 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Your statement is referencing a view.

Any chance that somebody changed the view definition?
-- An outer join may cause bad performance.
Re: Poorly performing SQL query [message #286589 is a reply to message #286585] Sat, 08 December 2007 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

An outer join may cause bad performance.

What does this mean?
If you need it then it does not cause bad performances.
If you does not need it then it causes bad performances as all what you do and is not necessary.
If the lone purpose of an outer join was to cause bad performances then it would no more exist (Darwin law).

Regards
Michel
Re: Poorly performing SQL query [message #286594 is a reply to message #286312] Sat, 08 December 2007 13:59 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Sounds like the old "outer joins are slow" argument, just like the "IN is faster than EXISTS" (or vice versa) or the "views are slow" argument... Wink Plus, of course, the OP did actually post the view definition...

Anyway, why do you think that this shouldn't incur sequential reads? I can see full scan IO here, as part of the HASH JOIN, so sequential reads are definitely on the cards....

Unfortunately, there's not enough information here to show exactly why Oracle is choosing a HASH JOIN with full scans, but you do have unpredicated selects in that UNION on lgncc_enquiry and lgncc_enquiryrelation for the simple reason that
clientid = :b1 and
clienttype = :b2

come from LGNCC_INTLOGHDR, whereas
xref1 = :b3 and
objecttype = :b4

come from LGNCC_ENQUIRY, hence why you end up with full scans, if the indexing strategy does not allow any other kind of access path when predicating on these columns.

It would be necessary to see what the index structure is on LGNCC_INTLOGHDR, plus number of rows, whether histograms exist, what the settings of various optimiser parameters are, etc. etc.
to truly work out what is going on, such as whether bind variable peeking has occurred here and is skewing your plan for this particular instance of the query.

What is the plan like for just running the first part of the query?, i.e.
select * from lgncc_commoncaseview where
clientid = :b1 and
clienttype = :b2

Does it match that what is shown in the tkprof output (with the possible changes due to environmental differences)? Also, post the plan when not using bind variables, to see if peeking has had an effect.

Regards
Re: Poorly performing SQL query [message #286656 is a reply to message #286312] Sun, 09 December 2007 07:48 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Don't be so angry, guys...

I intended to say that "in that particular case outer join may cause bad performance" because of following:

select * from lgncc_commoncaseview where
clientid = :b1 and
clienttype = :b2


If you look at view definition then you will see that both clientid and clienttype columns are coming from LGNCC_INTLOGHDR table (which is accessed by the outer join).
So IMHO (because outer join enforces join order) the optimizer performs following:
1. Full table scan on lgncc_enquiry table (because there is no and conditions in WHERE referencing that table) and store it in hash area
2. Full table scan on LGNCC_INTLOGHDR (probably there is no index on clientid and clienttype columns or optimizer thinks that full table scan is better/faster)

In that case I would like:
1. Verify an index existance on clientid and clienttype columns of LGNCC_INTLOGHDR table (and create one if needed).
2. Exchage OUTER join for a regular one when accessing LGNCC_INTLOGHDR table.
In my opinion - the OUTER join is not needed here, because there is an explicit value is given in WHERE for 2 columns accessed in outer joined table ( clientid and clienttype).

Michael
Re: Poorly performing SQL query [message #286875 is a reply to message #286312] Mon, 10 December 2007 05:47 Go to previous message
rdoggart
Messages: 10
Registered: December 2007
Location: Belfast
Junior Member
Guys,

Here is the plan when run with no bind variables:

select * from lgncc_commoncaseview where
clientid = '101000031874' and
clienttype = 1 and
(1=1)
union
select * from lgncc_commoncaseview where
xref1 = '101000031874' and
objecttype = 'C1' and
(1=1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.06          0          9          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2      5.75       9.62      16101      21927          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      5.81       9.69      16101      21936          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 64  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE 
      2   UNION-ALL  
      1    NESTED LOOPS  
      1     HASH JOIN  
      4      TABLE ACCESS BY INDEX ROWID OBJ#(35867) 
      4       INDEX RANGE SCAN OBJ#(38739) (object id 38739)
 118984      HASH JOIN OUTER 
 118984       TABLE ACCESS FULL OBJ#(35878) 
1488680       TABLE ACCESS FULL OBJ#(35880) 
      1     TABLE ACCESS BY INDEX ROWID OBJ#(35873) 
      1      INDEX UNIQUE SCAN OBJ#(35874) (object id 35874)
      1    NESTED LOOPS OUTER 
      1     NESTED LOOPS OUTER 
      1      NESTED LOOPS  
      1       TABLE ACCESS BY INDEX ROWID OBJ#(35878) 
      1        INDEX RANGE SCAN OBJ#(38738) (object id 38738)
      1       TABLE ACCESS BY INDEX ROWID OBJ#(35873) 
      1        INDEX UNIQUE SCAN OBJ#(35874) (object id 35874)
      1      INDEX RANGE SCAN OBJ#(38516) (object id 38516)
      1     TABLE ACCESS BY INDEX ROWID OBJ#(35867) 
      1      INDEX UNIQUE SCAN OBJ#(35868) (object id 35868)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                      1150        0.03          0.59
  db file scattered read                       2640        0.08          4.72
  SQL*Net more data to client                     1        0.00          0.00
  SQL*Net message from client                     2        4.28          4.28



Also we already have an index on clientid, clienttype and another on xref1/objecttype. We gather histograms by default.

Table Name	        CDN	Column	        NDV	 Nulls
lgncc_enquiry	        1477524			
		                ID	        1477524	 0
		                EnquiryType	157	 0
		                Deleteddate	120	 1483280
		                Caseref	        118215	 1359309
		                Xref1	        76112	
lgncc_intloghdr	        1595125			
		                Logid	        1595125	 0
		                Clientid	80867	 1354286
		                Clienttype	4	 0
lgncc_enquiryrelation	1693932			
		                Interactionid	1585278	 0
		                Enquiryid	1477457	 0
		                Relation	3	 0
lgncc_enquirytype	 197			
		                ID	        197	 0



Many Thanks
Ronnie
Previous Topic: Use of RBO in 10g
Next Topic: How to disable archivelog generation for a patricular session ?
Goto Forum:
  


Current Time: Sat Dec 03 05:44:03 CST 2016

Total time taken to generate the page: 0.15073 seconds