000028166 - KB-1096 - Reviews running slow

Document created by RSA Customer Support Employee on Jun 15, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000028166
Applies ToAffected Versions: 3.6.X; 3.5.X
IssueReviews running very slow / or system generally sluggish
 
CauseEnsure Oracle statistics are running and enabled
ResolutionThis should not be necessary and is discouraged from being enabled on systems running version 4 and above as there are internal routines and jobs to correctly update the optimizer statistics
1. Stop the Aveksa services(optional but recommended)
 
sudo service aveksa_agent stop

sudo service aveksa_server stop

2. Check the stats package with this SQL (run sqlplus as the sysdba)
 
SQL> SELECT JOB_NAME, RUN_COUNT, ENABLED FROM DBA_SCHEDULER_JOBS;

 
JOB_NAME RUN_COUNT ENABL

------------------------------ ---------- -----

PURGE_LOG 19 TRUE

FGR$AUTOPURGE_JOB 0 FALSE

GATHER_STATS_JOB 0 FALSE

AUTO_SPACE_ADVISOR_JOB 16 TRUE

MGMT_CONFIG_JOB 16 TRUE

MGMT_STATS_CONFIG_JOB 1 TRUE

RLM$EVTCLEANUP 424 TRUE

RLM$SCHDNEGACTION 442 TRUE

Look at the GATHER_STATS_JOB
3. If its false you can enable the automatic job (step 4) then recompute the optimizer statistics (step 5) or just update the statistics manuall as shown in step 5.
4. To enable the automatic gathering of statistics run (as sysdba)
 
exec dbms_scheduler.enable('GATHER_STATS_JOB');

5. To immediately recompute the Oracle statiscics run this command as sysdba.
 
exec dbms_stats.gather_schema_stats('AVUSER');

5. Check to see if it updated by running this sql
 
SQL> select table_name, status, num_rows, last_analyzed

from dba_tables where table_name like 'T_%'

(look at the last_analyzed column) it should have today's date
6. Restart Aveksa Services (if stopped in step #1)
7. Retest the review
 
NotesOther items
- Alternate check to see if stats are on
 
SELECT count(*) from user_histograms;
or
SELECT * from user_tables WHERE avg_space is not null;
 

This will return 'no rows selected' when no stats are present.
- To delete the current stats
 
exec dbms_stats.delete_schema_stats('AVUSER');

------------------------------------------------------------------------------------------------------------------------------
What are Oracle Statistics
"Oracle statistics" is about Oracle optimizer "Metadata statistics" in order to provide the cost-based SQL optimizer (which makes your operations faster) with the information about the nature of the tables. Oracle statistics tell you the size of the tables, the distribution of values within a columns, and other important information so that SQL statements will always generate the "best" execution plans.
bad statistics = slow system
 
 

last updated 10/22/2010

Attachments

    Outcomes