Re: sqllder hangs at 99-100% CPU on select of fet$

Responses inline:

> In short, what you see now are the dire consequences of years of
> neglect. (I won't even mention that you wouldn't have had the issue
> when you would have upgraded to 8i, and would have set up locally
> managed tablespaces)

Upgrading to 9i is my sole task for the rest of the year, aside from fixing this problem. You say "years of neglect", what maintenance should be done to avoid this problem in the future? I inherited this database.

> You need to investigate the fragmentation of that tablespace, the
> number of extents of the affected table in relation to it's size.

Is there a desireable extent/size ratio for tables? The pctincrease on this tablespace is 0.

> You can't also escape from export/import for that table, as it is the
> only method in 7.3 to get rid of the chaining issue.

Ugh. I believe that is indeed necessary but this table has (at the latest count) 80 million rows. :p

> You can issue the command
> analyze table <table_name> list chained rows into <chain table>
> provided you run $ORACLE_HOME/rdbms/admin/utlchain.sql to set up a
> chain table prior to analyzing it.

I'll look into this. Thanks.

> You can also ANALYZE table <table_name> compute statistics to
> determine the size of the average record, in comparison to the
> database block size. These statistics will end up in user_|dba_tables.

We did this in the afternoon and the fields you mention for that table are:

TABLE_NAME                      CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- -----------
XXXXXXXX                         10886334          41

The db_block_size is 4096. Can this be changed? What units is the avg_row_len in?

> Finally you'll need to convince your boss to send you to an
> administrators course.

What do you think of the self-train CDs from Oracle University? We have ordered Oracle 9i DBA Fundamentals I for now. We plan to go to part II if the CDs work, otherwise on-line classes will be utilized. I think in-class training, while being the most preferred, will be the toughest to achieve given that I am _the_ "DBA", and the nearest in-class center is 2.5 hours away.

> You seem to be completely clueless, and you are
> just lucky you have survived so far.

While I wince at reading it, I can't deny that I have much to learn. I had never touched Oracle before this position, and have been learning both Oracle and database administration along the way, mainly by O'Reilly books and newsgroups. We are at the point now where actual Oracle training is definitely in order and we are taking steps as I've mentioned above. Any advice on training/reading you offer will be gladly accepted. Received on Tue Sep 09 2003 - 18:05:26 CDT