Solved

Oracle View takes longer than the actual query

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now