Tuesday, April 14, 2009

Query execution time in Oracle?

Oracle 10g has R1 and R2 (r stands for Release).
I hope you have installed R3 (10.2.0.x)

To sped up the processing you have to do 2 important things:

1. To investigate and possibly to increase the size of SGA components - db_buffer_cache, shared_pool_cache

2. To run the statistics package (as SYS):

CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
dbms_stats.gather_database_stats(cascade=>true);
END compute_statistics;
/

Now either run:

execute compute_statistics;

or schedule once weekly:


set serveroutput on
set linesize 200000
variable x number;
begin
DBMS_OUTPUT.enable(200000);
dbms_job.submit(:x,'compute_statistics;',trunc(sysdate),'trunc(sysdate+7)');
commit;
dbms_output.put_line(TO_char(:x));
end;

No comments: