Retrieving Oracle internal information

The below query will give you information on many of the internals such as no of base events ,number of latches and information on DBWR processes.


select KVIIDSC,KVIIVAL from X$KVII;


KVIIDSC                                                             KVIIVAL
---------------------------------------------------------------- ----------
# of base events                                                       1115
# of base events in session                                       400
number of latches                                                    535
event range base                                                      12625
initial number of CPUs in the system                     4
DBWR max outstanding writes                              4096
number of DBWR processes                                   1
DBWR write chunk                                                 204
true if Statically Allocated Thread                           1
THRead mounted by this instance - zero if none     1
sga shadow value of instance_number                     1

11 rows selected.



The below query shows the information such as HWM,CPU info etc.

15:00:01 SQL> select KVITDSC,KVITVAL from X$KVIT;

KVITDSC                                                                                         KVITVAL
----------------------------------------------------------------                      ----------
number of logical CPUs in the system used by Oracle                           4
number of physical CPU cores in the system used by Oracle                 2
number of physical CPU sockets in the system used by Oracle              1
high water mark of number of CPUs used by Oracle                              4
high water mark of number of CPU cores on system                              2
high water mark of number of CPU sockets on system                           1
number of available CPUs in the system                                                 4
CPU dynamic reconfiguration supported                                                1
number of buffers                                                                                   63680
large dirty queue if kcbclw reaches this                                                 25
Max percentage of LRU list foreground can scan for free                    40
Initial percentage of LRU list to keep clean                                          2
number buffer objects                                                                            750
Flag that indicates recovery or db suspension                                        0
Error Log Number for thread open                                                         0
SGA: opcode for checkpoint cross-instance call                                    0
SGA:opcode for pq checkpoint cross-instance call                                0

17 rows selected.

Hope this helps .