Blog

It is very important for 3DEXPERIENCE Platform to have maintenance activity executed regularly , The platform did not really delete data. so we need “tidy” action to really delete data. Also as the database size becomes more and more bigger, the more chance that database choose wrong execution plan and the SQL executed will be very slow, and this can cause the system terrible slow. Below example use Linux and Oracle as environment.

So we will need following famous three actions to do the maintenance. do not change the sequence of the steps.

1. Tidy data

create tidy.sh as below , for this script , you will need mql’s directory in the path variable, otherwise for mql, use the whole path.

#!/bin/sh

context="set context user creator filename /usr/DassaultSystemes/R2024x/3DSpace/credentials/creator"

echo "`date`: tidy eService Administration"
mql -t -c "$context; tidy vault 'eService Administration';"
echo "`date`: tidy eService Production"
mql -t -c "$context; tidy vault 'eService Production';"
echo "`date`: tidy eService Sample"
mql -t -c "$context; tidy vault 'eService Sample';"
echo "`date`: tidy vplm"
mql -t -c "$context; tidy vault vplm;"
echo "`date`: tidy SIXW"
mql -t -c "$context; tidy vault SIXW;"
echo "`date`: tidy DataSetup"
mql -t -c "$context; tidy vault DataSetup;"

2. Rebuild Index

execute blow SQL to get SQL for re index,

it is get the index of schema of M1, X3DPASSTOKENS, X3DNOTIF, and then generated re index SQL at /tmp/reindex.sql

if you are not CDB , just remove “ALTER SESSION SET container = orclpdb1;”

below only need executed one time: the SPOOL command will write result to the input file, here is /tmp/reindex.sql

ALTER SESSION SET container = orclpdb1;
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 100
SPOOL /tmp/reindex.sql
select 'alter index "'||owner||'".'||'"'||index_name||'" rebuild online parallel;'||chr(10)||'alter index "'||owner||'"."'||index_name||'" noparallel;' from dba_indexes where owner in  (select distinct owner from dba_tables where upper(table_name) in ('MXVER6','TICKETGRANTINGTICKET','NOTIFICATION_PERSON')) and temporary !='Y' and index_type != 'LOB' order by owner, index_name;
SPOOL OFF

modify /tmp/reindex.sql , remove the first line

and remove the last line.

and add “ALTER SESSION SET container = orclpdb1;” (This only for CDB oracle)

copy the script to your directory (here mine is /app/maintenance)

create file /app/maintenance/reindex.sh as below , change the connection string before “<<EOF” to your case.

#!/bin/sh
sqlplus / as sysdba  <<EOF
@/app/maintenance/reindex.sql
EOF

and execute it.

chmod +x /app/maintenance/reindex.sh
/app/maintenance/reindex.sh

3. Recalculate Statistics

for oracle 19c and later, create /app/maintenance/restat.sql, replace ownname with the schema you got from previous SQL

execute sys.dbms_stats.gather_schema_stats(ownname=>'M1',cascade=>FALSE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>DBMS_STATS.DEFAULT_DEGREE,no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE AUTO',options=>'GATHER');
execute sys.dbms_stats.gather_schema_stats(ownname=>'X3DNOTIF',cascade=>FALSE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>DBMS_STATS.DEFAULT_DEGREE,no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE AUTO',options=>'GATHER');
execute sys.dbms_stats.gather_schema_stats(ownname=>'X3DPASSTOKENS',cascade=>FALSE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>DBMS_STATS.DEFAULT_DEGREE,no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,granularity=>'AUTO',method_opt=>'FOR ALL COLUMNS SIZE AUTO',options=>'GATHER');

create /app/maintenance/restat.sh

#!/bin/sh
sqlplus / as sysdba  <<EOF
@/app/maintenance/restat.sql
EOF

execute it

chmod +x /app/maintenance/restat.sh
/app/maintenance/restat.sh

Based on database volume increase speed, wee need create cron job for the scripts to be regularly executed, normally one month one time should be a start frequency.