前几日，同事发现一套库数据库的活动会话数激增，大量活动会话集中等待gc buffer busy acquire上，如下图所示
![](http://albertdba.com/"wp-content/uploads"/2020/09/20200930-1-1024x474.png)
经过更一步的查看，发现这些等待均在sql_id：f7hnmtx8bd5q3上，利用如下sql进行查看发现：
SQL_TEXT如下：
SELECT *
FROM
(SELECT p.*
FROM t p,
TD_PLAN_LEG d
WHERE p.THR_DEP = d.THR_DEP
AND p.THR_ARR = d.THR_ARR
AND p.FLTNO = d._FLTNO
AND pDATE = d.DATE
AND (p.THR_DEP = :1
OR p.THR_ARR = :2)
AND nvl(d.STATUS_inner, ' ') != '??'
AND d.LT_ID IS NULL
AND nvl(d.STATUS_inner, ' ') != '??'
AND p.OP_TM > :3
ORDER BY p.OP_TM)
WHERE ROWNUM <= :4
SQL历史执行效率：
TIME PLAN_HASH_VALUE EXECU_D BG_D DR_D ET_D CT_D IO_TIME CLUS_TIME AP_TIME CC_TIME GET_ONETIME ROWS_PRO ROWS_ONETIME ET_MS_ONCE
------------ --------------- ------- ----------- ----------- --------- --------- ------- --------- ------- ------- ----------- ---------- ------------ ----------
2020092201 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092201 2495209816 3333 1351590946 0 2403 2409 0 0 0 0 405518 261 0 721
2020092202 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092202 2495209816 3238 1381884655 0 2449 2477 0 0 0 0 426771 82 0 756
2020092203 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092203 2495209816 3228 1380940555 0 2464 2475 0 0 0 0 427801 12 0 763
2020092204 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092204 2495209816 3249 0 0 2469 2486 0 0 0 0 0 0 0 760
2020092205 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092205 2495209816 3239 1401077673 0 2496 2510 0 0 0 0 432565 0 0 771
2020092206 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092206 2495209816 3240 1374013765 0 2449 2467 0 0 0 0 424078 0 0 756
2020092207 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092207 2495209816 3319 0 0 2584 2589 0 0 0 0 0 181 0 778
2020092208 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092208 2495209816 3656 1628720696 0 2827 2826 0 0 0 0 445493 1439 0 773
2020092209 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092209 2495209816 3238 1434887002 0 2610 2611 0 0 0 0 443140 2253 1 806
2020092210 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092210 2495209816 3240 0 0 2623 2629 0 0 0 0 0 2046 1 809
2020092211 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092211 2495209816 3240 1442329567 0 2672 2669 0 0 0 0 445163 2659 1 825
2020092212 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092212 2495209816 3240 1437033232 0 2676 2675 0 0 0 0 443529 2561 1 826
2020092213 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092213 2495209816 3222 0 0 2687 2689 0 0 0 0 0 2888 1 834
2020092214 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092214 2495209816 3240 1440743191 0 2706 2707 0 0 0 0 444674 3367 1 835
2020092215 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092215 2495209816 3243 1444795359 0 2747 2747 0 0 0 0 445512 2677 1 847
2020092216 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092216 2495209816 3243 0 0 2742 2749 0 0 0 0 0 2975 1 846
2020092217 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092217 2495209816 3256 1464276872 0 2774 2771 0 0 0 0 449716 2929 1 852
2020092218 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092218 2495209816 3240 1459846368 0 2802 2801 0 0 0 0 450570 3543 1 865
2020092219 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092219 2495209816 3240 0 0 2806 2806 0 0 0 0 0 3022 1 866
2020092220 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092220 2495209816 3221 1443354745 0 2819 2820 0 0 0 0 448108 2694 1 875
2020092221 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092221 2495209816 3239 1458055393 0 2891 2879 0 0 0 0 450156 2592 1 892
2020092222 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092222 2495209816 3240 0 0 2883 2887 0 0 0 0 0 2059 1 890
2020092223 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092223 2495209816 3240 1458681730 0 2926 2928 0 0 0 0 450210 1936 1 903
2020092300 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092300 2495209816 3238 1437599879 0 2925 2923 0 0 0 0 443978 1370 0 903
2020092301 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092301 2495209816 3251 1265725098 0 2330 2326 0 0 0 0 389334 695 0 717
2020092302 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092302 2495209816 3240 1322541308 0 2443 2447 0 0 0 0 408192 223 0 754
2020092303 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092303 2495209816 3240 0 0 2466 2471 0 0 0 0 0 41 0 761
2020092304 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092304 2495209816 3240 1309926437 0 2432 2431 0 0 0 0 404298 0 0 751
2020092305 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092305 2495209816 3250 1347086218 0 2466 2458 0 0 0 0 414488 0 0 759
2020092306 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092306 2495209816 3248 1327059467 0 2452 2452 0 0 0 0 408577 0 0 755
2020092307 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092307 2495209816 3242 0 0 2491 2488 0 0 0 0 0 194 0 768
2020092308 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092308 2495209816 3245 1405256862 0 2544 2538 0 0 0 0 433053 1625 1 784
2020092309 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092309 2495209816 3246 1421208667 0 2590 2579 0 0 0 0 437834 2518 1 798
2020092310 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092310 2495209816 3248 0 0 2619 2609 0 0 0 0 0 1961 1 806
2020092311 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092311 2495209816 3239 1426602319 0 2637 2632 0 0 0 0 440445 2673 1 814
2020092312 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092312 2495209816 3195 1403306564 0 2610 2603 0 0 0 0 439220 3029 1 817
2020092313 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092313 2495209816 3247 0 0 2698 2687 0 0 0 0 0 2976 1 831
2020092314 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092314 2495209816 3238 1418853144 0 2696 2690 0 0 0 0 438188 3496 1 833
2020092315 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092315 2495209816 3236 1443916772 0 2736 2728 0 0 0 0 446204 3600 1 846
2020092316 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092316 2495209816 3239 0 0 2731 2726 0 0 0 0 0 3630 1 843
2020092317 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092317 2495209816 3239 1435088513 0 2780 2768 0 0 0 0 443065 3446 1 858
2020092318 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092318 2495209816 3256 1422988985 0 2794 2782 0 0 0 0 437036 3043 1 858
2020092319 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092319 2495209816 3239 0 0 2816 2811 0 0 0 0 0 3108 1 869
2020092320 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092320 2495209816 3237 1414992685 0 2818 2813 0 0 0 0 437131 2993 1 870
2020092321 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092321 2495209816 3239 1450253448 0 2867 2859 0 0 0 0 447747 2686 1 885
2020092322 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092322 2495209816 3239 0 0 2866 2853 0 0 0 0 0 2661 1 885
2020092323 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092323 2495209816 3239 1440828417 0 2906 2889 0 0 0 0 444837 2007 1 897
2020092400 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092400 2495209816 3256 1405971757 0 2892 2884 0 0 0 0 431810 1569 0 888
2020092401 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092401 2495209816 3239 262264721 0 2332 2331 0 0 0 0 80971 570 0 720
2020092402 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092402 2495209816 3204 1328880815 0 2433 2428 0 0 0 0 414757 252 0 759
2020092403 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092403 2495209816 3240 1367144118 0 2459 2453 0 0 0 0 421958 101 0 759
2020092404 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092404 2495209816 3237 1318574101 0 2437 2432 0 0 0 0 407344 35 0 753
2020092405 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092405 2495209816 3220 0 0 2438 2435 0 0 0 0 0 0 0 757
2020092406 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092406 2495209816 3254 1346142138 0 2480 2469 0 0 0 0 413688 0 0 762
2020092407 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092407 2495209816 3238 1397020466 0 2503 2492 0 0 0 0 431445 160 0 773
2020092408 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092408 2495209816 3235 0 0 2529 2520 0 0 0 0 0 1401 0 782
2020092409 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092409 2495209816 3239 1424896212 0 2566 2556 0 0 0 0 439919 2275 1 792
2020092410 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092410 2495209816 3237 1421132007 0 2572 2569 0 0 0 0 439027 1956 1 795
2020092411 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092411 2495209816 3248 0 0 2629 2614 0 0 0 0 0 2913 1 809
2020092412 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092412 2495209816 3249 1448536971 0 2666 2651 0 0 0 0 445841 2722 1 820
2020092413 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092413 2495209816 3238 1437662381 0 2675 2668 0 0 0 0 443997 2445 1 826
2020092414 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092414 2495209816 3202 0 0 2680 2672 0 0 0 0 0 3238 1 837
2020092415 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092415 2495209816 3256 1450466091 0 2768 2758 0 0 0 0 445475 2920 1 850
2020092416 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092416 2495209816 3240 1450111226 0 2794 2779 0 0 0 0 447565 3109 1 862
2020092417 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092417 2495209816 3237 0 0 2824 2804 0 0 0 0 0 2818 1 873
2020092418 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092418 2495209816 3239 1424042026 0 2840 2831 0 0 0 0 439655 3346 1 877
2020092419 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092419 2495209816 3256 1435714284 0 2894 2885 0 0 0 0 440944 3120 1 889
2020092420 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092420 2495209816 3237 0 0 2914 2902 0 0 0 0 0 2788 1 900
2020092421 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092421 2495209816 3240 1433834204 0 2967 2946 0 0 0 0 442541 2857 1 916
2020092422 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092422 2495209816 3200 1413189546 0 2938 2924 0 0 0 0 441622 2346 1 918
2020092423 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092423 2495209816 3256 0 0 3032 3014 0 0 0 0 0 2108 1 931
2020092500 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092500 2495209816 3238 1417260989 0 3005 2982 0 0 0 0 437696 1358 0 928
2020092501 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092501 2495209816 3233 1296496694 0 2456 2444 0 0 0 0 401020 416 0 760
2020092502 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092502 2495209816 3220 473519624 0 2521 2518 0 0 0 0 147056 164 0 783
2020092503 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092503 2495209816 3257 1419336329 0 2546 2548 0 0 0 0 435780 9 0 782
2020092504 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092504 2495209816 3239 1410697902 0 2554 2548 0 0 0 0 435535 0 0 789
2020092505 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092505 2495209816 3239 0 0 2562 2548 0 0 0 0 0 0 0 791
2020092506 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092506 2495209816 3238 1422005253 0 2555 2551 0 0 0 0 439162 0 0 789
2020092507 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092507 2495209816 3244 1442245766 0 2591 2583 0 0 0 0 444589 276 0 799
2020092508 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092508 2495209816 3247 0 0 2642 2634 0 0 0 0 0 1679 1 814
2020092509 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092509 2495209816 3236 1467804298 0 2665 2656 0 0 0 0 453586 2599 1 824
2020092510 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092510 2495209816 3238 1462271228 0 2677 2670 0 0 0 0 451597 2009 1 827
2020092511 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092511 2495209816 3235 0 0 2720 2709 0 0 0 0 0 3010 1 841
2020092512 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092512 2495209816 3254 1465449842 0 2739 2723 0 0 0 0 450353 2555 1 842
2020092513 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092513 2495209816 3240 1459543406 0 2726 2709 0 0 0 0 450476 2664 1 841
2020092514 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092514 2495209816 3185 0 0 2702 2694 0 0 0 0 0 3784 1 848
2020092515 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092515 2495209816 3240 1447314954 0 2746 2742 0 0 0 0 446702 3116 1 848
2020092516 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092516 2495209816 3258 1475259620 0 2867 2821 0 0 0 0 452811 3183 1 880
2020092517 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092517 2495209816 3239 0 0 2841 2822 0 0 0 0 0 3168 1 877
2020092518 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092518 2495209816 3237 1455741217 0 2835 2815 0 0 0 0 449719 3258 1 876
2020092519 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092519 2495209816 3237 1456570263 0 2858 2846 0 0 0 0 449975 3135 1 883
2020092520 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092520 2495209816 3238 0 0 2881 2869 0 0 0 0 0 3033 1 890
2020092521 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092521 2495209816 3238 1464946512 0 2912 2898 0 0 0 0 452423 2918 1 899
2020092522 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092522 2495209816 3256 1468725511 0 2946 2929 0 0 0 0 451083 2386 1 905
2020092523 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092523 2495209816 3240 0 0 2948 2936 0 0 0 0 0 2258 1 910
2020092600 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092600 2495209816 3238 1437269657 0 2915 2904 0 0 0 0 443876 1648 1 900
2020092601 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092601 2495209816 3239 1358782568 0 2399 2387 0 0 0 0 419507 577 0 741
2020092602 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092602 2495209816 3240 1385828878 0 2525 2519 0 0 0 0 427725 158 0 779
2020092603 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092603 2495209816 3203 1391299844 0 2507 2503 0 0 0 0 434374 23 0 783
2020092604 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092604 2495209816 3237 1351047932 0 2500 2497 0 0 0 0 417377 0 0 772
2020092605 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092605 2495209816 3243 0 0 2529 2522 0 0 0 0 0 0 0 780
2020092606 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092606 2495209816 3235 1392017833 0 2526 2517 0 0 0 0 430299 0 0 781
2020092607 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092607 2495209816 3239 1424101671 0 2559 2544 0 0 0 0 439673 189 0 790
2020092608 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092608 2495209816 3239 496269954 0 2593 2583 0 0 0 0 153217 1619 0 800
2020092609 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092609 2495209816 3244 1473823605 0 2627 2617 0 0 0 0 454323 2200 1 810
2020092610 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092610 2495209816 3253 1473324036 0 2670 2658 0 0 0 0 452912 1995 1 821
2020092611 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092611 2495209816 3238 0 0 2716 2707 0 0 0 0 0 2491 1 839
2020092612 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092612 2495209816 3237 1477721737 0 2741 2733 0 0 0 0 456510 2460 1 847
2020092613 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092613 2495209816 3239 1483323345 0 2768 2755 0 0 0 0 457957 2757 1 855
2020092614 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092614 2495209816 3241 1487746111 0 2808 2792 0 0 0 0 459039 3145 1 866
2020092615 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092615 2495209816 3214 1478749632 0 2820 2809 0 0 0 0 460096 2790 1 878
2020092616 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092616 2495209816 3239 0 0 2869 2858 0 0 0 0 0 3045 1 886
2020092617 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092617 2495209816 3240 1483068741 0 2880 2871 0 0 0 0 457737 2691 1 889
2020092618 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092618 2495209816 3256 1496586513 0 2925 2913 0 0 0 0 459640 3351 1 898
2020092619 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092619 2495209816 3240 686804354 0 2943 2923 0 0 0 0 211977 2956 1 908
2020092620 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092620 2495209816 3239 1492171972 0 2955 2941 0 0 0 0 460689 2728 1 912
2020092621 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092621 2495209816 3242 1499007445 0 2971 2961 0 0 0 0 462371 2518 1 917
2020092622 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092622 2495209816 3254 0 0 2983 2974 0 0 0 0 0 2323 1 917
2020092623 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092623 2495209816 3201 1489464912 0 3019 3006 0 0 0 0 465312 1919 1 943
2020092700 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092700 2495209816 3239 1489900471 0 3025 3011 0 0 0 0 459988 1195 0 934
2020092701 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092701 2495209816 3240 1357716042 0 2434 2429 0 0 0 0 419048 521 0 751
2020092702 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092702 2495209816 3244 1448164591 0 2566 2560 0 0 0 0 446413 276 0 791
2020092703 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092703 2495209816 3252 0 0 2571 2559 0 0 0 0 0 72 0 791
2020092704 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092704 2495209816 3221 1417052962 0 2503 2501 0 0 0 0 439942 2 0 777
2020092705 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092705 2495209816 3240 1435598638 0 2533 2534 0 0 0 0 443086 0 0 782
2020092706 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092706 2495209816 3244 0 0 2527 2528 0 0 0 0 0 0 0 779
2020092707 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092707 2495209816 3254 1470802612 0 2605 2599 0 0 0 0 451998 204 0 801
2020092708 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092708 2495209816 3239 1466922394 0 2601 2589 0 0 0 0 452894 1735 1 803
2020092709 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092709 2495209816 3238 1462310461 0 2647 2640 0 0 0 0 451609 2688 1 818
2020092710 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092710 2495209816 3244 1460262764 0 2656 2651 0 0 0 0 450143 2056 1 819
2020092711 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092711 2495209816 3254 1485315112 0 2720 2708 0 0 0 0 456458 2956 1 836
2020092712 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092712 2495209816 3239 1475673310 0 2718 2710 0 0 0 0 455595 2987 1 839
2020092713 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092713 2495209816 3240 1480502924 0 2752 2741 0 0 0 0 456945 3107 1 849
2020092714 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092714 2495209816 3242 0 0 2770 2758 0 0 0 0 0 3774 1 855
2020092715 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092715 2495209816 3254 1485748247 0 2802 2783 0 0 0 0 456591 3499 1 861
2020092716 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092716 2495209816 3185 0 0 2774 2759 0 0 0 0 0 3173 1 871
2020092717 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092717 2495209816 3239 1488000864 0 2849 2839 0 0 0 0 459401 3113 1 880
2020092718 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092718 2495209816 3238 1480569006 0 2868 2860 0 0 0 0 457248 3294 1 886
2020092719 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092719 2495209816 3243 1479350901 0 2897 2886 0 0 0 0 456167 2813 1 893
2020092720 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092720 2495209816 3240 1466487471 0 2915 2902 0 0 0 0 452620 2953 1 900
2020092721 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092721 2495209816 3254 1473511907 0 2981 2966 0 0 0 0 452831 2821 1 916
2020092722 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092722 2495209816 3240 0 0 2987 2976 0 0 0 0 0 2468 1 922
2020092723 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092723 2495209816 3240 1485591039 0 3023 3010 0 0 0 0 458516 2491 1 933
2020092800 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092800 2495209816 3239 1492094762 0 3032 3023 0 0 0 0 460665 1508 0 936
2020092801 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092801 2495209816 3244 1381156470 0 2472 2464 0 0 0 0 425757 752 0 762
2020092802 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092802 2495209816 3251 0 0 2585 2581 0 0 0 0 0 265 0 795
2020092803 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092803 2495209816 3239 1465140224 0 2599 2588 0 0 0 0 452343 10 0 802
2020092804 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092804 2495209816 3190 1446640595 0 2553 2540 0 0 0 0 453492 0 0 800
2020092805 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092805 2495209816 3253 0 0 2607 2600 0 0 0 0 0 0 0 801
2020092806 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092806 2495209816 3240 1467000606 0 2597 2590 0 0 0 0 452778 0 0 802
2020092807 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092807 2495209816 3242 1472818082 0 2610 2604 0 0 0 0 454293 183 0 805
2020092808 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092808 2495209816 3236 0 0 2631 2616 0 0 0 0 0 1622 1 813
2020092809 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092809 2495209816 3240 1489167496 0 2670 2650 0 0 0 0 459620 2737 1 824
2020092810 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092810 2495209816 3239 1480634051 0 2677 2666 0 0 0 0 457127 2304 1 826
2020092811 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092811 2495209816 3240 0 0 2729 2716 0 0 0 0 0 3116 1 842
2020092812 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092812 2495209816 3244 1490574897 0 2738 2727 0 0 0 0 459487 2975 1 844
2020092813 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092813 2495209816 3239 1495331918 0 2775 2758 0 0 0 0 461665 3305 1 857
2020092814 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092814 2495209816 3253 0 0 2811 2791 0 0 0 0 0 3988 1 864
2020092815 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092815 2495209816 3239 1494368501 0 2810 2799 0 0 0 0 461367 3532 1 867
2020092816 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092816 2495209816 3208 0 0 2798 2786 0 0 0 0 0 3395 1 872
2020092817 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092817 2495209816 3253 1503663984 0 2890 2868 0 0 0 0 462239 3418 1 888
2020092818 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092818 2495209816 3239 1492963087 0 2880 2870 0 0 0 0 460933 3405 1 889
2020092819 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092819 2495209816 3239 0 0 2924 2914 0 0 0 0 0 3268 1 903
2020092820 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092820 2495209816 3243 1502574273 0 2932 2925 0 0 0 0 463328 3413 1 904
2020092821 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092821 2495209816 3254 1511131260 0 2960 2945 0 0 0 0 464392 2994 1 910
2020092822 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092822 2495209816 3240 0 0 2963 2950 0 0 0 0 0 2852 1 915
2020092823 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092823 2495209816 3240 1511966632 0 3007 2992 0 0 0 0 466656 2395 1 928
2020092900 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092900 2495209816 3206 1483716066 0 2963 2954 0 0 0 0 462794 1355 0 924
2020092901 1636324798 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092901 2495209816 3254 0 0 2412 2406 0 0 0 0 0 406 0 741
2020092902 1636324798 99 352210787 13407233 21201 2845 7253 9775 0 6 3557685 37 0 214150
2020092902 2495209816 2178 981309957 0 1711 1707 0 0 0 0 450556 66 0 786
2020092903 1636324798 318 1160978121 38861241 60087 10011 3682 42585 0 31 3650875 56 0 188954
2020092903 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092904 1636324798 315 1138406066 37854487 59574 10038 3329 41809 0 29 3613988 0 0 189123
2020092904 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092905 1636324798 313 1143701796 38668999 59974 10059 3587 41987 0 33 3653999 0 0 191611
2020092905 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092906 1636324798 306 0 42268789 62487 9733 4179 44183 0 31 0 0 0 204205
2020092906 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092907 1636324798 323 1158824752 41644602 61905 10292 4001 43486 0 40 3587693 201 1 191657
2020092907 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092908 1636324798 309 1167837295 37852269 63066 10364 3967 44390 0 42 3779409 2124 7 204097
2020092908 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092909 1636324798 310 1118885638 36122238 63801 9831 4341 45415 0 46 3609309 3514 11 205811
2020092909 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092910 1636324798 275 0 33482927 65518 9199 9300 43109 0 48 0 3574 13 238246
2020092910 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092911 1636324798 235 915691746 71725197 66676 9162 7097 46978 0 44 3896561 4472 19 283727
2020092911 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092912 1636324798 257 942550994 60478871 65961 9210 6233 46797 0 42 3667514 5398 21 256657
2020092912 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092913 1636324798 163 646322619 45033189 68480 8699 10553 46613 0 86 3965169 4002 25 420122
2020092913 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092914 1636324798 178 655193101 34549897 67433 8435 9002 46876 0 78 3680860 4798 27 378839
2020092914 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092915 1636324798 162 0 42181497 69005 8512 9628 48119 0 73 0 5236 32 425955
2020092915 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092916 1636324798 180 659119160 37016987 67935 8320 8938 47657 0 74 3661773 6284 35 377415
2020092916 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092917 1636324798 178 668652544 36068743 66633 8204 8260 47129 0 66 3756475 4672 26 374341
2020092917 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092918 1636324798 182 690411953 47612967 68886 8662 9144 48329 0 64 3793472 6218 34 378496
2020092918 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092919 1636324798 180 716579287 38972462 68804 8613 8099 49062 0 68 3980996 5828 32 382243
2020092919 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092920 1636324798 198 725411011 42912011 66999 8657 8366 46635 0 72 3663692 4517 23 338377
2020092920 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092921 1636324798 182 0 56233403 68130 9001 9442 46655 0 79 0 3744 21 374340
2020092921 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092922 1636324798 200 761626806 56780398 67513 9089 8585 47243 0 60 3808134 3849 19 337564
2020092922 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020092923 1636324798 217 785661905 49927309 66720 9066 7530 46950 0 59 3620562 3528 16 307464
2020092923 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093000 1636324798 203 827248817 65561662 67630 9780 8513 46470 0 70 4075117 2246 11 333155
2020093000 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093001 1636324798 325 1200248776 124828228 62141 10674 6906 40984 0 19 3693073 619 2 191202
2020093001 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093002 1636324798 310 1110431303 39739255 57335 9643 2766 40456 0 23 3582036 185 1 184951
2020093002 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093003 1636324798 320 1149651569 39694801 61120 9942 3466 43492 0 27 3592661 47 0 191000
2020093003 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093004 1636324798 327 0 40854731 62249 10587 3609 43766 0 31 0 21 0 190365
2020093004 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093005 1636324798 319 1125342936 38863217 59590 10107 3507 41533 0 32 3527721 0 0 186801
2020093005 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093006 1636324798 309 1145849658 38889950 62572 10367 3745 44353 0 34 3708251 0 0 202500
2020093006 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093007 1636324798 327 1171387941 39245953 63327 10553 3852 44758 0 40 3582226 241 1 193662
2020093007 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093008 1636324798 296 0 38143234 64608 10248 4218 45887 0 41 0 2377 8 218271
2020093008 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093009 1636324798 291 1070234826 36419287 65024 9485 4331 46952 0 42 3677783 3606 12 223449
2020093009 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
2020093010 1636324798 297 1060264318 35236356 65779 9293 4500 47678 0 43 3569914 3473 12 221476
2020093010 2495209816 0 0 0 0 0 0 0 0 0 0 0 0 0
404 rows selected.从DBA_HIST_SQLSTAT中看到，该SQL有两个执行计划:
PLAH_HASH_VALUE：(1) 1636324798 (2)2495209816其中2495209816效率相对较高，1636324798一直存在但是在9月29日之前并没有启用过，一直在用2495209816，直到2020092902（2点）开始启用，并且在2020092903（3点）完全使用。但是ORACLE为何突然使用了差的执行计划了？
Inst: 1 Child: 0 Plan hash value: 2495209816
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 372K(100)| | | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 1 | 176 | 372K (1)| 01:14:32 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 125 | 372K (1)| 01:14:32 | 1024 | 1024 | |
| 4 | NESTED LOOPS | | 1 | 125 | 372K (1)| 01:14:32 | | | |
| 5 | NESTED LOOPS | | 120K| 125 | 372K (1)| 01:14:32 | | | |
|* 6 | TABLE ACCESS FULL | T1 | 120K| 4482K| 10211 (1)| 00:02:03 | | | |
|* 7 | INDEX RANGE SCAN | T_POSITIONS_KEY | 1 | | 3 (0)| 00:00:01 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| T | 1 | 87 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Inst: 1 Child: 38 Plan hash value: 1636324798
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1039K(100)| | | | |
|* 1 | COUNT STOPKEY | | | | | | | | | |
| 2 | VIEW | | 118K| 19M| | 1039K (1)| 03:27:50 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 118K| 14M| 16M| 1039K (1)| 03:27:50 | 1024 | 1024 | |
|* 4 | HASH JOIN | | 118K| 14M| 6024K| 1035K (1)| 03:27:10 | 9516K| 3276K| 10M (0)|
|* 5 | TABLE ACCESS FULL | T1 | 123K| 4576K| | 10211 (1)| 00:02:03 | | | |
|* 6 | TABLE ACCESS FULL | T | 118K| 9M| | 1024K (1)| 03:24:57 | | | |
----------------------------------------------------------------------------------------------------------------------------------经过分析发现，此SQL涉及对象在9月28日时触发了统计信息收集,并且因此游标滚动失效，9月29日零时进行了硬解析。因数据库中绑定变量窥探关闭（_optim_peek_user_binds，被设定为FALSE），无法”窥视“直方图，根据数据分布与bind值分析，因数据倾斜严重+关闭“窥探直方图”情况下被认为平均分布，优化器选择了选择了”CBO认为效率高，实际效率差”的执行计划。针对此情形，可以利用SQL_PROFILE进行绑定（借助coe_load_sql_profile.sql）脚本绑定后
![](http://albertdba.com/"wp-content/uploads"/2020/10/20201005-1-1024x412.png)
数据库GC类等待消失，SQL效率恢复至毫秒级别。