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.

Play with rowid_info

PROCEDURE ROWID_INFO
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
ROWID_TYPE NUMBER OUT
OBJECT_NUMBER NUMBER OUT
RELATIVE_FNO NUMBER OUT
BLOCK_NUMBER NUMBER OUT
ROW_NUMBER NUMBER OUT
TS_TYPE_IN VARCHAR2 IN DEFAULT



SQL> select object_id , object_name from dba_objects where owner='PERFSTAT' and object_name like 'TMP%';

OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
283365
TMP_LOG_HISTORY



SQL> select rowid from perfstat.TMP_LOG_HISTORY;

ROWID
------------------
AADNUcABYAAAXSKAAA


SQL> set serveroutput on
SQL> l
1 declare
2 my_rowid varchar2(200);
3 ts_type_in VARCHAR2(200);
4 rid_type number;
5 obj_num number;
6 file_num number;
7 block_num number;
8 row_num number;
9 begin
10 my_rowid:='AADNUcABYAAAXSKAAA';
11 ts_type_in:='SMALLFILE';
12 --dbms_rowid.rowid_info(my_rowid,rid_type, obj_num, file_num, block_num, row_num);
13 dbms_rowid.rowid_info(my_rowid,rid_type, obj_num, file_num, block_num, row_num,ts_type_in);
14 dbms_output.put_line ('object id is :'||to_char(obj_num));
15* end;
SQL> /
object id is :840988

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> select object_id,object_name from dba_objects where data_object_id=840988 ;

OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
283365
TMP_LOG_HISTORY




-- note taht data_object_id is not the same as object id

Play with hao_show_space and table reorganization

--get hao_show_space from http://space.itpub.net/15415488/viewspace-609531

SYS@XE> insert into t1 select * from dba_objects;

14582 rows created.

SYS@XE> commit;

Commit complete.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................64
UNUSED Bytes............................524288
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................1
FS3 Bytes ..............................8192
--------------------------------------------------
FS4 Blocks..............................17
FS4 Bytes ..............................139264
--------------------------------------------------
FULL BLOCKS.............................288
FULL_BYTES .............................2359296
##################################################
Data Blocks(under HWM)..................306
All Blocks (under HWM)..................320
Total Blocks............................384
Total Bytes.............................3145728
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................521
Last Used Block.........................64

PL/SQL procedure successfully completed.

SYS@XE> delete from t1 where owner='SYS';

6648 rows deleted.

SYS@XE> commit;

Commit complete.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................64
UNUSED Bytes............................524288
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................15
FS2 Bytes ..............................122880
--------------------------------------------------
FS3 Blocks..............................61
FS3 Bytes ..............................499712
--------------------------------------------------
FS4 Blocks..............................62
FS4 Bytes ..............................507904
--------------------------------------------------
FULL BLOCKS.............................168
FULL_BYTES .............................1376256
##################################################
Data Blocks(under HWM)..................306
All Blocks (under HWM)..................320
Total Blocks............................384
Total Bytes.............................3145728
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................521
Last Used Block.........................64

PL/SQL procedure successfully completed.

SYS@XE> select bytes from dba_segments where segment_name='T1' and owner='SYS';

BYTES
----------
3145728

SYS@XE> select count(distinct substr(rowid,1,15)) from t1;

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
268

SYS@XE> alter table t1 move ;

Table altered.

SYS@XE> select count(distinct substr(rowid,1,15)) from t1;

COUNT(DISTINCTSUBSTR(ROWID,1,15))
---------------------------------
208

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................36
UNUSED Bytes............................294912
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................208
FULL_BYTES .............................1703936
##################################################
Data Blocks(under HWM)..................208
All Blocks (under HWM)..................220
Total Blocks............................256
Total Bytes.............................2097152
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................92

PL/SQL procedure successfully completed.


SYS@XE> set autotrace on
SYS@XE> select count(*) from t1;

COUNT(*)
----------
15418


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 14146 | 62 (2)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
271 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@XE> delete from t1;

15418 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 775918519

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 61 (0)| 00:00:01 |
| 1 | DELETE | T1 | | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 61 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
45 recursive calls
17171 db block gets
291 consistent gets
0 physical reads
5533796 redo size
939 bytes sent via SQL*Net to client
929 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15418 rows processed

SYS@XE> commit;

Commit complete.

SYS@XE> select count(*) from t1;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 61 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
212 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................36
UNUSED Bytes............................294912
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................208
FS4 Bytes ..............................1703936
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................208
All Blocks (under HWM)..................220
Total Blocks............................256
Total Bytes.............................2097152
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................777
Last Used Block.........................92

PL/SQL procedure successfully completed.

SYS@XE> alter table t1 move ;

Table altered.

SYS@XE> exec hao_show_space('T1');
##################################################
--------hao_show_space kit created by Hao---------
##################################################
ASSM tablespace:
The segment space usage for TABLE "SYS.T1"
##################################################
UNUSED BLOCKS...........................5
UNUSED Bytes............................40960
------------------------HWM------------------------
UNFORMATTED_BLOCKS......................0
--------------------------------------------------
FS1 Blocks..............................0
FS1 Bytes ..............................0
--------------------------------------------------
FS2 Blocks..............................0
FS2 Bytes ..............................0
--------------------------------------------------
FS3 Blocks..............................0
FS3 Bytes ..............................0
--------------------------------------------------
FS4 Blocks..............................0
FS4 Bytes ..............................0
--------------------------------------------------
FULL BLOCKS.............................0
FULL_BYTES .............................0
##################################################
Data Blocks(under HWM)..................0
All Blocks (under HWM)..................3
Total Blocks............................8
Total Bytes.............................65536
##################################################
Last Used Ext FileId....................4
Last Used Ext BlockId...................257
Last Used Block.........................3

PL/SQL procedure successfully completed.

SYS@XE> select count(*) from t1;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

startup restrict

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup restrict;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2062264 bytes
Variable Size 264243272 bytes
Database Buffers 801112064 bytes
Redo Buffers 6324224 bytes
Database mounted.
Database opened.


SQL> alter user orara identified by sds123;

User altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
radev01:*:VCDRT2:/software/orara> sqlplus orara@vcdrt2

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:38:20 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options


radev01:*:VCDRT2:/software/orara> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:41:15 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> revoke dba from orara;

Revoke succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
radev01:*:VCDRT2:/software/orara> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 14 10:41:53 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show user;
USER is "SYS"
SQL> alter system enable restricted session;

System altered.

liqy@mp4is_liqy ~
$ sqlplus orara/sds123@VCDRT2

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 4月 14 10:43:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege


Enter user-name:

SQL> alter system disable restricted session;

System altered.

$ sqlplus orara/sds123@VCDRT2

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 4月 14 10:43:38 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exit

password value in dba_users

[CONCLUSION]
Same encrypted password string (PASSWORD column) in dba_users doesn't mean the actual password are the same. USERNAME looks the key to encrypt the password.
Below is the experiment.

SQL> select password, username from dba_users where username in ('LIQY');

PASSWORD USERNAME
------------------------------ ------------------------------
B68901BE8B75A6BA LIQY

SQL> create user liqy1 identified by values 'B68901BE8B75A6BA';

User created.

SQL> grant create session to liqy1;

Grant succeeded.

SQL> sers where username in ('LIQY','LIQY1'); <> connect liqy1/liqyliqy;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect liqy/liqyliqy;
Connected.


SQL> connect /
Connected.
SQL> alter user liqy identified by liqyliqy;

User altered.

SQL> s where username in ('LIQY','LIQY1'); <> connect /
Connected.
SQL> alter user liqy1 identified by liqyliqy;

User altered.

SQL> select password, username from dba_users where username in ('LIQY','LIQY>

PASSWORD USERNAME
------------------------------ ------------------------------
B68901BE8B75A6BA LIQY
56775B8ACC3F0E97 LIQY1