Index: openacs-4/packages/acs-core-docs/www/maint-performance.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/maint-performance.adp,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-core-docs/www/maint-performance.adp 7 Aug 2017 23:47:51 -0000 1.2 +++ openacs-4/packages/acs-core-docs/www/maint-performance.adp 8 Nov 2017 09:42:11 -0000 1.3 @@ -32,15 +32,15 @@

This should return a list of database queries on the page, including the exact query (so it can be cut-paste into psql or oracle) and the time each query took.

-

Figure 6.8. Query +

Figure 6.8. Query Analysis example

Query Analysis example

  • Identify a runaway Oracle query: first, use ps aux or top to get the UNIX process ID of a runaway Oracle process.

    Log in to SQL*Plus as the admin:

    -[$OPENACS_SERVICE_NAME ~]$ svrmgrl
    +[$OPENACS_SERVICE_NAME ~]$ svrmgrl
     
     Oracle Server Manager Release 3.1.7.0.0 - Production
     
    @@ -83,9 +83,10 @@
     

    to

     stats_command_string = true
     

    Next, connect to postgres (psql -service0 -) and select * from pg_stat_activity;. Typical -output should look like:

    +service0
    +) and
    +select * from
    +pg_stat_activity;. Typical output should look like:

       datid   |   datname   | procpid | usesysid | usename |  current_query
     ----------+-------------+---------+----------+---------+-----------------
      64344418 | openacs.org |   14122 |      101 | nsadmin | <IDLE>
    @@ -123,7 +124,7 @@
     query, install "autotrace". I usually follow the
     instructions here http://asktom.oracle.com/~tkyte/article1/autotrace.html.

    -Make sure, that the Oracle CBO works with +Make sure, that the Oracle CBO works with adequate statistics

    The Oracle Cost Based optimizer is a piece of software that tries to find the "optimal" execution plan for a given SQL statement. For that it estimates the costs of running a SQL