It is not uncommon for databases to be bi-modal, operating OLTP during the day (CPU-intensive) and doing aggregations and rollups (I/O-intensive) at night. I describe this technique in detail in my book “Oracle Tuning: The Definitive Reference”, but the idea is simple.
You can capture CPU and I/O statistics using dbms_stats and then swap-them in as your processing mode changes. Most shops do this with the dbms_scheduler (dbms_job) package so that the statistics are swapped at the proper time.
Oracle Metalink has detailed script listings in Note 149560.1 “Collect and Display System Statistics (CPU and IO) for CBO usage”:
/* e.g. activate the DAY statistics each day at 7:00 am */
DECLARE
I NUMBER;
BEGIN
DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s
tatown => ''SYSTEM'', statid => ''DAY'');', trunc(sysdate) + 1 + 7/24, 'sysdate
+ 1');
END;
/
/* e.g. activate the NIGHT statistics each day at 9:00 pm */
DECLARE
I NUMBER;
BEGIN
DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s
tatown => ''SYSTEM'', statid => ''NIGHT'');', trunc(sysdate) + 1 + 21/24, 'sysdate
+ 1');
END;
/
*** ********************************************************
*** Initialize the OLTP System Statistics for the CBO
*** ********************************************************
1. Delete any existing system statistics from dictionary:
SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS;
PL/SQL procedure successfully completed.
2. Transfer the OLTP statistics from OLTP_STATS table to the dictionary tables:
SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(-
> stattab => 'OLTP_stats', statid => 'OLTP', statown => 'SYS');
PL/SQL procedure successfully completed.
3. All system statistics are now visible in the data dictionary table:
SQL> select * from sys.aux_stats$;
SQL> select * from aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- ------------------ ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-09-2001 16:40
SYSSTATS_INFO DSTOP 08-09-2001 16:42
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN SREADTIM 7.581
SYSSTATS_MAIN MREADTIM 56.842
SYSSTATS_MAIN CPUSPEED 117
SYSSTATS_MAIN MBRC 9
where
=> sreadtim : wait time to read single block, in milliseconds
=> mreadtim : wait time to read a multiblock, in milliseconds
=> cpuspeed : cycles per second, in millions
*** ********************************************************
*** CPU_COST and IO_COST in PLAN_TABLE table
*** ********************************************************
SQL> explain plan for select * from oltp.test where c='AAAHxGAABAAAJS1AEZ';
Explained.
SQL> select operation, options, object_name, cpu_cost, io_cost
2 from plan_table;
OPERATION OPTIONS OBJECT_NAME CPU_COST IO_COST
------------------ -------------------- ------------ ---------- ----------
SELECT STATEMENT 10500 1
INDEX UNIQUE SCAN SYS_C002218 10500 1
SQL> truncate table plan_table;
SQL> explain plan for select * from oltp.test;
Explained.
SQL> select operation, options, object_name, cpu_cost, io_cost
2 from plan_table;
OPERATION OPTIONS OBJECT_NAME CPU_COST IO_COST
------------------ -------------------- ------------ ---------- ----------
SELECT STATEMENT 2677480 27
INDEX FAST FULL SCAN SYS_C002218 2677480 27
