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

Question

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:

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

2. 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;
   

3. Populate database with test data.

4. Run test.

5. 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.

Answer 1

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:

1. 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

2. 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.

Answer 2

Actually, one more possible approach came to my mind.

1. Create a db with n tablespaces.

2. Test framework would maintain a pool of these tablespaces.

3. Each test would lease a tablespace from a pool.

4. When tablespace is returned framework cleans it up and makes it ready to use by another test.

5. If all tablespaces are in use then framework just creates a new one!

What do you think?

It could be done independently to optimizations mentioned by @ora-600.