Document Properties
Kbid
2955P9
Last Modified
08-Jul-2020
Added to KB
09-Jul-2020
Public Access
Everyone
Status
Online
Doc Type
References
Product
  • ICM 7.10
  • ICM 11
Job - AnalyzeDatabaseSchema

Name

AnalyzeDatabaseSchema

Domain

root

Job Dependencies

none

AcquiredResources

Named Resources: Database

Cartridge

core

Pipeline

AnalyzeDatabaseSchema-Start

Affected Domain Objects

All business objects, globally

Affected Tables

Only Oracle dictionary tables (user_tables, user_indexes, ...)

Stored Procedure

gather_schema_stats

Default State

Enabled

Is Site Specific

False

Edit State

Locally Enabled

Live State

Locally Enabled

Description

Analyzes the database schema to optimize the database query performance. Disabling the Oracle GATHER_STATS_JOB entirely is not recommended because it also gathers dictionary stats (SYS/SYSTEM schemas). Therefore changing the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using dbms_stats.set_param('AUTOSTATS_TARGET', 'ORACLE') is recommended.

The following file can be used to adjust the settings: IS_SHARE/system/config/cluster/database.properties.

Scheduling

Depending on storefront traffic and batch tasks like data import this job should be executed at least once a day.

What Happens Switched Off

In production systems with heavy storefront traffic some tables (basket, etc) grow.

If this job does not run, querying these tables may take a lot of time, because the Oracle optimizer works with wrong statistics.

Trouble Shooting

The job calls the stored procedure gather_schema_stats, which can be executed manually in the database using SQL*Plus:

SQL> set serveroutput on size 10000000
SQL> exec gather_schema_stats
[2012-07-05 08:48:28.665 +00:00] gather_schema_stats: started.
[2012-07-05 08:49:00.736 +00:00]                user: ...
[2012-07-05 08:49:00.736 +00:00]    estimate_percent: 5
[2012-07-05 08:49:00.736 +00:00]             cascade: TRUE
[2012-07-05 08:49:00.736 +00:00]          method_opt: FOR ALL COLUMNS SIZE 1
[2012-07-05 08:49:00.736 +00:00]             options: GATHER
[2012-07-05 08:49:00.736 +00:00]        block_sample: FALSE
[2012-07-05 08:49:00.737 +00:00]              degree: 0
[2012-07-05 08:49:00.737 +00:00]         granularity: AUTO
[2012-07-05 08:49:00.737 +00:00]       no_invalidate: FALSE
[2012-07-05 08:49:00.737 +00:00]               force: FALSE
[2012-07-05 08:49:00.737 +00:00] gather_schema_stats: finished, elapsed time:
32.07 sec.

PL/SQL procedure successfully completed.

Comments

Only disable this job when you handle this another way.

Depending on storefront traffic or batch operations (e.g., import) this job should be executed at least once a day.

Product Version

6.1

Product To Version


Status
Disclaimer
The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.
The Intershop Knowledge Portal uses only technically necessary cookies. We do not track visitors or have visitors tracked by 3rd parties. Please find further information on privacy in the Intershop Privacy Policy and Legal Notice.
Home
Knowledge Base
Product Releases
Log on to continue
This Knowledge Base document is reserved for registered customers.
Log on with your Intershop Entra ID to continue.
Write an email to supportadmin@intershop.de if you experience login issues,
or if you want to register as customer.