on 12-03-2014 4:36 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you Stefan and Fidel for your very helpful answers,
Brtools traces revealed that the BEGIN DBMS_STATS.GATHER_TABLE_STATS command was correctly generated , with DEGREE => 3. It wasn't then a problem with the Brools.
I reran the same command brconnect -c -u / -f stats -t GLPCA on our Preproduction system (same size as PRD), and this time I could see the parallel sessions at the Oracle level.
There was indeed a bit of confusion from my side, between Oracle session and Unix thread.
So as Stefan guessed, the small size of GLPCA table in QAS, prevented the Oracle parallel processing to take place..
I also tried with the command line option -g, it works fine
Thank you!
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.