Home » RDBMS Server » Performance Tuning » SQL Query Performance
SQL Query Performance [message #288749] Tue, 18 December 2007 10:00 Go to next message
rdoggart
Messages: 10
Registered: December 2007
Location: Belfast
Junior Member
Hi All,

Having problem with the following query:
select  * from (
SELECT
  ENQUIRY.ID AS EnquiryID,
  CASES.CaseId,
  CASES.CaseRef,
  SUBJECT.id AS SubjectID,
  SUBJECT.name AS SubjectName,
  REASON.id AS ReasonID,
  REASON.name AS ReasonName,
  TYPE.id AS TypeID,
  TYPE.name AS TypeName,
  ENQUIRY.CreationDate AS OpenedDateTime
FROM LGNCC_CLOSEDCASEHDR CASES, LGNCC_ENQUIRY ENQUIRY,
     LGNCC_ENQUIRYSUBJECT SUBJECT, LGNCC_ENQUIRYREASON REASON, LGNCC_ENQUIRYTYPE TYPE
WHERE CASES.enquiryId = ENQUIRY.ID
  AND ENQUIRY.deleteddate is null
  AND ENQUIRY.enquirytype = TYPE.id
  AND TYPE.reason = REASON.id
  AND REASON.subject = SUBJECT.id
)
where upper(caseref) like '109969%'
and (1=1) order by openeddatetime desc;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.26       0.41          0         27          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      2.34       4.25      13312      13337          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.60       4.67      13312      13364          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 118  (FLINE_611)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT ORDER BY
      1   HASH JOIN
     27    INDEX FULL SCAN LGNCC_ENQUIRYSUBJECT_IDX_001 (object id 131778)
      1    HASH JOIN
     80     INDEX FULL SCAN LGNCC_ENQUIRYREASON_IDX_002 (object id 131772)
      1     HASH JOIN
    280      INDEX FAST FULL SCAN LGNCC_ENQUIRYTYPE_IDX_008 (object id 131782)
      1      HASH JOIN
      1       TABLE ACCESS FULL LGNCC_CLOSEDCASEHDR
1426265       TABLE ACCESS FULL LGNCC_ENQUIRY



The inner code is from a view so I extracted it to make the query joins visible.

All the statistics are upto date and histograms exist on indexed columns.

Can anyone suggest how to make the code more efficient, by reducing the 1426265 row hash join which returns one row.

Regards
Ronnie
Re: SQL Query Performance [message #288773 is a reply to message #288749] Tue, 18 December 2007 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How does Oracle can know that the hash join will return one row?

You exclude index with your useless upper function unless this is your purpose but in this case Oracle has other choice than hash joining for optimising performances.

Regards
Michel
Re: SQL Query Performance [message #288780 is a reply to message #288749] Tue, 18 December 2007 11:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
and (1=1) order by openeddatetime desc

I would like to know what is the use/need of the above line. Also I am trying to understand the need of upper function this line
upper(caseref) like '109969%'
and I am not able to understand why do you need upper function here. All you are saying is get me all the records which start with 109969 and it is a number. Why do you need upper(function) and why it is nested why can't you have it in the same level (i.e) why can't you include the outer where clause inside the inner select or Am I missing something very basic here ?

Regards

Raj

[Updated on: Tue, 18 December 2007 11:40]

Report message to a moderator

Re: SQL Query Performance [message #288791 is a reply to message #288749] Tue, 18 December 2007 13:29 Go to previous messageGo to next message
rdoggart
Messages: 10
Registered: December 2007
Location: Belfast
Junior Member
Hi,

The inner select is the code from a view that is normally called, I extracted it so you could see both the outer call and the view source.

The code is generated from an application so I have limited options for making changes.

Ronnie
Re: SQL Query Performance [message #288793 is a reply to message #288791] Tue, 18 December 2007 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what do you expect from us if you can't modify the query?
Add an index on upper(caseref).

Regards
Michel
Re: SQL Query Performance [message #288928 is a reply to message #288749] Wed, 19 December 2007 01:30 Go to previous messageGo to next message
rdoggart
Messages: 10
Registered: December 2007
Location: Belfast
Junior Member
I just wanted to make sure I had not missed anything obvious, as we all make mistakes. Adding an index on upper(caseref) makes no difference as I already tried that.

Ronnie
Re: SQL Query Performance [message #288936 is a reply to message #288928] Wed, 19 December 2007 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure the index can be used?
For instance, did you gather statistics with dbms_stats on the table and index after creating it?
What is your version?

Regards
Michel

Re: SQL Query Performance [message #288938 is a reply to message #288749] Wed, 19 December 2007 01:47 Go to previous messageGo to next message
rdoggart
Messages: 10
Registered: December 2007
Location: Belfast
Junior Member
Its 9.2.0.6 and I did regather the stats after creating the index, the actual query uses '%119686%' for most of the searches so the index is excluded. I know that '119686%' would us the index, but it all depends on what the user types in for the search criteria.

Thanks
Ronnie
Re: SQL Query Performance [message #288941 is a reply to message #288938] Wed, 19 December 2007 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aaargh! You change the problem.
I give up!

Regards
Michel
Re: SQL Query Performance [message #288949 is a reply to message #288749] Wed, 19 December 2007 02:15 Go to previous message
rdoggart
Messages: 10
Registered: December 2007
Location: Belfast
Junior Member
Michel,

Thanks for your help, I relooked at the plans from the queries and noticed that when the index existed it used nested loops rather than hash joins. I put in a first_rows hint and now both are performing well.

Ronnie
Previous Topic: Query taking long time to execute
Next Topic: Query taking too much time than usual to execute
Goto Forum:
  


Current Time: Sun Dec 04 18:41:11 CST 2016

Total time taken to generate the page: 0.23714 seconds