Re: How to find no of rows

Andrew Allen wrote:
>
> "M.Balaji" wrote:
> >
> > Andrew Allen <ajallen_at_mailhost.det.ameritech.net> wrote in message news:<3D7EAB1B.AC2F5237_at_mailhost.det.ameritech.net>...
> > > "M.Balaji" wrote:
> > > >
> > > > Hi All,
> > > > When i run a SQL statement, to find out the no of rows processed and
> > > > no of times the SQL statement as executed i use the following query.
> > > >
> > > > set echo off
> > > > set feedback off
> > > > set heading off
> > > > set line 100
> > > >
> > > > select '----------------------- '|| chr(10),
> > > > 'Session Id : ' || b.sid || ' Serial# ' || b.serial#
> > > > || chr(10) ||
> > > > 'Username : ' ||
> > > > decode(b.username,NULL,'System',b.username) || chr(10) ||
> > > > 'Rows Processed : ' || a.rows_processed || chr(10) ||
> > > > 'Executions : ' || a.executions || chr(10) ||
> > > > 'Sql Statement : ' || a.sql_text || chr(10)
> > > > from v$sql a , v$session b
> > > > where a.hash_value = b.sql_hash_value and b.status = 'ACTIVE' and
> > > > (b.username is not null and b.username <> 'SYS')
> > > > /
> > > >
> > > > For the following SQL statement,with the above query, i canno see how
> > > > many rows
> > > > processed
> > > >
> > > > insert into table_a select * from table_b;
> > > >
> > > > Anyone aware of how to get the no of rows processed. Please help me
> > > > out.
> > > >
> > > > Thanks and Regards
> > > > M.Balaji
> > >
> > > I believe you want SQL%ROWCOUNT
> >
> > No it's not SQL%ROWCOUNT.
> > What i used to do i run the insert statment in one sql plus session
> > and in another sql plus session i will login and try to find the no of
> > rows it has selected or inserted into the table.(no of rows inserted
> > will be no of rows processed I guess)
> >
> > I think i have cleared your point.
> >
> > Thanks and Regards
> > M.Balaji
>
> Maby, and maby not. You see, your query will show you a specific
> sql statement, the number of times it was executed, and the total
> number of rows processed for all executions of the statement. The
> best you could do is get an average number of rows processed per
> execution -- if the number of executions is one then you will have
> the answer you want. This is not a very good way to do this and is
> not the intended or design purpose of these views. Perhaps you
> should revisit your business requirements and application design
> because what you are attempting to do will not produce reliable
> results.

sorry hit send too soon. Also, your query is looking for ACTIVE transactions. It the sql you are looking for in your query has gone inactive then you will return an empty result set.

-- 
Andrew Allen
Livonia, MI
E- Mailto:ajalle_at_ameritech.net