1 The importance of testing techniques

Test is related with the techniques needed to test any hypothesis. You can’t find all the answers you need in the documentation and books.
The problem I personally had is the impossibility to test by myself, and being force to be asking or searching readings about topics I could easily test by myself saving a lot of time, and getting a lot of accuracy and confidence in what I do.
Testing means you can answer questions like:
Why a reversed index is bigger?
Which is the difference between open_cursors and session_cached_cursors?
Etc.

1.A Tuning

You inevitably will reach a point where you CAN’T tune, if you can’t perform an adequate test.
For example you want to tune your index size, specifying the exact number length; honestly I don’t expect you are going to find a paragraph explaining you how much you can in space specifying the exact length of a number, so the only chance you have is to test it.
Even if you found this information, you can find this had changed in you current patch release or there is a bug on your platform invalidating this aftereffects.
But running a test you can see it
CREATE TABLE NUMBER_TEST
(
C_NUMBER_32 NUMBER(32,0) NOT NULL,
C_NUMBER_6 NUMBER(6,0) NOT NULL,
C_NUMBER NUMBER NOT NULL)
PCTFREE 10
/
INSERT INTO NUMBER_TEST
SELECT C_NUMBER,C_NUMBER,C_NUMBER FROM SOURCE_TEST;
CREATE INDEX FON.TEST_32 ON NUMBER_TEST(C_NUMBER_32);
CREATE INDEX FON.TEST_6 ON NUMBER_TEST(C_NUMBER_6);
CREATE INDEX FON.TEST_ ON NUMBER_TEST(C_NUMBER);
SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME LIKE 'TEST%'
GROUP BY SEGMENT_NAME; The output is enough, you don’t have to loose hours changing your tables to specify the exact number, because you don’t get any improvement, at least not in index size.
Only when you can measure an interesting improvement specifying the exact number of decimal, you can start to evaluate the change.

2 Use the second test to compare performance

I want you pay attention how different can be the performance measure, between the first and the second test.
So if you want to compare two different tests, is better you compare the second tests of each. The exception is when you want to test the first execution, this can require to restart your database several times.

NORMAL INSERT	Test1	Test2	Test3
recursive calls	1724	0	0
db block gets	5181	3058	3057
consistent gets	1361	1070	1069
physical reads	225	0	0
redo size	2,953,548	2,848,720	2,843,936
APPEND INSERT	Test1	Test2	Test3
Recursive calls	966	0	0
db block gets	1092	140	140
consistent gets	759	346	346
physical reads	0	0	0
redo size	3,071,888	2,983,528	2,983,528
APP INS NOLOG	Test1	Test2	Test3
Recursive calls	1,327	0	0
db block gets	1,103	141	141
consistent gets	892	346	346
physical reads	0	0	0
redo size	81,504	80,944	80,944

If you compare, you will see starting from the second execution the rest seems to be similar.
So unless you want to time the first execution, you should compare the second execution of every scenario to analyze the test.
In this example you can see the third test seems to be the best, but nologging tables is not advisable if you are running archivelog mode, and you should be running on archivelog.
The description of the test
First Execution Normal Insert
SQL> INSERT INTO CUENTAS_BACK SELECT * FROM CUENTAS_ME;
16507 filas creadas. SQL> ROLLBACK;
Second Execution Normal Insert
SQL> INSERT INTO CUENTAS_BACK SELECT * FROM CUENTAS_ME;
16507 filas creadas. SQL> ROLLBACK;
Third Execution Normal Insert
SQL> INSERT INTO CUENTAS_BACK SELECT * FROM CUENTAS_ME;
16507 filas creadas. First Execution Append Insert
SQL> INSERT /*+ APPEND */ INTO CUENTAS_BACK SELECT * FROM CUENTAS_ME;
16507 filas creadas.
Execution Plan
----------------------------------------------------------
ERROR:ORA-12838: no se puede leer/modificar un objeto despuÚs de modificarlo en paralelo
SP2-0612: Error al generar el informe AUTOTRACE EXPLAIN Second Execution Append Insert
SQL> INSERT /*+ APPEND */ INTO CUENTAS_BACK SELECT * FROM CUENTAS_ME;
16507 filas creadas.
Execution Plan
----------------------------------------------------------
ERROR:ORA-12838: no se puede leer/modificar un objeto despuÚs de modificarlo en paralelo
SP2-0612: Error al generar el informe AUTOTRACE EXPLAIN SQL> ROLLBACK;
Third Execution Append Insert
SQL> INSERT /*+ APPEND */ INTO CUENTAS_BACK SELECT * FROM CUENTAS_ME;
16507 filas creadas.
Execution Plan
----------------------------------------------------------
ERROR: ORA-12838: no se puede leer/modificar un objeto despuÚs de modificarlo en paralelo
SP2-0612: Error al generar el informe AUTOTRACE EXPLAIN First Execution Append Insert (NOLOGGING TABLE)
SQL> ALTER TABLE CUENTAS_BACK NOLOGGING;
Tabla modificada.
SQL> ROLLBACK;
Rollback terminado.
SQL> INSERT /*+ APPEND */ INTO CUENTAS_BACK SELECT * FROM CUENTAS_ME;
16507 filas creadas.
Execution Plan
----------------------------------------------------------
ERROR:ORA-12838: no se puede leer/modificar un objeto despuÚs de modificarlo en paralelo
SP2-0612: Error al generar el informe AUTOTRACE EXPLAIN SQL> ROLLBACK;
Second & Third Execution Append Insert (NOLOGGING TABLE)
SQL> INSERT /*+ APPEND */ INTO CUENTAS_BACK SELECT * FROM CUENTAS_ME;
16507 filas creadas.
Execution Plan
----------------------------------------------------------
ERROR:ORA-12838: no se puede leer/modificar un objeto despuÚs de modificarlo enparalelo
SP2-0612: Error al generar el informe AUTOTRACE EXPLAIN

3 Setting environment for testing

3.A Resetting Memory: Shared Pool and Buffer Cache

Going to the previous example if you want to compare the first execution, when you need to bind again, and load from disk data again. You can do this restarting the database, or you can resent the buffer pool cache and shared pool cache in the following way.
Flushing shared pool command is an example about to test your testing techniques, flushing shared pool don’t flush everything, a simple select * from v$sql can shows it.
Flushing buffer cache will flush only unpinned buffers, x$bh.state is set to 0.
I tested this functionality and to compare some binds test, I had to restart the database. 3.A.i Shared Pool -- Yes,execute three times
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL; 3.A.ii Buffer Cache On 10g
ALTER SYSTEM FLUSH BUFFER_CACHE;
On 9.2
ALTER SESSION SET EVENTS = ' immediate trace name flush_cache ';

3.B Disabling features

3.B.i OPTIMIZER_FEATURES_ENABLE parameter OPTIMIZER_FEATURES_ENABLE=
{ 8.0.0 | 8.0.3 | 8.0.4| 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 |
8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 |10.0.0 | 10.1.0 }
Allows to disable new optimizer features. 3.B.ii Hidden Parameters To run some test you may need to disable some specific features using hidden parameters. This means you have to search and set the parameter and test if the specific hidden parameter, really disables what you need.
You can find information about hidden parameters in the view M.HIDDEN_PARAMETERS.

3.C Statistics ****falta

Copy statistics from production database example

4 Create Database and schema objects to test

Some test needs specific database and/or schema structures to test.

4.A Database Objects

For example if you want to test freelists, you must analyze if your tablespace is auto managed or not.

4.B Schema Objects

4.B.i Tables having one record per block To analyze dumps or to make easier the cound you can create tables with one record per block.
create table test_1row_1block pctfree 99 pctused 0 as select * from dba_objects where rownum <11 ; 4.B.ii Index leaf blocks To create a lot of leaf blocks for testing, you can specify a value of x higher than 50.
Create index idx on table tbl(col1,col2) pctfree x;

5 Generating test data

5.A Using dba_objects

Because dba_objects views has columns with data enough to run several tests, you can use it as the fastest way to generate data.
For example to generate an exact amount of record: 1,000,000 of records
DROP TABLE TEST.TEST_A;
CREATE TABLE TEST.TEST_A AS
SELECT A.*
FROM DBA_OBJECTS A, ( SELECT 1 FROM DBA_OBJECTS WHERE ROWNUM <= (SELECT CEIL(1000000/COUNT(*)) FROM DBA_OBJECTS ) )
WHERE ROWNUM <1000000+1;
SELECT COUNT(*) FROM TEST.TEST_A;

5.B DBMS_RANDOM

DBMS_RANDOM package generates random values, to get a more specific random data. 5.B.i Numbers Specifying a range of number you get a range of random number in that range.
SQL> select dbms_random.value(1,5) from dba_objects where rownum <3; Under the same dbms_random.seed(x), you get the same output;
SQL> exec dbms_random.seed(3);
SQL> select dbms_random.value(1,5) from dba_objects where rownum <3; SQL> exec dbms_random.seed(3);
SQL> select dbms_random.value(1,5) from dba_objects where rownum <3; 5.B.i.a Precision SQL> select val,round(val,2),trunc(val,2),round(val,-1),trunc(val,-1) FROM
2 ( select dbms_random.value(1100,1300) val FROM DBA_OBJECTS where rownum<15); 5.B.ii Strings dbms_random.String(Format, length );
'u', 'U' - returning string in uppercase alpha characters
SQL> select dbms_random.string('u',10) from dba_objects where rownum <3; 'l', 'L' - returning string in lowercase alpha characters
SQL> select dbms_random.string('l',10) from dba_objects where rownum <3; 'a', 'A' - returning string in mixed case alpha characters
SQL> select dbms_random.string('a',10) from dba_objects where rownum <3; 'x', 'X' - returning string in uppercase alpha-numeric
SQL> select dbms_random.string('x',10) from dba_objects where rownum <3; 'p', 'P' - returning string in any printable characters.
SQL> select dbms_random.string('p',10) from dba_objects where rownum <3;

5.C Using P_DATAGEN package

This is a very simple package, I did on the fly, to generate arithmetic and logarithmic increases in the number and string data. This is enough to small and medium test tables, you can modify to meet your specific needs. 5.C.i Number sequences 5.C.i.a Correlative SQL> exec M.P_DATAGEN.Initialize;
SQL> select m.p_datagen.correlative FROM DBA_OBJECTS where rownum<15; 5.C.i.b Arithmetic The number of repetitions increase in this way 1,2,3,4,5,…
SQL> exec M.P_DATAGEN.Initialize;
SQL> select m.p_datagen.arithmetic FROM DBA_OBJECTS where rownum<15; 5.C.i.c Logarithmic The number or repetitions increase in this way, 1, 1+2=3, …
SQL> exec M.P_DATAGEN.Initialize;
SQL> SELECT M.P_DATAGEN.Logarithmic FROM DBA_OBJECTS where rownum<15; 5.C.i.d Repetition To create effect like clustering you can specify the maximum number, before restarting the cound.
exec M.P_DATAGEN.Initialize;
SELECT M.P_DATAGEN.Logarithmic(2) FROM DBA_OBJECTS where rownum<15; 5.C.i.e Negatives SQL> exec M.P_DATAGEN.Initialize;
SQL> select m.p_datagen.correlative*-1 FROM DBA_OBJECTS where rownum<15; 5.C.i.f Random sequences SQL> exec M.P_DATAGEN.Initialize;
SQL> select TRUNC(DBMS_RANDOM.VALUE(CORR-2,CORR+2)) FROM
2 ( select m.p_datagen.correlative corr FROM DBA_OBJECTS where rownum<15); 5.C.ii Character sequences SQL> SELECT M.P_DATAGEN.NUMBER_TO_CHAR(ROWNUM-1) FROM DBA_OBJECTS where rownum <15; By default uses this characters cCharacters := 'abcdefghijklmnopqrstuvwxyz1234567890';
But you can change this specifying the characters you want
SELECT M.P_DATAGEN.NUMBER_TO_CHAR(ROWNUM-1,'01') FROM DBA_OBJECTS where rownum <15; 5.C.iii Date sequences You can generate dates using m.P_DATAGEN.F_DATE and timestamps m.P_DATAGEN.F_TIMESTAMP.
The reason to create this functions is to allow randomly generating the hour, minutes, seconds. If you don’t want that data randomly generated, better you get numbers and convert to date using TO_DATE(NUMBER,’j’) 5.C.iii.a Random Time sequences SQL> SELECT TO_CHAR(m.P_DATAGEN.F_DATE (SYSDATE+ROWNUM,SYSDATE+ROWNUM),'DD-MM-YYYY HH:MI:SS') FECHA
2 FROM DBA_OBJECTS WHERE ROWNUM <15; 5.C.iii.b Only date sequence SQL> exec m.p_datagen.nCorr := to_char(sysdate,'j');
SQL> select to_date(m.p_datagen.correlative,'j') FROM DBA_OBJECTS where rownum<15; 5.C.iii.c Trunc Before using trunc on dates, you should understand how it works
SQL> SELECT TO_CHAR(FECHA,'DDMONYYYY HH:MI:SS') FECHA,
2 TO_CHAR(TRUNC(FECHA,'YYYY'),'DDMONYYYY HH:MI:SS') C_YYYY,
3 TO_CHAR(TRUNC(FECHA,'MM'),'DDMONYYYY HH:MI:SS') C_MM,
4 TO_CHAR(TRUNC(FECHA,'HH'),'DDMONYYYY HH:MI:SS') C_HH
5 FROM ( SELECT m.P_DATAGEN.F_DATE(SYSDATE-800,SYSDATE-700) FECHA FROM DUAL);

5.D Analyzing column data distribution

5.D.i Data Order This query compares each value with the previous, if the previous is higher, it adds one to NO_ASCEN, the reversal for the colum NO_DESCEN, and if is equal to the previous, the column EQUAL adds a value.
20:52:04 SQL> select sum(ascen) no_ascen,sum(descen) no_descen,sum(equal) equal,count(*) total from
20:52:05 2 (
20:52:05 3 select object_id,lag(object_id,1,null) over (partition by 1 order by object_id ),
20:52:05 4 case when object_id < lag(object_id,1,null) over (partition by 1 order by object_id ) then 1 else 0 end as
cen,
20:52:05 5 case when object_id > lag(object_id,1,null) over (partition by 1 order by object_id ) then 1 else 0 end de
scen,
20:52:05 6 case when object_id = lag(object_id,1,null) over (partition by 1 order by object_id ) then 1 else 0 end eq
ual
20:52:05 7 from cachun.test_a)
20:52:06 8 /

6 Multiple user Testing Techniques

6.A Running several tasks at the same time

This technique comes from Tom Kyte.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:9088073430469#9099572884547