Re: internal queries?

d.a. wrote:
>
> 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

You may get some benefit from a larger pool because peoplesoft has so many gazillions of tables et al (most of which of course never get used). It would help if you posted your current init.ora :-)

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."