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@xxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx > > -----Original Message----- > From: Ryan [mailto:ryan.gaffuri@xxxxxxx] > Sent: Sunday, February 10, 2002 2:39 PM > To: oracle-l@xxxxxxxxxxxxx > 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@xxxxxxxx> > To: <oracle-l@xxxxxxxxxxxxx> > 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@xxxxxxxxxxxxx > > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Cary Millsap > > Sent: Tuesday, February 10, 2004 12:02 PM > > To: oracle-l@xxxxxxxxxxxxx > > 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@xxxxxxxxxxxxx > > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of ryan.gaffuri@xxxxxxx > > Sent: Tuesday, February 10, 2004 7:10 AM > > To: oracle-l@xxxxxxxxxxxxx > > 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@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //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@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //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@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------