HP/UX 11.11 Oracle10g issue

[The Gunners]

Hi All,
Thought Id throw this to the floor for advice. I have an RP4410 system with HP/UX 11.11 and Oracle10g running. All seems fine until I log into the Database , and run query for example 'select * from dba_data_files;' - this should normally shoot up the screen in this type of environment , however it stalls and takes about 5 mins. sar returns 99% idle , and memory seems ok as well - any ideas - here is swap status as well - anyone advise ? thanks
swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 0 4096 0% 0 - 1 /dev/vg00/lvol2
reserve - 1788 -1788
memory 4605 757 3848 16%
total 8701 2545 6156 29% - 0 -

[Aneesh Mohan · Accepted solution]

select * from dba_data_files ,should read from Oracle base tables and provide you the result.

1) Check the Database resource manager using EM about the database load.

2)Check you shared_pool usage and hit ratio.

2) Check dbv and confirm there is no problems to the datafiles .

3) Check alert log for any errors.


Regards,
Aneesh

[Jean-Luc Oudart]

davey

did you check if there was a lock on the table (ie open another sql session and check for lock and locked_object) ?

Regards
Jean-Luc

[Aneesh Mohan]

one more thing..

how about other data dictionary views ?

dba_objects,dba_tablespaces..etc

Aneesh

[Ben Dehner]

If you are using shared servers, this could be a symptom of an artificial deadlock. In this situation, one or more processes have a record lock, and other sessions are holding the shared servers, but doing nothing in a wait state because they are caught behind cascading record locks. All of the shared server process are allocated and running but stuck doing nothing. This can be checked with the query

select name, status from v$shared_server

If this returns no rows, you aren't using shared servers, this doesn't apply. If most of the returned rows show status as WAIT (COMMON), then you are using shared servers, but they aren't hung up. If most of them show EXEC state, you've got a problem.