| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Anyway to accurately predict time for long running queries?
i dont have any test runs to do. It has to work the first time. I have alot
of data to load. I was hoping for a methodology for calculating a rough
estimate for long running queries.
there has to be a way to calculate an estimate. The hard part is the
estimate of how many logical and physical I/Os.
----- Original Message -----
From: "DENNIS WILLIAMS" <DWILLIAMS_at_LIFETOUCH.COM>
To: <oracle-l_at_freelists.org>
Sent: Tuesday, February 10, 2004 3:55 PM
Subject: RE: Anyway to accurately predict time for long running queries?
> Ryan - I'm confused. If you've done some test runs and can project the
total
> times from those runs, then why would you need stuff like longops?
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
> -----Original Message-----
> From: Ryan [mailto:ryan.gaffuri_at_cox.net]
> Sent: Sunday, February 10, 2002 2:39 PM
> To: oracle-l_at_freelists.org
> Subject: Re: Anyway to accurately predict time for long running queries?
>
>
> longops doesnt appear to predict delete statements. It's pretty inaccurate
> if you have to do sort-merges(I've seen the blocks go to double the total
> predicted). An interesting thing about v$session_longops is that when you
> query the 'message' column you often see 'sort merge' or some such and
your
> not doing a sort merge join.
>
> For some reason the time_remaining field is not populated in this instance
> and I can't figure out why... TIMED_STATISTICS is on, but I dont know if
> that affects it. The annoying part about v$session_longops is that it is
> persistant and SIDs get reused, so if i go 'give me all the data in the
> message column for a given sid', I can get old data.
>
> These are just one time large batch loads that need to get done before
> development can continue. It's gigabytes worth of data. So I can
understand
> when people go 'when can you have this done, so I can get started on my
> stuff'. I really don't have an answer for them.
>
> I've run a few so far and I can guess based on how long those have taken
to
> run. I've noticed on large batch loads partically CTAS and inserts its
more
> important that you know the number of bytes you are going to 'push' than
the
> number of records. This is when I find it useful to compact tables with 99
> pct_free and 1 pct_used, saves alot of time both on the read and the
write.
>
> I have tom kyte's books, I don't remember him thoroughly discussing
> v$session_longops. I'll take another look.
> ----- Original Message -----
> From: "Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com>
> To: <oracle-l_at_freelists.org>
> Sent: Tuesday, February 10, 2004 2:18 PM
> Subject: RE: Anyway to accurately predict time for long running queries?
>
>
> > And in addition,
> >
> > you may also save some performance related data (lio count, pio count, =
> > time taken, total time taken in seconds) from important queries (every =
> > time such queries are run) in a history table and it will help you see =
> > patterns. I'll probably be doing something similar pretty soon for some
=
> > jobs that *must* run in between 2-3 minutes, >5 is we need to raise an =
> > alarm. It is still in discussion stage though ... =20
> >
> > Run is about 100-500 times on your system at random times with different
=
> > loads on your system. Once you have the counts, you'll get closer to =
> > predicting. I'd probably put a disclaimer that the times provided are =
> > estimates only ... Be prepared to build some instrumentation into your
=
> > code.
> >
> > Tom Kyte has a good chapter on how to use longops ... it is better than
=
> > Metalink doc I saw IMO, YMMV.=20
> >
> > ps: The name is Cary not Carrie ...=20
> > Raj
>
> -------------------------------------------------------------------------=
> > -------
> > Rajendra dot Jamadagni at nospamespn dot com
> > All Views expressed in this email are strictly personal.
> > QOTD: Any clod can have facts, having an opinion is an art !
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap
> > Sent: Tuesday, February 10, 2004 12:02 PM
> > To: oracle-l_at_freelists.org
> > Subject: RE: Anywy to accurately predict time for long running queries?
> >
> >
> > Ryan,
> >
> > A couple of things to think about....
> >
> > Count the LIOs and PIOs that a job requires. This will probably be some
> > function of the number of rows the job processes. Compute the average
> > LIO and PIO latencies for your system. Use the rough response time
> > estimate R =3D est_LIO_count * avg_LIO_latency + est_PIO_count *
> > avg_PIO_latency.
> >
> > If you're *writing* the app yourself, then consider using the Oracle
> > long-ops stuff. I can't remember how to access it (and my network
> > connection is too slow to look it up right now). For example, if you
> > have a PL/SQL loop that goes through something N times, then you update
> > the progress meter every so often (say on iterations N/10, 2N/10, 3N/10,
> > ...) so your user will know approximately how far along the program has
> > gotten.
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > * Nullius in verba *
> >
> > Upcoming events:
> > - Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle
> > - SQL Optimization 101: 2/16 Dallas
> > - Hotsos Symposium 2004: March 7-10 Dallas
> > - Visit www.hotsos.com for schedule details...
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ryan.gaffuri_at_cox.net
> > Sent: Tuesday, February 10, 2004 7:10 AM
> > To: oracle-l_at_freelists.org
> > Subject: Anywy to accurately predict time for long running queries?
> >
> > People here want me to accurately predict how long it will take long
> > running SQL to execute. I'm reading carrie milsap's book now, and I
> > believe he has some methods that give good estimates on overall
> > performance(I have not gotten to that part yet).
> >
> > any known methods to accurately predict sql? BTW, I dont have specs on
> > the hardware, its a customer site... and yes I know you really need
> > that.=20
> >
> > I dont think this is doable, but im wondering if anyone has put together
> > any papers on the subject.=20
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Feb 10 2004 - 15:49:08 CST
 |
 |