English

How To: Use an Oracle database job to analyze the state_lineages table

Summary

The SDE.STATE_LINEAGES table is analyzed when the table is created during ArcSDE installation or during an upgrade. From that point on, ArcSDE does not analyze the table, the DBA is responsible for it.

Once the database is being used in a production environment, the number of rows in the table can grow significantly.

If the database statistics for the table are not current and accurate, the Oracle optimizer might choose a sub-optimal execution plan for fetching rows. This can have a negative affect on all version queries, because the query performs a selection from the table to detect the lineage for a version. Therefore, the state_lineages table may need to be analyzed daily, depending on the frequency of edit operations during the day.

The solution is to use an Oracle database job that analyzes the table during off peak hours.

Procedure

Ensure one SNP process is running to execute queued jobs in the background. SNP processes will periodically wake up and execute any queued jobs. SNP background processes differ from other Oracle background processes in that the failure of an SNP process does not cause the instance to fail. If an SNP process fails, Oracle will restart it.

SNP background processes will not execute jobs if the system has been started in restricted mode. You can, however, use the ALTER SYSTEM statement to turn this behavior on and off as follows:

Code:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;

Creating the database job requires performing two steps: creating the stored procedure to be executed by the job, and creating the job that executes the procedure.

In SQL*Plus, as the SDE user, create the stored procedure that the database job will execute:

Code:
CREATE OR REPLACE PROCEDURE s_lineage_analyze IS

sqlstm VARCHAR2(64);

BEGIN
sqlstm := 'ANALYZE TABLE state_lineages COMPUTE STATISTICS';
EXECUTE IMMEDIATE sqlstm;
END;
/

You could simply execute the new stored procedure when they needed to update the table's statistics:

Code:
EXECUTE s_lineage_analyze;

It is better, however, to use a database job that can execute the procedure on a daily basis. Create the database job by submitting the request to the Oracle instance in SQL*Plus.

1. Declare a variable called 'jobno' to store the number of the job when you submit the request.

Code:
VARIABLE jobno number;

2. Submit the database job by using the SYS.DBMS_JOB package. If your user does not have permissions to execute the procedure, connect as the SYS user and grant the appropriate privileges. This example submits a job to execute the 'sde.s_lineage_analye' procedure every night at midnight.

Code:
BEGIN
DBMS_JOB.SUBMIT
(:jobno,
'sde.s_lineage_analyze;',
trunc(sysdate) + 1,
'SYSDATE + 1');
COMMIT;
END;
/

Once submitted, you can print the value of the jobno variable.

Code:
PRINT jobno;

Use the 'jobno' to execute the database job. This will confirm that the job has been submitted and works correctly.

Code:
EXECUTE dbms_job.run(jobno);

Alternatively, you can select from user_jobs for the job's relevant information:

Code:
SELECT job, next_date, interval, failures, broken FROM user_jobs;

Note:
Portions of this article are from Oracle's Documentation. It is not intended to replace or supersede Oracle documentation. Please refer to Oracle documentation for additional information and further clarifications.