All,
I seek your expertise and opinion on this.. We have large warehouse database where a query runs as part of the ETL.. Query runs into around 2K lines and very complex. We recently had an incident and this query wasn't performing to the SLA.. So we pinned it with a known profile.. However, since it was pinned with a known plan, I see the optimizer is switching to a different plan..
Is this a normal behavior ?
Is this happening because of the adaptive plans set to TRUE ?
How to tell Oracle to stick to a known plan and not switch to a new one.
I have 10 other queries on the same DB but don't see it switching to the new plan.
Just to let you know there has not been any changes to the underlying table structure, view or any other environment
Thanks in advance.
Snapshot Avg LIO Avg PIO CPU (secs) Elapsed (secs)
INSTANCE_NUMBER Time Execs Per Exec Per Exec Per Exec Per Exec PLAN_HASH_VALUE SNAP_ID ROWS_PROCESSED_DELTA SQL_PROFILE
--------------- ------------ ----------------- ------------------- ------------------- ------------------- ------------------- --------------- ---------- -------------------- -------------------------------------------------------------
2 14-DEC 17:56 1 10,185,320.00 1,186,889.00 240.07 273.97 3840698008 48512 281174 coe_14vfvkm2aysbq_1570382218
1 14-DEC 18:11 1 12,076,506.00 1,389,254.00 281.74 325.83 3840698008 48513 330487 coe_14vfvkm2aysbq_1570382218
1 14-DEC 18:41 1 1,905,404.00 368,086.00 45.45 62.33 3840698008 48515 45819 coe_14vfvkm2aysbq_1570382218
1 14-DEC 19:11 1 602,953.00 318,723.00 13.07 26.70 3840698008 48517 5317 coe_14vfvkm2aysbq_1570382218
1 14-DEC 19:41 1 505,426.00 296,654.00 10.59 25.68 3840698008 48519 2724 coe_14vfvkm2aysbq_1570382218
1 14-DEC 20:11 1 700,250.00 340,536.00 13.32 28.24 3840698008 48521 9811 coe_14vfvkm2aysbq_1570382218
1 14-DEC 20:41 1 1,379,395.00 378,643.00 30.82 50.25 3840698008 48523 29088 coe_14vfvkm2aysbq_1570382218
2 14-DEC 21:11 1 1,611,209.00 306,927.00 35.01 43.68 3840698008 48525 35701 coe_14vfvkm2aysbq_1570382218
2 14-DEC 21:41 1 995,592.00 298,804.00 20.47 36.14 3840698008 48527 18879 coe_14vfvkm2aysbq_1570382218
1 14-DEC 22:11 1 2,120,434.00 375,053.00 43.10 62.53 3840698008 48529 53585 coe_14vfvkm2aysbq_1570382218
2 14-DEC 22:41 1 2,282,055.00 335,604.00 46.53 66.67 3840698008 48531 61468 coe_14vfvkm2aysbq_1570382218
1 14-DEC 23:11 1 6,131,390.00 739,663.00 139.31 208.85 2696677079 48533 175894 coe_14vfvkm2aysbq_1570382218
1 14-DEC 23:41 1 1,406,121.00 315,163.00 27.50 45.97 3840698008 48535 33242 coe_14vfvkm2aysbq_1570382218
2 15-DEC 00:11 1 1,149,613.00 217,237.00 29.58 49.51 3840698008 48537 24422 coe_14vfvkm2aysbq_1570382218
1 15-DEC 00:41 1 1,994,915.00 348,391.00 45.46 80.31 3840698008 48539 43683 coe_14vfvkm2aysbq_1570382218
2 15-DEC 01:11 1 2,624,594.00 439,108.00 60.98 110.08 3840698008 48541 60597 coe_14vfvkm2aysbq_1570382218
1 15-DEC 01:41 1 1,504,421.00 343,133.00 33.56 45.14 3840698008 48543 30727 coe_14vfvkm2aysbq_1570382218
2 15-DEC 02:11 1 758,805.00 205,426.00 16.89 33.71 1755686026 48545 11886 coe_14vfvkm2aysbq_1570382218
1 15-DEC 02:41 1 3,522,875.00 397,394.00 65.19 78.11 3840698008 48547 94445 coe_14vfvkm2aysbq_1570382218
1 15-DEC 03:11 1 2,008,278.00 348,604.00 40.79 66.60 3840698008 48549 45046 coe_14vfvkm2aysbq_1570382218
2 15-DEC 03:41 1 789,306.00 326,369.00 17.05 33.70 3840698008 48551 11118 coe_14vfvkm2aysbq_1570382218
1 15-DEC 04:11 1 1,023,246.00 383,969.00 22.23 34.63 2696677079 48553 17640 coe_14vfvkm2aysbq_1570382218
1 15-DEC 04:41 1 723,226.00 217,668.00 16.94 34.79 1755686026 48555 12242 coe_14vfvkm2aysbq_1570382218
1 15-DEC 05:11 1 588,104.00 323,554.00 12.76 27.92 1755686026 48557 4210 coe_14vfvkm2aysbq_1570382218
2 15-DEC 05:41 1 697,209.00 197,524.00 17.55 30.83 3840698008 48559 11548 coe_14vfvkm2aysbq_1570382218
1 15-DEC 06:11 1 769,020.00 201,590.00 19.94 35.54 3840698008 48561 13084 coe_14vfvkm2aysbq_1570382218
1 15-DEC 06:41 1 3,073,768.00 396,435.00 77.24 104.94 3840698008 48563 75919 coe_14vfvkm2aysbq_1570382218
1 15-DEC 07:11 1 4,791,893.00 497,629.00 92.51 144.88 2696677079 48565 124696 coe_14vfvkm2aysbq_1570382218
1 15-DEC 07:41 1 1,200,703.00 228,905.00 31.70 54.30 3840698008 48567 28273 coe_14vfvkm2aysbq_1570382218
2 15-DEC 08:11 1 2,352,174.00 376,314.00 62.54 95.18 3840698008 48569 57841 coe_14vfvkm2aysbq_1570382218
2 15-DEC 08:41 1 2,097,979.00 406,714.00 51.28 66.85 3840698008 48571 54151 coe_14vfvkm2aysbq_1570382218
1 15-DEC 09:11 1 2,410,573.00 440,403.00 54.11 69.80 2696677079 48573 63580 coe_14vfvkm2aysbq_1570382218
1 15-DEC 09:41 1 1,030,115.00 330,276.00 20.21 34.56 3840698008 48575 16901 coe_14vfvkm2aysbq_1570382218
2 15-DEC 10:11 1 1,170,508.00 332,769.00 25.92 50.56 2696677079 48577 25124 coe_14vfvkm2aysbq_1570382218
2 15-DEC 10:41 1 1,085,309.00 325,632.00 24.81 47.38 3840698008 48579 21254 coe_14vfvkm2aysbq_1570382218
2 15-DEC 11:11 1 993,460.00 333,040.00 21.81 50.61 3840698008 48581 18024 coe_14vfvkm2aysbq_1570382218
2 15-DEC 11:41 1 1,080,504.00 330,960.00 24.92 44.34 3840698008 48583 20340 coe_14vfvkm2aysbq_1570382218
2 15-DEC 12:11 1 1,458,126.00 339,964.00 36.67 58.62 3840698008 48585 33627 coe_14vfvkm2aysbq_1570382218
1 15-DEC 12:41 1 1,574,859.00 343,572.00 45.88 106.43 3840698008 48587 33502 coe_14vfvkm2aysbq_1570382218
2 15-DEC 13:11 1 1,419,843.00 343,338.00 36.63 50.74 3840698008 48589 34892 coe_14vfvkm2aysbq_1570382218
1 15-DEC 13:41 1 714,060.00 377,033.00 14.56 25.01 3840698008 48591 8461 coe_14vfvkm2aysbq_1570382218
1 15-DEC 14:11 1 1,257,543.00 332,451.00 29.41 51.78 2696677079 48593 26347 coe_14vfvkm2aysbq_1570382218