Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anyway to accurately predict time for long running queries?

RE: Anyway to accurately predict time for long running queries?

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 10 Feb 2004 14:55:08 -0600
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D607AFFF03@EXCHMN3>


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
-----------------------------------------------------------------
Received on Tue Feb 10 2004 - 14:55:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US