SQL执行计划突变案例分析

发表于由

前几日，同事发现一套库数据库的活动会话数激增，大量活动会话集中等待gc buffer busy acquire上，如下图所示

经过更一步的查看，发现这些等待均在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）脚本绑定后

数据库GC类等待消失，SQL效率恢复至毫秒级别。

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous报错修复

发表于由

休假前，同事在异地恢复一套数据库时报错：RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous。以下内容为还原报错场景与修复手段：

ora11g@Travelskydba-ora dbs]$ rman target / catalog cata_user/oracle@ora977b

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:13:01 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD3 (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 04-SEP-20
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/04/2020 10:13:08
RMAN-12010: automatic channel allocation initialization failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous


SQL> select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS_TIME
---------- ---------- ---------- -------- ----------------- -----------------
         1          2 1663399389 PROD3                    1 20200904 09:23:41
        49         50 1663401346 PROD3                    1 20200904 09:56:18

报错根因为此数据库在CATALOG库中注册了2次（期间重新建立过DB-create d atabase操作），所以在备份还原controfile时需要指定dbid：

RMAN> set dbid=1663401346       

executing command: SET DBID
database name is "PROD3" and DBID is 1663401346

RMAN> restore controlfile;

Starting restore at 04-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1
channel ORA_DISK_1: piece handle=/opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1 tag=TAG20200904T100809
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/ora11g/albert/controlfile1.ctl
output file name=/opt/app/ora11g/albert/controlfile2.ctl
Finished restore at 04-SEP-20

为了避免后续再次出现此类问题，可以将已经废弃的数据库dbid从catalog中unregister,避免每次set dbid操作

[ora11g@Travelskydba-ora app]$ rman target / catalog cata_user/oracle@travelskydba

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:35:56 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
connected to recovery catalog database

RMAN> set dbid=1663399389 

executing command: SET DBID
database name is "PROD3" and DBID is 1663399389

RMAN> unregister database;

database name is "PROD3" and DBID is 1663399389

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

RMAN> 

SQL> select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS_TIME
---------- ---------- ---------- -------- ----------------- -----------------
        49         50 1663401346 PROD3                    1 20200904 09:56:18

SQL> 

[ora11g@Travelskydba-ora dbs]$ rman target / catalog cata_user/oracle@travelskydba

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 4 10:37:32 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD3 (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 04-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1
channel ORA_DISK_1: piece handle=/opt/app/ora11g/orabase/product/11.2.0/db_1/dbs/03v9idof_1_1 tag=TAG20200904T100809
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/ora11g/albert/controlfile1.ctl
output file name=/opt/app/ora11g/albert/controlfile2.ctl
Finished restore at 04-SEP-20

shutdown abort导致事务提交？

发表于由

前不久，同事问了我一个这样的问题，并且附上了实验记录，在oracle中，为何shudown abort后导致了事务的提交，而并非回滚？现将他的实验记录贴出来，细心的朋友可能会马上发现问题。

SQL> conn scott/scott
Connected.
SQL> set line 100 pagesize 200
SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7499 ALLEN      SALESMAN	      7698 19810220 00:00:00	   1600        300	   30
      7521 WARD       SALESMAN	      7698 19810222 00:00:00	   1250        500	   30
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 19810928 00:00:00	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7844 TURNER     SALESMAN	      7698 19810908 00:00:00	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

14 rows selected.

SQL> delete from emp where comm is not null;

4 rows deleted.

SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

10 rows selected.

SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1.3495E+11 bytes
Fixed Size		    2270072 bytes
Variable Size		 3.6776E+10 bytes
Database Buffers	 9.7979E+10 bytes
Redo Buffers		  197844992 bytes
Database mounted.
Database opened.
SQL> conn scott/scott
Connected.
SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

10 rows selected.

同事前来问我，为何重启后，事务提交了？不是应该回滚的么？

细心的同学可能已经发现了问题，执行完事务后，会话进行shutdown abort失败（权限不足），使用了conn 命令进行了用户的切换，引用Oracle官方文档中的描述”：

Usage

CONNECT commits the current transaction to the database, disconnects the current username from Oracle Database, and reconnects with the specified username.

这个问题虽然看起来过于细节也很基础，但是稍不注意便会造成数据的隐式COMMIT，特别是在SQLPLUS中操作数据的过程中，需要特别小心。

开发不规范引发的TOP SQL

发表于由

近日分析优化了一个TOP SQL，从这个SQL中可以得出，开发规范的重要性，数据库的性能平稳与数据库SQL开发与应用规范密不可分。

--原SQL代码较长，文本处理后的SQL如下：
SELECT ROWNUM, T.*
  FROM (SELECT OWNER, OBJECT_NAME, NAMESPACE.....
          FROM T1_PART TB
         where TB.OBJECT_TYPE = :1 
           and TB.CREATED = :2
         ORDER BY TB.OBJECT_ID) T
 WHERE ROWNUM BETWEEN 1 AND 1000

SQL执行统计信息如下：

此条SQL存在两个执行计划，平均行数都是0行，但是buffer get却非常之大，最差的执行计划逻辑读平均要3百万+，此表示一个分区表，并且按照CREATED日期字段做了INTERVAL按天分区，并且CREATED字段上存在索引。

从执行计划中获悉，虽然谓词部分包含了分区键，但是仍然走了PARTITION RANG ALL的执行计划，并且以全表扫描扫描了所有分区，没有跨分区访问，为何没有分区裁剪呢？

继续阅读 →

缺失合理索引导致SQL效率低下案例

发表于由

近日同事在梳理生产系统TOP SQL时发现有一个SQL消耗了很多资源，情况如下：

SQL统计信息

SQL文本如下：

SELECT t.ID, t.FLT_OC_FLIGHT_ID, t.FLT_AIRLINE_CODE, t.FLT_FLIGHT_NO, t.FLT_FLIGHT_DATE
	, t.FLT_ALL_STATIONS, t.FLT_UPDATE_AGENT, t.FLT_UPDATE_TIME, t.FLT_NIGHT_FLIGHT_FLAG, t.FLT_INTERNATIONAL
	, t.FLT_ORI_STATION_CODE, t.FLT_DES_STATION_CODE, t.SEG_DATA_SOURCE, t.IS_VALID, t.INSERT_TIME
	, t.UPDATE_TIME, t.FLT_MSG_TID
FROM T_DEP_FDC_FLIGHT t
	INNER JOIN T_DEP_FDC_STATION s ON t.ID = s.STA_FLIGHT_ID 
WHERE t.FLT_AIRLINE_CODE IN (:1, :2, :3, :4)
	AND instr(t.FLT_ALL_STATIONS, :5) BETWEEN 1 AND length(t.FLT_ALL_STATIONS) - 3
	AND s.STA_STATION_CODE = :6
	AND s.STA_SCH_DEP_TIME <= :7
	AND s.STA_SCH_DEP_TIME >= :8
	AND t.IS_VALID = 1
	AND t.FLT_OC_FLIGHT_ID IS NULL

因为该SQL执行时间超过5s，Oracle会自动monitor,我们可以得到该SQL的SQL_MONITOR

从该条SQL的执行计划来看，T_DEP_FDC_FLIGHT 与T_DEP_FDC_STATION采用了Nested loop的方式进行JOIN，并且 T_DEP_FDC_FLIGHT 为驱动表。但是被驱动表扫描索引678K后，回表再过滤只剩下58行记录，并且 T_DEP_FDC_STATION 谓词条件的数据分布均匀，那么我们完全可以创建合适的索引，使得 T_DEP_FDC_STATION 表驱动 T_DEP_FDC_FLIGHT （小结果集驱动大结果集）。

模拟SQL：

select t.*
  from a t
 inner join s
    on t.object_id = s.object_id
 where t.NAMESPACE in (1, 24, 9, 64)
   and instr(t.LAST_DDL_TIME, '2') between 0 and
       length(t.LAST_DDL_TIME) - 3
   and s.NAMESPACE >= 23
   and s.NAMESPACE <= 25
   and s.status = 'VALID'
   and t.owner='SYS'
   and t.OBJECT_NAME is not null

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |  4640 (100)|          |     19 |00:00:03.66 |   18746 |
|   1 |  NESTED LOOPS                |         |      1 |     26 |  4640   (1)| 00:00:56 |     19 |00:00:03.66 |   18746 |
|   2 |   NESTED LOOPS               |         |      1 |    135 |  4640   (1)| 00:00:56 |  69487 |00:00:03.62 |   17632 |
|*  3 |    TABLE ACCESS FULL         | A       |      1 |    135 |  4497   (1)| 00:00:54 |   1076K|00:00:02.83 |   16476 |
|*  4 |    INDEX RANGE SCAN          | S_IDX01 |   1076K|      1 |     1   (0)| 00:00:01 |  69487 |00:00:00.57 |    1156 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| S       |  69487 |      1 |     2   (0)| 00:00:01 |     19 |00:00:00.03 |    1114 |
--------------------------------------------------------------------------------------------------------------------------

创建索引：
SQL> create index s_idx01 on s(status,NAMESPACE);

Index created.


select t.*
  from a t
 inner join s
    on t.object_id = s.object_id
 where t.NAMESPACE in (1, 24, 9, 64)
   and instr(t.LAST_DDL_TIME, '2') between 0 and
       length(t.LAST_DDL_TIME) - 3
   and s.NAMESPACE >= 23
   and s.NAMESPACE <= 25
   and s.status = 'VALID'
   and t.owner='SYS'
   and t.OBJECT_NAME is not null


Plan hash value: 3503728021
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |    84 (100)|          |     35 |00:00:00.04 |      64 |      1 |
|   1 |  NESTED LOOPS                 |         |      1 |      5 |    84   (0)| 00:00:02 |     35 |00:00:00.04 |      64 |      1 |
|   2 |   NESTED LOOPS                |         |      1 |     83 |    84   (0)| 00:00:02 |     50 |00:00:00.03 |      52 |      1 |
|   3 |    TABLE ACCESS BY INDEX ROWID| S       |      1 |     83 |     4   (0)| 00:00:01 |     54 |00:00:00.03 |      21 |      1 |
|*  4 |     INDEX RANGE SCAN          | S_IDX01 |      1 |     83 |     2   (0)| 00:00:01 |     54 |00:00:00.03 |       5 |      1 |
|*  5 |    INDEX UNIQUE SCAN          | A_PK1   |     54 |      1 |     0   (0)|          |     50 |00:00:00.01 |      31 |      0 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | A       |     50 |      1 |     1   (0)| 00:00:01 |     35 |00:00:00.01 |      12 |      0 |
------------------------------------------------------------------------------------------------------------------------------------

高逻辑读Top Sql 优化案例一则

发表于由

前不久，有个高逻辑读的sql需要进行优化处理，详情如下：

SQL_TEXT如下：
SELECT DISTINCT object_name
FROM t2
WHERE (substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
	AND object_name IS NOT NULL
	AND object_type IS NULL
	AND object_name NOT IN (
		SELECT object_name
		FROM t3
		WHERE object_name IS NOT NULL
	)
	AND regexp_like(object_name, '[[:upper:]]{4}'))

SQL的统计信息如下 ：
Historical Plans Summary(dba_hist_sqlstat):

  RN PLAN_HASH_VALUE AVG_ETIME_S  AVG_CPU_S    AVG_BUFFERS  AVG_READS   AVG_ROWS      EXECS FIRST_SNAP          LAST_SNAP
---- --------------- ------------ ------------ ----------- ---------- ---------- ---------- ------------------- -------------------
   1       527727976      113.451       32.239      907578     899049         18          8 2020-05-20 10:00:15 2020-05-28 10:00:51


Current Plans Summary(gv_sql):

 RN PLAN_HASH_VALUE AVG_ETIME_S  AVG_CPU_S    AVG_BUFFERS  AVG_READS   AVG_ROWS TOTAL_EXEC FIRST_LOAD_TIME      LAST_ACTIVE
--- --------------- ------------ ------------ ----------- ---------- ---------- ---------- -------------------- --------------------
  1       527727976      154.255       45.998     1131102    1130264         23          1 2020-05-28/09:00:02  2020-05-28 09:02:36


执行计划如下：
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null and
object_type is null    and object_name not in        (select
object_name from t3 where object_name is not null)    and
regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 4047468152

--------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |   239 (100)|          |     51 |00:00:00.03 |     863 |
|   1 |  HASH UNIQUE        |      |      1 |      1 |   239   (2)| 00:00:03 |     51 |00:00:00.03 |     863 |
|*  2 |   HASH JOIN ANTI    |      |      1 |      1 |   238   (1)| 00:00:03 |     77 |00:00:00.03 |     863 |
|*  3 |    TABLE ACCESS FULL| T2   |      1 |     70 |   153   (1)| 00:00:02 |     77 |00:00:00.01 |     557 |
|*  4 |    TABLE ACCESS FULL| T3   |      1 |  85011 |    85   (2)| 00:00:02 |  71958 |00:00:00.01 |     306 |
--------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T2@SEL$1
   4 - SEL$5DA710D3 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$1")
      FULL(@"SEL$5DA710D3" "T3"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T3"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T3"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$5DA710D3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"="OBJECT_NAME")
   3 - filter(("OBJECT_TYPE" IS NULL AND  REGEXP_LIKE
              ("OBJECT_NAME",'[[:upper:]]{4}',HEXTORAW('105FF26E1D000000FCE30302000000000000000000000000703EDC060000
              0000000000000000000000000000000000003600000000000000605FF26E1D00000004000000010000000000000081000000')
               ) AND SUBSTR("CREATED",0,10)=TO_CHAR(SYSDATE@!-1,'yyyy-mm-dd') AND "OBJECT_NAME" IS NOT NULL))
   4 - filter("OBJECT_NAME" IS NOT NULL)

生产中，T2表63127459行记录（8g），T3表5703行记录（3m），join时都使用了全表扫描，其中cretad ndv很高，每天的数据量记录数并不多，可以创建如下索引：

create index t2_idx on t2(substr(created, 0, 10));
create index T3_IDX01 on t3(object_name);

执行计划如下，仍然不是最优的执行计划，更高效的应该是走NESTED LOOPS ANTI

-------------------------------------
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null and
object_type is null    and object_name not in        (select
object_name from t3 where object_name is not null)    and
regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 1600442906

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |    93 (100)|          |    247 |00:00:00.03 |     317 |
|   1 |  HASH UNIQUE                  |        |      1 |      5 |    93   (3)| 00:00:02 |    247 |00:00:00.03 |     317 |
|*  2 |   HASH JOIN ANTI              |        |      1 |      5 |    92   (2)| 00:00:02 |    404 |00:00:00.02 |     317 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2     |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    TABLE ACCESS FULL          | T3     |      1 |  85303 |    85   (2)| 00:00:02 |  72028 |00:00:00.01 |     306 |
--------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T2@SEL$1
   4 - SEL$5DA710D3 / T2@SEL$1
   5 - SEL$5DA710D3 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$1" "T2_IDX")
      FULL(@"SEL$5DA710D3" "T3"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T3"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T3"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$5DA710D3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"="OBJECT_NAME")
   3 - filter(("OBJECT_TYPE" IS NULL AND  REGEXP_LIKE ("OBJECT_NAME",'[[:upper:]]{4}',HEXTORAW('5815F42D1D000000FC
              E30302000000000000000000000000703EDC0600000000000000000000000000000000000000003600000000000000A815F42D1D0000000400
              0000010000000000000081000000') ) AND "OBJECT_NAME" IS NOT NULL))
   4 - access("T2"."SYS_NC00005$"=TO_CHAR(SYSDATE@!-1,'yyyy-mm-dd'))
   5 - filter("OBJECT_NAME" IS NOT NULL)

我们可以添加HINT改变执行计划：

方式1：使用qb_name hint

select /*+ use_nl(t2,t3@bl) */
distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ qb_name(bl) */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')

SQL_ID  guj3z7b3w630x, child number 0
-------------------------------------
select /*+ use_nl(t2,t3@bl) */ distinct object_name   from t2  where
substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')    and
object_name is not null    and object_type is null    and object_name
not in        (select /*+ qb_name(bl) */          object_name from t3
       where object_name is not null)    and regexp_like(object_name,
'[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

方式2：使用hint：nl_aj + qb_name方式 

select /*+ nl_aj(@bl) */
distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ qb_name(bl) */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

方式3： nl_aj ，在子查询中加入hint，可以不用指定查询块 

select distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ nl_aj */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')


SQL_ID  7hb1ksq4s1v6p, child number 0
-------------------------------------
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null
and object_type is null    and object_name not in        (select /*+
nl_aj */          object_name           from t3          where
object_name is not null)    and regexp_like(object_name,
'[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

生产系统SQL低效索引案例

发表于由

前不久，无意中看到生产中一则TOP SQL，消耗资源较多，感觉有很大优化空间

SQL_ID  1agm6bmnhh609, child number 24
--------------------------------------
select *
  from UMETRIP_EVENTLOG
where FLIGHTDATE = :1 
   and EVENTTYPE = 'GATE_CHG' 
   and PASSREASON = '-91000

TIME         EXECU_D      BUFFER        DISK      ET_D     ROWS_PRO                                        
------------ ------- ----------- ----------- --------- ------------                                        
2020052004         2      229527       75055       205         4201                                        
2020052101         2      288633       67290       166         4061                                        
2020052202         2      226528       74570       181         4224                                        
2020052203         2      205266       50359       134         4421                                        
2020052303         2      206985       67949       156         4638                                        
2020052306         1      152840       76679       196         1723                                        
2020052601         2      213294       47103       125         3441                                        
2020052603         2      209591        9441        36         3765                                        
2020052810         2      248329       30198        91         4765                                        
2020052813         2      271982       46553       123         4849

这条SQL执行次数并不多，却在AWR中进入了TOP 10 SQL，执行1-2次消耗了200000+的逻辑读，表记录数25042676行，NDV如下：

COLUMN_NAME                       NDV NUL  NUM_NULLS     DATA_TYPE      LAST_ANALYZED        HISTOGRAM
------------------------------ ---------- --- ---------- ------------ -------------------- ---------
CITYCODE                             2379 Y         6073 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
DESTA                                2365 Y          849 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
EVENTTYPE                              59 Y            0 VARCHAR2     2020-05-27 22:09:30  FREQUENCY
EXTRAINFO                         2189824 Y     14600645 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
FLIGHTDATE                           1891 Y          723 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
FLIGHTDYNFIELD                         45 Y         3651 VARCHAR2     2020-05-27 22:09:30  NONE
FLIGHTNO                            58972 Y            0 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
ID                               25042676 Y            0 NUMBER       2020-05-27 22:09:30  HEIGHT BA
INFOAFTER                          713856 Y          336 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
INFOBEFOR                          243808 Y     21175542 VARCHAR2     2020-05-27 22:09:30  NONE
INSERTTIME                         626176 Y            0 DATE         2020-05-27 22:09:30  HEIGHT BA
ISUSED                                  2 Y          165 NUMBER       2020-05-27 22:09:30  FREQUENCY
PASSREASON                             91 Y      3295111 NUMBER       2020-05-27 22:09:30  FREQUENCY


Plan hash value: 3303868666
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |       |       | 22722 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| UMETRIP_EVENTLOG      |    72 |  7776 | 22722   (1)| 00:04:33 |
|*  2 |   INDEX RANGE SCAN          | UMETRIP_EVENTLOG_IDX2 | 17964 |       |  5724   (1)| 00:01:09 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("PASSREASON"=(-91000))
   2 - access("EVENTTYPE"='GATE_CHG' AND "FLIGHTDATE"=:1)
       filter("FLIGHTDATE"=:1)

目前SQL执行计划走了UMETRIP_EVENTLOG_IDX2的索引，EVENTTYPE/ID/CITYCODE/FLIGHTDATE，起到过滤作用的只有EVENTTYPE，而FLIGHTDATE只能起到过滤作用。

继续阅读 →

生产数据库查询与插入hang案例

发表于由

前不久，生产Oracle数据库上出现一个比较紧急的案例，前台业务查询与插入操作全部夯死，涉及这些SQL的业务全部中断。接到电话后立即查看系统状态

此时系统中出现了latch: row cache objects ，library cache lock这些本不该在生产系统中出现的事件：
SQL> select event,count(*) from v$session where wait_class <> 'Idle' group by event order by 2 desc;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
library cache lock                                                        2
SQL*Net message to client                                                 2
log file sync                                                             1
latch: row cache objects                                                  1



这些等待的Blocking_session均为空
SQL> select username,sid,serial#,sql_id,blocking_session,event from  v$session where wait_class <> 'Idle';

USERNAME                              SID    SERIAL# SQL_ID        BLOCKING_SESSION EVENT
------------------------------ ---------- ---------- ------------- ---------------- ----------------------------------------------------------------
Albert                               1015       3251 fdn44h4am80mb                  gc cr multi block request
Albert                               1184      54311 6sx33q2nq3c67                  library cache lock
Albert                               2738       4293 3ndnsw62wz2f1                  latch: row cache objects
Albert                               2957      17039                                reliable message
Albert                               3414      13501 7jj34snwajctj                  library cache lock
SYS                                  4192      29879 dh8qnqqr8jqyt                  SQL*Net message to client

此时需要找到Library cache lock等事件的阻塞源头，尽快恢复中断业务，可以利用如下代码进行确认追踪

SQL> select /*+ ordered */
  2  w1.sid waiting_session,
  3  h1.sid holding_session,
  4  w.kgllktype lock_or_pin,
  5  w.kgllkhdl address,
  6  decode(h.kgllkmod,
  7          0,
  8          'None',
  9          1,
 10          'Null',
 11          2,
 12          'Share',
 13          3,
 14          'Exclusive',
 15          'Unknown') mode_held,
 16  decode(w.kgllkreq,
 17          0,
 18          'None',
 19          1,
 20          'Null',
 21          2,
 22          'Share',
 23          3,
 24          'Exclusive',
 25          'Unknown') mode_requested
 26    from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 27  where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and
 28         ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and
 29         (((w.kgllkmod = 0) or (w.kgllkmod = 1)) and
 30         ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
 31     and w.kgllktype = h.kgllktype
 32     and w.kgllkhdl = h.kgllkhdl
 33     and w.kgllkuse = w1.saddr
 34     and h.kgllkuse = h1.saddr;

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS          MODE_HELD MODE_REQU
--------------- --------------- ---- ---------------- --------- ---------
           3414            2738 Lock 0000001FDF10B328 Share     Exclusive
           1184            2738 Lock 0000001FDF10B328 Share     Share

可以从上述结果中看到，2738会话持有Hanle address “0000001FDF10B328”的独占锁：Exclusive，所以应急杀掉2738会话，KILL SESSION会话后，故障恢复。

SQL> r
  1* select username,sid,serial#,sql_id,blocking_session,event from  v$session where wait_class <> 'Idle'

USERNAME                              SID    SERIAL# SQL_ID        BLOCKING_SESSION EVENT
------------------------------ ---------- ---------- ------------- ---------------- ----------------------------------------------------------------
Albert                                   1015       3251 35gvvuy7b2p2s                  db file scattered read
Albert                                   1184      54311 6sx33q2nq3c67                  SQL*Net more data from client
Albert                                   2957      17041 6sx33q2nq3c67                  SQL*Net more data from client
Albert                                 4192      29961 dh8qnqqr8jqyt                  SQL*Net message to client

事后根因分析，为何在业务高峰期间会有library cache lock长时间占有不释放的情况？

确认ASH时间点后，VTERM\GS02是受害者，基本大部分会话都是被2738这个会话阻塞
SQL> select --to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  2   event,
  3   sql_id,
  4   program,
  5   machine,
  6   sql_opname,
  7   BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  8   count(*)
  9    from v$active_session_history a
 10   where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 11         '20200501 00:00:00' and '20200501 10:44:00'
 12     and event = 'library cache lock'
 13   group by --to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 14            event,
 15            sql_id,
 16            program,
 17            machine,
 18            BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 19            sql_opname
 20   order by 7
 21  ;

EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
library cache lock                                               0uujs8nqcm145 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                         7  
library cache lock                                               49qykykfhwqtm plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        22
library cache lock                                               973s831gy6jk4 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        38
library cache lock                                                             sqlldr@hostname (TNS V1-V3)                      hostname                                                                                                                          _                                                                                        58
library cache lock                                               g639trfsrp9m5 plsqldev.exe                                     VTERM\Travelskydba01                                             PL/SQL EXECUTE                                                   _                                                                                        62
library cache lock                                               3y3gt31q23uk1 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        65
library cache lock                                               6bwmj9n38kjp6 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                       494
library cache lock                                               973s831gy6jk4 plsqldev.exe                                     VTERM\Travelskydba01                                             SELECT                                                           _                                                                                      1475
library cache lock                                               7jj34snwajctj detrreceipt@hostname2 (TNS V1-V3)                hostname2                                                        INSERT                                                           1_2738                                                                                 2419
library cache lock                                               6sx33q2nq3c67 sqlldr@hostname (TNS V1-V3)                      hostname                                                         INSERT                                                           _                                                                                      2419
library cache lock                                               7jj34snwajctj detrreceipt@hostname2 (TNS V1-V3)                hostname2                                                        INSERT                                                           _                                                                                      2515
library cache lock                                               6sx33q2nq3c67 sqlldr@hostname (TNS V1-V3)                      hostname                                                         INSERT                                                           1_2738                                                                                10760

12 rows selected

ASH中后续并没有记录2738这个会话再等latch: row cache objects，但是抓到了其他的进程信息，例如PROGRAM/MACHINE，猜测故障当时此会话已经不是ACTIVE

ASH中并没有记录2738这个会话再等latch: row cache objects
SQL> select --to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  2   event,
  3   sql_id,
  4   program,
  5   machine,
  6   sql_opname,
  7   BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  8   count(*)
  9    from ash_20200501 a
 10   where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 11         '20200430 00:00:00' and '20200501 10:44:00'
 12     and session_id=2738 and program ='plsqldev.exe'
 13   group by --to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 14            event,
 15            sql_id,
 16            program,
 17            machine,
 18            BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 19            sql_opname
 20   order by 1
 21  ;

EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
                                                                 3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                    SELECT                                                           _                                                                            58210
                                                                 3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                                                                                     _                                                                             6

SQL>

2738这个会话最早在4月30日18点20开始出现，一直持续到故障发生时（故障发生时间：20200501 10:00左右）

738这个会话最早在4月30日18点20开始出现，一直持续到故障发生时：
SQL> select rownum, a.*
  2    from (select to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  3                 event,
  4                 sql_id,
  5                 program,
  6                 machine,
  7                 sql_opname,
  8                 BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  9                 count(*)
 10            from ash_20200501 a
 11           where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 12                 '20200430 00:00:00' and '20200501 10:44:00'
 13             and session_id = 2738
 14             and program = 'plsqldev.exe'
 15           group by to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 16                    event,
 17                    sql_id,
 18                    program,
 19                    machine,
 20                    BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 21                    sql_opname
 22           order by 1) a where rownum <2
 23  ;

    ROWNUM SAMPLE_TIME       EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------- ----------------- ---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
         1 20200430 18:20:47                                                                  3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                                                                                       _                                                                                         1
继续阅读 →

RAC集群节点关闭长时间Waiting for ASM to shutdown处理过程与案例分析

发表于由

5月1日晚，接到值班同事求助电话，RAC集群重启节点后出现监听无法注册SERVICE的情况，应用连接在数据库实例重启后无法在维护节点上连接。

放下电话登录到系统中查看CRS状态出现如下错误

CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors。

CRS此时已经处于非正常状态，listener/service都是以资源的方式注册在CRSD中，所以出现SERVICE的异常现象是符合情理的。随后我准备重启CRS的时候，在stop的过程中却出现了长时间hang的情况，等待Waiting for ASM to shutdown。

[root@travelskydba-rac grid]# cd bin
[root@travelskydba-rac bin]# ./crsctl stat res tt
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
[root@travelskydba-rac bin]# ./crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
[root@travelskydba-rac bin]# ./crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.crsd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.crsd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.crf' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.ctssd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.evmd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.asm' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.mdnsd' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.crf' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.evmd' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'travelskydba-rac' succeeded

CRS-2675: Stop of 'ora.asm' on 'travelskydba-rac' failed  ---卡在这里很长时间，大概有8分钟左右
CRS-2679: Attempting to clean 'ora.asm' on 'travelskydba-rac'
CRS-2681: Clean of 'ora.asm' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.cssd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.gipcd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.drivers.acfs' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'travelskydba-rac' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'travelskydba-rac' has completed
CRS-4133: Oracle High Availability Services has been stopped.

2020-05-01 22:37:28.633:
[ctssd(2029)]CRS-2405:The Cluster Time Synchronization Service on host travelskydba-rac is shutdown by user
[client(89699)]CRS-10001:01-May-20 22:37 ACFS-9290: Waiting for ASM to shutdown
.... --省略重复输出
[client(124967)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125047)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125174)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125491)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
[client(125548)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
[client(125614)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
2020-05-01 22:47:28.727:
[/opt/app/11.2.0/grid/bin/oraagent.bin(759)]CRS-5818:Aborted command 'stop' for resource 'ora.asm'. Details at (:CRSAGF00113:) {0:0:52684} in /opt/app/11.2.0/grid/log/travelskydba-rac/agent/ohasd/oraagent_grid//oraagent_grid.log.
2020-05-01 22:47:30.730:
[ohasd(196541)]CRS-2757:Command 'Stop' timed out waiting for response from the resource 'ora.asm'. Details at (:CRSPE00111:) {0:0:52684} in /opt/app/11.2.0/grid/log/travelskydba-rac/ohasd/ohasd.log.
2020-05-01 22:47:33.404:
[cssd(872)]CRS-1603:CSSD on node travelskydba-rac shutdown by user.
2020-05-01 22:47:33.513:
[ohasd(196541)]CRS-2767:Resource state recovery not attempted for 'ora.cssdmonitor' as its target state is OFFLINE
2020-05-01 22:47:33.609:
[cssd(872)]CRS-1660:The CSS daemon shutdown has completed
2020-05-01 22:47:35.756:
[gpnpd(790)]CRS-2329:GPNPD on node travelskydba-rac shutdown.

DB alert日志：
NOTE: ASMB terminating
Errors in file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_asmb_188518.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 1828 Serial number: 22601
Errors in file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_asmb_188518.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 1828 Serial number: 22601
ASMB (ospid: 188518): terminating the instance due to error 15064
Fri May 01 22:47:30 2020
System state dump requested by (instance=1, osid=188518 (ASMB)), summary=[abnormal instance termination].
System State dumped to trace file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_diag_188401_20200501224730.trc
Dumping diagnostic data in directory=[cdmp_20200501224730], requested by (instance=1, osid=188518 (ASMB)), summary=[abnormal instance termination].
Instance terminated by ASMB, pid = 188518

节点2：集群日志：
2020-05-01 22:47:33.505:
[cssd(133163)]CRS-1625:Node travelskydba-rac, number 1, was shut down
2020-05-01 22:47:33.517:
[cssd(133163)]CRS-1601:CSSD Reconfiguration complete. Active nodes are travelskydba2-rac travelskydba3-rac .


节点3集群日志：
2020-05-01 22:47:33.505:
[cssd(141351)]CRS-1625:Node travelskydba-rac, number 1, was shut down
2020-05-01 22:47:33.517:
[cssd(141351)]CRS-1601:CSSD Reconfiguration complete. Active nodes are tr730e67-rac travelskydba3-rac .

也就是说节点1集群完全停下来，耗时22:37-22:47 10分钟，hang期间，让值班员联系硬件重启服务器,但是在硬件重启操作前，CRS却停下来了。

保险起见，还是让硬件重启一下服务器（因21点左右，硬件团队同事刚刚对此节点集群的私网心跳线进行更换）。

继续阅读 →

闪回系列专题（2）基于UNDO技术的闪回特性

发表于由

基于依赖UNDO技术的FLASHBACK特性有四种，分别是：

1.FLASHBACK TABLE (恢复行级数据)
2.FLASHBACK QUERY
3.FLASHBACK VERSION QUERY
4.FLASHBACK TRANSACTION QUERY

（1）FLASHBACK TABLE 

--引用自Oracle官方文档
Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table. 

You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause. 

By default, Oracle Database disables all enabled triggers defined on table during the Flashback Table operation and then reenables them after the Flashback Table operation is complete. Specify ENABLE TRIGGERS if you want to override this default behavior and keep the triggers enabled during the Flashback process.

This clause affects only those database triggers defined on table that are already enabled. To enable currently disabled triggers selectively, use the ALTER TABLE ... enable_disable_clause before you issue the FLASHBACK TABLE statement with the ENABLE TRIGGERS clause. 
--创建测试表：
SQL> conn albert/albert
Connected.
SQL>  create table Liangce as select * from dba_objects;

Table created.

SQL> select object_type,count(*) from liangce group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
EDITION                      1
INDEX PARTITION            370
TABLE SUBPARTITION          32
CONSUMER GROUP              25
SEQUENCE                   182
TABLE PARTITION            358
SCHEDULE                     3
QUEUE                       24
RULE                         1
JAVA DATA                  314
PROCEDURE                  139
OPERATOR                    23
LOB PARTITION                5
DESTINATION                  2
WINDOW                       9
SCHEDULER GROUP              4
DATABASE LINK                1
LOB                        238
PACKAGE                    785
PACKAGE BODY               748
LIBRARY                    149
PROGRAM                     19
RULE SET                    15
CONTEXT                      7
TYPE BODY                  126
JAVA RESOURCE              864
TRIGGER                     69
JOB CLASS                   13
UNDEFINED                   11
DIRECTORY                    5
TABLE                     1600
INDEX                     1978
SYNONYM                  30947
VIEW                      4531
FUNCTION                   131
JAVA CLASS               27024
JAVA SOURCE                  2
INDEXTYPE                    5
CLUSTER                     10
TYPE                      1448
RESOURCE PLAN               10
JOB                         11
EVALUATION CONTEXT          11

43 rows selected.

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,
-----------------
20200425 13:22:02

开始“误操作”行为：

SQL> update liangce set owner='Travelskydba' WHERE OWNER <> 'SYS';

33643 rows updated.

SQL> commit;

Commit complete.

SQL> select owner,count(*) from liangce group by owner order by 2 desc;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                 38607
Travelskydba                        33643

-----commit后发现为误操作行为-----

执行恢复操作：

SQL> alter table liangce enable row movement;

Table altered.

SQL> flashback table liangce to timestamp to_timestamp('20200425 13:22:02','yyyymmdd hh24:mi:ss'); 

--也可支持to_scn

Flashback complete.

SQL>  select owner,count(*) from liangce group by owner order by 2 desc;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                 38607
PUBLIC                              30932
OLAPSYS                               721
SYSTEM                                622
CTXSYS                                389
WMSYS                                 333
RMAN                                  252
EXFSYS                                192
OTSC                                   77
DBSNMP                                 57
APPUSER                                17
B2C3U_XREZAGENT                        10
OUTLN                                  10
ORACLE_OCM                              8
LCA                                     6
APPQOSSYS                               5
HJ                                      4
DEMO                                    3
TRAVELSKYDBA                            2
LIANGCE                                 2
ALBERT                                  1

21 rows selected.

（2）FLASHBACK QUERY

--引用Oracle 官方文档--
To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Oracle Flashback Query include:

    Recovering lost data or undoing incorrect, committed changes.

    For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.

    Comparing current data with the corresponding data at an earlier time.

    For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.

    Checking the state of transactional data at a particular time.

    For example, you can verify the account balance of a certain day.

    Selecting data that was valid at a particular time or at any time within a user-defined valid time period.

    For example, you can find employees with valid employee information as of a particular timestamp or between a specified start and end time in the specified valid time period. (For more information, see Temporal Validity Support.)

    Simplifying application design by removing the need to store some kinds of temporal data.

    Oracle Flashback Query lets you retrieve past data directly from the database.

    Applying packaged applications, such as report generation tools, to past data.

    Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

创建测试表，借助闪回查询功能进行数据恢复：

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> show user
USER is "ALBERT"
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,
-----------------
20200425 14:06:00

SQL> delete from t1;

72252 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
         0

SQL> select count(*) from t1 as of timestamp to_timestamp('20200425 14:06:00','yyyymmdd hh24:mi:ss');

  COUNT(*)
----------
     72252

SQL> insert into t1 select * from t1 as of timestamp to_timestamp('20200425 14:06:00','yyyymmdd hh24:mi:ss');

72252 rows created.

SQL> commit;

Commit complete.

（3）FLASHBACK VERSION QUERY

--引用自Oracle官方文档--
Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.

创建测试表并开始闪回操作：

SQL> select *from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
ALBERT                                777
TRAVELSKYDBA                           20
SYS                                    46
SYS                                    28
SYS                                    29
SYS                                     3
SYS                                    25
SYS                                    41
SYS                                    54

9 rows selected.

SQL> 
SQL> 
SQL> delete from t2 where owner='ALBERT';  --事务1 版本1

1 row deleted.

SQL> delete from t2 where owner='TRAVELSKYDBA';  --事务1 版本1

1 row deleted.

SQL> update t2 set owner='TRAVELSKYDBA'; --事务2 版本2

7 rows updated.

SQL> commit;

Commit complete.

SQL> insert into t2 values('ALBERT',77); --事务3 版本3

1 row created.

SQL> commit;

Commit complete.

一张表经历了多次事务，我们可以利用闪回查询进行不同事务版本中的闪回切换：


SSQL> SELECT owner,
  2         object_id,
  3         versions_startscn,
  4         to_char(versions_starttime, 'yyyymmdd hh24:mi:ss') versions_starttime,
  5         versions_endscn,
  6         to_char(versions_endtime, 'yyyymmdd hh24:mi:ss') versions_endtime,
  7         versions_xid,
  8         versions_operation
  9    FROM t2 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('20200425 15:54:00', 'yyyymmdd hh24:mi:ss') AND TO_TIMESTAMP('20200425 15:58:40', 'yyyymmdd hh24:mi:ss')
 10   order by 3;

OWNER                           OBJECT_ID VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME  VERSIONS_XID     VERSIONS_OPERATION
------------------------------ ---------- ----------------- ------------------ --------------- ----------------- ---------------- ------------------
TRAVELSKYDBA                           20         108825318 20200425 15:54:49                                    0A001700E59E1900 D  --事务1 版本1：delete from t2 where owner='TRAVELSKYDBA'; 
ALBERT                                777         108825318 20200425 15:54:49                                    0A001700E59E1900 D  --事务1 版本1：delete from t2 where owner='ALBERT'; 
TRAVELSKYDBA                            3         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2：update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           29         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2：update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           28         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2：update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           46         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2：update t2 set owner='TRAVELSKYDBA';
TRAVELSKYDBA                           25         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2：update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           41         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2：update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           54         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2：update t2 set owner='TRAVELSKYDBA'; 
ALBERT                                 77         108825465 20200425 15:56:56                                    0A000800649D1900 I  --事务3 版本3：insert into t2 values('ALBERT',77);
TRAVELSKYDBA                           20                                            108825318 20200425 15:54:49                  
SYS                                    46                                            108825426 20200425 15:56:16                  
SYS                                    54                                            108825426 20200425 15:56:16                  
SYS                                    29                                            108825426 20200425 15:56:16                  
SYS                                     3                                            108825426 20200425 15:56:16                  
SYS                                    25                                            108825426 20200425 15:56:16                  
SYS                                    41                                            108825426 20200425 15:56:16                  
ALBERT                                777                                            108825318 20200425 15:54:49                  
SYS                                    28                                            108825426 20200425 15:56:16                  

19 rows selected
 
 
--current t2 table状态：

SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                           46
TRAVELSKYDBA                           28
TRAVELSKYDBA                           29
TRAVELSKYDBA                            3
TRAVELSKYDBA                           25
TRAVELSKYDBA                           41
TRAVELSKYDBA                           54
ALBERT                                 77

8 rows selected.
 
恢复T2表至最初状态（未发生过事务）

SQL> alter table t2 enable row movement;

Table altered.

SQL> flashback table t2 to scn 108825317; VERSIONS_ENDSCN为18

Flashback complete.


SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
ALBERT                                777
TRAVELSKYDBA                           20
SYS                                    46
SYS                                    28
SYS                                    29
SYS                                     3
SYS                                    25
SYS                                    41
SYS                                    54

9 rows selected.

恢复至T2表至INSERT之前状态：

SQL>  flashback table t2  to scn 108825426;

Flashback complete.

SQL>  select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                           46
TRAVELSKYDBA                           28
TRAVELSKYDBA                           29
TRAVELSKYDBA                            3
TRAVELSKYDBA                           25
TRAVELSKYDBA                           41
TRAVELSKYDBA                           54

7 rows selected.


SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                           46
TRAVELSKYDBA                           28
TRAVELSKYDBA                           29
TRAVELSKYDBA                            3
TRAVELSKYDBA                           25
TRAVELSKYDBA                           41
TRAVELSKYDBA                           54
ALBERT                                 77

8 rows selected.


SQL> flashback table t2 to scn 108825465;

Flashback complete.

SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                           46
TRAVELSKYDBA                           28
TRAVELSKYDBA                           29
TRAVELSKYDBA                            3
TRAVELSKYDBA                           25
TRAVELSKYDBA                           41
TRAVELSKYDBA                           54
ALBERT                                 77

8 rows selected.

（4）Flashback Transaction Query

--引用自Oracle官方文档---
Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY, whose columns are described in Oracle Database Reference.

The column UNDO_SQL shows the SQL code that is the logical opposite of the DML operation performed by the transaction. You can usually use this code to reverse the logical steps taken during the transaction. However, there are cases where the UNDO_SQL code is not the exact opposite of the original transaction. For example, a UNDO_SQL INSERT operation might not insert a row back in a table at the same ROWID from which it was deleted.

闪回事务查询功能中，除了数据恢复之外，还有一项重要的功能是，提供给我们是谁修改了某个表的某行数据，例如，我们发现ALBERT中的T2表被进行了修改，但是不知道是谁修改了，可以利用如下查询进行操作：

SQL> conn p_liangce/p_liangce;
Connected.
SQL> 
SQL> 
SQL> select * from albert.t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
ALBERT                                666

8 rows selected.

SQL> update albert.t2 set object_id=666666 where owner='ALBERT'

1 rows updated.

SQL> commit;

Commit complete.

LIANGCE这个用户对ALBERT 用户下表T2某行进行了UPDATE操作，我们可以用如下方式进行追踪与数据修改

此时ALBERT用户向DBA数据被修改，DBA可以利用闪回事务查询中的LOGON_USER进行修改
SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
ALBERT                             666666  --原来应为666

8 rows selected.

SQL> 
SQL> select logon_user,
  2         start_timestamp,
  3         commit_timestamp,
  4         operation,
  5         table_name,
  6         table_owner,
  7         row_id,
  8         undo_sql
  9    from flashback_transaction_query
 10   where table_name = 'T2'
 11     AND table_OWNER = 'ALBERT'
 12     and start_timestamp between
 13         to_timestamp('20200425 16:00:00', 'yyyymmdd hh24:mi:ss') and
 14         to_timestamp('20200425 16:30:00', 'yyyymmdd hh24:mi:ss')
 15  ;

LOGON_USER                     START_TIMESTAMP COMMIT_TIMESTAMP OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
------------------------------ --------------- ---------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAA  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAA';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAB  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAB';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAJ  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAJ';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAK  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAK';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAL  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAL';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAM  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAM';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAN  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAN';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAO  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAO';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAI  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','54');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAH  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','41');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAG  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','25');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAF  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','3');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAE  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','29');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAD  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','28');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAC  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','46');
P_LIANGCE                      2020/4/25 16:27 2020/4/25 16:28: UPDATE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAO  update "ALBERT"."T2" set "OBJECT_ID" = '666' where ROWID = 'AAAkGVAANAAD1AFAAO';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAC  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAC';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAD  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAD';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAE  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAE';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAF  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAF';

LOGON_USER                     START_TIMESTAMP COMMIT_TIMESTAMP OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
------------------------------ --------------- ---------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAG  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAG';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAH  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAH';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAI  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAI';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAQ  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','54');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAP  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','41');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAO  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','25');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAN  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','3');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAM  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','29');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAL  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','28');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAK  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','46');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAB  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','20');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAA  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('ALBERT','777');

-数据恢复：
SQL> update "ALBERT"."T2" set "OBJECT_ID" = '666' where ROWID = 'AAAkGVAANAAD1AFAAO'; --执行UNDO SQL前请谨慎确认是否执行的是误操作之后的反向操作。

1 row updated.

SQL> 
SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> select * from albert.t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
ALBERT                                666

8 rows selected.