Home » RDBMS Server » Server Utilities » expdp slow, delays in QUEUE_INT.SEND (Oracle 11.2.0.4, Windows Server 2012 R2)
expdp slow, delays in QUEUE_INT.SEND [message #665348] Thu, 31 August 2017 14:25 Go to previous message
ctvarner
Messages: 11
Registered: August 2017
Junior Member
I have three 11.2.0.4 Oracle instances on a Windows 2012 R2 server. One instance is fine and does not exhibit the behavior described below.

For the other two, expdp is painfully slow. With METRICS=Y, the output shows the startup phase taking anywhere from 60 to 120 seconds. Once the actual data export gets underway, every table takes a minimum of 2-3 seconds, including empty tables. With TRACE set to 0690300 (typical 0480300, plus 0010000 for expdp/impdp details, plus 0200000 for queue services details), the trace log shows a delay of 2-3 seconds on every "KUPC$QUEUE_INT.SEND".

Here, note the delay of 2.709 seconds between "...SEND called" and "...SEND returned". Also note MYSCHEMA.MYTABLE is empty. 

KUPW:11:47:27.095: 2: In procedure BUILD_OBJECT_STRINGS
KUPW:11:47:27.095: 2: In procedure MOVE_DATA UNLOADing process_order 2 TABLE_DATA:"MYSCHEMA"."MYTABLE"
KUPW:11:47:27.095: 2: In procedure FORMAT_TABLE_DATA_MSG
KUPW:11:47:27.095: 2: In procedure CREATE_MSG
KUPW:11:47:27.095: 2: KUPV$FT.MESSAGE_TEXT called. 
KUPW:11:47:27.095: 2: KUPV$FT.MESSAGE_TEXT returned. 
KUPW:11:47:27.095: 2: In procedure SEND_MSG. Fatal=0
KUPW:11:47:27.095: 2: KUPC$QUEUE_INT.SEND called. 

*** 2017-08-31 11:47:29.804
KUPW:11:47:29.804: 2: KUPC$QUEUE_INT.SEND returned. 
KUPW:11:47:29.804: 2: . . exported "MYSCHEMA"."MYTABLE"                 0 KB       0 rows

Following advice on the internet, in the two slow instances, I rebuilt all indexes and analyzed all tables in MYSCHEMA; gathered dictionary stats, system stats, and fixed object stats; confirmed (to the best of my ability) that things like memory_target, memory_max_target, and streams_pool_size are all set appropriately. I also confirmed there are no invalid objects in SYS or SYSTEM (or in fact anywhere in any instance). The queue tables in all three schemas are defined identically.

In one of the slow instances, gathering dictionary/system/fixed-object stats was the key to the kingdom. Once I did that, expdp startup in that instance became nearly instantaneous, exporting an empty table is nearly instantaneous, the delays on QUEUE_INT.SEND in the trace log are gone, and a schema backup that ran nearly two hours the night before completed in under three minutes. This is the kind of performance I am expecting. In the other slow schema, despite taking all the same steps, expdp performance remains exactly the same, as shown in the trace log entry above.

A colleague suggested bumping AQ_TM_PROCESSES from 1 to 2. The internet is clear that I shouldn't need to fool with that parameter in 11g at all, and equally clear that it should never be set to zero (it wasn't), but somewhat less clear on what effect (if any) that parameter still has in 11g. At any rate, I tried bumping it from 1 to 2, and this had no effect either

What am I missing?

Report message to a moderator

[Message index]
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: ORA-39014: One or more workers have prematurely exited. ORA-31671: Worker process DW00 had an unhand
Next Topic: Adding disk to diskgroup
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 05:17:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.