Still the same cluster limitations

I am slightly disappointed to report that the new extended data types cannot be used in the clustered tables. CLOB columns have never been allowed, and are still not allowed in 12c, but I expected the new data types to pass seamlessly. That is not the case:

SQL> create cluster testclu(intkey number(30,0));

Cluster created.

Elapsed: 00:00:00.03
SQL> create index testclu_ind on cluster testclu;

Index created.

Elapsed: 00:00:00.03

Now, let’s create a normal table with the extended character type:

create table testtab1(
intkey number(30,0),
big_text varchar2(32767))
/
Table created.

Elapsed: 00:00:00.02
SQL>
SQL> desc testtab1
Name                       Null?    Type
—————————————– ——– —————————-
INTKEY                         NUMBER(30)
BIG_TEXT                     VARCHAR2(32767)

SQL>

Now, let’s try and put the table with an extended data type into a cluster.

SQL> create table testtab2
2  (intkey number(30),
3   big_text varchar2(32767))
4  cluster testclu(intkey);
create table testtab2
*
ERROR at line 1:
ORA-14691: Extended character types are not allowed in this table.

If the size of the big_text column is reduced to 4000, the problem is gone:

1  create table testtab2
2  (intkey number(30),
3   big_text varchar2(4000))
4* cluster testclu(intkey)
SQL> /

Table created.

Elapsed: 00:00:00.03
SQL>

I was unable to find any documentation on this restriction.

Posted in Uncategorized | Leave a comment

SYSBACKUP travails, part II

The SYSBACKUP connectivity problems described in the previous post are caused by unpublished bug 15828768.

There is a way to connect, using SYSBACKUP privilege:

 

RMAN> connect target ‘system@local as sysbackup’

target database Password:
connected to target database: ORA12C (DBID=214280212, not open)

Please, note the quotes around the connection string. Without the quotes, this will not work. It works with both single and double quotes:

RMAN> connect target “system@local as sysbackup”

target database Password:
connected to target database: ORA12C (DBID=214280212, not open)

This would require me to change quite a few scripts, in order to utilize SYSBACKUP connections with a commercial backup utility. Fortunately, Oracle is aware of the problem and is actively working on it.

 

 

Posted in Uncategorized | Leave a comment

Quirks with the SYSBACKUP privilege in 12c

Oracle 12c has a great new privilege, tailor made for the paranoid oracle DBA guys like me. However, there are still some quirks: SYSBACKUP privilege doesn’t work over Oracle*Net. If the authentication method is SID based local authentication, all is well:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed Size    2298736 bytes
Variable Size    1040190608 bytes
Database Buffers    3976200192 bytes
Redo Buffers    8695808 bytes
Database mounted.
SQL>

Now, let’s do a cold backup of tablespace USERS:

[oracle@medo admin]$ rman target system
Recovery Manager: Release 12.1.0.1.0 – Production on Sat Feb 22 17:20:06 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
target database Password:
connected to target database: ORA12C (DBID=214280212, not open)
RMAN> backup tablespace users;
Starting backup at 22-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data/oradata/ora12c/ORA12C/datafile/o1_mf_users_9f093j2d_.dbf
channel ORA_DISK_1: starting piece 1 at 22-FEB-14
channel ORA_DISK_1: finished piece 1 at 22-FEB-14
piece handle=/data/oradata/fra/ORA12C/backupset/2014_02_22/o1_mf_nnndf_TAG20140222T172029_9jl8of7v_.bkp tag=TAG20140222T172029 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-FEB-14
Starting Control File and SPFILE Autobackup at 22-FEB-14
piece handle=/data/oradata/fra/ORA12C/autobackup/2014_02_22/o1_mf_s_840215852_9jl8ohd8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-FEB-14
RMAN>
User SYSTEM does not have SYSDBA privilege. However, the V$PWFILE_USERS doesn’t show that while the instance is in the “MOUNTED” state:
SQL> select * from v$pwfile_users;
USERNAME    SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
—————————— —– —– —– —– —– —– ———-
SYS    TRUE  TRUE  FALSE FALSE FALSE FALSE    1
When the database is open, the user “SYSTEM” is clearly visible:
SQL> alter database open;
Database altered.
SQL> select * from v$pwfile_users;
USERNAME    SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
—————————— —– —– —– —– —– —– ———-
SYS    TRUE  TRUE  FALSE FALSE FALSE FALSE    0
SYSTEM    FALSE FALSE FALSE TRUE  FALSE FALSE    3
This shows that SYSTEM is not a SYSDBA user. However, the connection as SYSDBA will succeed:

SQL*Plus: Release 12.1.0.1.0 Production on Sat Feb 22 17:33:53 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> connect system as sysdba
Enter password:
Connected.

While the database is not open, SYSBACKUP connection is equivalent to a SYSDBA connection, because it’s impossible to read the user data while the database is not open. When the database is opened, the first attempt is surprising:

SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

This is because we are connecting to the container database which doesn’t contain the table SCOTT.EMP. Let’s try with the pluggable database which does contain that table:

SQL> connect system@local as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges

User SYSTEM cannot connect as SYSDBA, because that privilege wasn’t granted to that user. There is new connection syntax which allows connecting as SYSBACKUP, which was granted to the user SYSTEM:

SQL> connect system@local as sysbackup
Enter password:
Connected.
Let’s now open the pluggable database:
SQL> alter pluggable database open;
Pluggable database altered.
SQL> select * from scott.emp;
select * from scott.emp
               
ERROR at line 1:
ORA-01031: insufficient privileges

That’s the main advantage of the SYSBACKUP role: it prevents the backup operator from seeing the user data. Backup also works, which was the intention. The problem arises with connecting through the listener. It works with the user “SYS” or any other SYSDBA  user:

RMAN> connect target sys@local
target database Password:
connected to target database: ORA12C (DBID=214280212)

It doesn’t work with the SYSBACKUP users:

[oracle@medo admin]$ rman
Recovery Manager: Release 12.1.0.1.0 – Production on Sat Feb 22 18:09:20 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target system@local
target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges
RMAN>

This is a big problem because various network backup utilities use Oracle*Net connection. Unfortunately, rman attempts sysdba connection with “as SYSDBA”  and that doesn’t work. The problem is in the rman utility itself because sqlplus allows network connection as SYSBACKUP:

SQL*Plus: Release 12.1.0.1.0 Production on Sat Feb 22 18:14:15 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> connect system@local as sysbackup
Enter password:
Connected.

Posted in Uncategorized | Leave a comment

Missing Information

Since the version 11G it is possible to exclude committed blocks from specific tablespace from being written to flashback recovery area. FBDA process writes committed blocks to flashback area and is quite IO intensive. There are, among other things, flashback checkpoints as well. They occur when when flashback logs fill up and start anew. It is, therefore, desirable to exclude tablespaces to which users don’t have access from having the committed changes written to flashback. Here is an example how to do that for the trace analyzer (TRCA) tablespace:

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
TEMP
USERS
TRCANLZR

Elapsed: 00:00:00.12
SQL> alter tablespace TRCANLZR flashback off;

Tablespace altered.

Elapsed: 00:00:00.45

This is somewhat typical situation when to turn the flashback off. Flashback is primarily a protection from the human error. I advocate turning the flashback off for tablespaces containing no user content or content that humans are extremely unlikely to modify, like the data dictionary for various applications.
There is, however, a problem:
DBA_TABLESPACES view contains no information about that and, even more problematic, DBMS_METADATA returns no information about that:

SQL> set long 1024
SQL> select dbms_metadata.get_ddl(‘TABLESPACE’,tablespace_name) SQL
2 from dba_tablespaces
3 where tablespace_name=’TRCANLZR’
4 /

SQL
——————————————————————————–

CREATE TABLESPACE “TRCANLZR” DATAFILE
SIZE 134217728
AUTOEXTEND ON NEXT 134217728 MAXSIZE 1073741824
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO

The information about the flashback status is important enough to be included in V$DATABASE view and, in my modest opinion, it should be included in the DBA_TABLESPACES and returned by DBMS_METADATA.
By the way, in Oracle 12c, flashback is also common for all pluggable databases, not only redo logs and undo tablespace.

Posted in Miscellaneous Oracle, Uncategorized | Leave a comment

Multi-threaded Oracle 12c architecture on Linux

Introduction

Oracle has long had so called multi-threaded architecture on Windows. On the Unix platforms, Oracle was using multi-process architecture until version 12c. The difference is that Oracle server processes in the threaded architecture become threads of a single process. The reason for such an architecture is lower overhead caused by process switching. Switching threads is done on the user level and doesn’t need a system trap to proceed. System traps are expensive operations, implemented in the CPU hardware itself, which cause the CPU to save one set of registers, load another set of registers , update process statistics and return to the user mode execution. The same applies to handling semaphores. Semaphores are actually implemented as device drivers on Unix systems. Every semaphore manipulation causes an interrupt and a context switch. Threads model, supported by the PTHREAD library (/usr/lib/libpthread.so on Linux systems) avoids all those context switches. Threads have become an option since the advent of 64 bit systems which support large address spaces. On 32 bit systems, both the code and SGA would have to fit within a single process, with the address space of only 4GB. That was a severe restriction on the address space and multi-process model was used to avoid that. However, these days Oracle is hardly ever run on 32 bit platforms so this is a useful optimization that makes sense.

Threaded execution

As it may be expected, the threaded model is used when the THREADED_EXECUTION parameter is set to TRUE. This is a static parameter, cannot be changed dynamically by ALTER SYSTEM command, and its value must be the same across all instances of a RAC database. It is possible to use the same dedicated server model as in the process model, but the additional parameter must be set for the listener: DEDICATED_THROUGH_BROKER_LISTENER=ON

With this parameter set, the traditional “/ as sysdba” way of connecting to database no longer works:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 4 13:22:33 2013

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

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

The only way to connect to the database is to connect as SYS using SYS/<passwd>@TNS as SYSDBA:

[oracle@ora12c ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 4 13:26:01 2013

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

SQL> connect sys@test1 as sysdba

Enter password:

Connected.

SQL>

The unintended consequence of this is that dbstart and dbshut scripts no longer work:

[oracle@ora12c ~]$ dbshut $ORACLE_HOME

Processing Database instance “orcl”: log file /home/oracle/app/oracle/product/12.1.0/dbhome_1/shutdown.log

[oracle@ora12c ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 4 13:28:07 2013

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

SQL> connect sys@test1 as sysdba

Enter password:

ERROR:

ORA-12541: TNS:no listener

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> select count(*) from v$session;

COUNT(*)

———-

40

As you can se, the dbshut script has brought down the listener but not the instance. Of course, it is not possible to connect to pluggable databases without listener, but one will definitely need to replace the connection strings in dbshut and dbstart scripts. The same applies to the dbstart script, which starts the listener and starts the instance in the NOMOUNT mode, but doesn’t open the instance or any of the PDB components:

[oracle@ora12c ~]$ dbstart $ORACLE_HOME

Processing Database instance “orcl”: log file /home/oracle/app/oracle/product/12.1.0/dbhome_1/startup.log

[oracle@ora12c ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Fri Oct 4 13:33:09 2013

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

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> select status from V$INSTANCE;

STATUS

————

STARTED

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter pluggable database test1 open;

Pluggable database altered.

SQL>

Details

Threaded execution is not very well documented. There is a short section in the “Concepts” manual, the parameter description in the “Reference” manual and that’s it. Search on My Oracle Support for the parameter THREADED_EXECUTION comes up empty. So does the search for the listener parameter DEDICATED_THROUGH_BROKER. That means that the situation must be investigated using the native Linux tools. First, let’s see what processes are running when the database is in the threaded mode:

[oracle@ora12c ~]$ ps -fp `pgrep ora_`

UID PID PPID C STIME TTY STAT TIME CMD

oracle 4488 1 0 13:31 ? Ss 0:01 ora_pmon_orcl

oracle 4490 1 0 13:31 ? Ss 0:03 ora_psp0_orcl

oracle 4492 1 8 13:31 ? Ss 3:13 ora_vktm_orcl

oracle 4496 1 0 13:31 ? Ssl 0:09 ora_u004_orcl

oracle 4502 1 3 13:31 ? Ssl 1:10 ora_u005_orcl

oracle 4508 1 0 13:31 ? Ss 0:01 ora_dbw0_orcl

So, we have PMON, PSP0 (aka “process spawner”), VKTM (“virtual time keeper”), database writer and two unknown and undocumented processes, u004 and u005. Not only are these two processes completely undocumented in the documentation and on My Oracle Support, nothing about them can be found in the X$MESSAGES table:

SQL> select description,dest from x$messages

2 where dest like ‘U0%’;

no rows selected

The V$BGPROCESS table also comes up empty. So, what threads are running within those two processes? Fortunately, there is pidstat utility:

[oracle@ora12c ~]$ pidstat -t -p 4496 >/tmp/u004.txt

[oracle@ora12c ~]$ pidstat -t -p 4508 >/tmp/u005.txt

Here are threads that those two processes are running::

U004

Linux 2.6.32-358.18.1.el6.x86_64 (ora12c.home.com) 10/04/2013 _x86_64_ (2 CPU)

02:26:39 PM TGID TID %usr %system %guest %CPU CPU Command

02:26:39 PM 4496 – 0.06 0.16 0.00 0.22 1 ora_scmn_orcl

02:26:39 PM – 4496 0.00 0.00 0.00 0.00 1 |__ora_scmn_orcl

02:26:39 PM – 4497 0.00 0.00 0.00 0.00 0 |__oracle

02:26:39 PM – 4498 0.00 0.01 0.00 0.02 1 |__ora_gen0_orcl

02:26:39 PM – 4499 0.01 0.01 0.00 0.01 1 |__ora_mman_orcl

02:26:39 PM – 4505 0.01 0.01 0.00 0.02 1 |__ora_dbrm_orcl

02:26:39 PM – 4509 0.00 0.01 0.00 0.02 1 |__ora_lgwr_orcl

02:26:39 PM – 4510 0.03 0.06 0.00 0.09 1 |__ora_ckpt_orcl

02:26:39 PM – 4511 0.00 0.01 0.00 0.01 1 |__ora_lg00_orcl

02:26:39 PM – 4512 0.00 0.00 0.00 0.00 1 |__ora_lg01_orcl

02:26:39 PM – 4513 0.00 0.00 0.00 0.01 1 |__ora_smon_orcl

02:26:39 PM – 4515 0.00 0.01 0.00 0.01 1 |__ora_lreg_orcl

02:26:39 PM – 4595 0.00 0.01 0.00 0.01 1 |__ora_rvwr_orcl

U005

Linux 2.6.32-358.18.1.el6.x86_64 (ora12c.home.com) 10/04/2013 _x86_64_ (2 CPU)

02:26:34 PM TGID TID %usr %system %guest %CPU CPU Command

02:26:34 PM 4508 – 0.01 0.02 0.00 0.03 1 ora_dbw0_orcl

02:26:34 PM – 4508 0.01 0.02 0.00 0.03 1 |__ora_dbw0_orcl

The thread named ORA_SCMN_ORCL in the U005 process is actually executing the SQL commands, thus the name. This thread is launched in the process U005 upon the execution of the SQL command and has sub-threads, which can easily be seen by using the following comand to monitor a SQL execution: “top -H -u oracle”. The “-H” switch turns on thread display. This command will monitor threads being executed by user “oracle”. The output looks like this:

top – 14:40:55 up 1:52, 3 users, load average: 1.15, 1.07, 0.83

Tasks: 323 total, 2 running, 321 sleeping, 0 stopped, 0 zombie

top – 14:43:34 up 1:54, 3 users, load average: 1.07, 1.07, 0.86

Tasks: 323 total, 2 running, 321 sleeping, 0 stopped, 0 zombie

Cpu(s): 49.2%us, 2.8%sy, 0.0%ni, 48.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Mem: 5991356k total, 3484640k used, 2506716k free, 93948k buffers

Swap: 6127608k total, 0k used, 6127608k free, 2864648k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

5285 oracle 20 0 4501m 507m 377m R 97.1 8.7 26:14.85 oracle_5285_orc

4492 oracle -2 0 2065m 17m 15m S 2.7 0.3 5:44.27 ora_vktm_orcl

2222 oracle 20 0 195m 1624 1036 S 0.7 0.0 0:39.02 X11-NOTIFY

4490 oracle 20 0 2065m 17m 15m S 0.7 0.3 0:05.38 ora_psp0_orcl

4488 oracle 20 0 2065m 21m 19m S 0.3 0.4 0:02.43 ora_pmon_orcl

4510 oracle 20 0 3176m 1.8g 1.7g S 0.3 31.0 0:06.95 ora_ckpt_orcl

4506 oracle 20 0 4501m 507m 377m S 0.3 8.7 0:13.33 ora_dia0_orcl

4608 oracle 20 0 4501m 507m 377m S 0.3 8.7 0:09.52 ora_p001_orcl

5767 oracle 20 0 15172 1388 924 R 0.3 0.0 0:00.72 top

2165 oracle 20 0 146m 5512 2828 S 0.0 0.1 0:00.01 gnome-keyring-d

2166 oracle 20 0 146m 5512 2828 S 0.0 0.1 0:00.00 gnome-keyring-d

2249 oracle 20 0 146m 5512 2828 S 0.0 0.1 0:00.00 gnome-keyring-d

2174 oracle 20 0 246m 6396 5028 S 0.0 0.1 0:00.27 gnome-session

2243 oracle 20 0 246m 6396 5028 S 0.0 0.1 0:00.00 gnome-session

2182 oracle 20 0 20036 624 388 S 0.0 0.0 0:00.00 dbus-launch

2183 oracle 20 0 21804 1272 612 S 0.0 0.0 0:00.13 dbus-daemon

2199 oracle 20 0 196m 1948 1460 S 0.0 0.0 0:00.00 VboxClient

Our thread name is now oracle_5285_orcl, which conforms to the standard server process name. Now we can use V$PROCESS table which in the version 12c contains STID column, in addition to the well known SPID column. The STID column contains the thread id, the SPID column is “server process id”. We can trace our server process thread back to U005 process:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select spid from v$process where stid=5285;

SPID

————————

4502

Server process 4502 is our process U005, from the listing at the beginning of this section

Now, one more important question remains: where are threads allocating the process memory from? There are two possibilities: threads will allocate the process memory from the shared pool, as is the case with the shared server, or, the memory will be allocated to the process, as non-sharable memory, private to the process. It is important to note that the other threads will still have access to this specific thread PGA as all threads share the address space. So, let’s find out.

Memory Allocation

The details of the memory allocation can be discerned by an experiment. The idea is to measure the size of the shared pool free memory before the experiment, than create a large PL/SQL table which will be contained in the PGA and measure the size of the shared pool free memory again. If the shared pool free memory goes down by the size of this table, the memory is allocated from SGA. If, on the other hand, the virtual size of the process 4502 grows by that amount and the number of shared pages remains the same, the memory is allocated from the OS, using calloc or malloc. The first step toward creating a large PL/SQL table is to create a nad populate a large table:

CREATE TABLE SCOTT.TST

(ID NUMBER(15,0) NOT NULL,

VAL VARCHAR2(10),

CONSTRAINT TST_PK PRIMARY KEY (ID)

)

SEGMENT CREATION IMMEDIATE

7 NOLOGGING TABLESPACE USERS;

Table created.

Elapsed: 00:00:00.78

insert /*+ APPEND */into tst(id,val)

select level,dbms_random.string(‘A’,10)

from dual

connect by level <= 1000000;

commit;

1000000 rows created.

Elapsed: 00:01:26.47

SQL>

Commit complete.

Elapsed: 00:00:00.13

SQL>

The table contains two columns, ID and VAL, simulating key value store. The ID is a NUMBER column, while VAL is VARCHAR2(10) column, filled completely randomly, using DBMS_RANDOM. This is our baseline:

SQL> select pool,name,bytes from v$sgastat

2 where pool=’shared pool’ and name =’free memory’;

POOL NAME BYTES

———— ————————– ———-

shared pool free memory 81919928

Now the process size:

[oracle@ora12c Desktop]$ ps -lp 4502

F S UID PID PPID C PRI NI ADDR SZ WCHAN TTY TIME CMD

0 S 500 4502 1 45 80 0 – 1203794 semtim ? 00:54:39 ora_scmn_orcl

Now, let’s execute our experiment:

SQL> set echo on

SQL> set termout on

SQL> @test

SQL> set serveroutput on

SQL> declare

2 type testtbl is table of TST%ROWTYPE;

3 biggie testtbl;

4 begin

5 select * bulk collect into biggie from tst;

6 dbms_output.put_line(‘Number of lines’||biggie.count);

7 end;

8 /

Number of lines1000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.94

The PL/SQL table biggie is allocated from the process memory, it should be placed entirely into the private memory of the process. Without that table, the size of the shared pool should not change significantly. Let’s see what do the measurements say:

SQL> /

POOL NAME BYTES

———— ————————– ———-

shared pool free memory 75519608

Shared pool free memory is almost 7M smaller than the original size,. Let’s see the address size of the process:

[oracle@ora12c Desktop]$ ps -lp 4502

F S UID PID PPID C PRI NI ADDR SZ WCHAN TTY TIME CMD

0 S 500 4502 1 45 80 0 – 1186025 semtim ? 00:54:43 ora_scmn_orcl

[oracle@ora12c Desktop]$

The address size is actually smaller than it was before the test was executed. The inescapable conclusion is that in threaded mode, Oracle dedicated servers allocate PGA from the shared pool, just like in the case of multi-threaded server (MTS). This is an important factor in determining how much memory to allocate to the instance. The conclusion is that Oracle Corp. should really do a better job with documentation and technical white papers. I am aware that this is only the first version and that likelihood of someone actually using this version in production, even without the threaded architecture, is very small, but this is a ground breaking architecture change and should be documented.

Posted in Uncategorized | Tagged | 7 Comments

Pidstat

Recently, I’ve been faced with a strange problem: around 10AM every morning, my database was showing signs of IO contention. Wait times for db file sequential and multipblock reads were increasing for no apparent reason and there were no signs of increased activity reported by the users. Fortunately, my system is running RH 6.x which has things like iostat. There is one particularly useful utility called “pidstat”. It allows me to select only oracle processes and report IO, CPU or switching activity. It’s a part of the systat package, the same package which contains sar, vmstat and iostat:

[root@medo mgogala]# rpm -qf /usr/bin/pidstat
sysstat-10.0.3-2.fc17.x86_64

The use is very simple. To report on disk usage by the oracle processes,  the following can be used:

[root@medo mgogala]# pidstat -d -C oracle
Linux 3.8.4-102.fc17.x86_64 (medo.home.com)     04/02/2013     _x86_64_    (4 CPU)

10:54:47 AM       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
10:54:47 AM      2910      0.02      0.00      0.00  oracle
10:54:47 AM      2914      0.00      0.00      0.00  oracle
10:54:47 AM      2918      0.00      0.00      0.00  oracle
10:54:47 AM      2920      0.00      0.00      0.00  oracle
10:54:47 AM      2922      0.00      0.00      0.00  oracle
10:54:47 AM      2924      0.01      0.00      0.00  oracle
10:54:47 AM      2926      0.00      0.00      0.00  oracle
10:54:47 AM      2928      0.01      0.00      0.00  oracle
10:54:47 AM      2930      0.02      0.06      0.00  oracle
10:54:47 AM      2932      0.04      0.04      0.00  oracle
10:54:47 AM      2934      0.07      0.00      0.00  oracle
10:54:47 AM      2936      0.00      0.00      0.00  oracle
10:54:47 AM      2938      0.27      0.00      0.00  oracle
10:54:47 AM      2942      0.01      0.00      0.00  oracle
10:54:47 AM      2944      0.01      0.00      0.00  oracle
10:54:47 AM      2951      0.04      0.09      0.00  oracle
10:54:47 AM      2954      0.00      0.00      0.00  oracle
10:54:47 AM      2956      0.02      0.00      0.00  oracle
10:54:47 AM      2958      0.01      0.00      0.00  oracle
10:54:47 AM      2960      0.00      0.00      0.00  oracle
10:54:47 AM      4215      0.10      0.00      0.00  oracle
10:54:47 AM      4217      0.00      0.00      0.00  oracle
10:54:47 AM      4219      0.02      0.00      0.00  oracle
10:54:47 AM      4221      0.03      0.00      0.00  oracle
10:54:47 AM      4223      0.00      0.00      0.00  oracle

For the CPU usage, one would use “-u” instead of “-d”.

Posted in Uncategorized | Tagged | Leave a comment

Expert consolidation in Oracle database 12c

Today, I entered the term “Oracle Database 12c” in the books section of Amazon.com, my favourite, and was surprised by the results. There are few books announced for April 2012, some for May and June 2013, without the database even being available to the public. Expert consolidation in 12c? Really? What kind of expert can “consolidate” by using a database version that has not been made available to the public yet? I am very interested in expert consolidation with the database version 15F, whatever that “consolidation” might be. How about “problem-solution oriented approach”, from the title of another book? What kind of problems do people have with the database that has not yet been released? This is hilarious. My message to all those worthy book authors is: may the farce be with you, guys. In the expert way, of course…you are the experts, aren’t you?

Posted in Miscellaneous Oracle | Leave a comment