|
Oracle FAQ
|
Your Portal to the Oracle Knowledge Grid
|
Home ->
Community ->
Usenet ->
c.d.o.server ->
Re: ---SQL Tunning Question
Re: ---SQL Tunning Question
- Do a statistics first
- 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
- 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).
- find out how large the tables are
- 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.
- Check if the initial extent can hold all the data to reduce the io
- 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
- If table scan is neccesary, change the parameters in initsid.ora to
enable multi_block_read
- If memory is not enough, add memory.
- If the tables are small and immediate response is required, move teh
tables into the memory.
- OS level tuning.
<BAMBAM6235_at_AOL.COM> wrote in message
news:371a97e5.5762168_at_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
Received on Sat Apr 17 1999 - 12:42:01 CDT
Original text of this message
