stats_parallel_degree

Farid · ‎12-03-2014

Hello,

We are running SAP ECC6, on Oracle 11.2.0.4.0, HP-UX 11.31

Our Production Database (5 TB) consists of some large tables , which size is more than 200 GB ; GLPCA,RESB, ...

We are scheduling the update of the statistics every morning at 06:00 though transaction DB13,

The Oracle Statistics are being processed in parallel :

stats_parallel_degree = 8 , which means that basically up to 8 tables/indexes are being processed at the same time, but

one single thread processes each table. For the sake of simplicity, I call it "external parallelism"

It means that when the statistics of some large tables are obsoletes , and are automatically recalculated, it takes some significant amount of time, more than 15 hours for the DB13 jobs to be processed.

For all those reasons, I am trying to implement the "internal parallelism" , through the dbms_stats package, in order to configure several threads for the largest tables, as explained in details in the following sap note :

424239 - New BRCONNECT parameter: stats_dbms_stats

408532 - Using the DBMS_STATS package for collecting statistics

914174 - Minor functional enhancements in BR*Tools (1)

I spent the two past days , doing some tests on our QAS database (much smaller size), trying to update in parallel the statistics of table GLPCA, through several threads, but it does not work. I am probably missing something, but I can not figure out what

I tried all kinds of combination for the following parameter :

stats_dbms_stats

ALL:R:1,GLPCA:R:3

stats_dbms_stats

ALL:R:0,GLPCA:R:3

stats_dbms_stats

ALL:R:2,GLPCA:R:3

But still, the table GLPCA keeps being processed by one unique thread, and not three threads as expected.

I am joining the DB24 log, maybe someone can help me pinpoint what I am missing :

As you can see, 4 threads are started in parallel because of the parameter

stats_parallel_degree

4

But one single threads is then assigned to table GLPCA, I was expecting three threads

Thank you for your attention

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Detail log: cepiemwr.sta

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

BR0801I BRCONNECT 7.20 (30)

BR0805I Start of BRCONNECT processing: cepiemwr.sta 2014-12-03 14.55.57

BR0484I BRCONNECT log file: /oracle/RUQ/sapcheck/cepiemwr.sta

BR0101I Parameters

Name Value

oracle_sid RUQ

oracle_home /oracle/RUQ/112_64

oracle_profile /oracle/RUQ/112_64/dbs/initRUQ.ora

sapdata_home /oracle/RUQ

sap_profile /oracle/RUQ/112_64/dbs/initRUQ.sap

system_info ruqadm/oraruq server02 HP-UX B.11.31 U ia64

oracle_info RUQ 11.2.0.4.0 8192 1092 21727164 server02 UTF8 UTF8 1599868393 &RUQ

sap_info 700 SAPSR3 RUQ TEMPLICENSE R3_ORA INITIAL

make_info hpia64 OCI_102 Feb 9 2013

command_line brconnect -c -u / -f stats -t GLPCA

stats_table GLPCA

stats_dbms_stats ALL:R:1,GLPCA:R:3

stats_change_threshold 50

stats_parallel_degree 4

BR0280I BRCONNECT time stamp: 2014-12-03 14.55.58

BR0813I Schema owner found in database RUQ: SAPSR3*

BR0280I BRCONNECT time stamp: 2014-12-03 14.55.58

BR0807I Name of database instance: RUQ

BR0808I BRCONNECT action ID: cepiemwr

BR0809I BRCONNECT function ID: sta

BR0810I BRCONNECT function: stats

BR0812I Database objects for processing: GLPCA

BR0851I Number of tables with missing statistics: 1

Owner SAPSR3: 1

GLPCA

BR0852I Number of tables to delete statistics: 0

BR0854I Number of tables to collect statistics without checking: 0

BR0855I Number of indexes with missing statistics: 5

Owner SAPSR3: 5

GLPCA~0 GLPCA~1 GLPCA~2 GLPCA~3 GLPCA~7

BR0856I Number of indexes to delete statistics: 0

BR0857I Number of indexes to collect statistics: 0

BR0853I Number of tables to check (and collect if needed) statistics: 1

Owner SAPSR3: 1

GLPCA

BR0846I Number of threads that will be started in parallel to the main thread: 4

BR0126I Unattended mode active - no operator confirmation required

BR0280I BRCONNECT time stamp: 2014-12-03 14.55.58

BR0817I Number of monitored/modified tables in schema of owner SAPSR3: 1/0

BR0280I BRCONNECT time stamp: 2014-12-03 14.55.59

BR0877I Checking and collecting table and index statistics...

BR0847I Thread 1 started successfully

BR0847I Thread 2 started successfully

BR0280I BRCONNECT time stamp: 2014-12-03 14.56.00

BR0848I Thread 1 finished with return code 0

BR0280I BRCONNECT thread 2 time stamp: 2014-12-03 14.56.02

BR0881I Collecting statistics for table SAPSR3.GLPCA with method/sample E/P3 ...

BR0280I BRCONNECT thread 2 time stamp: 2014-12-03 14.56.20

BR0884I Statistics collected for table: SAPSR3.GLPCA, rows old/new: -1/1702667

BR0280I BRCONNECT thread 2 time stamp: 2014-12-03 14.56.20

BR0850I 2 of 2 objects processed - 0.003 of 0.003 units done

BR0204I Percentage done: 100.00%, estimated end time: 14:56

BR0001I **************************************************

BR0280I BRCONNECT time stamp: 2014-12-03 14.56.21

BR0848I Thread 2 finished with return code 0

BR0280I BRCONNECT time stamp: 2014-12-03 14.56.21

BR0879I Statistics checked for 1 table

BR0878I Number of tables selected to collect statistics after check: 0

BR0880I Statistics collected for 1/1 tables/indexes

BR0894I Tables with the longest duration of collecting statistics for owner SAPSR3

Pos. Owner Table Duration Rows/old Rows/new Meth./Samp. Space[KB] Used[KB:%] Data[KB:%] Lobs Space[KB] Used[KB:%] Data[KB:%]

[m:s]

1 SAPSR3 GLPCA 0:18 -1 1702667 E/P3 -1 896776:100 746580:83 0 0 0:0 0:0

BR0895I Indexes with the longest duration of collecting statistics for owner SAPSR3

Pos. Owner Index Duration Rows/old Rows/new Meth./Samp. Space[KB] Used[KB:%] Data[KB:%]

[m:s]

1 SAPSR3 GLPCA~0 0:03 -1 1681586 E/P1

BR0900I Usage of space allocated in tablespaces for analyzed tables and indexes

Pos. Tablespace Tables Analy. Space[KB] Used[KB:%] Data[KB:%] Indexes Valid. Space[KB] Used[KB:%] Data[KB:%] Lobs Proc. Space[KB] Used[KB:%] Data[KB:%]

1 PSAPSR3 -1 1 -1 896776:100 746580:83 -1 0 0 0:0 0:0 -1 0 0 0:0 0:0

BR0806I End of BRCONNECT processing: cepiemwr.sta 2014-12-03 14.56.21

BR0280I BRCONNECT time stamp: 2014-12-03 14.56.21

BR0802I BRCONNECT completed successfully

stefan_koehler · ‎12-03-2014

Hi Raoul,

at first i would enable a BR*Tools trace (BR_TRACE 15) to cross-check the DBMS_STATS call by brconnect. Afterwards i would check DBMS_STATS by tracing it, if everything was alright with BR*Tools (parameter degree is correctly set). Please be aware that DBMS_STATS may use serial execution regardless what you specified for parameter "degree".

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68582


degree
When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.

This may fit to your statement "doing some tests on our QAS database (much smaller size), trying to update in parallel the statistics of table GLPCA, through several threads, but it does not work". However just trace it and you gonna see the root cause

Regards

Stefan

P.S.: By the way setting event "38028" disables small object optimization for dbms_stats.

fidel_vales · ‎12-04-2014

Hi,

I think you are confused with the meaning of the parameter.

in the BR* tools "area" there are two ways of parallelization

1) parallelization @ BR*tools level

2) parallelization @ Oracle level.

If you specify parallelization @ brtools level then BR*tools will start more than one "brtools" process to do whatever in parallel. In your case statistical calculation. But for ONE table you cannot start more than ONE BRCONNECT process.

If you specify 10 tables and parallelization 8, then there will be 8 brconnect parallel processes, each one processing one of those tables.

Then, you can specify parallelization at Oracle level.

That is, each of those BR* processes will launch an oracle process in parallel (if it make sense, of course). In your case, for one table this make sense, one BRCONNECT parallel process will call DBMS_STATS to be processes with parallelism 8

This is the definition of the parameter:

stats_parallel_degree

This parameter defines the number of parallel threads for updating statistics with BRCONNECT update statistics.

it is equivalent to use the "-p" option on the command line

This is your command:


command_line                   brconnect -c -u / -f stats -t GLPCA
stats_table                    GLPCA
stats_dbms_stats               ALL:R:1,GLPCA:R:3
stats_change_threshold         50
stats_parallel_degree          4

on the log file you see:


BR0846I Number of threads that will be started in parallel to the main thread: 4

It knows it needs to start 4 threads, you have the parameter set,. then it starts 2 threads, realize it does need more than one and does not start more (this decision is not seen on the log but it is the logical conclusion as it does not start more):


BR0280I BRCONNECT time stamp: 2014-12-03 14.55.59
BR0877I Checking and collecting table and index statistics...
BR0847I Thread 1 started successfully
BR0847I Thread 2 started successfully
BR0280I BRCONNECT time stamp: 2014-12-03 14.56.00
BR0848I Thread 1 finished with return code 0

What you want, in this case is to tell ORACLE to parallize the DBS_STATS process. It does not make sense to parallelize the BRCONNECT for one table. You need to use the comman line option -g:


g|-degree: defines the degree of parallelism used by DBSM_STATS for update statistics
Syntax:  -g|-degree <number>|auto|default|null                
Default:  null                
This setting is valid for all tables, for which there is no parallelism setting in  stats_dbms_stats. However, note that the setting  -f degree (see above), if used, takes precedence over the setting in  stats_dbms_stats.

or set it for the table in the control table DBSTATC.

As usual, I strongly recommend to read the documentation in order to know exactly what the tools do.

You also have to be careful if you set both at the same time. Imagine that you set BR* parallelism of 8 and Oracle parallelism of 8. That means 8 BR* processes running 8 processes (EACH) at Oracle. If you are not careful you could

1.- run out of processes

2.- run out of "hardware" (but this is, usually, more I/O than CPU bounded)

Collecting statistics for large table : stats_dbms_stats parallelism not taken into account

Accepted Solutions (1)

Accepted Solutions (1)

Answers (1)

Answers (1)

stats_parallel_degree

Re: Problems when opening a PDF in my SAPUI5 app

Re: Fiori App - Post Goods Receipt for Purchasing ...

Re: Problems when opening a PDF in my SAPUI5 app

Re: SAP CPI Merge different payloads to single

Re: Fiori Elements List report Grid table is not s...