Transaction execution time and block size
LausJun 4 2013 — edited Jun 5 2013Hi,
I have Oracle Database 11g R2 64 bit database on Oracle Linux 5.6. My system has ONE hard drive.
Recently I experimented with 8.5 GB database in TPC-E test. I was watching transaction time for 2K,4K,8K Oracle block size. Each time I started new test on different block size, I would created new database from scratch to avoid messing something up (each time SGA and PGA parameters ware identical).
In all experiments a gave to my own tablespace (NEWTS) different configuration because of oracle block-datafile size limits :
2K oracle block database had 3 datafiles, each 7GB.
4K oracle block database had 2 datafiles, each 10GB.
8K oracle block database had 1 datafile of 20GB.
Now best transaction (tranasaction execution) time was on 8K block, little longer tranasaction time had 4K block, but 2K oracle block had definitly worst transaction time.
I identified SQL query(when using 2K and 4K block) that was creating hot segments on E_TRANSACTION table, that is largest table in database (2.9GB), and was slowly executed (number of executions was low compared to 8K numbers).
Now here is my question. Is it possible that multiple datafiles are reasone for this low transaction times. I have AWR reports from that period, but as someone who is still learning things about DBA, I would like to asq, how could I identify this multi-datafile problem (if that is THE problem), by looking inside AWR statistics.
THX to all.