Solved

Oracle View takes longer than the actual query

Posted on 2014-11-04
7
476 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 49

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

705 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