How much more memory is needed by 11gR2 DBConsole

When assessing database upgrade, we'll need to know actual overhead need for newer version, which may not be found from Oracle document, or accurate enough for our own environment.  e.g, in order to run 11gR2 database, my finding for development is 500Mb and production is 800Mb. The 300Mb (if I remember correctly) in oracle document may easily lead to ORA-4031.

Today did the experiment on four DBConsoles , comparing 10.2.0.2 and 11.2.0.2.
The following shows the figure of Total Memory (VM) and Residential Memory(User memory) using HP-UX 11i IA v3 glance utility,  when I shutdown 4 DBConsoles one by one.

shutdown 10g dbconsole	init	1 Console	2 Consoles	3 Consoles	4 Consoles	Average
Total VM(Gb)	22.5	21.8	21.3	20.7	20	0.625
user memory (Gb)	10.6	10.4	10.2	10	9.7	0.225
shutdown 11g dbconsole	init	1 Console	2 Consoles	3 Consoles	4 Consoles	Average
Total VM(Gb)	27.8	26	24.2	22.5	20.6	1.8
user memory (Gb)	16.4	15.9	15.3	14.8	14.3	0.525

 

From above tables, it is clear that 11.2.0.2 DBConsole is almost 3 times than that in 10.2.0.2 , both total memory and physical memory.

For those doing capacity planning, please don't take for granted and overlook harware requirement by different version.

Install Cygwin Xserver

Xserver Hummybird Exceed is funny, after launch it, I can't open multiple x-windows -- they overlap (the first window is right under 2nd one, thus can't interactive it) , e.g running dbua to upgrade multiple databases in parallel to save downtime.  Hence, I think of using Cygwin/X to avoid install x11, which is easy without any license issue.

1. Open Cygwin setup application, install X11 package with default choice.
2. Type "starxwin" to launch X server
3. In the terminal , type "xhost + " to allow all connections from any
computers. (This step is important)
4. In putty session, export DISPALY=< x-server ip:0 >
5. For testing purpose, test launch "xclock"

DBA management

Is pursuing MBA a good idea to achieve vertical growth for a DBA professional? | LinkedIn

11g Real Time SQL monitor

V$SQL_MONITOR

V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.
When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.

V$SQL_PLAN_MONITOR

V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. As with V$SQL_MONITOR, statistics exposed in V$SQL_PLAN_MONITOR are generally updated every second when the statement executes. These statistics are recycled on the same basis as V$SQL_MONITOR.
To eliminate the overhead of SQL plan monitoring, statistics collected for each operation of the plan don't record timing information such as elapsed time, CPU time, or I/O time. Instead, this timing information can be estimated quite accurately by joining V$SQL_PLAN_MONITOR with V$ACTIVE_SESSION_HISTORY on SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, and SQL_PLAN_LINE_ID (simply named PLAN_LINE_ID in V$SQL_PLAN_MONITOR). The result of that join is a sample of the activity performed by each operation in the plan, from which an estimate of CPU time and wait time can be derived.

What is the retention of ASH

Oracle collects Active Session History (ASH) statistics (mostly wait statistics for different events) for all active sessions every second from v$session and stores them in a circular FIFO buffer in the SGA.
ASH records are very recent session history within the last 5-10 mins. (This may not true, it depends on how busy the database it is. I show this later).
The MMNL (Manageability Monitor light - shows up as "Manageability Monitor light 2" process) process, if the buffer fills up before the AWR flushes (by default every hour) the MMNL process will flush the data (in v$active_session_history) to disk (data stored in dba_hist_active_session_history). The is column called IS_AWR_SAMPLE to indicates whether this sample has been flushed or will be flushed to the AWR DBA_HIST_ACTIVE_SESS_HISTORY) (Y) or not (N)

ASH resides in the SGA and it’s size is calculated by the lesser of:

    * total # of cpu x 2MB memory
    * 5% of shared pool

SYS@TT> select min(sample_time), max(sample_time) from v$ACTIVE_SESSION_HISTORY ;

MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
17-APR-11 11.43.44.860 PM
18-APR-11 02.49.08.124 PM

LIQY: Near 15 hours' data in memory.


SYS@TT> select min(sample_time), max(sample_time) from sys.WRH$_ACTIVE_SESSION_HISTORY ;

MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
MAX(SAMPLE_TIME)
---------------------------------------------------------------------------
16-AUG-10 10.37.41.345 AM
18-APR-11 02.00.07.434 PM
SYS@TT> select  min(end_interval_time ), max(end_interval_time )  from dba_hist_snapshot ;

MIN(END_INTERVAL_TIME)
---------------------------------------------------------------------------
MAX(END_INTERVAL_TIME)
---------------------------------------------------------------------------
16-AUG-10 12.00.49.868 PM
18-APR-11 03.00.46.004 PM

LIQY:
1.) DBA_HIST_ACTIVE_SESSIONS_HISTORY is a view built on sys.WRH$_ACTIVE_SESSION_HISTORY.
2.) Observer that Historical ASH (on disk) is almost the same as ASH snapshots.


Testes that suceeds to generate ASH report within AWR retention, while fails to do so when the slot beyonds AWR retention.  Hence, I think ASH retention is same as AWR's.



References:
Active Session History (ASH)

More active with Twitter laterly

Busy with 11g R2 upgrade...