on 10-06-2011 2:59 PM
Hallo.
When I execute transaction COHV the performance are bad.
I investigated and I found the statement:
for example
select * from eskn where mandt = '100' and aufnr = '1171132' and loekz = ' ';
very long beacause it doesn't use the index
ESKN~G
with column
MANDT, AUFNR
If I execute the statement:
select * from eskn where aufnr = '1171132' and loekz = ' ';
index is used and statement are fast
So it seems that the MANDT brings the problem.
I have Oracle 11.2.0.2 and upgraded two months ago from Oracle 10.2.0.4
When I was in Oracle 10.2.0.4 there wasn't the problem.
Could you help me?
Thanks.
Mario
An info for you: if I delete the statistic for table ESKN from DB20 transaction,
the statement is fast and index:
ESKN~G is used.
Why this ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Ferruccio,
Why this ?
Because of if no statistics are available - dynamic sampling kicks in and an execution plan is generated with "on-the-fly-collected" statistics. In your particular case it works - but it usually it is not recommended.
However the provided information is not enough.
If you want us to help you we need more information - please collect the needed information with the SQL_ID Data Collector script from sapnote #1257075 and provide the output.
Regards
Stefan
I downloaded SQL_SQL_ID_DataCollector_11g+.txt from Oss note 1438410
This is the statement:
.
.
.
.
FROM
( SELECT
-1 DBID,
'select * from eskn where mandt = '100' and aufnr = '1171132' and loekz = ' '' SQL_ID,
'X' SEGMENT_INFO,
' ' COMPLEX_INDEX_STATS,
'X' ASH_INFO,
'X' FRAGMENTATION_INFO,
'X' VIEW_INFO,
'X' INDEX_COLUMNS,
'X' COMPLEX_SQL_TEXT,
'X' INCLUDE_PREDICATES,
SYSDATE - 7 AWR_BEGIN_DATE,
SYSDATE AWR_END_DATE,
' ' DEBUGGING_INFO
FROM
DUAL
.
.
.
.
using the statement:
'select * from eskn where mandt = '100' and aufnr = '1171132' and loekz = ' ''
but, from DBACOCKPIT I obtain:
(ORA-923) ORA-00923: FROM keyword not found where expected
From sqlplus.exe I obtain:
SQL> @SQL_ID_DataCollector.txt
SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.
Enter value for sql_id: select * from eskn where mandt = '100' and aufnr = '1171132' and loekz = ' '
SELECT
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SP2-0042: unknown command "<sql_id>" - rest of line ignored.
What could I do?
Thanks.
Mario
Edited by: Ferruccio Galli on Oct 7, 2011 9:03 AM
Hello Ferruccio,
well you need to enter the SQL_ID and not the SQL statement itself.
This is also documented in the script itself:
Usage:
DBACOCKPIT (SQL Command Editor):
Replace &&sql_id at all locations with the SQL_ID you want to analyze
Execute
Choose monospaced output (e.g. "List" -> "Print Preview" or "List Output") for better readability
Regards
Stefan
Ok.
You could download sql.out at the location:
https://docs.google.com/leaf?id=0B9qeUpPJdEoQNWUwNTM2NDktMDBhYS00MTE2LWI5NmMtMTNmOTQ4ZTc2NGY3&hl=it
I noted that field AUFNR in table ESKN isn't populated but the selct is on that table on the transaction COHV
It is strange
Thanks for your help!
Mario
Hello Ferruccio,
well now its pretty obvious why a full table scan is performed. Well at first let me ask you just another important question:
Is this SQL really executed with literals by your SAP program (ABAP hint u201C&substitute values&u201D). Normally SAP uses bind variables and no bind peeking which makes a huge difference.
However .. i will quote the corresponding part from the SQL report
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NU M_BUCKETS AVG_COL_LEN DATA_LENGTH NULLABLE NUM_NULLS DATA_TYPE LAST_ANALYZED SAMPLE_SIZE USER_STATS
MANDT 1 1.00000 1 4 9 N 0 VARCHAR2 201 1-10-07 07:02:24 147362 NO
AUFNR 1 1.00000 1 2 36 N 0 VARCHAR2 201 1-10-07 07:02:24 147362 NO
LOEKZ 1 1.00000 1 2 3 N 0 VARCHAR2 201 1-10-07 07:02:24 147362 NO
So as you can see the collected statics affirm that every column in the WHERE clause has only one distinct value. In that case it makes no sense to use the index (no sort or anything like that is used by your SQL which could make it more intersting), because of you read all the data statistically.
My assumption is that you have more different AUFNR values than 1 in your table ESKN - so the question would be why wrong statistics are collected. This would need deeper investigations on your system.
How did you collect the statstics? Have you done it with brconnect? If yes please post the corresponding brconnect statistic log.
Regards
Stefan
I don't understand.
The SAP program LKOREF02 has the code:
.
.
select * from eskn into table eskn_tab
where aufnr = p_aufnr
and loekz = space.
.
.
But I don't understand:
I obtain:
SQL> select count(*) from sapsr3.ESKN where AUFNR is not null;
COUNT(*)
-
4915054
SQL> select count(*) from sapsr3.ESKN where AUFNR is null;
COUNT(*)
-
0
But if I execute
select AUFNR from ESKN;
I obtain always empty value.
So what could I check?
It is a strange behaviour.
Hello Ferruccio,
well that's pretty normal, because of every table column has a NOT NULL constraint.
So even the column has no value in ABAP INSERT / UPDATE statement - the DBSL insert a blank into the database table.
Please do the following to verify my assumption:
SQL> select distinct(AUFNR) from sapsr3.ESKN;
Ok this SAP coding (LKOREF02) is using no literals - so your test example with literals differs strongly from the real executed one.
But still one open question:
- How did you collect the statstics? Have you done it with brconnect? If yes please post the corresponding brconnect statistic log.
Regards
Stefan
I obtain:
SQL> select distinct(AUFNR) from sapsr3.ESKN;
AUFNR
-
SQL>
so no value it seems.
I collect statistics using brtools:
-
Detail log: cegxzqwd.sta
-
BR0801I BRCONNECT 7.20 (16)
BR0805I Start of BRCONNECT processing: cegxzqwd.sta 2011-10-06 16.49.59
BR0484I BRCONNECT log file: F:\oracle\TES\sapcheck\cegxzqwd.sta
BR0101I Parameters
Name Value
oracle_sid TES
oracle_home F:\oracle\TES\11202
oracle_profile F:\oracle\TES\11202\database\initTES.ora
sapdata_home G:\oracle\TES
sap_profile F:\oracle\TES\11202\database\initTES.sap
system_info SAPServiceTES STESVV01 Windows 5.2 Build 3790 Service Pack 2 AMD64
oracle_info TES 11.2.0.2.0 8192 552 3816993493 STESVV01 UTF8 UTF8 773218351
sap_info 700 SAPSR3 0002LK0003TES0011Z01163691220013NetWeaver_ORA
make_info NTAMD64 OCI_10201_SHARE Aug 2 2011
command_line brconnect -u / -c -f stats -o SAPSR3 -t ESKN -e null -m E -s P3 -f allsel,collect,method,sample,keep
stats_owner SAPSR3
stats_table ESKN
stats_dbms_stats ALL:R:0
stats_method E
stats_sample_size P3
stats_change_threshold 50
stats_parallel_degree 1
BR0280I BRCONNECT time stamp: 2011-10-06 16.50.02
BR0813I Schema owner found in database TES: SAPSR3*
BR0280I BRCONNECT time stamp: 2011-10-06 16.50.02
BR0807I Name of database instance: TES
BR0808I BRCONNECT action ID: cegxzqwd
BR0809I BRCONNECT function ID: sta
BR0810I BRCONNECT function: stats
BR0811I Owner of database schema for processing: SAPSR3
BR0812I Database objects for processing: ESKN
BR0851I Number of tables with missing statistics: 1
Owner SAPSR3: 1
ESKN
BR0852I Number of tables to delete statistics: 0
BR0854I Number of tables to collect statistics without checking: 1
Owner SAPSR3: 1
ESKN
BR0855I Number of indexes with missing statistics: 2
Owner SAPSR3: 2
ESKN0 ESKNG
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: 0
BR0862I Force option with value 'allsel,collect,keep,method,sample' set
BR0126I Unattended mode active - no operator confirmation required
BR0280I BRCONNECT time stamp: 2011-10-06 16.50.02
BR0877I Checking and collecting table and index statistics...
BR0280I BRCONNECT time stamp: 2011-10-06 16.50.02
BR0881I Collecting statistics for table SAPSR3.ESKN with method/sample E/P3 ...
BR0280I BRCONNECT time stamp: 2011-10-06 16.50.19
BR0884I Statistics collected for table: SAPSR3.ESKN, rows old/new: -1/4881767
BR0280I BRCONNECT time stamp: 2011-10-06 16.50.19
BR0850I 1 of 1 object processed - 0.002 of 0.002 units done
BR0204I Percentage done: 100.00%, estimated end time: 16:50
BR0001I **************************************************
BR0280I BRCONNECT time stamp: 2011-10-06 16.50.19
BR0879I Statistics checked for 0 tables
BR0878I Number of tables selected to collect statistics after check: 0
BR0880I Statistics collected for 1/0 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 ESKN 0:17 -1 4881767 E/P3 -1 1113112:100 901029:81 0 0 0:0 0:0
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 1113112:100 901029:81 -1 0 0 0:0 0:0 -1 0 0 0:0 0:0
BR0806I End of BRCONNECT processing: cegxzqwd.sta 2011-10-06 16.50.19
BR0280I BRCONNECT time stamp: 2011-10-06 16.50.19
BR0802I BRCONNECT completed successfully
Hello Ferruccio,
there seems to be something else collecting statistics too.
Because of the SQL report from above states that the last collection was on "2011-10-07 07:02:24", but your brconnect log states "2011-10-06 16.49.59".
If the query "select distinct(AUFNR)" is correct, the statistics are correct as well - so you maybe need to HINT the SQL or manipulate the statistics.
Regards
Stefan
Yes, the log is of yesterday, after I deleted the statistic of ESKN so this morning, at 07:02, they were recreated.
You are right, but the log is the same.
The query is right.
I can't change the sql beacause LKOREF02 is a standard report.
How can I manipulate the statistic?
So what do you suggest?
Thanks!
Mario
Hello Ferruccio,
I can't change the sql beacause LKOREF02 is a standard report.
You don't need to modify the SAP report to hint a particular static SQL (like this one). Please check my blog "Hinting a SQL without adding a hint":
Manipulating the statistics can be done with DBMS_STATS PL/SQL package, but i would not recommend this in your case.
-> http://download.oracle.com/docs/cd/E11882_01/appdev.112/e23448/d_stats.htm#autoId0
Also keep in mind, that the SQL will only be fast by using index ESKN~G, if the column AUFNR in WHERE clause contains a value that is not blank. If it is blank you will have the overhead of reading the whole index and table.
Regards
Stefan
ESKN contains no values on the field AUFNR.
So I don't understand why with statistics the plan is:
SQL Statement
SELECT
*
FROM
eskn
WHERE
mandt = '100' and aufnr = '1171132' and loekz = ' '
Execution Plan
SELECT STATEMENT ( Estimated Costs = 23.479 , Estimated #Rows = 4.912.067 )
1 TABLE ACCESS FULL ESKN
( Estim. Costs = 23.478 , Estim. #Rows = 4.912.067 )
Estim. CPU-Costs = 7.081.833.220 Estim. IO-Costs = 22.916
Filter Predicates
and the statement is slow.
Instead, if I delete the statistic on the table ESKN the plan is:
SQL Statement
SELECT
*
FROM
eskn
WHERE
mandt = '100' and aufnr = '1171132' and loekz = ' '
Execution Plan
SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 5 )
2 TABLE ACCESS BY INDEX ROWID ESKN
Estim. CPU-Costs = 3.297 Estim. IO-Costs = 0
Filter Predicates
1 INDEX RANGE SCAN ESKN~G
Search Columns: 2
Estim. CPU-Costs = 1.634 Estim. IO-Costs = 0
Access Predicates
and the statement is fast.
Why this?
Thanks.
Mario
Hello Ferruccio,
Why this?
1) Once again - the SQL statement that you use for testing is completely different from the one that is executed by SAP through ABAP (DBSL). You are using literals instead of bind variables (!!!).
2) If you delete the statistics and run your SQL - statistics are sampled on-the-fly before generating the execution plan. These statistics can differ enormously from the full sampled ones by DBMS_STATS. For more information about dynamic sampling - check the official documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#PFGRF30101
If you really want to know why the other execution plan is generated when dynamic sampling kicks in - you need to perform an CBO trace and take a deeper look at the cost calculation. For further details check this white paper: http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf
Regards
Stefan
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
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.