Oracle 11g – Understanding Connect Overhead

oracleoracle-11g-r2performance

Currently one of our new applications is connecting to an Oracle 11g instance over 30,000 daily (at every user request) via their application server, using the same account. (it will be significantly more when its in production) We are trying to convincing the developers to use a connection pool instead of connect/disconnect. However, I want to make sure, I first understand all the overheads associated with creating a connection.

I know the large volume of connections create a lot of overhead, and a lot of process will have to be spawned. Which results in connect being a fairly expensive operation.

However, what steps are exactly happening with a connect that creates the overhead?

Best Answer

The problem is the locking in the SGA that effectively cause the the concurrency is going down. This kind of apps make the database a close to single user platform instead of a highly scalable and concurrent accessible database. The cause of this is that the connections have to be maintained in a shared memory table where inserting and deleting means locking and causes serialization. There is a nice video series about this kind of real world performance problems connection pooling Implementing connection pooling in the demo app in the video causes a 10 fold performance upgrade ...

The spawning of the processes does cause a little overhead but that is handled well by the OS.

Next to that, the listener log wil grow quickly, causing every next connection to be slower than the previous one.

If session auditing is enabled - a default - the sys.aud$ table will explode and make your system tablespace grow very rapidly. I see many databases where this is the cause of an enormous system tablespace and sometimes even bigger than the rest of the database, because of this connection problem.

Related Solutions

Oracle 11g – Create Database fails

One of the key differences between these platforms is that a database in MySQL is a schema in Oracle. Since a schema in Oracle is also a user, the command you are looking for is CREATE USER. I highly recommend that you read the Oracle Concepts Guide before continuing. You may also find this documentation on MySQL>>Oracle migrations using SQL Developer useful. Here is the part relevant to your situation:

When migrating MySQL databases to Oracle, SQL Developer maps each MySQL database to a tablespace in Oracle. Database objects, such as tables, indexes and views are stored in the respective tablespaces and are referenced from the Oracle schema for the user that owns them.

From the error message perspective, the salient part to pull out is "ORA-01100: database already mounted". This indicates that a database has already been created and mounted. It may also be open. Here is an example reproducing your issue, opening the database (which may not be necessary if it is already open) and creating a django user.

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted


SQL> SELECT status FROM v$instance;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT status FROM v$instance;

STATUS
------------
OPEN

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted


SQL> CREATE USER django IDENTIFIED BY "No More ORA-001100.";

User created.

You should also assign permissions and create a django tablespace setting it as the default tablespace for django.

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.

Best Answer

Related Solutions

Oracle 11g – Create Database fails

Sql-server – How to connect multiple users/schemas in Oracle 11g

Related Question