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 -r1.4 -r1.4.2.1 --- openacs-4/packages/acs-core-docs/www/maint-performance.adp 25 Apr 2018 08:38:28 -0000 1.4 +++ openacs-4/packages/acs-core-docs/www/maint-performance.adp 2 Mar 2019 19:30:05 -0000 1.4.2.1 @@ -1,5 +1,5 @@ -{/doc/acs-core-docs {ACS Core Documentation}} {Diagnosing Performance Problems} +{/doc/acs-core-docs/ {ACS Core Documentation}} {Diagnosing Performance Problems} Diagnosing Performance Problems 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 Analysis +

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
    +a runaway Oracle process.

    Log in to SQL*Plus as the admin:

    [$OPENACS_SERVICE_NAME ~]$ svrmgrl
     
     Oracle Server Manager Release 3.1.7.0.0 - Production
     
    @@ -71,10 +71,9 @@
     enabled in the database. This imposes a performance penalty and
     should not be done in normal operation.

    Edit the file postgresql.conf - its location depends on the PostGreSQL installation - and change

    #stats_command_string = false

    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>
    @@ -111,7 +110,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