Solved

Oracle View takes longer than the actual query

Posted on 2014-11-04
7
452 Views
Last Modified: 2014-11-05
I have created an Oracle view to gather data from multiple tables.  Needless to say, the query is rather complex and perhaps not the cleanest.

SELECT DISTINCT C.CUSTNO, C.FNAME, C.MI, C.LNAME, C.STREET, C.COUNTRYCODE, C.ZIP, C.CITY, C.STATE, S.SHIPMETHOD, S.FULLORDERNO, C.NAMEX, A.REF1, A.REF2, P.DAYPHONE
FROM ECOMTEST.ORDERSUBHEAD S, ECOMTEST.ORDERHEADER H, ECOMTEST.CUSTOMERS C, ECOMTEST.CUSTOMERADDL A, ECOMTEST.CUSTOMERPHONE P
WHERE S.SHIPMETHOD = '40'
AND SUBSTR(H.FULLORDERNO, 1, 8) = S.ORDERNO
AND NOT SUBSTR(H.FULLORDERNO, 1, 8) IN (
  SELECT DISTINCT SUBSTR(X.FULLORDERNO, 1, 8)
  FROM ECOMTEST.ORDERSUBHEAD S, ECOMTEST.ORDERXREF X
  WHERE S.SHIPMETHOD = '40'
  AND X.SEARCHTYPE = 'OS'
  AND SUBSTR(X.FULLORDERNO, 1, 8) = S.ORDERNO)
AND C.CUSTEDP = H.CUSTEDP
AND P.CUSTEDP(+) = H.CUSTEDP
AND A.CUSTEDP(+) = H .CUSTEDP

Anyway, the view returns the rows correctly but it is taking around 46 seconds to return the values.  If I run the view's SQL query straight in SQL Developer, the rows return in about 7 seconds.  Any idea why this is?
0
Comment
Question by:CCraneCompany
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40423118
The view just runs the select. There really isn't any difference.

Assuming you are running both selects in sql developer (sql developer buffers the data):
Are you comparing apples to apples?

If not, run both from sqlplus and compare.

The first time you run the query the blocks are loaded into memory.  The second time you run it, the blocks are in memory so you are saving the disk reads.

To compare everything the same, flush the buffer cache between runs.

As a privileged user:
alter system flush buffer_cache;

Possibly the results cache:
exec dbms_result_cache.flush;
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 250 total points
ID: 40423198
I second slightwv's comment.
In addition, could you also check the explain plan for view and plain sql?

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40423577
"select distinct" is the enemy of performance

outer joins don't help either

WHERE .... IN ( big list of stuff here ) is another possible performance problem
AND NOT SUBSTR(H.FULLORDERNO, 1, 8) IN
                                        ( SELECT DISTINCT SUBSTR(X.FULLORDERNO, 1, 8)
                                        FROM ECOMTEST.ORDERSUBHEAD S
                                           , ECOMTEST.ORDERXREF X
                                        WHERE S.SHIPMETHOD = '40'
                                        AND X.SEARCHTYPE = 'OS'
                                        AND SUBSTR(X.FULLORDERNO, 1, 8) = S.ORDERNO
                                        )

Open in new window


filtering and joining on substrings  is a problem unless you have function based indexes
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 18

Expert Comment

by:sventhan
ID: 40423844
Is your Server and the Client machine are all located in the same site? If yes then you would not see much difference. Both view and SQL should get you the same execution time.
0
 

Author Comment

by:CCraneCompany
ID: 40424522
Thank you for your responses and here are some answers to your questions

Server and Client are on the same network
I understand that DISTINCT, JOINS and WHERE IN are performance killers.  If you have a better solution for these I would be happy to consider it
While views are supposed to execute the same SQL, the execution of views is different than the actual SQL
SELECT * FROM ECOMTEST.SUREPOST_BILLTO_TEST
SELECT DISTINCT C.CUSTNO, C.FNAME, C.MI, C.LNAME, C.STREET, C.COUNTRYCODE, C.ZIP, C.CITY, C.STATE,...

I ran the SQL Tuning Advisor on the SQL query and that got me to around 7 seconds on the actual SQL Query.  Running the SELECT * FROM ECOMTEST.SUREPOST_BILLTO_TEST (running the view) gave me the results in 47 seconds.

After thinking about it all night, I realized that I had not run the SQL Tuning Advisor on the actual view.  Doing so, Oracle generated an execution plan to follow.  Executing the suggested plan gave me the results from the View in under 5 seconds.

But that still leaves the question.  Why does a View not return the values within the same (or near same) time frame than the actual query.  Especially if an execution plan was already performed for that query.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40424531
Are you using stored plans?

I maintain the view and the table should be the same.  Stored plans can affect the optimizers abilities some.

My money is still on caching or buffering from sql developer.

From sqlplus:
flush the buffer cache and results cache
run the query
flush the buffer cache and results cache
run the view
--then reverse them in case something is left around:
flush the buffer cache and results cache
run the view
flush the buffer cache and results cache
run the query
0
 

Author Closing Comment

by:CCraneCompany
ID: 40425016
I will accept both of your answers as it was a combination of buffer cache and the explain plan.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question