Count no. of record for a specific period of time !!

[Chris Fung]

Hi All,

I would like to do some capacity planning work and therefore would like to create some automated scripts on Oracle database (8i) to get the no. of record update/inserted in descrete period e.g. 0:00 - 1:00; 1:00 - 2:00 etc from a statistics table.

The table has the following fields
- Job_id
- No. of records updated/inserted
- start_time
- end_time

It is quite simple if the job starts and stops within a perdefined range of measurement e.g. 0:00 - 1:00.

However, some of the jobs will start within the 0:00 - 1:00 range and finished in 1:00 - 2:00 range or even in 2:00 - 3:00 and so on, I would like to distribute the no. of record update/inserted on minutes basis and divided by the whole duration and then assign the appropriate % of records in different time ranges.

Any idea to accomplish this task ? Or any other suggestion for this kind of exercise !!

Much appreciated for your advice,

Cheers,

Chris

[Steven E. Protter]

There might be something useful to you in this thread.

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=228893


SEP

[T G Manikandan]

there are many utilities with Oracle which can be very helpful in analysing/planning.

For analysing you can use the SQL TRACE utility.
THere are many scripts which are inbuilt such as
UTLESTAT.SQL
SQLUTLCHAIN.SQL
UTLDTREE.SQL inside the $ORACLE_HOME/rdbms/admin dir.

Also there is a utility called capacity planner which comes along with the Oracle Diagnostics pack.

check the metalink for more information on that.
Do check the attache as what is capacity planner and how to use it.

Revert

[Yogeeraj_1]

hi,
-
Please clarify further (with a more detailed example if possible)
-
Do you want to write a SQL statement to count the number of records or write a package to perform this operation?
-
thank you
Yogeeraj

[Chris Fung]

Hi there,

I think I am looking for a simple sql to solve this problem. However, not sure a simple SQL can fullfil the requirement.

Details:

We are running data warehouse and the night batch will be started on the midnight and will be finished around the morning (9:00) on average.

Since we are going to measure the loading of the system. We will capture the system statistics like CPU, memory and disk I/O (vmstat, sar iostat etc)throughout the whole data loading period.

In the same token, we have to associate how many data has been updated/inserted/delete during the data loading process (The whole cycle of the data loading process is divided into many small jobs and will be started when the required source files are ready - the readiness of source files is fluctuated from day to day) and in decrete period (This is the period of measurement) of time. e.g. start time > 0:00 and end time <= 1:00; start time > 1:00 and end time <= 2:00 etc.

In some cases, when the job is started in one measuring period (e.g. >0:00 and <=1:00) and it may finished in another measuring period (e.g. >2:00 and <=3:00 or even >3:00 and <=4:00 etc). Now I have difficulties in how this can be done through either simple SQL or procedure / package.

Many Thanks,

Chris,

[Paddy_1]

Your tables of interest to poll would be
(a)system.aud$
(b)V$ tables

Create a trigger on system.aud$ by turning on auditing. Then grab the number of records from V$SESSION table.

Just a thought.

[prasad_15]

Hi,

to record the insert progress what i usually do is i keep running an insert into a temporary table from another session while putting a dbms_lock.sleep(t) . and then just query the temporary table as below

select l_cnt,next_idn,to_char(l_tme,'mm/dd/yyyy hh24:mi:ss') date1, (next_idn-l_cnt) row_s
from
(select l_cnt,l_tme,lead(l_cnt) over(order by l_tme) next_idn from p4k_1 )
where (next_idn-l_cnt) <>0


if you have audit columns on the table you can implement it in similar way for update .

Hope this helps .

Cheers!