REM lwr.sql REM (c)2008-9 David Kurtz, Go-Faster Consultancy Ltd. www.go-faster.co.uk REM script to test lwr idea -- this script is not intended to be executed, but the various commands required are included, and I usually cut and paste them into SQL*Plus -------------------------------------------------------------------------------- clear screen set echo on spool lwr -------------------------------------------------------------------------------- --need to set up separate buffer pools for test -------------------------------------------------------------------------------- set serveroutput on show parameters cache_size alter system set db_cache_size = 400M; alter system set db_recycle_cache_size = 120M scope=both; alter system set db_8k_cache_size = 80M scope=both; alter system set db_16k_cache_size = 80M scope=both; alter system set db_32k_cache_size = 80M scope=both; show parameters background_dump_dest -------------------------------------------------------------------------------- --create some working storage tablespaces to do the test in -------------------------------------------------------------------------------- CREATE TABLESPACE lwr8k EXTENT MANAGEMENT LOCAL AUTOALLOCATE DATAFILE '/inuit/oradata16/ahris/lwr8k.dbf' size 10M BLOCKSIZE 8k / CREATE TABLESPACE lwr8k_1M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M DATAFILE '/inuit/oradata16/ahris/lwr8k_1M.dbf' size 10M BLOCKSIZE 8k / ALTER DATABASE DATAFILE '/inuit/oradata16/ahris/lwr8k.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 250M / ALTER DATABASE DATAFILE '/inuit/oradata16/ahris/lwr8k_1M.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 250M / -------------------------------------------------------------------------------- CREATE TABLESPACE lwr16k EXTENT MANAGEMENT LOCAL AUTOALLOCATE DATAFILE '/inuit/oradata16/ahris/lwr16k.dbf' size 10M BLOCKSIZE 16k / CREATE TABLESPACE lwr16k_1M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M DATAFILE '/inuit/oradata16/ahris/lwr16k_1M.dbf' size 10M BLOCKSIZE 16k / ALTER DATABASE DATAFILE '/inuit/oradata16/ahris/lwr16k.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 250M / ALTER DATABASE DATAFILE '/inuit/oradata16/ahris/lwr16k_1M.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 250M / -------------------------------------------------------------------------------- CREATE TABLESPACE lwr32k EXTENT MANAGEMENT LOCAL AUTOALLOCATE DATAFILE '/inuit/oradata16/ahris/lwr32k.dbf' size 10M BLOCKSIZE 32k / CREATE TABLESPACE lwr32k_1M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M DATAFILE '/inuit/oradata16/ahris/lwr32k_1M.dbf' size 10M BLOCKSIZE 32k / ALTER DATABASE DATAFILE '/inuit/oradata16/ahris/lwr32k.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 250M / ALTER DATABASE DATAFILE '/inuit/oradata16/ahris/lwr32k_1M.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 250M / -------------------------------------------------------------------------------- ALTER DATABASE DATAFILE 'D:\oracle\product\10.2.0\oradata\hcm89\psdefault.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 250M / GRANT RESOURCE TO scott; GRANT CREATE TABLE TO scott; GRANT EXECUTE ON dbms_monitor TO scott; GRANT SELECT_CATALOG_ROLE TO scott; -------------------------------------------------------------------------------- --build the package the does all the work -------------------------------------------------------------------------------- @@lwrpkg -------------------------------------------------------------------------------- --drop and recreate sets of working storage tables used by tests -------------------------------------------------------------------------------- set serveroutput on execute lwr.drop_tables(10, p_pool=>'DEFAULT'); execute lwr.drop_tables(10, p_pool=>'RECYCLE'); execute lwr.drop_tables(10, p_pool=>'PRESERVE'); execute lwr.drop_tables(10, p_pool=>'DELETE'); execute lwr.drop_tables(10, p_pool=>'DEFAULT', p_tablespace=>'LWR8k'); execute lwr.drop_tables(10, p_pool=>'DEFAULT', p_tablespace=>'LWR8k_1M'); execute lwr.drop_tables(10, p_pool=>'RECYCLE', p_tablespace=>'LWR8k'); execute lwr.drop_tables(10, p_pool=>'RECYCLE', p_tablespace=>'LWR8k_1M'); execute lwr.drop_tables(10, p_tablespace=>'LWR16k'); execute lwr.drop_tables(10, p_tablespace=>'LWR16k_1M'); execute lwr.drop_tables(10, p_tablespace=>'LWR32k'); execute lwr.drop_tables(10, p_tablespace=>'LWR32k_1M'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_pool=>'DEFAULT', p_tablespace=>'LWR8k'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_pool=>'DEFAULT', p_tablespace=>'LWR8k_1M'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_pool=>'RECYCLE', p_tablespace=>'LWR8k'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_pool=>'RECYCLE', p_tablespace=>'LWR8k_1M'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_tablespace=>'LWR16k'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_tablespace=>'LWR16k_1M'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_tablespace=>'LWR32k'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_tablespace=>'LWR32k_1M'); execute lwr.create_gtts(10, p_rowlen=>250, p_pk=>TRUE, p_gtt_type=>'P'); execute lwr.create_gtts(10, p_rowlen=>250, p_pk=>TRUE, p_gtt_type=>'D'); -------------------------------------------------------------------------------- --the default table is just something to fill the main buffer cache with some other blocks -------------------------------------------------------------------------------- execute lwr.drop_default_table; execute lwr.create_default_table(25000,1500); --execute lwr.create_default_table(25000,4000); -------------------------------------------------------------------------------- --clear out any lwr jobs from the job scheduler -------------------------------------------------------------------------------- BEGIN FOR i IN (SELECT * FROM user_jobs WHERE what like 'lwr.%') LOOP dbms_job.remove(i.job); END LOOP; END; / COMMIT / -------------------------------------------------------------------------------- --summary report of how things are set up, run it after the test to see how much space needed -------------------------------------------------------------------------------- set lines 132 column job format 990 column what format a100 column object_type format a12 column object_name format a25 column table_name format a25 column segment_name format a25 column segment_type format a10 column tablespace_name format a15 compute sum of mb on report break on report skip 1 -------------------------------------------------------------------------------- show parameters cache_size select object_name, object_type, status from user_objects where object_name like 'LWR%' ORDER BY 1,2 / select table_name, tablespace_name, buffer_pool, blocks from user_tables where table_name like 'LWR%' order by 1 / select segment_name, segment_type, tablespace_name , sum(extents) extents , sum(blocks) blocks , sum(bytes)/1024 Mb , avg(bytes/blocks) blocksize from user_segments where segment_name like 'LWR%' group by segment_name, segment_type, tablespace_name order by 1,2,3 / -------------------------------------------------------------------------------- --this is how to administer the test --use to execute the tests - one of these at a time or set p_wait to true -------------------------------------------------------------------------------- set serveroutput on spool lwr_test execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K' , p_pool=>'DEFAULT' , p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K_1M' , p_pool=>'DEFAULT' , p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K' , p_pool=>'RECYCLE' , p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K_1M' , p_pool=>'RECYCLE' , p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR16k' , p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR16k_1M' , p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR32k' , p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR32k_1M' , p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K' , p_pool=>'DEFAULT' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'R'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K_1M' , p_pool=>'DEFAULT' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'R'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K' , p_pool=>'RECYCLE' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'R'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K_1M' , p_pool=>'RECYCLE' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'R'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR16k' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'R'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR16k_1M' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'R'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR32k' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'R'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR32k_1M' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'R'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K' , p_pool=>'DEFAULT' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'D'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K_1M' , p_pool=>'DEFAULT' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'D'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K' , p_pool=>'RECYCLE' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'D'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR8K_1M' , p_pool=>'RECYCLE' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'D'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR16k' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'D'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR16k_1M' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'D'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR32k' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'D'); execute lwr.submit(10, p_iterations=>1, p_tablespace=>'LWR32k_1M' , p_trace=>1, p_wait=>TRUE, p_delete_type=>'D'); execute lwr.submit(10, p_iterations=>1, p_rows=>10000 , p_pool=>'PRESERVE', p_trace=>1, p_wait=>TRUE); execute lwr.submit(10, p_iterations=>1, p_rows=>10000 , p_pool=>'DELETE' , p_trace=>1, p_wait=>TRUE); spool off --use these commands to see what is running select job, next_date, what from user_jobs; select * from dba_jobs_running r; select * from sys.x$ksqrs where KSQRSIDT = 'RO' / --_db_fast_obj_truncate select l.*, s.program from v$lock l, v$session s where l.type = 'RO' and s.sid = l.sid / /*------------------------------------------------------------------------------ --this section is commented out because its just some stuff used during development and testing -------------------------------------------------------------------------------- set serveroutput on execute lwr.drop_tables(10, p_tablespace=>'LWR32k'); execute lwr.create_tables(10, p_rows=>10000, p_rowlen=>250, p_pk=>TRUE, p_tablespace=>'LWR32k'); execute lwr.drop_tables(10); execute lwr.create_gtts(10, p_rowlen=>250, p_pk=>TRUE, p_gtt_type=>'P'); --execute lwr.create_tables(10,10000,250, p_pk=>TRUE, p_tablespace=>'LWR32k'); BEGIN lwr.test(p_instance=>1, p_iterations=>1, p_tablespace=>'LWR32k', p_trace=>TRUE, p_rows=>100, p_rowlen=>250, p_delete_type=>'T'); lwr.test(p_instance=>1, p_iterations=>1, p_tablespace=>'LWR32k', p_trace=>TRUE, p_rows=>100, p_rowlen=>250, p_delete_type=>'R'); lwr.test(p_instance=>1, p_iterations=>1, p_tablespace=>'LWR32k', p_trace=>TRUE, p_rows=>100, p_rowlen=>250, p_delete_type=>'D'); END; / BEGIN lwr.test( p_instance=>5 , p_iterations=>10 , p_pool=>'DEFAULT' , p_tablespace=>'LWR32k' , p_delete_Type=>'T' ); END; / execute lwr.submit(1,1, p_tablespace=>'LWR8K', p_pool=>'RECYCLE', p_trace=>1, p_wait=>TRUE); -------------------------------------------------------------------------------- INSERT INTO lwr12_RECYCLE SELECT * FROM lwr11_RECYCLE 10000 rows processed TRUNCATE TABLE lwr11_RECYCLE INSERT INTO lwr11_RECYCLE SELECT * FROM lwr12_RECYCLE 10000 rows processed TRUNCATE TABLE lwr12_RECYCLE execute lwr.create_default_table(10,10); execute lwr.drop_default_table; --the default table is just something to fill the main buffer cache with some other blocks execute lwr.drop_default_table; --execute lwr.create_default_table(25000,4000); DROP TABLESPACE lwr8k INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE lwr8k_1M INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE lwr16k INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE lwr16k_1M INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE lwr32k INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE lwr32k_1M INCLUDING CONTENTS AND DATAFILES; --------------------------------------------------------------------------------*/