Re: Performance problem

From: Someone <answers_at_erols.com.tyfns>
Date: 1997/10/03
Message-ID: <3435AB32.5C26903D@erols.com.tyfns>#1/1

This is a multi-part message in MIME format.

--------------1A8C48BC09AC7B5BBB765A50
Content-Type: text/plain; charset=us-ascii
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Transfer-Encoding: 7bit

There are a number of factors to address with large query performance beyond the SQL and database objects. How much real memory (RAM) is available to the instance? You need a large amount available for any kind of performance (at least 1 Gb RAM allocated to ORACLE would be nice).

I suggest you DO NOT do the order by clause. Instead, create an external ASCII file with the result of the query and use the Unix sort command on the output to achieve the result desired for the ORDER BY. I have found in large sets of this nature that sorting externally with the Unix sort command is much faster. Pipe the output of the sorted file with awk, gawk, Perl, or your other favorite Unix scripting/programming language.

Are you experiencing I/O contention due to (1) ratio of disks to controllers, or (2) contention for controllers by disks? Either of these can contribute to the performance.

Have you hand tuned the query making sure the smaller (driver) table is on the right hand side? Are you avoiding operations on indexed columns? Are you using cost based optimizer with computed stats on the 24 M row table? (Estimated sucks! Yes, it may take all weekend to compute stats on the table, but you should do it once a month as routine maintenance to keep performance up). Are indexes efficient?

Just a few things to consider.

Enjoy!

Sheilah Scheurich wrote:

> nmk_1970_at_hotmail.com wrote:
> >
> > Hi netters,
> >
> > I have a table which consists of 24 M Rows with the data size of
> > 8 GB which is spread across 8*1 GB each. I have a need to join
> > these table with a small table of 112 rows . The small table has
> > to be joined twice with the big table to achieve the task. when I
 do
> > this it is really sucks. Also I have to do group by with 10
> > columns on this query. I have temp segment size of 1.2 GB .
> > Currently I am running oracle 7.3.2.3 on sun solaris 2.5.
> > Even after 12 hours I am not able to get the output.
> >
> > Any suggestions are welcome.
> >
> > Thanks in advance.
> >
> > /muralikrishna
> >
> > -------------------==== Posted via Deja News

 ====-----------------------


> >       http://www.dejanews.com/     Search, Read, Post to Usenet

>

> Have you tried using Explain Plan to see if you are using your indexes

>

> properly? Sounds like a cartesional join. You can also try using a
> temporary table.
> --
> Sheilah Scheurich
> DBA

>

> My opinions are my own and not that of my
> employer.

>
> To respond via email, remove underscores.

--------------1A8C48BC09AC7B5BBB765A50
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Someone
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Someone
n:              ;Someone
email;internet: answers_at_erols.com.tyfns
title:          Important
note:           Remove tyfns from my email address to reply.  TYFNS stands for "Thank you for not spamming."

x-mozilla-cpt: ;0
x-mozilla-html: FALSE
end: vcard

--------------1A8C48BC09AC7B5BBB765A50-- Received on Fri Oct 03 1997 - 00:00:00 CDT