Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Performance Problems

Re: Performance Problems

From: Tanel Põder <>
Date: Thu, 23 Mar 2006 20:08:16 +0200
Message-ID: <0b4501c64ea4$df4b3990$eb3bfea9@porgand>


see the execution plan of your query and check your dba_data_Files view text. for example in 10.2 the dba_data_Files selects from v$dbfile which in turn selects from x$kccfn which always does physical IOs against controlfile. if your execution plan happens to use such x$ table as the inner table in a nested loop join you might need to do excessive physical IOs against your controlfiles (or datafiles in some cases).

make sure that no x$kcc% and x$ktf% table is used as inner table in NL join in the execution plan (or if it is, then a fixed table index is used for access - you should see stuff like "X$KCCFE (ind:1) " in dbms_xplan output if an index is used).

don't analyze your data dictionary just like that in your db, you might have some bad surprises when parsing new queries later on. but if you are going to analyze DD, you should also analyze X$ tables with dbms_stats.gather_fixed_object_stats or using gather_table_stats on relevand x$ tables individually. gather_schema_stats on SYS schema doesn't do fixed tables in 9i as far as I recall. this could be important especially in your case with 30k datafiles.


> We have a large datawarehouse database on Oracle and are encountering
> strange performance problems. A query such as "select count(*) from
> dba_data_files;" will run in a few seconds or as long as 15 minutes
> depending on when this is run. We've narrowed it down to where it is
> not even directly related to load on the DB. A few days ago, I ran this
> query right after we started the DB (no activity in the DB at all) and
> it took 6 minutes! The waits we were seeing were "db file sequential
> read" and "buffer busy wait" for this query. Keep in mind our DB is
> ~27TB with ~30,000 datafiles!
> Oracle on Solaris 10.
> Any help will be appreciated.
> Thanks!
> --

Received on Thu Mar 23 2006 - 12:08:16 CST

Original text of this message