Hello
We have a Oracle 11g Release 2 database with five identical working Schemas
being accessed by a VB Client Server business application
The db server, with 32GB RAM just hosts this one database
Copies of Client applications are installed across RDP Servers
Two of this Schemas now hold data for about ten & four years respectively
- application users for these two underlying Schemas are facing increasingly slow responses for more than a year now
- archaic design is definitely an issue here but we are not much in a position to persuade the vendor for the required overhaul
I am sure performance can be improved if tuning is done at the database end,
but that perhaps should be a separate question for now
Meanwhile, I've been manually running the below statements which somewhat eases response times for a couple of days
I would like to automate this through the Job Scheduler (individually for the affected Schemas) ...
But SQL Developer (Type of Job: PL/SQL Block) does not allow me to
For eg, the Run Log shows the following when a Compile is attempted -
"ORA-06550: line 2, column 7:
PLS-00222: no function with name 'ALTER_COMPILE' exists in this scope
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 3, column 7:
PLS-00222: no function with name 'ALTER_COMPILE' exists in this scope
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
"
Below are all the statements I have been trying to put in the Scheduler -
EXEC DBMS_STATS.gather_schema_stats(SCHEMA1,DBMS_STATS.AUTO_SAMPLE_SIZE);
alter procedure SCHEMA1.PROC1 compile;
alter procedure SCHEMA1.PROC2 compile;
EXEC DBMS_DDL.alter_compile('PROCEDURE', SCHEMA1, 'PROC1');
EXEC DBMS_DDL.alter_compile('PROCEDURE', SCHEMA1, 'PROC2');
Pls advise the correct way to go about the task
Thank you
My first concern is that you are needing to regular compile plsql in the database.... that should be a rarity (like when you deploy applications etc).
Similar, statistics should be gathered automatically by the database every night.
Anyway...if you need to schedule a plsql program,. the scheduler syntax looks like this
begin
dbms_scheduler.create_job (
job_name => 'MYJOB',
job_type => 'PLSQL_BLOCK',
job_action => 'begin DBMS_DDL.alter_compile(''PROCEDURE', ''SCHEMA1'', ''PROC1''); end;',
start_date => systimestamp,
repeat_interval => 'freq=daily; byhour=10; byminute=0; bysecond=0;',
enabled => true);
end;
/