Need DDL for an Oracle Table that would be 1 row = 1 8K Block

Question

For performance testing in our performance environment we want a means to turn over SGA the way production does. Our performance environment is a fraction of production and production's very nature makes it difficult to simulate on the performance environment.

When we test on the performance environment we tend to be very focussed on the type of workload we are testing and on the data that we have configured for the scenario. The result of all of this is that after a few runs we get performance improvements that we are not likely to see on production because, on the performance environment we are churning over the same data again and again in cache.

Clearing SGA (its an Oracle environment) also clears system level buffers for table definitions and schemas that negatively impact performance the first time we test. The second time we test the cache is too full of data and are not simulating the IO properly.

The solution we have imagined is to have an app read data from some purpose built table to 'turn over' SGA in the same way diverse data and workloads turn over SGA in production.

The imagined solution has this app connect to each database on the cluster in turn (bypassing the connection load balancer) and reading a row from the database from a purpose built table. The app round-robbin's the connections reading a row in turn and returning some small portion of the data but forcing Oracle to read the block. Given that the purpose built table contains more row data in KB's than the total SGA of the cluster, and given that we throttle the reads we can then say that we turn over all SGA in N minutes, which is what we want. With this we should be able to simulate SGA turn over at different rates and thereby simulate how our test load behaves under the varying load levels that production experiences.

The above spec imagines a row of fixed size that will be small enough to fit into a block but large enough that no more than one row will fit into a block. This will make the calculations easier when calculating for how many rows are required to turn over SGA or what rate to read the rows at to turn over SGA say in 5 or 10 minutes.

My question is this - what will the imagined row definition look like given that we use 8K block sizes. What other parameters will be advised and what gotchas do we have to consider in this imagined solution?

Any competing ideas are most welcome.

Answer 1

If all you want to do is waste a lot of space, you don't need a fancy table at all. A single varchar(4000) (filled up) should be enough:

SQL> create table foo(a varchar(4000));

Table created.

SQL> insert into foo select rpad(to_char(rownum), 4000, '*')
                     from dual connect by level <= 1024 ;

1024 rows created.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks from user_tables where table_name = 'FOO';

  NUM_ROWS     BLOCKS
---------- ----------
      1024   1126

Reason: there's no room to put another 4000 byte row in a 8k block given the block and row headers.

Or play with a small row but a stupid PCT_FREE. For example:

SQL>  create table foo (a varchar(128)) pctfree 99 ;

Table created.

SQL> insert into foo select rpad(to_char(rownum), 128, '*') 
                     from dual connect by level <= 1024 ;

1024 rows created.

SQL> commit ;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks from user_tables where table_name = 'FOO';

  NUM_ROWS     BLOCKS
---------- ----------
      1024   1126

Add an ordinary primary key and you can select whatever you want from there, with one row eating up about one block in SGA.

What you must really avoid is full-scanning that table (or anything that goes to direct reads and bypasses the SGA).

---

That being said, have you looked into simply reducing the SGA on your test system? If your volume of data (or the "width" of the dataset you can test) is X times smaller than production, start testing with X times less SGA and tweak (up or down) to get your IO subsystem to work. (You'll probably want more than X times less if your production SGA isn't large.)

I believe you'll have a better chance of having the SGA contents more representative of a real system (especially index/data block mix).