We are trying to optimize the below query(SELECT statement from USERS table). On first time execution, it takes 47ms and on subsequent execution it takes averagely about 5ms. If I run it again after 10 minutes, it takes same 47ms on average.
Does it mean that I need to increase the size of SGA to retain the query/blocks there for longer time. The USERS table is a small table containing about 500 rows. Followings are SGA parameters. The machine is not being used actively because this is a dev database.
sga_max_size = 325MB
sga_target = 325MB
Query:
DECLARE
v_id number;
v_user varchar2(100);
v_password varchar2(1000);
v_description varchar2(100);
v_active char(1);
v_update_time date;
start_time TIMESTAMP;
end_time TIMESTAMP;
BEGIN
start_time := SYSTIMESTAMP;
SELECT ID, name, password,description,active, update_time
INTO v_id, v_user, v_password,v_description,v_active, v_update_time
FROM USERS
WHERE user_name = 'userName2';
end_time := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE ( (EXTRACT (SECOND FROM end_time) - EXTRACT (SECOND FROM start_time)) * 1000);
END;
/
We are using Oracle 10g R2 with Linux.