Expression queue latency on Oracle 10g and Oracle 11g

Technote (troubleshooting)


Problem(Abstract)

Latency is seen in expression queue execution when unusually large number of expressions are queued up with the database Oracle 10g and Oracle 11g.

Cause

The problem is reported when user actions triggers large number of expression evaluations. It can be triggered in below scenario

  • Importing a new workspace from template
  • Importing or updating elements

    The resulting spike in expression queue table size, will stale the statistics and the execution plan is non-optimal.

Environment

Oracle 10g , Oracle 11g

Diagnosing the problem

Oracle database reports like ADDM , AWR show the top slowest query with each query taking more time to complete.

Resolving the problem

Database Administrator (DBA) should perform SQL tuning using the Oracle Enterprise Manager to run the advisor and pick optimal execution plans from alternatives.
Solutions are:

  • Most recommended solution is to Gather Statistics frequently on following tables, to prevent stale indexes
    - formulaqueue
    ( Schedule a job to gather statistics on above table every 2 hours )
    - formulaXX
    - attXX
    - eleXX
    ( Schedule a job to gather statistics on above table every 8 hours. Here XX is the table id of workspace. If necessary, gather more frequently )
  • Run SQL Advisors such as Tuning Advisor, Memory Advisor and apply relevant recommendations
  • Choose any of following recommendations by weighing in cost based optimizer:
    - Gather statistics for stale indexes
    - Perform SQL profile plan
    - Implement alternate plans

Rate this page:

(0 users)Average rating

Document information


More support for:

Rational Focal Point
Documentation

Software version:

6.4.1, 6.4.1.1, 6.4.1.2, 6.4.1.3, 6.5, 6.5.0.1

Operating system(s):

Linux, Solaris, Windows

Reference #:

1461146

Modified date:

2011-02-01

Translate my page

Machine Translation

Content navigation