on 06-11-2015 5:08 PM
Hallo.
We are upgrading our SAP ECC 6.0 systems.
After migration from:
windows 2003 + Oracle 11.2.0.3 + NO COMPRESSION
to
windows 2008 + Oracle 11.2.0.4 + COMPRESS FOR OLTP
I have jobs that use the view CAUFV very very slow
Before the upgrade near 10000 secs. now on 33000 not yet finished.
The bad select is:
SELECT
*
FROM
"CAUFV"
WHERE
"MANDT"=:A0 AND "PLNBEZ"=:A1 AND "GLTRP" BETWEEN :A2 AND :A3 AND "WERKS"=:A4 AND "GETRI"=:A5 AND
"LOEKZ"=:A6 AND "IDAT2"=:A7
Do anyone know any issue on Oracle with the compression activated?
What could I check?
thanks a lot for your help.
Mario
Hi Mario,
> Do anyone know any issue on Oracle with the compression activated?
It is probably not an issue / bug of the compression itself (e.g like this one ), but rather based on the patch set upgrade and physically changed data structure (+ stats). The probable error is located in the generated / used execution plan i guess.
The easiest way would be to run a SQL monitoring report for this particular SQL ID and then provide all the corresponding object and statistic data with SQLd360.
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL /host/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<SQL_ID>', type => 'HTML', report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
SQLd360 can be downloaded from Github and here is the documentation: SQLd360, SQL diagnostics collection made faster | Mauro Pagano
Regards
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Stefan.
I will try to investigate using the tool that you mentioned (if I am able...)
Furthermore, I executed:
brconnect -u / -c -f stats -t system_stats
brconnect -u / -c -f stats -t oradict_stats
brconnect -c -u / -f stats -t all -f collect -p 4
But the problem persist.
Other query that use CAUFV have the same problem.
I found this note "176754 - Problems with CBO and RBO"
Point 12. CBO: The optimum index is not used in View or Join with OR operations
There is a mentioned on CAUFV
But..how colud I solve?
And why isn't the same problem on 11.2.0.3
Thanks a lot!
Mario
Hi Mario,
> But..how colud I solve? And why isn't the same problem on 11.2.0.3
We / you can only answer these questions with the requested information. The BR*connect runs were more like a shot in the dark as we / you don't know the root cause of your issue.
However in general the optimizer evolves from patchset to patchset (or from one-off-patch to one-of-patch, etc.) and you also introduced advanced compression which can have a huge impact on the physical object structure. All these are major influencing factors on the CBO (and runtime engine)
Regards
Stefan
Do you think that, to bypass the compression issue, I could decompress AUFK and AFKO so I can exclude that the problem is due on compression action?
How to decompress the tables ?
Are the following commands right?
brspace -u / -c force -f tbreorg -a dtab -t AUFK
brspace -u / -c force -f tbreorg -a dtab -t AFKO
Thanks!
Hi Mario,
> I find SqlID using transaction ST04
Yes, you can find it in ST04(old), DBACOCKPIT or ST05 (EXPLAIN PLAN).
> Furthermore, I am on Windows, Oracle is in Bundle with SAP so I haven't OracleTuning Pack.
The diagnostic + tuning pack is included, if you have purchased your Oracle database license by SAP. For more details please check SAPnote #740897.
Regards
Stefan
Ok.
Step by step I will arrive..
I don't understand if I have to do this:
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL C:\Temp\report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => 'fzzczryngjtu4', type => 'HTML', report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
but it produces an empty report
or this:
cd sqld360-master
sqlplus / as sysdba
SQL> @sqld360.sql fzzczryngjtu4 T 31
that it has worked very much and it has produced a .zip file
Thanks a lot for your patience
Hi Mario,
the PL/SQL procedure "DBMS_SQLTUNE.report_sql_monitor" only works, if the SQL is running (or the corresponding data is not aged out of shared pool yet). In general it is something like a real time SQL monitoring feature where you can see if the CBO has done something wrong (any many other work load related data) while the SQL is running.
So just start your query once again, wait 5 to 10 minutes and then run the procedure again.
Regards
Stefan
Ah ok.
I will run it.
But I note this:
the statement is:
SELECT
*
FROM
"CAUFV"
WHERE
"MANDT"=:A0 AND "PLNBEZ"=:A1 AND "GLTRP" BETWEEN :A2 AND :A3 AND "WERKS"=:A4 AND "GETRI"=:A5 AND
"LOEKZ"=:A6 AND "IDAT2"=:A7
In the not compressed and not upgraded system the plan is:
SELECT STATEMENT ( Estimated Costs = 439 , Estimated #Rows = 0 )
7 FILTER
Filter Predicates
6 NESTED LOOPS
4 NESTED LOOPS
( Estim. Costs = 438 , Estim. #Rows = 1 )
Estim. CPU-Costs = 15.901.779 Estim. IO-Costs = 436
2 TABLE ACCESS BY INDEX ROWID AFKO
( Estim. Costs = 438 , Estim. #Rows = 1 )
Estim. CPU-Costs = 15.898.090 Estim. IO-Costs = 436
Filter Predicates
1 INDEX SKIP SCAN AFKO~P
( Estim. Costs = 433 , Estim. #Rows = 27 )
Search Columns: 2
Estim. CPU-Costs = 15.847.061 Estim. IO-Costs = 431
Access Predicates Filter Predicates
3 INDEX UNIQUE SCAN AUFK~0
Search Columns: 2
Estim. CPU-Costs = 1.804 Estim. IO-Costs = 0
Access Predicates
5 TABLE ACCESS BY INDEX ROWID AUFK
Estim. CPU-Costs = 3.690 Estim. IO-Costs = 0
Filter Predicates
Instead , in the compressed and upgraded system the plan is:
SELECT STATEMENT ( Estimated Costs = 705 , Estimated #Rows = 0 )
6 FILTER
Filter Predicates
5 NESTED LOOPS
( Estim. Costs = 705 , Estim. #Rows = 1 )
Estim. CPU-Costs = 327.264.588 Estim. IO-Costs = 667
3 NESTED LOOPS
( Estim. Costs = 705 , Estim. #Rows = 41 )
Estim. CPU-Costs = 327.264.588 Estim. IO-Costs = 667
1 TABLE ACCESS FULL AUFK
( Estim. Costs = 623 , Estim. #Rows = 41 )
Estim. CPU-Costs = 326.479.485 Estim. IO-Costs = 586
Filter Predicates
2 INDEX UNIQUE SCAN AFKO~0
( Estim. Costs = 1 , Estim. #Rows = 1 )
Search Columns: 2
Estim. CPU-Costs = 9.021 Estim. IO-Costs = 1
Access Predicates
4 TABLE ACCESS BY INDEX ROWID AFKO
( Estim. Costs = 2 , Estim. #Rows = 1 )
Estim. CPU-Costs = 19.383 Estim. IO-Costs = 2
Filter Predicates
So TABLE FULL ACCESS AUFK could be the problem?
What could I do?
Thanks
You are right but I am not able to capture that session id
I ran
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL C:\Temp\report_sql_monitor_list.htm
SELECT DBMS_SQLTUNE.report_sql_monitor_list (type => 'HTML', report_level => 'ALL') AS report
FROM dual;
SPOOL OFF
but in the list, there isn't my sqlid=fzzczryngjtu4 even if I see from ST04
I have been able to use SQLd360 and I have a sqld360_tes_fzzczryngjtu4_stesvv01_20150612_1147.zip
but where could I find in this zip extracted the information that could help to solve the issue?
Thanks.
Hello Mario , good afternoon Stefan !
Mario , I think you have missed this stats provided by SAP (these 2 tables should have special stats)
1020260 - Delivery of Oracle statistics (Oracle >= 10g)
Best Regards , Sergo.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sergo,
great hint - thanks for mentioning. I received the SQLd360 output from Mario last Friday and it is custom code (Z*). The execution plan (+ predicates) looks like this.
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 705 (100)| |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 1173 | 705 (6)| 00:00:04 |
| 3 | NESTED LOOPS | | 41 | 1173 | 705 (6)| 00:00:04 |
|* 4 | TABLE ACCESS FULL | AUFK | 41 | 14760 | 623 (6)| 00:00:04 |
|* 5 | INDEX UNIQUE SCAN | AFKO~0 | 1 | | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| AFKO | 1 | 813 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:A3>=:A2)
4 - filter(("T0002"."IDAT2"=:A7 AND "T0002"."WERKS"=:A4 AND
"T0002"."LOEKZ"=:A6 AND "T0002"."MANDT"=:A0))
5 - access("T0001"."MANDT"=:A0 AND "T0001"."AUFNR"="T0002"."AUFNR")
6 - filter(("T0001"."PLNBEZ"=:A1 AND "T0001"."GETRI"=:A5 AND
"T0001"."GLTRP">=:A2 AND "T0001"."GLTRP"<=:A3))
So the delivered custom statistics for AUFK.PSPEL, DISTINCT_KEYS of AUFK~D and AFKO.AUFNT, AFKO.PRONR and DISTINCT_KEYS of AFKO~3 do not apply here in this case.
Regards
Stefan
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.