Re: Performace question

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