• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

Oracle View takes longer than the actual query

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
CCraneCompany
Asked:
CCraneCompany
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
ajexpertCommented:
I second slightwv's comment.
In addition, could you also check the explain plan for view and plain sql?

Thanks
0
 
PortletPaulCommented:
"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!

 
sventhanCommented:
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
 
CCraneCompanyAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
CCraneCompanyAuthor Commented:
I will accept both of your answers as it was a combination of buffer cache and the explain plan.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now