Application testing: using multiple users in Oracle instead of mem H2. How to make it as fast as possible

h2oracletesting

I decided to stop using H2 for obvious reasons (I have Oracle on production, compatibility mode is a fake). So, I wrote simple test framework which for each test in my application does the following:

Generate random username (in example below it's test_user).

Create new user and tablespace:

create tablespace test_user_ts
datafile 'test_user_tabspace.dat'
size 10M reuse
autoextend on next 500K;

create temporary tablespace test_user_ts_tmp
tempfile 'test_user.tabspace_temp.dat'
size 10M reuse
autoextend on next 500K;

create user test_user
identified by test_password
default tablespace test_user_ts
temporary tablespace test_user_ts_tmp;

grant create session to test_user;

grant all privileges to test_user;

Populate database with test data.
Run test.

Clean up:

drop user test_user cascade;
drop tablespace test_user_ts_tmp;
drop tablespace test_user_ts;

The problem is that stages 1-3 are slow. How can I make them as fast as possible? Is there maybe any way of copying existing db schema to another one?

Db version: Oracle 11g

I have full control over Oracle instance. It runs on a vagrant image on my dev machine.

Best Answer

Speed up 2:

If you create the tablespace with let's say 100M and extend it with 50M or 100M, your database is much quicker than with 500k extension. This way the database keeps extending the file over and over again.
You can also use ASM which is much faster in creating files than traditional file-systems.

EDIT1: ASM has a different structure than normal filesystems. For example crating a 32G file in ext4 takes 3-8 minutes. In ASM it just takes a fraction of a second. The reason is, in ASM you don't have to update lots of filesystem records to allocate the space. I would say in general ASM is slightly faster than other filesystems because it is optimized for Oracle databases.

Speed up 3:

How do you populate the database with test data?

impdp: If you have EE you can load with multiple threads
insert-statement file: there is not much you can do
sqlLoader: sqlLoader is pretty fast and has some options to tune the process
INSERT INTO AS SELECT... in EE you can use parallel query, in SE you have to split it up and start multiple external threads.

Optimize 5:

Like ibre5041 mentioned you don't have to remove the tablespaces all the time. If you still want to create a new tablesapce for each test then remove the tablespace with drop tablespace test_user including contents and datafiles;. This command will remove the datafiles as well.
You can use the default temporary tablespace. You don't need a temp tbs for each user.

EDIT1:

Each database has a default temporary tablespace which is being used by the database users if you do not specify one. So if you configure the database as follows:

alter database default temporary tablespace temp;

(this is the default configuration by the way)

You can create a user with this command: create user <username> identified by <pw> default tablespace test_user_ts quota unlimited on test_user_ts;

From performance aspects you don't have to create multiple temp tablespaces.

In Memory (EDIT1)

Starting with Oracle 12c you can use the In-Memory option (keep in mind it's a extra cost option). It is a completely different memory structure than the normal data buffer cache. We did some benchmarks with it and it's very very fast.

In Oracle 11g there is no In-Memory option but this does not mean you can't optimize the database performance. I would recommend the following action plan to figure out the optimal memory settings:

use AWR report or statspack report (depends on whether you have diagnostic pack licensed)

check the buffer hit ratio (should be above 90%)

or check if the / = x. "x" should be below 10%

check the buffer cache advisory listings (this is not really reliable but it gives you an idea)

check the size of the buffer cache compared to shared pool, the shared pool should not be grater than the buffer cache

Adjust the memory parameter

use huge pages (use_large_pages=TRUE) (the os needs to be configured for it, does not apply on win)

if the buffer cache hit ratio is smaller than 90% increase sga size (sga_target and sga_max_size)

if the shared pool is overextended then set a minimum size for the buffer cache to at least 60% of the sga size (db_cache_size = sga_target/100*60)

maybe(!!!) you can use KEEP and RECYCLE pool to optimize the data buffer cache usage.

Related Solutions

Sql-server – Is it possible to schedule table update/refresh in Oracle Application Express

You can do the following steps in a procedure: 1) User UTL_HTTP to read the csv file; 2) Save the file on a oracle directory; 3) Create an external table that will map this CSV file; 4) Schedule this procedure accordingly, using dbms_job_scheduler

This assumes your user has permissions to execute UTL_HTTP, UTL_FILE and DBMS_JOB_SCHEDULER, besides read/write permission in the given oracle directory.

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.