Apparently it wasn't "solved" by installing the August bundle, but rather temporarily alleviated by restarting the instance. Overnight after my previous post, the problem returned in one schema but not the other - either 1.5 business-days, or 3.5 calendar-days, after applying the bundle and seeing the problem go away. I swear Oracle has an evil sense of humor, and waits for me to post here before making the problem come back. (EDIT: I just realized Flyby's link above points to the same blog post. Afterward the patch, we restarted that set of expdp's from the top, and it got back to the same point where we had killed it. To put the before/after in perspective, a big set of expdp's ran 13.5 hours before we killed it to install the patch bundle. My only theory is that the April patch bundle was not installed correctly. It's been several days and the expdp's are screaming fast. For lack of anything else to try, we installed the August 2017 bundle - which is all-inclusive, including all patches from April - and lo & behold, that seems to have resolved the problem. The fix for that bug was included in the April 2017 bundle, and the April bundle was installed on the affected server in early August (before this problem became apparent). aaaaaaaaand then they slowed down again, with no changes applied to the DB.īlog post import-data-pump-performance-problem describes my situation well, and it identifies Bug 21286665 as the culprit. I would greatly appreciate any suggestions or advice for anything I might check, tests I could run, trace options I could turn on, log messages to look for, anything at all that could lead to an explanation for why this instance is displaying these symptoms, without regard to any other instances that are not. but suppose I hadn't mentioned the other two instances at all. Suppose I had an Oracle 11.2.0.4 instance with expdp symptoms as described, slow startup, 2-3 second delays between tables regardless of size, trace logs showing that delay is between "QUEUE_INT.SEND called" and "QUEUE_INT.SEND return". Oracle bug/patch, although not entirely ruled out). I believe the two instances that are now working properly rule out a hardware problem, and they largely rule out a software problem as well (i.e. and I agree completely, there is obviously at least one critical difference, something that would explain why I had no problem in the first, and the problem went away in the second, and the problem remains in the third. There are differences between them that surely don't matter. The three instances are not identical - a small one supports product A, a small-to-medium supports final ready-for-production testing of product B, and a larger one supports development & testing of that same product B but with more full and partial schemas. At any rate, I tried bumping it from 1 to 2, and this had no effect either 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. 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.Ī colleague suggested bumping AQ_TM_PROCESSES from 1 to 2. This is the kind of performance I am expecting. 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. In one of the slow instances, gathering dictionary/system/fixed-object stats was the key to the kingdom. The queue tables in all three schemas are defined identically. I also confirmed there are no invalid objects in SYS or SYSTEM (or in fact anywhere in any instance). exported "MYSCHEMA"."MYTABLE" 0 KB 0 rowsįollowing 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. KUPW:11:47:27.095: 2: In procedure MOVE_DATA UNLOADing process_order 2 TABLE_DATA:"MYSCHEMA"."MYTABLE"
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |