internal queries?

We have a daily script that runs at the end of the online day to show the "most expensive" sql statements executed during the day based upon an algorithm using disk reads, buffer gets, sorts and number of executions during the day. On that report we are consistently seeing the following (and other similar) sql statements come up:

select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1

select pos#,intcol#,col#,spare1 from icol$ where obj#=:1

We are assuming these are normal oracle "internal" or system queries that happen behind the scenes since the documentation says that the ccol$ and icol$ tables hold data dictionary information (ccol$: constraints for columns and icol$: indexes for columns). Anyone recognize these queries?

It is an Oracle 8.1.6 database used for a peoplesoft application with about 100 (?) concurrent users.

Does it seem out of the ordinary that the first statement is executed about 2 million times a day? If not, does anyone have any suggestions? (Do we need to up the shared pool size to help with the dictionary cache?)

Here's some dictionary cache info also:

V$ROWCACHE:

dc_constraints                          63.16    Percent Hits
dc_database_links                       99.99    Percent Hits
dc_files                               100.00    Percent Hits
dc_free_extents                         53.41    Percent Hits
dc_global_oids                          58.82    Percent Hits
dc_histogram_data                      100.00    Percent Hits
dc_histogram_data_values               100.00    Percent Hits
dc_histogram_defs                       96.65    Percent Hits
dc_object_ids                           99.55    Percent Hits
dc_objects                              96.41    Percent Hits
dc_outlines                            100.00    Percent Hits
dc_profiles                             99.98    Percent Hits
dc_rollback_segments                    99.80    Percent Hits
dc_segments                             98.35    Percent Hits
dc_sequence_grants                     100.00    Percent Hits
dc_sequences                            99.95    Percent Hits
dc_synonyms                             97.67    Percent Hits
dc_tablespace_quotas                    97.99    Percent Hits
dc_tablespaces                          99.90    Percent Hits
dc_used_extents                         46.39    Percent Hits
dc_user_grants                          99.98    Percent Hits
dc_usernames                            99.99    Percent Hits
dc_users                                99.98    Percent Hits

Thanks in advance for any and all opinions/suggestions.

-Dan Received on Mon Sep 03 2001 - 20:08:42 CDT