Re: Performace question

Hi from Paris

I would suggest to check PCTFREE and PCTUSED values, and eventually extents size...

I mean:
- this is definitely a temporary table (from the application view) - try to know the max rows this table has to handle -> this is the high water mark, the table should be of 1 extent with the required size - are the records only inserted then deleted ??? then : PCTFREE 0 and PCTUSED 99 this will avoid wasting space

...

"Sybrand Bakker" <gooiditweg_at_sybrandb.verwijderdit.demon.nl> a écrit dans le message de news: gs3gu0lfdj5l4c3pjphd63q66nmq08pkgh_at_4ax.com...
> On Fri, 14 Jan 2005 22:11:14 +0800, "music4" <music4_at_163.net> wrote:
>
>>Greetings,
>>
>>My program access Oracle database via OCI. But today I found a strange
>>that
>>a Oracle process occupy more than 10% CPU usgae, and IOWAIT is also higher
>>than 10%. Then I found the Oracle process always access table A. Then I
>>use
>>sqlplus to check "select count(*) from table_a", it took several seconds,
>>but there were only less then 10 records in it. After I do a "truncate
>>table
>>table_a", the Oracle process's CPU usage get down to less than 2% and
>>IOWAIT
>>get down to less then 1%.
>>
>>Could you please figure out why that happened, and how to solve the
>>problem
>>without truncate table. (Since I could not perform truncate at real
>>in-service site.)
>>
>>Thanks in advance!
>>Evan
>>
>
> assumption 1: the number of records in the affected table widely
> varies. It is an intermediate table only.
> assumption 2: the affected select performs a full table scan (fts).
> A fts will always traverse the table until the high water mark.
> The high water is not reset, when records are deleted.
> Truncate table empties a table by resetting the high water mark.
> (Actually that is the only thing it does).
> Got the picture?
> One solution is to upgrade to 10g, in 10g you can coalesce a table
> without truncate and at the same time reset the high water mark.
> The other solution is to redesign the process
> The third solution is to implement periodic rebuilds of the affected
> table.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Sat Jan 15 2005 - 09:10:32 CST