Solved: Slow R3Load export for a specific table

Hello,

We are currently migrating from ECC5 to ECC6 EhP4.

We use R3Load to export/import ou Oracle 10 Sap DB.

We had to cancel the export as it was going to take days to export a specific table.

Exporting all the other tables took a few hours.

We had exported it with exp, truncated it and restarted sapinst so that we could migrate.

The structure of the table is:

SQL> desc sapprd.zcl_dao_stat

Name Null? Type

MANDT NOT NULL VARCHAR2(3)

NOACC NOT NULL VARCHAR2(10)

TABNAME NOT NULL VARCHAR2(30)

TABPAR NOT NULL VARCHAR2(30)

WHERE_STRING CLOB

FAE_WHERE_STRING CLOB

TCODE NOT NULL VARCHAR2(20)

REPID NOT NULL VARCHAR2(40)

CPROG NOT NULL VARCHAR2(40)

ERNAM NOT NULL VARCHAR2(12)

ERDAT NOT NULL VARCHAR2(8)

ERTIM NOT NULL VARCHAR2(6)

SQL> select count(*) from sapprd.zcl_dao_stat;

COUNT(*)

1184716

The size whithout the LOB is 300 MB.

The exp.dmp file size is 500 MB.

R3Load was exporting 1 MB per hour.

1. Would you know why R3Load was taking so much time for this table?

2. What could I change to this table to speed up the R3Load export?

3. Does Sap Oss offer support to debug why R3Load is slow on ZTables?

4. Would think that it would have also cause a slow R3Load import on the target system?

Thanks in advance for your answers.

SAP Managed Tags:
Oracle Database

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Hi Benoît,

1. Would you know why R3Load was taking so much time for this table?

It is taking so much time, because of CLOB fields

2. What could I change to this table to speed up the R3Load export?

Check the note 1045847 - ORACLE DIRECT PATH LOAD SUPPORT IN R3LOAD.

3. Does Sap Oss offer support to debug why R3Load is slow on ZTables?

Ask them first

4. Would think that it would have also cause a slow R3Load import on the target system?

What is your target database? Oracle or SAP DB?

Best regards,

Orkun Gedik

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hello Orkun,


> It is taking so much time, because of CLOB fields

I have other tables with LOBs and it is not taking so much time.


> Check the note 1045847 - ORACLE DIRECT PATH LOAD SUPPORT IN R3LOAD.

Ok I will look at it.


> Ask them first

But you look faster


> What is your target database? Oracle or SAP DB?

Source and target DBs are Oracle 10.

And I hope to upgrade to 11g on Thursday.

See you,

Hello,

I have one more question.

The note specifies:

To use this feature one has to add "-loadprocedure fast" (without the

double quotes) to the command line when R3Load is started.

How could I tell sapinst to launch R3Load with this option?

Thanks in advance for your answer.

Hello,


> Check the note 1045847 - ORACLE DIRECT PATH LOAD SUPPORT IN R3LOAD.

The note only speaks about slow import.

It does not mention slow export.

How are you sure that it is my problem?

Regards,

Hi Benoît,

>> 2. What could I change to this table to speed up the R3Load export?

Sorry, because of you exported this table, I think that you wrote down wrong.

Ok, at this stage, table reorganization may be helpful to speed-up the export process.

As a second suggestion, you can split the table. Note 1043380 - Efficient Table Splitting for Oracle Databases

Third one is splitting table preparation on SAPInst.

Additionally, I tried same conditions in my test environment. So, as far as I know that there's no any other way to speed-up this process with that kind of table.

Best regards,

Orkun Gedik

Edited by: Orkun Gedik on Jul 5, 2011 12:54 AM


Hello,
> 
> I have one more question.
> The note specifies:
> To use this feature one has to add "-loadprocedure fast" (without the
> double quotes) to the command line when R3Load is started.
> 
> How could I tell sapinst to launch R3Load with this option?
> 
> Thanks in advance for your answer.

You can find the answer in the thread;

Hello,

Before closing the thread, I reask my question.

Do I have a way to diagnose why R3Load is taking so much time to export this specific table?

Thanks in advance for your answer.

Hi Benoît,

At this stage, you can analyze check the results at the database layer, by using AWR stats, maybe.

Best regards,

Orkun Gedik


Hi Benoît,
> 
> At this stage, you can analyze check the results at the database layer, by using AWR stats, maybe.
> 
> Best regards,
> 
> Orkun Gedik

Hello,

If I use exp or expdp, it exports much faster.

Therefore awr stats are not very helpful in so for as I just see that R3Load performs

a select in the DB.

The slowness is not at the DB layer, but at the R3Load layer.

This is why I would like to know why R3Load is so slow and which options could faster it.

Thanks in advance for your answer.

Hello Benoît,

well let's start again ..

1) Sapnote #1045847 is absolutely useless in your case

2) exp / expd uses a complete different "architecture" than R3load for unloading data

3) The suggestion to split the export over several R3loads is also disputable without knowing the bottleneck

3) The AWR statistics on database level are uselss in your case, but we would need the statistic data for that particular SQL that is running to export/unload the table. These data is also stored inside the AWR, but you will need to use the script awrsqrpt.sql for that.

Do you export the data sorted? (Sapnote #954268) ... if yes maybe the R3load export SQL is using an index to unload the data sorted and is getting slow .. but this is just like looking at a crystal ball .. the needed information and statistic is stored in your AWR.

In such "wrong access path" cases you can modify the export by renaming the table and creating a view with a hint on that table. There was also a SAP script available for that, but i don't find it right now.

Regards

Stefan

Hi,

I will back to you, after my vacation

Best regards,

Orkun Gedik

Hello Stephan,


> 3) The AWR statistics on database level are uselss in your case, but we would need the statistic data for that particular SQL that is running to export/unload the table. These data is also stored inside the AWR, but you will need to use the script awrsqrpt.sql for that.
>
> Do you export the data sorted? (Sapnote #954268) ... if yes maybe the R3load export SQL is using an index to unload the data sorted and is getting slow .. but this is just like looking at a crystal ball .. the needed information and statistic is stored in your AWR.

I do not know if I export the data sorted, I use Sapinst default settings:

mySAP ERP 2004 SR1

> System Copy

> Source System

> ABAP System

> Oracle

> Non-Unicode

> ABAP Database Content Export -> /share/dbexportTST

> Next

> Next (Standard Method (R3load-Based))

> SAPSID -> TST -> Next

> Next

> Instance Number 01 -> Next

> Next (/sapmnt)

> Database Schmea -> SAPPRD -> Next

> Target DB -> ORA

Export Directory -> /share/dbexportTST

-> Next

> Next (Update statistics before the export starts)

> Export in Alphabetical Order

Data File Code Page (1100)

Number of parallel jobs 3 (could be 7 on grenat)

-> Next

> Start

Just to be sure, that I understand you, what I should do is:

1. Run the export

2.During the long running export, run the awrsqrpt.sql in an sqlplus

Is it correct?

Where could I get awrsqrpt.sql?

Thanks in advance for your answer.


Hello Stephan,
> 
> Where could I get awrsqrpt.sql?
> 
> Thanks in advance for your answer.

Hi,

that should be in $ORACLE_HOME/rdbms/admin

Volker

Hi friends,

I would like to clarify some issues over the case;

>> 1) Sapnote #1045847 is absolutely useless in your case

I mentioned that this note is not necessary in my previous message.

>> 3) The suggestion to split the export over several R3loads is also disputable without knowing the bottleneck

It may not be find to help root cause but may help to speed-up the process. This can be a workaround.

>> 3) The AWR statistics on database level are uselss in your case, but we would need the statistic data for that particular SQL that is running to export/unload the table. These data is also stored inside the AWR, but you will need to use the script awrsqrpt.sql for that.

For this case, AWR stats might be useful, in order to identify the SQL statement during the R3load process. By doing so, statistical values can be interpreted. This is why I recommend AWR stats.

I hope that you didn't misunderstand me but I wanted to clarify the issue.

Best regards,

Orkun Gedik

Hello Benoît,

well ok ..


> Next (Update statistics before the export starts)
> Export in Alphabetical Order
Data File Code Page (1100)

You can control the sorted or unsorted export by the option "Export in Alphabetical Order".

You should do the following:


SQL> exec dbms_workload_repository.create_snapshot();
**** Start the Export and let it run for 10 minutes or so ****
**** Identify the sql_id of the corresponding SQL for the slow R3load export ****
SQL> exec dbms_workload_repository.create_snapshot();
SQL> @?/rdbms/admin/awrsqrpt.sql
**** Enter the sql_id and the corresponding snap ids ****

Regards

Stefan

Hello Stephan,

Could you please also provide me the R3Load option to export only this table?

This would allow me to test without running the whole sapinst and I could also test on a NW7.01 Oracle 11g.

FYI, in my SAPUSER.STR, I have:

---

tab: ZCL_DAO_STAT

att: USER 7 ?N T all ZCL_DAO_STAT~0 USER 7

fld: MANDT CLNT 3 0 0 not_null 1

fld: NOACC CHAR 10 0 0 not_null 2

fld: TABNAME CHAR 30 0 0 not_null 0

fld: TABPAR CHAR 30 0 0 not_null 0

fld: WHERE_STRING STRG 0 0 0 null 0

fld: FAE_WHERE_STRING STRG 0 0 0 null 0

fld: TCODE CHAR 20 0 0 not_null 0

fld: REPID CHAR 40 0 0 not_null 0

fld: CPROG CHAR 40 0 0 not_null 0

fld: ERNAM CHAR 12 0 0 not_null 0

fld: ERDAT DATS 8 0 0 not_null 0

fld: ERTIM TIMS 6 0 0 not_null 0

---

I could use a STR file with only this table.

Thanks in advance for your answer

Hello Benoît,

for sure - here we go.

The environment is for an AIX operating system.

You maybe need to adjust it to your operating system and the corresponding code page.


export SAPSYSTEMNAME=<SID>
export dbms_type=ORA
export dbs_ora_tnsname=<SID>
export ORACLE_<SID>=<SID>
export ORACLE_BASE=/oracle
export TNS_ADMIN=/oracle/<SID>/102_64/network/admin
export dbs_ora_schema=<SCHEMA>
export NLS_LANG=AMERICAN_AMERICA.UTF8
export LIBPATH=/usr/lib:/mnt/<SID>/BIN/exe:/oracle/client/10x_64/instantclient
export DIR_LIBRARY=/mnt/<SID>/BIN/exe


shell> cat ZCL_DAO_STAT.STR
tab: ZCL_DAO_STAT
att: USER 7 ?N T all ZCL_DAO_STAT~0 USER 7
fld: MANDT CLNT 3 0 0 not_null 1
fld: NOACC CHAR 10 0 0 not_null 2
fld: TABNAME CHAR 30 0 0 not_null 0
fld: TABPAR CHAR 30 0 0 not_null 0
fld: WHERE_STRING STRG 0 0 0 null 0
fld: FAE_WHERE_STRING STRG 0 0 0 null 0
fld: TCODE CHAR 20 0 0 not_null 0
fld: REPID CHAR 40 0 0 not_null 0
fld: CPROG CHAR 40 0 0 not_null 0
fld: ERNAM CHAR 12 0 0 not_null 0
fld: ERDAT DATS 8 0 0 not_null 0
fld: ERTIM TIMS 6 0 0 not_null 0

shell> cat ZCL_DAO_STAT.cmd
tsk: "ZCL_DAO_STAT.TSK"
icf: "ZCL_DAO_STAT.STR"
dcf: "DDLORA.TPL"
dat: "/tmp/DATA/" bs=1k fs=1000M
dir: "ZCL_DAO_STAT.TOC"

shell> R3load -ctf E ZCL_DAO_STAT.STR DDLORA.TPL ZCL_DAO_STAT.TSK ORA -l ZCL_DAO_STAT.LOG
shell> R3load -e ZCL_DAO_STAT.cmd -datacodepage 4102 -l ZCL_DAO_STAT_export.log -stop_on_error

Regards

Stefan

Hello Stephan,

I have one more question.

Is it ok if I run this on server where my sap sandbox is running,

or it is mandatory to have sap stopped?

Thanks in advance for your answer.

Hello Benoît,

you can run an export while your SAP system is up and running. You (maybe) will get an inconsistent data dump, but for your performance analysis this should be no problem at all.

Regards

Stefan

Hello,

R3load reports an error.

I did the setenv for SAPSYSTEMNAME but does not seems to be used:

% R3load -ctf E zcl_dao_stat.STR DDLORA.TPL zcl_dao_stat.TSK ORA -l zcl_dao_stat.LOG

sapparam: sapargv( argc, argv) has not been called.

sapparam(1c): No Profile used.

sapparam: SAPSYSTEMNAME neither in Profile nor in Commandline

% echo $SAPSYSTEMNAME

BAS

FYI, I set the variables that you provided:

setenv SAPSYSTEMNAME BAS

setenv dbms_type ORA

setenv dbs_ora_tnsname BAS

setenv ORACLE_SID BAS

setenv ORACLE_BASE /oracle

setenv TNS_ADMIN /oracle/BAS/112_64/network/admin

setenv dbs_ora_schema SAPPRD

setenv NLS_LANG AMERICAN_AMERICA.WE8DEC

setenv LD_LIBRARY_PATH /usr/sap/BAS/SYS/exe/run:/oracle/client/10x_64/instantclient

setenv DIR_LIBRARY /usr/sap/BAS/SYS/exe/run

Would you know what I should do more so that R3load would run?

Thanks in advance for your answer.

Good morning,

It looks like the task file was generated:

% cat zcl_dao_stat.TSK

D ZCL_DAO_STAT E xeq

% cat zcl_dao_stat.LOG

R3load: START OF LOG: 20110804084711

R3load: sccsid @(#) $Id: //bas/701_REL/src/R3ld/R3load/R3ldmain.c#8 $ SAP

R3load: version R7.01/V1.4

Compiled Oct 30 2010 01:59:48

R3load -ctf E zcl_dao_stat.STR DDLORA.TPL zcl_dao_stat.TSK ORA -l zcl_dao_stat.LOG

R3load: job completed

R3load: END OF LOG: 20110804084711

It must have been just a warning.

Regards,

Hello Stephan,

I am running the export.

1. Could you provide me the select you use to find:

Identify the sql_id of the corresponding SQL for the slow R3load export ****

2. Concerning, the snapshots created with dbms_workload_repository.create_snapshot(),

should I delete them after my tests or are they deleted automatically after a few days?

Thanks in advance for your answers.

Hello,

3. Is it ok to find the sql_id or do you have an easier way?

---

SELECT

S.SQL_ID, S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT

FROM

V$SESSION S,

V$SQLTEXT_WITH_NEWLINES T

WHERE S.SQL_ID IS NOT NULL

AND S.SQL_ID = T.SQL_ID

ORDER BY S.SID,T.PIECE;

---

Thanks in advance for your answers.

Hello Benoît,


select s.sql_id, s.plan_hash_valu, e.username, e.machine, e.program, e.module, s.sql_fulltext
from v$sql s, v$session e
where e.sql_id = s.sql_id
and s.sql_fulltext like '%ZCL_DAO_STAT%';

You don't need to delete the AWR snapshots, they are purged automatically depending on your settings:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_workload_repos.htm#BACEHJJI

Regards

Stefan

Thanks Stephan for your sql query.

This what I have done/get:

SQL> set head off

SQL> select s.sql_id, s.plan_hash_value, e.username, e.machine, e.program,

e.module, s.sql_fulltext FROM v$sql s, v$session e

WHERE e.sql_id = s.sql_id

AND s.sql_fulltext like '%ZCL_DAO_STAT%';

017w3pu08yfug 3947402320 SYS

azurite1

sqlplus@azurite1 (TNS V1-V3)

select s.sql_id, s.plan_hash_value, e.username, e.machine, e.program,

e.module,

crsjh2vgc4z23 3767248744 SAPPRD

azurite1

R3load@azurite1 (TNS V1-V3)

SELECT "MANDT", "NOACC", "TABNAME", "TABPAR", "WHERE_STRING", "FAE_WHERE_STRING"

==> It is crsjh2vgc4z23

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/awrsqrpt.sql

...

Instance DB Name Snap Id Snap Started Level

BAS BAS 26375 04 Aug 2011 00:00 1

26376 04 Aug 2011 01:00 1

26377 04 Aug 2011 02:00 1

26378 04 Aug 2011 03:00 1

26379 04 Aug 2011 04:00 1

26380 04 Aug 2011 05:00 1

26381 04 Aug 2011 06:00 1

26382 04 Aug 2011 07:00 1

26383 04 Aug 2011 08:00 1

26384 04 Aug 2011 08:53 1

26385 04 Aug 2011 09:00 1

26386 04 Aug 2011 10:00 1

26387 04 Aug 2011 11:00 1

26388 04 Aug 2011 11:11 1

26389 04 Aug 2011 11:16 1

Specify the Begin and End Snapshot Ids

Enter value for begin_snap: 26388

Begin Snapshot Id specified: 26388

Enter value for end_snap: 26389

Enter value for sql_id: crsjh2vgc4z23

Specify the Report Name

The default report file name is awrsqlrpt_1_26388_26389.txt. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name DB Id Instance Inst Num Startup Time Release RAC

BAS 1721027557 BAS 1 03-Aug-11 20:01 11.2.0.2.0 NO

Snap Id Snap Time Sessions Curs/Sess

Begin Snap: 26388 04-Aug-11 11:11:03 50 34.8

End Snap: 26389 04-Aug-11 11:16:00 50 35.3

Elapsed: 4.96 (mins)

DB Time: 0.66 (mins)

SQL Summary DB/Inst: BAS/BAS Snaps: 26388-26389

Elapsed

SQL Id Time (ms)

crsjh2vgc4z23 3,732

Module: R3load@azurite1 (TNS V1-V3)

SELECT "MANDT", "NOACC", "TABNAME", "TABPAR", "WHERE_STRING", "FAE_WHERE_STRING"

, "TCODE", "REPID", "CPROG", "ERNAM", "ERDAT", "ERTIM" FROM "ZCL_DAO_STAT" ORDER

BY "MANDT", "NOACC"

SQL ID: crsjh2vgc4z23 DB/Inst: BAS/BAS Snaps: 26388-26389

-> 1st Capture and Last Capture Snap IDs

refer to Snapshot IDs witin the snapshot range

-> SELECT "MANDT", "NOACC", "TABNAME", "TABPAR", "WHERE_STRING", "FAE_WHE...

###

Plan Hash Total Elapsed 1st Capture Last Capture

Value Time(ms) Executions Snap ID Snap ID

1 3767248744 3,732 1 26389 26389

Plan 1(PHV: 3767248744)

Plan Statistics DB/Inst: BAS/BAS Snaps: 26388-26389

-> % Total DB Time is the Elapsed Time of the SQL statement divided

into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap

Elapsed Time (ms) 3,732 3,732.3 9.5

CPU Time (ms) 3,710 3,710.0 9.5

Executions 1 N/A N/A

Buffer Gets 485,770 485,770.0 94.8

Disk Reads 0 0.0 0.0

Parse Calls 1 1.0 0.2

Rows 242,884 242,884.0 N/A

User I/O Wait Time (ms) 0 N/A N/A

Cluster Wait Time (ms) 0 N/A N/A

Application Wait Time (ms) 0 N/A N/A

Concurrency Wait Time (ms) 0 N/A N/A

Invalidations 0 N/A N/A

Version Count 1 N/A N/A

Sharable Mem(KB) 23 N/A N/A

Execution Plan

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT				16687 (100)
1	TABLE ACCESS BY INDEX ROWID	ZCL_DAO_STAT	801K	262M	16686 (1)	00:09:53
2	INDEX FULL SCAN	ZCL_DAO_STAT~0	801K		932 (1)	00:00:34

Can you conclude why it is so time consuming?

Thanks in advance for your answer.

Hello Benoît,

how long did the export take now?

Regarding the SQL statistics provided by AWR everything looks very well (it also uses the primary index because of the ORDER BY clause like expected).

The snapshot was taken for round about 5 minutes and that particular SQL crsjh2vgc4z23 was executed in only 3.7 seconds where it spends nearly all the time in CPU (which is pretty ok for that SQL).

All blocks could be read from buffer cache (buffer gets and disk reads) ... so everything looks pretty fine here.

If it still tooks much longer than 4 seconds - please run the following query to provide some information:


select SNAP_ID, SQL_ID, EVENT, MODULE, MACHINE 
from DBA_HIST_ACTIVE_SESS_HISTORY 
where SNAP_ID in (26388,26389) and SQL_ID = 'crsjh2vgc4z23';

You also stated that you are using LOBs .. is the data really located in the LOB segment or is it stored "in-line" ?

Regards

Stefan

Hello Stephan,

It is still going to take days.

As it took more than 4s, I send you the select you provided to me.

It is running for more than 10 minutes now.

SQL> set head off

SQL> select s.sql_id, s.plan_hash_value, e.username, e.machine, e.program,

e.module, s.sql_fulltext FROM v$sql s, v$session e

WHERE e.sql_id = s.sql_id

AND s.sql_fulltext like '%ZCL_DAO_STAT%';

crsjh2vgc4z23 3767248744 SAPPRD

azurite1

R3load@azurite1 (TNS V1-V3)

SELECT "MANDT", "NOACC", "TABNAME", "TABPAR", "WHERE_STRING", "FAE_WHERE_STRING"

017w3pu08yfug 3947402320 SYS

azurite1

sqlplus@azurite1 (TNS V1-V3)

select s.sql_id, s.plan_hash_value, e.username, e.machine, e.program,

e.module,

SQL> select SNAP_ID, SQL_ID, EVENT, MODULE, MACHINE

from DBA_HIST_ACTIVE_SESS_HISTORY

where SNAP_ID in (26388,26389) and SQL_ID = ' crsjh2vgc4z23';

26389 crsjh2vgc4z23

R3load@azurite1 (TNS V1-V3)

azurite1

You have asked if the lob were in-line.

I guess you wanted to know if they where in row or out row?

If you provide the select to know this info, I can run it.

Thanks in advance for your answers.

Hello Stephan,

You were right as it took : 14 minutes

R3load: START OF LOG: 20110804142326

R3load: END OF LOG: 20110804143615

What also confused me is that the compression rate is extremly high.

Oracle DB 389 MB -R3Load -> 12 MB dump file

% ls -altrh /tmp/DATA/

total 24160

drwxrwxrwt 5 root sys 1.4K Aug 4 14:22 ../

drwxr-xr-x 2 basadm sapsys 190 Aug 4 14:23 ./

-rw-rr 1 basadm sapsys 12M Aug 4 14:36 ZCL_DAO_STAT.001

So, what you are thinking is that, on my source server (NW 04), my Oracle 10.2.0.4 query was using a non optimum query plan?

Thanks in advance for your answers.

Hello Benoît,

well that in-line or out-line LOB was just for my information. An in-line LOB is used, if the data is less than 4000 bytes and "in row storage" is enabled. The database object (table) is round about 390 MB, but that does not mean that the amount of data has nearly the same size (just think about increasing extent sizes, DMLs, etc.).

I am just confused about the amount of data that is read by that SQL in just "4 seconds" and the time, that is needed in "real-time" comparing to the AWR data (sample time):

- Buffer gets are 485770 = 485770 x 8 / 1024 = round about 3.8 GB

How big is the corresponding LOB segment and your primary index?


select l.segment_name,  l.column_name, l.in_row, l.cache, round(s.bytes/1024/1024,1) MB
from dba_lobs l, dba_segments s
where l.segment_name = s.segment_name
and l.table_name = 'ZCL_DAO_STAT';

select segment_name, round(s.bytes/1024/1024,1) MB from  
DBA_SEGMENTS
where segment_name = 'ZCL_DAO_STAT~0';

Could you also run the following SQL (10 times in 5 second intervall) while performing the export


select program, status, event, p1, p1raw, p2, p2raw, p3, p3raw
from v$session 
where sql_id = 'crsjh2vgc4z23';

It is pretty hard to analyze this issue without access to your system.

The execution plan looks pretty fine for that SQL (with ORDER BY clause) ... so i don't think that this is the issue right here.

Regards

Stefan

Hello stephan,

This is for the index:

SQL> select segment_name, round(bytes/1024/1024,1) MB from

DBA_SEGMENTS

where segment_name = 'ZCL_DAO_STAT~0';

SEGMENT_NAME MB

ZCL_DAO_STAT~0 37

This is for the table:

elect l.segment_name, l.column_name, l.in_row, l.cache, round(s.bytes/1024/1024,1) MB

from dba_lobs l, dba_segments s

where l.segment_name = s.segment_name

and l.table_name = 'ZCL_DAO_STAT'; 2 3 4

SEGMENT_NAME COLUMN_NAME IN_ CACHE MB

SYS_LOB0000150947C00005$$ WHERE_STRING YES NO .1

SYS_LOB0000150947C00006$$

FAE_WHERE_STRING

YES NO .1

SEGMENT_NAME COLUMN_NAME IN_ CACHE MB

SQL> select * from dba_lobs where SEGMENT_NAME like '%150947C00005%';

SAPPRD ZCL_DAO_STAT

WHERE_STRING

SYS_LOB0000150947C00005$$ PSAPPRDUSR

SYS_IL0000150947C00005$$ 8192 43200

NO YES NONE NONE NONE YES ENDIAN NEUTRAL NO NO YES

YES

SQL> select * from dba_lobs where SEGMENT_NAME like '%150947C00006%';

SAPPRD ZCL_DAO_STAT

FAE_WHERE_STRING

SYS_LOB0000150947C00006$$ PSAPPRDUSR

SYS_IL0000150947C00006$$ 8192 43200

NO YES NONE NONE NONE YES ENDIAN NEUTRAL NO NO YES

YES

SQL> select sum(dbms_lob.getlength(WHERE_STRING)) from sapprd.zcl_dao_stat;

12868715

SQL> select sum(dbms_lob.getlength(FAE_WHERE_STRING)) from sapprd.zcl_dao_stat;

16179968

SQL> select count(*) from sapprd.zcl_dao_stat ;

809260

Does this answer your questions?

Hello again,

The session is not in the db.

SQL> select program, status, event, p1, p1raw, p2, p2raw, p3, p3raw

from v$session

where sql_id = 'crsjh2vgc4z23';

no rows selected

Regards,

Hello Benoît,

the "in-line" limit LOB length of 4000 bytes is for each data set and not in sum, but as you can see the LOB segment is nearly empty .. so most data (or all) is "in-line".

The session is not in the db.

If you run the export and that particular SQL is not executed - so the AWR data is correct and your time is lost anywhere else and not on the database itself. CPU bound on the R3load application server? I/O output limit on R3load application server?

Who knows ... you need to check ... if your provided data is correct and the SELECT on ZCL_DAO_STAT runs only 4 seconds you have to check your environment outside of the oracle database.

Regards

Stefan

Hello Stephan,


> Who knows ... you need to check ... if your provided data is correct and the SELECT on ZCL_DAO_STAT runs only 4 seconds you have to check your environment outside of the oracle database.

The 4 seconds you are mentioning is only for part of the select that I run and interrupted on my Sandbox.

The other things is that on my Oracle 11g sandbox it has run definitively faster becuase it is not 4 seconds but it is less than one hour.

On my source server, I had to stop the export after several hours...

I should try on my ECC 5 / Oracle 10.2.0.4 and post the awr result.

But I can only do this in three weeks...

Concerning, the other bottle necks, I should add that the R3load export had worked fast for all the other tables.

Therefore CPU and I/O should not be responsible for this slowness.

After expdp/truncate of the table, the whole db export was fast.

If I had a CPU or IO problem it would not have been fast.

Regards,

Hello Benoît,

once again - you can not compare expdp with R3load. R3load converts (to a unique format) and compress the data and is using more CPU. The R3load is certainly much faster after you have truncated the table, because of there is no data anymore )

Even your provided statistic data was not from your environment, that have the issue (if i understand you correct you also have different oracle releases in both environments) - it's useless. Please provide the whole requested data again from your environment where the export takes several hours.

Regards

Stefan

Hello stephan,


> Even your provided statistic data was not from your environment, that have the issue (if i understand you correct you also have different oracle releases in both environments) - it's useless. Please provide the whole requested data again from your environment where the export takes several hours.

I will provide the info from my old server.

But I am going to be away for 3 weeks.

Therefore I should try this when I am back.

Thanks for your help.

Hello Stephan,

Sorry for this late answer.

But I was quite busy these last weeks.

I have also taken the C_TADM_70_04 certification.

It can help to understand R3Load

I have reproduced the R3Load export in my old Oracle 10.2 machine.

I could not run the exact same export because this table got truncated in my source systems.

Therefore the table was re-imported with imp so that I could run a R3Load export again.

Unfortunately, I did not have the problem anymore on my 10.2 system.

The table got R3Load exported in less than 1 hour.

The table plan is the same.

Then I do not know if it is going well because of:

1. The defragmentation of the table (exp,truncate,imp)

2. The query plan that would have changed (due to the reorg and automatic stats after imp)

3. Something else.

The select seems so easy (no where clause and simple table),

that there should not be that many different possible query plans.

I post the result.

Would you notice something that I have missed?

SQL> @?/rdbms/admin/awrsqrpt.sql

Specify the Report Type

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: text

Type Specified: text

Specify the number of days of snapshots to choose from

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

TST TST 25988 19 Oct 2011 12:22 1

25989 19 Oct 2011 12:36 1

Specify the Begin and End Snapshot Ids

Enter value for begin_snap: 25988

Begin Snapshot Id specified: 25988

Enter value for end_snap: 25989

End Snapshot Id specified: 25989

Specify the SQL Id

Enter value for sql_id: crsjh2vgc4z23

SQL ID specified: crsjh2vgc4z23

Specify the Report Name

The default report file name is awrsqlrpt_1_25988_25989.txt. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Snap Id Snap Time Sessions Curs/Sess

Begin Snap: 25988 19-Oct-11 12:22:35 18 1.6

End Snap: 25989 19-Oct-11 12:36:13 19 2.5

Elapsed: 13.65 (mins)

DB Time: 1.15 (mins)

SQL Summary DB/Inst: TST/TST Snaps: 25988-25989

Elapsed

SQL Id Time (ms)

crsjh2vgc4z23 22,668

Module: R3load@jade2 (TNS V1-V3)

SELECT "MANDT", "NOACC", "TABNAME", "TABPAR", "WHERE_STRING", "FAE_WHERE_STRING"

, "TCODE", "REPID", "CPROG", "ERNAM", "ERDAT", "ERTIM" FROM "ZCL_DAO_STAT" ORDER

BY "MANDT", "NOACC"

SQL ID: crsjh2vgc4z23 DB/Inst: TST/TST Snaps: 25988-25989

-> 1st Capture and Last Capture Snap IDs

refer to Snapshot IDs witin the snapshot range

-> SELECT "MANDT", "NOACC", "TABNAME", "TABPAR", "WHERE_STRING", "FAE_WHE...

Plan Hash Total Elapsed 1st Capture Last Capture

Value Time(ms) Executions Snap ID Snap ID

1 3767248744 22,668 1 25989 25989

Plan 1(PHV: 3767248744)

Plan Statistics DB/Inst: TST/TST Snaps: 25988-25989

-> % Total DB Time is the Elapsed Time of the SQL statement divided

into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap

Elapsed Time (ms) 22,668 22,667.9 32.7

CPU Time (ms) 20,884 20,884.2 40.3

Executions 1 N/A N/A

Buffer Gets 971,222 971,222.0 98.5

Disk Reads 17,502 17,502.0 94.5

Parse Calls 1 1.0 0.1

Rows 477,047 477,047.0 N/A

User I/O Wait Time (ms) 2,741 N/A N/A

Cluster Wait Time (ms) 0 N/A N/A

Application Wait Time (ms) 0 N/A N/A

Concurrency Wait Time (ms) 0 N/A N/A

Invalidations 0 N/A N/A

Version Count 1 N/A N/A

Sharable Mem(KB) 22 N/A N/A

Execution Plan

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time
0	SELECT STATEMENT				29 (100)
1	TABLE ACCESS BY INDEX ROWID	ZCL_DAO_STAT	1358	461K	29 (0)	00:00:01
2	INDEX FULL SCAN	ZCL_DAO_STAT~0	1358		2 (0)	00:00:01

Full SQL Text

SQL ID SQL Text

crsjh2vgc4z2 SELECT "MANDT", "NOACC", "TABNAME", "TABPAR", "WHERE_STRING", "FA

AE_WHERE_STRING", "TCODE", "REPID", "CPROG", "ERNAM", "ERDAT", "E

RTIM" FROM "ZCL_DAO_STAT" ORDER BY "MANDT", "NOACC"

Thanks in advance for your answer.

Answers (0)

Ask a Question

Top Q&A Solution Author

User

Count

Slow R3Load export for a specific table

Accepted Solutions (1)

Accepted Solutions (1)

Answers (0)

Re: Issue during getting ESG metrics under SCT API...

Re: CF Deployment Error: Error getting tenant t0

Re: print settings issue

Re: SAP Datasphere test tenant ?

Re: RabbitMQ X CPI - Virtual Host