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.1.2.11 -r1.1.2.12 --- openacs-4/packages/acs-core-docs/www/maint-performance.adp 21 Jun 2016 07:44:36 -0000 1.1.2.11 +++ openacs-4/packages/acs-core-docs/www/maint-performance.adp 23 Jun 2016 08:32:45 -0000 1.1.2.12 @@ -19,15 +19,16 @@ degrade in performance. For PostGreSQL, see the section called “Vacuum Postgres nightly”. For Oracle, use exec -dbms_stats.gather_schema_stats('SCHEMA_NAME') (Andrew Piskorski's Oracle notes).

  • +dbms_stats.gather_schema_stats('SCHEMA_NAME') +(Andrew Piskorski's Oracle notes).

  • You can track the exact amount of time each database query on a page takes:

    1. Go to Main Site : Site-Wide Administration : Install Software -

    2. Click on "Install New Application" in "Install from OpenACS -Repository"

    3. Choose "ACS Developer Support">

    4. After install is complete, restart the server.

    5. Browse to Developer Support, which is automatically mounted at -/ds.

    6. Turn on Database statistics

    7. Browse directly to a slow page and click "Request Information" -at the bottom of the page.

    8. +

    9. Click on "Install New Application" in "Install +from OpenACS Repository"

    10. Choose "ACS Developer Support">

    11. After install is complete, restart the server.

    12. Browse to Developer Support, which is automatically mounted at +/ds.

    13. Turn on Database statistics

    14. Browse directly to a slow page and click "Request +Information" at the bottom of the page.

    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.

      @@ -72,7 +73,7 @@

      To kill a troubled process:

       alter system kill session 'SID,SERIAL#';  --substitute values for SID and SERIAL#

      (See Andrew -Piskorski's Oracle notes)

      +Piskorski's Oracle notes)

    16. Identify a runaway Postgres query. First, logging must be enabled in the database. This imposes a performance penalty and @@ -112,23 +113,23 @@ Statspack, a package to monitor and save the state of the v$ performance views. These reports help finding severe problems by exposing summary data about the Oracle wait interface, executed -queries. You'll find the installation instructions in +queries. You'll find the installation instructions in $ORACLE_HOME/rdbms/admin/spdoc.txt. Follow the instructions -carefully and take periodic snapshots, this way you'll be able to -look at historical performance data.

      Also turn on the timed_statistics in your init.ora file, so that +carefully and take periodic snapshots, this way you'll be able +to look at historical performance data.

      Also turn on the timed_statistics in your init.ora file, so that Statspack reports (and all other Oracle reports) are timed, which makes them a lot more meaningful. The overhead of timing data is about 1% per Oracle Support information.

      To be able to get a overview of how Oracle executes a particular -query, install "autotrace". I usually follow the instructions here -http://asktom.oracle.com/~tkyte/article1/autotrace.html.

      +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 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 query -in a particular way (by default up to 80.000 permutations are being -tested in a Oracle 8i). To get an adequate cost estimate, the CBO -needs to have adequate statistics. For that Oracle supplies the +tries to find the "optimal" execution plan for a given +SQL statement. For that it estimates the costs of running a SQL +query in a particular way (by default up to 80.000 permutations are +being tested in a Oracle 8i). To get an adequate cost estimate, the +CBO needs to have adequate statistics. For that Oracle supplies the dbms_stats package.