| Can dictionary tables get corrupt ? [message #49621] |
Thu, 31 January 2002 04:32  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
The Subject says it all, can dictionary tables get corrupted ?
I have a query which looks into all_indexes and all_ind_columns. The same query runs with a snap in prod, while takes A LOT of time in development. Both database are 81620 on the same server, with the same parameters except for the optimzer_mode which is 'choose' for prod and 'all_rows' for dev ?
|
|
|
|
| Re: Can dictionary tables get corrupt ? [message #49622 is a reply to message #49621] |
Thu, 31 January 2002 05:36  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
I got the solution. Went to Metalink and did some research.
Dictionary tables ALWAYS run best on rule_based optimization. So when my dev db was put into optimization_mode=all_rows, all queries involving dictionary tables took a LONG time. Same query in prod (in choose mode) was running in seconds.
Solution:
Chnaged the query and added hint
/*+ choose */
and all went well, results back in seconds.
That's it. Thought I would share it with other DBA's.
Conclusion:
The dictionary was NOT corrupt, my initial guess. What if it really get's corrupt ? Geez ...
Avoid using cost_based optimizated queries for dictionary tables. Always use rule_based optimization for dictionary tables.
NOTE: Metalink mentioned analyzing tables, but not recommended for sys tables (dictionary tables).
|
|
|
|
|
|
| Re: Can dictionary tables get corrupt ? [message #49680 is a reply to message #49655] |
Mon, 04 February 2002 03:24 |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Metalink is the Web site of Oracle where they have all tech support docs. I became a member with the CSI number my company has. I do not know how else you get in there.
In fact, Oracle tech support encourages you to do everything in Metalink, including creating TARs. Most of the times, you are better off searchig the Metalink site and getting answers/posts, than to actually open a TAR and get help from them. When you open a TAR, they respond to it and you can go and check them as needed. Then you can also put feedbacks there.
Good Luck.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
