cancel
Showing results for 
Search instead for 
Did you mean: 

Wrong path access table ESKN on transaction COHV

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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 ?

stefan_koehler
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

stefan_koehler
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Excuse me but I don't understand...

What is SQL_ID ?

stefan_koehler
Active Contributor
0 Kudos

Hello Ferruccio,

the SQL_ID is a unique identifier for your each SQL statement.

You can get that ID from "ST05 -> Enter SQL statement" or from "DBACOCKPIT -> Shared Cursor Cache -> Explain Plan"

Regards

Stefan

Former Member
0 Kudos

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

stefan_koehler
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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.

stefan_koehler
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

stefan_koehler
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

stefan_koehler
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

stefan_koehler
Active Contributor
0 Kudos

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

Answers (0)