I have a personal Oracle installation on my development machine (Oracle 12.1 EE, Windows 7, 16 GB RAM (about 7 of which is unused at all times), SSD) which is used exclusively for running automated tests.
This means:
- Default Oracle installation using their GUI – all defaults were accepted during installation.
- One database user/schema.
- The schema is wiped before and after every run of the whole test suite (i.e. database objects are regularly dropped and recreated; the user itself is not dropped). This step does not need to be optimized, its time is insignifikant in relation to the total runtime.
- During the test run, between individual scenarios, the tables are either wiped by
ROLLBACKorTRUNCATEdepending on circumstances. - All objects, including all data rows, are recreated everytime the suite runs. There is no permanent data staying around between suite runs or individual tests.
- It would be totally irrelevant if there were any data loss at any time, up to including having to wipe the whole schema or even the DB itself.
So.
Obviously, there is a lot of overhead right now, since Oracle doesn't know that I don't care about my data. So it will have its redo log etc. chugging along like usual.
What is a good configuration to make my use case as quick as possible? I.e., turning off as many security/consistency/history features as possible; avoiding writing buffers to the HDD as far as possible, etc.?
The time for the one-time initial setup and teardown (i.e. CREATE TABLE … DROP TABLE) is insignificant in relation to the runtime of the test suite.
Best Answer
You can either "drop user test_user cascade;", then create the user again with all needed grants and objects, or drop the database and restore a backup of the database while it is empty. Depending on the number of tables, indexes and constraints one of the two methods will be faster. The more tables, indexes and constraints, the more likely just dropping the database and restoring a backup of the empty database will be faster.