You Asked

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

and Connor said...

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;
/

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Scheduling Compilation or Execution of Stored Procedures - Review 1

RV, February 25, 2020 - 2:22 pm UTC

Many thanks Connor :-)
I absolutely get your points ...
But right now I am just trying to stabilise the db responses so that the application users can get on with their work unhindered
Once that's done, I'll concentrate on the performance tuning

1. Coming back to the issues, I was able to automate Compilation of the required Procedures, through the SQL Developer interface - the JOBs should be running on their own from tomorrow

2. Request your help on automating this statement though -

DBMS_STATS.gather_schema_stats('INFRA',DBMS_STATS.AUTO_SAMPLE_SIZE);
-- admit that I do not fully understand it yet

Running this (manually, with EXEC) has definitely helped in getting some of the processes which become unresponsive

TIA

February 26, 2020 - 1:31 am UTC

If you run this

SQL> select client_name, count(*)
  2  from  DBA_AUTOTASK_JOB_HISTORY
  3  group by client_name;

CLIENT_NAME                                                        COUNT(*)
---------------------------------------------------------------- ----------
auto space advisor                                                     1376
auto optimizer stats collection                                        1376
sql tuning advisor                                                       30

if you see a row for optimizer stats collection, then already we are (nightly) gathering optimizer stats.

Unless you have a specific need, it is unlikely that you need to have your own stats gather job.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Scheduling Compilation or Execution of Stored Procedures - Review 1

More to Explore

PL/SQL demos

PL/SQL docs