Re: Row Count

Hi Bill,

I have experience only on large Unix based systems with about 200 G distributed database. Nevertheless, I am mentioning some points which you might find useful.

1. As part of monitoring, we periodically log jobs running on the databases into tables. All jobs consuming large amount of resource invariably get logged. Logging consists of the elapsed time, CPU time etc. the job took. If a particular program/job repeats, the history can be used to provide an idea of the resources it is going to require. b) If it is only ad-hoc queries, they can only be submitted from thru a common interface. The interface can do an explain plan and mention the cost( I am assuming that you are running a CBO).However I admit that sometimes the cost may not be a correct indicator of actual resources reqd by the query. c) The resources being consumed by a job can be monitored using the v$ tables - viz. cpu consumed, no. of rows accessed, no. of full tables scans, no. of consistent reads, no. of buffers used etc. Once a particular job crosses a defined threshold, an alert can be flashed to the administrator who can initiate corrective action. This information can also be logged in (a). However this information is available only when the job is actually running. But over a period of time, history will be built up and some idea can be obtained regarding the resources reqd.

I hope this helps. Send me an e-mail if you need more information.

All the best and take care.

Ajit
Sr. System & DBA

In article <34D41FEE.5AB8_at_erols.com>,
  Bill Bollhorst <bbollhorst_at_erols.com> wrote:
>
> The project I am currently working on has a requirement to provide
> feedback to the user about the query that (s)he has sent. This
> feedback consists of the number of rows that satisfy the query and
> the amount of time to satisfy the query.
>
> The customer's idea is that if the user sends a large query and
> they are informed about it ASAP, they can take action to abort the
> query or let it run in the background.
>
> Personally I laughed when I saw these. :-) How do you get the
> number of rows and/or time without actually doing the query?
> If you are going to waste the time counting the rows, just
> do the query.
>
> If there is an answer or a compromise to address this type of
> requirement I would appreciate an opinion.
>
> Thanks in advance,
>
> Bill

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet