Solved

Oracle View takes longer than the actual query

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Repeat query 13 71
Oracle - SQL Query with Function 3 65
Oracle DB monitor SW 21 71
Read XML values 8 52
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

679 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