Xref: alice comp.databases.oracle.server:46213 Path: alice!news-feed.fnsi.net!netnews.com!newspeer.monmouth.com!cyclone.rr.com!elnws02.ce.mediaone.net!24.131.128.13!elnws01.ce.mediaone.net.POSTED!not-for-mail From: "v tsien" Newsgroups: comp.databases.oracle.server References: <371a97e5.5762168@NEWS.FLASH.NET> Subject: Re: ---SQL Tunning Question Lines: 49 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Message-ID: <2N3S2.1633$Dz5.7902484@elnws01.ce.mediaone.net> Date: Sat, 17 Apr 1999 12:42:01 -0500 X-Trace: elnws01.ce.mediaone.net 924371134 24.131.160.32 (Sat, 17 Apr 1999 12:45:34 CDT) NNTP-Posting-Date: Sat, 17 Apr 1999 12:45:34 CDT Organization: MediaOne Express -=- Central Region 1. Do a statistics first 2. Test the query with sqlplus first and find out the initial response time and access path by using set timing on and set autotrace on 2. Check if the join uses indexes or tablespace scan, if not how many indexes each table is using and if it is feasible to add an index (it is a balance work). 3. find out how large the tables are 4. check the pattern of the table update and inserts and find out if the pctfree and pctused can be changed can be best met for the business requirement. 5. Check if the initial extent can hold all the data to reduce the io 6. Check if there is index tablespace scan in addition to tablespace scan, if so either avoid index tablespace scan or if the data is mininum move the data into index and do index only 7. If table scan is neccesary, change the parameters in initsid.ora to enable multi_block_read 8. If memory is not enough, add memory. 9. If the tables are small and immediate response is required, move teh tables into the memory. 10. OS level tuning. wrote in message news:371a97e5.5762168@NEWS.FLASH.NET... > Hi Experts, > > I'm having a performance problem with a query and I would appreciate > any help you could provide. > > The query generates a report when called from PowerBuilder. This > query contains 15 select statements with the UNION set operator. > Each query joins 6 tables with the exception of the first which joins > 3. All of them use multiple user defined functions and standard > functions. This is being run against a 7.3 database. > > When run, it takes approximately 25 seconds to execute while returning > only 466 rows in a development environment. My questions are: > > 1. Is there any way to improve the performance of this query? If so, > can you please suggest? > > 2. If not, is there another way that I could achieve the same results > without crippling the database? Again, suggestions are appreciated. > > Any help that you could provide would be greatly appreciated. Please > email me with your thought. Thanking you in advance for your time and > efforts. > > Rick