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.

  • 1
    There is no reason to re-create tablespace a users again and again. When creating tablespace Oracle has to "format" every disk block. That is probably the slowest part.
    – ibre5041
    Dec 29 '14 at 8:58
  • Is it 2) or 3) that's slow? What exactly do you mean by slow and what would be acceptable? How often does your test data change?
    – Mat
    Dec 29 '14 at 9:09
  • Well, I don't know which one (2 or 3) is slow. I didn't do proper benchmarks. It takes a few seconds to prepare schema for running a test. It would be great if it could take less than one sec. Test data changes very rarely. Dec 29 '14 at 10:13

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.


  • 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.
  • * Optimize 5: So are you saying that removing all tables from tablespace and populating it once again with test data should be faster? * Speed up 2 ASM seems to be worth checking. * Speed up 3: I use insert-statement file.. Dec 29 '14 at 14:07
  • Could you tell me more about using one default temporary tablespace for many users? Please keep in mind that I am running tests in parallel. Is there a way of creating an only-in-memory tablespace in Oracle 11g? Dec 29 '14 at 14:14
  • I updated my answer... see the EDIT1 marks.
    – o0x258
    Dec 29 '14 at 15:40
  • So, there's no the best solution to the problem. Instead we have many possible optimizations. I have enough food for thoughts for at least a week of work ;) Dec 29 '14 at 23:52
  • 2
    Yes, there is not one solution because databases can be very different in their requirements. When I analyze a database to find the best setup it takes me about 2 working days (incl. documentation). That means I want to encourage you to put a lot of time into it. After a few database optimizations you'll be much faster. :)
    – o0x258
    Dec 30 '14 at 7:37

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.