Re: System Tables with the Highest IO

Hi from Paris

the tables you mentioned are fixed tables, used by Oracle to store list and structure of indexes, objects and users....

Theses tables are only updated by recursive SQL, which is generated whenever you issue DDL statements. SYS objects are also read at the database startup, we expect most of these to be put on the dictionnary cache for instance lifetime.

Is your shared pool big enough ? check 'free memory' on V$SGASTAT, if no lines or less of 1% of the shared pool, then it is too small. Dumb / minimal values for SHARED_POOL_SIZE:

Oracle 7.0 -> 10 Mo
8.0 -> 20 Mo
8i -> 50 Mo
9i -> 100 Mo

Also, it could be your applications are doing a strong use of DDL statements, which is not a very good idea in a production system, and which is not a scalable way of working.

I suggest you take instance statistics (either with utlbstat/estat or PERFSTAT), and check the SYSTEM datafile is used, check the dictionary cache statistics, and have a look at "invalidations" on the library cache statistics. This will confirm you are doing DDL.

Then, you could see what these DDL are, by dynamically viewing the V$SQLAREA view, for requests with
PARSING_SCHEMA_ID equal to 1 (sys)

Cdlt

"michael ngong" <mngong_at_yahoo.com> a écrit dans le message de news:ecf365d5.0305070345.6bc9df58_at_posting.google.com...
> Checking the tables with the highest IO and it turned out the system
> tablespace had the highest io.On a drill down the culprits were
> ind$,obj$ user$ system tables .
> I am looking into a way to address this so far no concrete steps
> arrived at.
> Ideas,clues tips will be appreciated
>
>
>
> Michael Tubuo Ngong
Received on Wed May 07 2003 - 07:03:13 CDT