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.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).
You can track the exact amount of time each database query on a page takes:
Go to Main Site : Site-Wide Administration : Install Software -
Click on "Install New Application" in "Install from OpenACS -Repository"
Choose "ACS Developer Support">
After install is complete, restart the server.
Browse to Developer Support, which is automatically mounted at
-/ds
.
Turn on Database statistics
Browse directly to a slow page and click "Request Information" -at the bottom of the page.
Click on "Install New Application" in "Install +from OpenACS Repository"
Choose "ACS Developer Support">
After install is complete, restart the server.
Browse to Developer Support, which is automatically mounted at
+/ds
.
Turn on Database statistics
Browse directly to a slow page and click "Request +Information" at the bottom of the page.
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.
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)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.
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.