Tuning Redwood Server Database Queries

Row limits (all databases except Oracle) and hints (Oracle) have been introduced. These can be set on the low-level SQL-layer that connects to the database.

note

Special care must be taken when you set these; always refer to your DBA for assistance. Redwood recommends you also consult a Redwood Redwood Server consultant prior to setting these.

Accessing Support Facility

You use the built-in Support Facility to inspect, set and modify row limits or hints. The Support Facility is available at the following URL http[s]://<server>:<port>/<context>/support.

• <server> - FQDN of your Redwood Server system
• <port> - Port of your Redwood Server system
• <context> - the web application context, usually scheduler

An example URL: https://pr1.example.com:53000/redwood/support

Inspecting and Setting Row Limits or Hints

To display the available options, specify the following in the Query field of the Support Facility:

/* HELP */

note

Please type the exact text above, upper-case. There is always a space after a /*,:, and before */.

The output of the HELP keyword can be seen below:

/* SECURITY: <objectType> */ <query> - set security for <objectType>
/* SECURITY: ? <objectType> */ - get security for <objectType>
/* CACHE: enable */ - enable cache
/* CACHE: disable */ - disable cache
/* CACHE: flush */ - flush cache
/* CACHE: ? */ - query cache enabled/disabled
/* CACHE: statistics */ - get cache statistics
/* ROW_LIMIT: enable */ - enable row limits
/* ROW_LIMIT: disable */ - disable row limits
/* ROW_LIMIT: ? */ - query row limits enabled/disabled
/* CONTEXT_RULES: */ flush - (single word only) reset thread context mapping rules to defaults
/* CONTEXT_RULES: */ <name>=<regex> - (may be multiple lines, ordered) set thread context mapping rules
/* CONTEXT_RULES: ? */ - get thread context mapping rules
/* HINT: <context> */ <hints> - set hints for <context> to <hints>
/* HINT: ? */ <query> - query all hints

note

The above listed keywords are case-sensitive.

note

Although a test query is executed using your hint/row limit prior to saving your settings, great care must be taken when these are set.

ROW_LIMITS

You set ROW_LIMITS for all supported databases except Oracle. Note that ROW_LIMITS are not applied to queries that specifically expect all rows to be returned.

HINTS

You set HINTS for Oracle databases.

See Also

Oracle Database - Using Optimizer Hints

onsiteTopic