Re: performance problem : tkprof show many fetches with high elapsed time

Matt,

I know ! It's hard to believe but the ID column is unique. Proof just below. I would very much like to find out why the update which executes 12 times updates more than 72K records. I would expect only 12 updates since ID is unique. What is happening here ? When I run the update manually 5 times, I get in trace file 5 records. Anybody an idea why tkprof show different ?

select a.index_name,index_type,uniqueness,b.column_name from dba_indexes a,dba_ind_columns b
where a.table_name='PROFILESTOREDATA' and owner='AML0_PP' and b.table_owner='AML0_PP'
and a.table_name=b.table_name and a.owner=b.table_owner

INDEX_NAME                     INDEX_TYPE   UNIQUENESS   COLUMN_NAME
-----------------------------------------------------------------------------------------------------------------
IDX_PROFILESTOREDATA   NORMAL        UNIQUE              ID

mccmx_at_hotmail.com schreef:

> > So you too think that the update statements has updated more than 6000
> > records ?
> >
> > Benny.
> >
>
> Yes. I do.
>
> See below:
>
> UPDATE PROFILESTOREDATA SET DATA=:1
> WHERE ID=:2
>
> call count cpu elapsed disk query current rows
> ------- ----- ----- ------- ------ ------- -------- ------
> Parse 12 0.00 0.00 0 0 0 0
> Execute 12 59.22 868.48 65205 211734 176298 72221
> Fetch 0 0.00 0.00 0 0 0 0
> ------- ----- ----- ------- ------ ------- -------- ------
> total 24 59.22 868.48 65205 211734 176298 72221
>
>
> The rows column shows 72,221. If you divide that by 12 executions you
> get over 6,000.
>
> Also the following section also confirms that you are updating 6395
> rows.
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 6395 UPDATE
> 6395 PARTITION RANGE SINGLE PARTITION: KEY KEY
> 6395 INDEX UNIQUE SCAN IDX_PROFILESTOREDATA PARTITION: KEY KEY
> (object id 18090)
>
> I don't believe that the ID column is unique in that table...
>
> Matt
Received on Fri Jan 05 2007 - 02:35:52 CST