data load slow
Follow up by mail Click here


Subject: data load slow
Author: Tso P, South Africa
Date: Apr 20, 2017, 14:02, 65 days ago
Os info: solaris 11
Oracle info: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Message: Hi Guys

Developers are loading data using the web services, now I got a call that the load is slow.

They are saying that yesterday the load was running faster than today...

Is it adviseable to gather stats while the load is running?

Do I need to gather stats for the laods?

What should I check to resolve this issue?

Please help!!!

Thanks in advance...
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Tso P, South Africa
Date: Apr 20, 2017, 14:10, 65 days ago
Message: From the AWR I get the below stats:


Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 138.3 101.4
cursor: pin S 204 2.1 10.06 1.5 Concurrency
log file sync 1,228 1.9 1.59 1.4 Commit
library cache: mutex X 2,283 1.1 0.47 .8 Concurrency
PX Deq: Slave Session Stats 651 .2 0.36 .2 Other
direct path read 3,318 .2 0.07 .2 User I/O
SQL*Net more data to client 1,733 .1 0.07 .1 Network
SQL*Net message to client 41,261 .1 0.00 .1 Network
Disk file operations I/O 373 0 0.13 .0 User I/O
cursor: mutex X 1,509 0 0.02 .0 Concurrency


From the database server:

Top


load averages: 1.47, 1.89, 1.94; up 27+23:42:14 14:09:43
583 processes: 582 sleeping, 1 on cpu
CPU states: 94.7% idle, 0.4% user, 4.9% kernel, 0.0% iowait, 0.0% swap
Memory: 64G phys mem, 71G free mem, 204G total swap, 204G free swap

PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
24195 oracle 1 59 0 12G 12G sleep 295:50 0.05% oracle
17676 oracle 1 59 0 8140M 8100M sleep 518:18 0.03% oracle
7778 oracle 1 1 0 12G 12G sleep 122:59 0.03% oracle
14669 oracle 1 59 0 15G 15G sleep 101:31 0.03% oracle
14633 oracle 1 1 0 15G 15G sleep 368:01 0.02% oracle
7705 oracle 1 1 0 12G 12G sleep 518:21 0.02% oracle
14644 oracle 1 59 0 15G 15G sleep 121:08 0.02% oracle
21200 oracle 1 59 0 10M 9208K cpu/87 0:18 0.01% top


Please Help!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Philip Wisse, Netherlands
Date: Apr 20, 2017, 14:39, 65 days ago
Message: Hi Tso,

Your AWR report shows no problems whatsoever.

Same for the Top output, allthough kernel should be lower than user.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Tso P, South Africa
Date: Apr 20, 2017, 14:44, 65 days ago
Message: Just to add on my thread I checked the long running queries and I found these gather stats sessions...

USERNAME OPNAME TARGET_DESC SOFAR TOTALWORK MESSAGE
---------- ----------------------------------- -------------------------------- ---------- ---------- -----------------------------------------------------------------
1 Partitions done

SYS Gather Table's Index Statistics Table WRH$_SEG_STAT 1 1 Gather Table's Index Statistics: Table WRH$_SEG_STAT : 1 out of 1
Indexes done

SYS Gather Auto Statistics Auto 59 59 Gather Auto Statistics: Auto : 59 out of 59 Objects done
SYS Gather Table Partition Statistics Table WRH$_SEG_STAT 1 1 Gather Table Partition Statistics: Table WRH$_SEG_STAT : 1 out of
1 Partitions done

SYS Gather Table Partition Statistics Table WRH$_SEG_STAT 1 1 Gather Table Partition Statistics: Table WRH$_SEG_STAT : 1 out of
1 Partitions done

USERNAME OPNAME TARGET_DESC SOFAR TOTALWORK MESSAGE
---------- ----------------------------------- -------------------------------- ---------- ---------- -----------------------------------------------------------------

SYS Gather Table Partition Statistics Table WRH$_SEG_STAT 1 1 Gather Table Partition Statistics: Table WRH$_SEG_STAT : 1 out of
1 Partitions done

SYS Gather Table's Index Statistics Table WRH$_SEG_STAT 1 1 Gather Table's Index Statistics: Table WRH$_SEG_STAT : 1 out of 1
Indexes done

SYS Gather Table's Index Statistics Table WRH$_SEG_STAT 1 1 Gather Table's Index Statistics: Table WRH$_SEG_STAT : 1 out of 1
Indexes done

SYS Gather Table's Index Statistics Table WRH$_SYSSTAT 1 1 Gather Table's Index Statistics: Table WRH$_SYSSTAT : 1 out of 1

USERNAME OPNAME TARGET_DESC SOFAR TOTALWORK MESSAGE
---------- ----------------------------------- -------------------------------- ---------- ---------- -----------------------------------------------------------------
Indexes done


I see about 200 lines of these gather stats sessions...

Is is it safe to kill these sessions...?

Please help!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Philip Wisse, Netherlands
Date: Apr 20, 2017, 15:03, 65 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Tso,

When 'sofar' equals 'totalwork' usually the work has been done.
Now I've seen sofar growing bigger than totalwork but this must be incidents.
So you should query 'where sofar < totalwork'

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Tso P, South Africa
Date: Apr 20, 2017, 16:04, 65 days ago
Message: Thanks Philip

Your correct:

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;

And I don't have those lines...

What might be the bottleneck here...

Please Help!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: vishal Tank, India
Date: Apr 21, 2017, 09:10, 65 days ago
Message: Oracle Support always advice to gather the statistics when ables the data load is optimal so that it enables the optimizer to have the latest details to construct good execution plans
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Tso P, South Africa
Date: Apr 21, 2017, 13:33, 64 days ago
Message: Thanks for the replies...

Today I see the below :


Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
enq: TX - allocate ITL entry 6 1401.8 233640.85 68.5 Configuration
DB CPU 575 28.1
cursor: pin S 2,569 25.8 10.06 1.3 Concurrency
log file sync 8,954 20 2.23 1.0 Commit
cursor: pin S wait on X 13 14.5 1116.16 .7 Concurrency
library cache lock 7 3.4 484.09 .2 Concurrency
direct path read 31,209 2.2 0.07 .1 User I/O
library cache: mutex X 2,758 1.2 0.45 .1 Concurrency
SQL*Net message to client 411,583 .9 0.00 .0 Network
SQL*Net more data to client 11,504 .8 0.07 .0 Network



SQL_ID EVENT COUNT(*) PERCENT
------------- ---------------------------------------------------------------- ---------- ----------
1zsn7y569u5g3 enq: TX - allocate ITL entry 33 100%



SQL_ID CURRENT_OBJ# OBJECT_NAME WAIT_COUNT
------------- --------------------------------------- ----------------------------------- ----------
1zsn7y569u5g3 92891 OHI_PROCEDURES_B 176
1zsn7y569u5g3 95542 OHI_PROCEDURE_IDX1 1


UPDATE OHI_PROCEDURES_B SET DYN_FLCO_ID_011 = :1 , DYN_FLCO_ID_009 = :2 , DYN_FLCO_ID_010 = :3 , DYN_FLCO_ID_008 = :4 , DYN_FLCO_ID_015 = :5 , DYN_CHAR_004 = :6 , LAST_UPDATED_DATE = :7 , OBJECT_VERSION_NUMBER = :8 , LAST_UPDATED_BY = :9 WHERE ((ID = :10 ) AND (OBJECT_VERSION_NUMBER = :11 ))


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Tso P, South Africa
Date: Apr 21, 2017, 13:39, 64 days ago
Message: ADDM:

addm:


Findings and Recommendations
----------------------------

Finding 1: ITL Waits
Impact is .05 active sessions, 29.95% of total activity.
--------------------------------------------------------
Waits for ITL enqueue were consuming significant database time.

Recommendation 1: Schema Changes
Estimated benefit is .05 active sessions, 29.95% of total activity.
-------------------------------------------------------------------
Action
Consider increasing INITRANS for the TABLE
"OHI_CLAIMS_OWNER.OHI_PROCEDURES_B" with object ID 92891 without
rebuilding it.
Related Object
Database object with ID 92891.
Action
Consider increasing PCTFREE for the TABLE
"OHI_CLAIMS_OWNER.OHI_PROCEDURES_B" with object ID 92891.
Related Object
Database object with ID 92891.
Rationale
The database segment belongs to container OHICFGPDB1 with database ID
2975261291.
Rationale
The SQL statement with SQL_ID "1zsn7y569u5g3" was found waiting for the
Interested Transaction List (ITL) enqueue on the TABLE
"OHI_CLAIMS_OWNER.OHI_PROCEDURES_B" with object ID 92891.
Related Object
SQL statement with SQL_ID 1zsn7y569u5g3.
UPDATE OHI_PROCEDURES_B SET DYN_FLCO_ID_011 = :1 , DYN_FLCO_ID_009 =
:2 , DYN_FLCO_ID_010 = :3 , DYN_FLCO_ID_008 = :4 , DYN_FLCO_ID_015 =
:5 , DYN_CHAR_004 = :6 , LAST_UPDATED_DATE = :7 ,
OBJECT_VERSION_NUMBER = :8 , LAST_UPDATED_BY = :9 WHERE ((ID = :10 )
AND (OBJECT_VERSION_NUMBER = :11 ))
Rationale
The SQL statement executed in container OHICFGPDB1 with database ID
2975261291.

Recommendation 2: Schema Changes
Estimated benefit is .05 active sessions, 29.95% of total activity.
-------------------------------------------------------------------
Action
Consider rebuilding the TABLE "OHI_CLAIMS_OWNER.OHI_PROCEDURES_B" with
object id 92891 using a higher value for INITRANS.
Related Object
Database object with ID 92891.
Action
Consider rebuilding the TABLE "OHI_CLAIMS_OWNER.OHI_PROCEDURES_B" with
object ID 92891 using a higher value for PCTFREE.
Related Object
Database object with ID 92891.
Rationale
The database segment belongs to container OHICFGPDB1 with database ID
2975261291.
Rationale
The SQL statement with SQL_ID "1zsn7y569u5g3" was found waiting for the
Interested Transaction List (ITL) enqueue on the TABLE
"OHI_CLAIMS_OWNER.OHI_PROCEDURES_B" with object ID 92891.
Related Object
SQL statement with SQL_ID 1zsn7y569u5g3.
UPDATE OHI_PROCEDURES_B SET DYN_FLCO_ID_011 = :1 , DYN_FLCO_ID_009 =
:2 , DYN_FLCO_ID_010 = :3 , DYN_FLCO_ID_008 = :4 , DYN_FLCO_ID_015 =
:5 , DYN_CHAR_004 = :6 , LAST_UPDATED_DATE = :7 ,
OBJECT_VERSION_NUMBER = :8 , LAST_UPDATED_BY = :9 WHERE ((ID = :10 )
AND (OBJECT_VERSION_NUMBER = :11 ))
Rationale
The SQL statement executed in container OHICFGPDB1 with database ID
2975261291.


Please Help
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Philip Wisse, Netherlands
Date: Apr 21, 2017, 15:12, 64 days ago
Message: Hi Tso,

These SQL statements are updates, I was expecting inserts.

Besides the Oracle database are there more servers involved?

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: data load slow
Author: Tso P, South Africa
Date: Apr 21, 2017, 15:21, 64 days ago
Message: Thanks

Yes, we have weblogic server and the developers loads data using soapUI.

Thanks for the response.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here