SQL Tuning-With as

最新推荐文章于 2022-10-29 11:11:56 发布
最新推荐文章于 2022-10-29 11:11:56 发布
阅读量605 收藏
点赞数 1
分类专栏： Oracle Sql Tuning 文章标签： sql date access filter jdbc loops
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Coast_lichao/article/details/7915087
版权

报表库在上线完后的第二天，两个节点CPU持续95%，经查看又是latch:cache buffers chain.
看看ASH信息:

INST_ID	PROGRAM	        SQL_ID	      EVENT	                      COUNT(*)	PERCENT
2	     JDBC Thin Client	cukkhnzrh2y28	latch: cache buffers chains	  66959	  19%
2	     JDBC Thin Client	gg7hj1tgv56ur	latch: cache buffers chains	  47383	  13%
1	     JDBC Thin Client	7nuzsbsjzzv81	latch: cache buffers chains	  34413	  10%
1	     JDBC Thin Client	f557q4b4y9vr7	latch: cache buffers chains	  37986	  10%
1	     JDBC Thin Client	djk4dr940v1y4	latch: cache buffers chains	  33866	  9%
2	     JDBC Thin Client	6pc72a9dg2gp6	latch: cache buffers chains	  31801	  9%
1	     JDBC Thin Client	8zdz3kr2pts6c	latch: cache buffers chains	  27348	  8%
2	     JDBC Thin Client	7zfqtg5zju251	latch: cache buffers chains	  23753	  7%
2	     JDBC Thin Client	43urhu1n8rf3p	latch: cache buffers chains	  12605	  3%
2	     JDBC Thin Client	afmyfqmqx1bxx	latch: cache buffers chains	  9900	  3%
2	     JDBC Thin Client	5aq4fsqnb85mq	latch: cache buffers chains	  11478	  3%
2	     JDBC Thin Client	44a7wsxkpzh5p	latch: cache buffers chains	  5637	  2%
1	     JDBC Thin Client	8swu3mguwaahv	latch: cache buffers chains	  2238	  1%
2	     JDBC Thin Client	fx985p8s4tk7v	latch: cache buffers chains	  4806	  1%
2	     JDBC Thin Client	4s8wdxq63jwz0	latch: cache buffers chains	  1836	  1%
2	     JDBC Thin Client	92bkxcjc36k11	latch: cache buffers chains	  2577	  1%

查看第一个SQL:cukkhnzrh2y28                                                                
SELECT :"SYS_B_00" type,
       :"SYS_B_01" pay_qty,
       :"SYS_B_02" price,
       SUM(T1.SHOULD_MONEY_C) / :"SYS_B_03" pay_fee,
       :"SYS_B_04" drt_qty,
       -SUM(T1.DERATE_MONEY_C) / :"SYS_B_05" drt_fee,
       :"SYS_B_06" total_get_qty,
       SUM(T1.FACT_MONEY_C) / :"SYS_B_07" get_fee
  FROM (
        SELECT T.CUSTOMER_ORDER_ID,
               MAX(DECODE(BUSI_FEE_ID,:"SYS_B_08",FACT_MONEY2)) FACT_MONEY_C,
               MAX(DECODE(BUSI_FEE_ID,:"SYS_B_09",SHOULD_MONEY2)) SHOULD_MONEY_C,
               MAX(DECODE(BUSI_FEE_ID,:"SYS_B_10",DERATE_MONEY2)) DERATE_MONEY_C
          FROM (
                SELECT A.*,
                       B.BUSI_FEE_ID,
                       B.FACT_MONEY FACT_MONEY2,
                       B.SHOULD_MONEY SHOULD_MONEY2,
                       B.DERATE_MONEY DERATE_MONEY2
                  FROM RP_PBOSS_DETAIL_577 A,
                       RP_FEE_DETAIL_577 B,
                       (SELECT CUSTOMER_ORDER_ID,MAX(AA.RECEIVE_TYPE) RECEIVE_TYPE FROM RP_PBOSS_DETAIL_577 AA GROUP BY CUSTOMER_ORDER_ID) C
                 WHERE A.OFFER_ORDER_ID = B.OFFER_ORDER_ID(+)
                   AND A.DONE_DATE = B.DONE_DATE(+)
                   AND A.CUSTOMER_ORDER_ID = C.CUSTOMER_ORDER_ID
                   AND A.DONE_DATE between to_date(:"SYS_B_11", :"SYS_B_12") and to_date(:"SYS_B_13", :"SYS_B_14") + :"SYS_B_15"
                   AND A.OP_ID IN (:"SYS_B_16")
                   AND C.RECEIVE_TYPE = :"SYS_B_17"
                UNION ALL
                SELECT A.*,
                       B.BUSI_FEE_ID,
                       B.FACT_MONEY FACT_MONEY2,
                       B.SHOULD_MONEY SHOULD_MONEY2,
                       B.DERATE_MONEY DERATE_MONEY2
                  FROM RP_PBOSS_DETAIL_577 A,
                       RP_FEE_DETAIL_577 B,
                       (SELECT CUSTOMER_ORDER_ID,MAX(AA.RECEIVE_TYPE) RECEIVE_TYPE FROM RP_PBOSS_DETAIL_577 AA GROUP BY CUSTOMER_ORDER_ID) C
                 WHERE A.OFFER_ORDER_ID = B.OFFER_ORDER_ID(+)
                   AND A.DONE_DATE = B.DONE_DATE(+)
                   AND A.CUSTOMER_ORDER_ID = C.CUSTOMER_ORDER_ID
                   AND A.COMPLETE_DATE between to_date(:"SYS_B_18", :"SYS_B_19") and to_date(:"SYS_B_20", :"SYS_B_21") + :"SYS_B_22"
                   AND A.OP_ID IN (:"SYS_B_23")
                   AND C.RECEIVE_TYPE = :"SYS_B_24"
                   AND A.ORDER_STATE = :"SYS_B_25"
                UNION ALL
                SELECT A.*,
                       B.BUSI_FEE_ID,
                       (:"SYS_B_26" - B.FACT_MONEY) FACT_MONEY2,
                       (:"SYS_B_27" - B.SHOULD_MONEY) SHOULD_MONEY2,
                       (:"SYS_B_28" - B.DERATE_MONEY) DERATE_MONEY2
                  FROM RP_PBOSS_DETAIL_577 A,
                       RP_FEE_DETAIL_577 B,
                       RP_CANCEL_DETAIL_577 C,
                       (SELECT CUSTOMER_ORDER_ID,MAX(AA.RECEIVE_TYPE) RECEIVE_TYPE FROM RP_PBOSS_DETAIL_577 AA GROUP BY CUSTOMER_ORDER_ID) D
                 WHERE A.OFFER_ORDER_ID = B.OFFER_ORDER_ID(+)
                   AND A.DONE_DATE = B.DONE_DATE(+)
                   AND A.CUSTOMER_ORDER_ID = C.CUSTOMER_ORDER_ID
                   AND A.CUSTOMER_ORDER_ID = D.CUSTOMER_ORDER_ID
                   AND C.CANCEL_DATE between to_date(:"SYS_B_29", :"SYS_B_30") and to_date(:"SYS_B_31", :"SYS_B_32") + :"SYS_B_33"
                   AND C.OP_ID IN (:"SYS_B_34")
                   AND D.RECEIVE_TYPE = :"SYS_B_35"
                   AND A.ORDER_STATE = :"SYS_B_36"
                   ) T
         WHERE T.PROD_CATALOG_ID = :"SYS_B_37"
           AND T.ORG_ID in (:"SYS_B_38")
           AND NOT (T.BUSINESS_ID = ANY(:"SYS_B_39", :"SYS_B_40") AND T.OFFER_TYPE = :"SYS_B_41" AND T.EXPIRE_DATE < TO_DATE(:"SYS_B_42", :"SYS_B_43"))
           AND T.BUSINESS_ID NOT IN (:"SYS_B_44", :"SYS_B_45", :"SYS_B_46",:"SYS_B_47", :"SYS_B_48")
         GROUP BY T.CUSTOMER_ORDER_ID) T1

Plan hash value: 1930065266

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                   | Rows| Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                        |     |       | 19151 (100)|          |
|   1 |  SORT AGGREGATE                              |                        |   1 |    39 |            |          |
|   2 |   VIEW                                       |                        |   3 |   117 | 19151   (2)| 00:03:50 |
|   3 |    SORT GROUP BY                             |                        |   3 |   195 | 19151   (2)| 00:03:50 |
|   4 |     VIEW                                     |                        |   3 |   195 | 19150   (2)| 00:03:50 |
|   5 |      UNION-ALL                               |                        |     |       |            |          |
|   6 |       FILTER                                 |                        |     |       |            |          |
|   7 |        SORT GROUP BY                         |                        |   1 |   512 |  5724   (2)| 00:01:09 |
|   8 |         FILTER                               |                        |     |       |            |          |
|   9 |          HASH JOIN                           |                        |   1 |   512 |  5723   (2)| 00:01:09 |
|  10 |           NESTED LOOPS OUTER                 |                        |   1 |   501 |    16  (13)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID       | RP_PBOSS_DETAIL_579    |   1 |   455 |    14   (8)| 00:00:01 |
|  12 |             BITMAP CONVERSION TO ROWIDS      |                        |     |       |            |          |
|  13 |              BITMAP AND                      |                        |     |       |            |          |
|  14 |               BITMAP CONVERSION FROM ROWIDS  |                        |     |       |            |          |
|  15 |                SORT ORDER BY                 |                        |     |       |            |          |
|  16 |                 INDEX RANGE SCAN             | IDX_RPPD_008_579       |1291 |       |     2   (0)| 00:00:01 |
|  17 |               BITMAP CONVERSION FROM ROWIDS  |                        |     |       |            |          |
|  18 |                INDEX RANGE SCAN              | IDX_RPPD_003_579       |1291 |       |     6   (0)| 00:00:01 |
|  19 |            PARTITION RANGE ITERATOR          |                        |   1 |    46 |     1   (0)| 00:00:01 |
|  20 |             TABLE ACCESS BY LOCAL INDEX ROWID| RP_FEE_DETAIL_579      |   1 |    46 |     1   (0)| 00:00:01 |
|  21 |              INDEX RANGE SCAN                | IDX_RP_FEE_INT_008_579 |   1 |       |     1   (0)| 00:00:01 |
|  22 |           TABLE ACCESS FULL                  | RP_PBOSS_DETAIL_579    | 479K|  5149K|  5703   (2)| 00:01:09 |
|  23 |       FILTER                                 |                        |     |       |            |          |
|  24 |        SORT GROUP BY                         |                        |   1 |   512 |  6647   (2)| 00:01:20 |
|  25 |         FILTER                               |                        |     |       |            |          |
|  26 |          NESTED LOOPS OUTER                  |                        |   1 |   512 |  6646   (2)| 00:01:20 |
|  27 |           HASH JOIN                          |                        |   1 |   466 |  6645   (2)| 00:01:20 |
|  28 |            TABLE ACCESS BY INDEX ROWID       | RP_PBOSS_DETAIL_579    |   1 |   455 |   937   (1)| 00:00:12 |
|  29 |             INDEX RANGE SCAN                 | IDX_RPPD_003_579       |4205 |       |     6   (0)| 00:00:01 |
|  30 |            TABLE ACCESS FULL                 | RP_PBOSS_DETAIL_579    | 479K|  5149K|  5703   (2)| 00:01:09 |
|  31 |           PARTITION RANGE ITERATOR           |                        |   1 |    46 |     2   (0)| 00:00:01 |
|  32 |            TABLE ACCESS BY LOCAL INDEX ROWID | RP_FEE_DETAIL_579      |   1 |    46 |     2   (0)| 00:00:01 |
|  33 |             INDEX RANGE SCAN                 | IDX_RP_FEE_INT_008_579 |   3 |       |     1   (0)| 00:00:01 |
|  34 |       FILTER                                 |                        |     |       |            |          |
|  35 |        SORT GROUP BY                         |                        |   1 |   543 |  6778   (2)| 00:01:22 |
|  36 |         FILTER                               |                        |     |       |            |          |
|  37 |          NESTED LOOPS OUTER                  |                        |   1 |   543 |  6777   (2)| 00:01:22 |
|  38 |           NESTED LOOPS                       |                        |   1 |   497 |  6776   (2)| 00:01:22 |
|  39 |            MERGE JOIN CARTESIAN              |                        |   1 |    42 |  5839   (2)| 00:01:11 |
|  40 |             TABLE ACCESS FULL                | RP_CANCEL_DETAIL_579   |   1 |    31 |   137   (3)| 00:00:02 |
|  41 |             BUFFER SORT                      |                        | 479K|  5149K|  5703   (2)| 00:01:09 |
|  42 |              TABLE ACCESS FULL               | RP_PBOSS_DETAIL_579    | 479K|  5149K|  5703   (2)| 00:01:09 |
|  43 |            TABLE ACCESS BY INDEX ROWID       | RP_PBOSS_DETAIL_579    |   1 |   455 |   937   (1)| 00:00:12 |
|  44 |             INDEX RANGE SCAN                 | IDX_RPPD_003_579       |4205 |       |     5   (0)| 00:00:01 |
|  45 |           PARTITION RANGE ITERATOR           |                        |   1 |    46 |     2   (0)| 00:00:01 |
|  46 |            TABLE ACCESS BY LOCAL INDEX ROWID | RP_FEE_DETAIL_579      |   1 |    46 |     2   (0)| 00:00:01 |
|  47 |             INDEX RANGE SCAN                 | IDX_RP_FEE_INT_008_579 |   3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

瞟一眼执行计划：瓶颈很明显，看看22,30,40.其实也看到，cache buffers chain latch保护的相应chain bucket中的对象为RP_PBOSS_DETAIL_579。所以，还是sql
的执行计划有问题，导致的大量热块度，latch争用,CPU持续超过95%。
更改sql如下：
with CC as
(SELECT/*+ materialize */CUSTOMER_ORDER_ID,MAX(AA.RECEIVE_TYPE) RECEIVE_TYPE FROM RPT.RP_PBOSS_DETAIL_579 AA GROUP BY CUSTOMER_ORDER_ID)
SELECT :"SYS_B_00" type,
       :"SYS_B_01" pay_qty,
       :"SYS_B_02" price,
       SUM(T1.SHOULD_MONEY_C) / :"SYS_B_03" pay_fee,
       :"SYS_B_04" drt_qty,
       -SUM(T1.DERATE_MONEY_C) / :"SYS_B_05" drt_fee,
       :"SYS_B_06" total_get_qty,
       SUM(T1.FACT_MONEY_C) / :"SYS_B_07" get_fee
  FROM (
        SELECT T.CUSTOMER_ORDER_ID,
               MAX(DECODE(BUSI_FEE_ID,:"SYS_B_08",FACT_MONEY2)) FACT_MONEY_C,
               MAX(DECODE(BUSI_FEE_ID,:"SYS_B_09",SHOULD_MONEY2)) SHOULD_MONEY_C,
               MAX(DECODE(BUSI_FEE_ID,:"SYS_B_10",DERATE_MONEY2)) DERATE_MONEY_C
          FROM (
                SELECT A.*,
                       B.BUSI_FEE_ID,
                       B.FACT_MONEY FACT_MONEY2,
                       B.SHOULD_MONEY SHOULD_MONEY2,
                       B.DERATE_MONEY DERATE_MONEY2
                  FROM RPT.RP_PBOSS_DETAIL_579 A,
                       RPT.RP_FEE_DETAIL_579 B,
                       CC
                 WHERE A.OFFER_ORDER_ID = B.OFFER_ORDER_ID(+)
                   AND A.DONE_DATE = B.DONE_DATE(+)
                   AND A.CUSTOMER_ORDER_ID = CC.CUSTOMER_ORDER_ID
                   AND A.DONE_DATE between to_date(:"SYS_B_11", :"SYS_B_12") and to_date(:"SYS_B_13", :"SYS_B_14") + :"SYS_B_15"
                   AND A.OP_ID IN (:"SYS_B_16")
                   AND CC.RECEIVE_TYPE = :"SYS_B_17"
                UNION ALL
                SELECT A.*,
                       B.BUSI_FEE_ID,
                       B.FACT_MONEY FACT_MONEY2,
                       B.SHOULD_MONEY SHOULD_MONEY2,
                       B.DERATE_MONEY DERATE_MONEY2
                  FROM RPT.RP_PBOSS_DETAIL_579 A,
                       RPT.RP_FEE_DETAIL_579 B,
                       CC
                 WHERE A.OFFER_ORDER_ID = B.OFFER_ORDER_ID(+)
                   AND A.DONE_DATE = B.DONE_DATE(+)
                   AND A.CUSTOMER_ORDER_ID = CC.CUSTOMER_ORDER_ID
                   AND A.COMPLETE_DATE between to_date(:"SYS_B_18", :"SYS_B_19") and to_date(:"SYS_B_20", :"SYS_B_21") + :"SYS_B_22"
                   AND A.OP_ID IN (:"SYS_B_23")
                   AND CC.RECEIVE_TYPE = :"SYS_B_24"
                   AND A.ORDER_STATE = :"SYS_B_25"
                UNION ALL
                SELECT A.*,
                       B.BUSI_FEE_ID,
                       (:"SYS_B_26" - B.FACT_MONEY) FACT_MONEY2,
                       (:"SYS_B_27" - B.SHOULD_MONEY) SHOULD_MONEY2,
                       (:"SYS_B_28" - B.DERATE_MONEY) DERATE_MONEY2
                  FROM RPT.RP_PBOSS_DETAIL_579 A,
                       RPT.RP_FEE_DETAIL_579 B,
                       RPT.RP_CANCEL_DETAIL_579 C,
                       CC
                 WHERE A.OFFER_ORDER_ID = B.OFFER_ORDER_ID(+)
                   AND A.DONE_DATE = B.DONE_DATE(+)
                   AND A.CUSTOMER_ORDER_ID = C.CUSTOMER_ORDER_ID
                   AND A.CUSTOMER_ORDER_ID = CC.CUSTOMER_ORDER_ID
                   AND C.CANCEL_DATE between to_date(:"SYS_B_29", :"SYS_B_30") and to_date(:"SYS_B_31", :"SYS_B_32") + :"SYS_B_33"
                   AND C.OP_ID IN (:"SYS_B_34")
                   AND CC.RECEIVE_TYPE = :"SYS_B_35"
                   AND A.ORDER_STATE = :"SYS_B_36"
                   ) T
         WHERE T.PROD_CATALOG_ID = :"SYS_B_37"
           AND T.ORG_ID in (:"SYS_B_38")
           AND NOT (T.BUSINESS_ID = ANY(:"SYS_B_39", :"SYS_B_40") AND T.OFFER_TYPE = :"SYS_B_41" AND T.EXPIRE_DATE < TO_DATE(:"SYS_B_42", :"SYS_B_43"))
           AND T.BUSINESS_ID NOT IN (:"SYS_B_44", :"SYS_B_45", :"SYS_B_46",:"SYS_B_47", :"SYS_B_48")
         GROUP BY T.CUSTOMER_ORDER_ID) T1;

Plan hash value: 1402984985

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        |Rows| Bytes |Cost(%CPU)|
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                             |  1 |    39 |7243   (3)|
|   1 |  TEMP TABLE TRANSFORMATION                  |                             |    |       |          |
|   2 |   LOAD AS SELECT                            |                             |    |       |          |
|   3 |    SORT GROUP BY                            |                             |201K|  2161K|7044   (2)|
|   4 |     TABLE ACCESS FULL                       | RP_PBOSS_DETAIL_579         |478K|  5144K|5702   (2)|
|   5 |   SORT AGGREGATE                            |                             |  1 |    39 |          |
|   6 |    VIEW                                     |                             |  3 |   117 | 199  (10)|
|   7 |     SORT GROUP BY                           |                             |  3 |   420 | 199  (10)|
|   8 |      VIEW                                   |                             |  3 |   420 | 198  (10)|
|   9 |       UNION-ALL                             |                             |    |       |          |
|* 10 |        FILTER                               |                             |    |       |          |
|* 11 |         HASH JOIN                           |                             |  1 |   127 |  63  (10)|
|  12 |          NESTED LOOPS OUTER                 |                             |  1 |   112 |   3   (0)|
|* 13 |           TABLE ACCESS BY INDEX ROWID       | RP_PBOSS_DETAIL_579         |  1 |    78 |   2   (0)|
|* 14 |            INDEX RANGE SCAN                 | IDX_RPPD_009_579            |  3 |       |   1   (0)|
|  15 |           PARTITION RANGE ITERATOR          |                             |  1 |    34 |   1   (0)|
|* 16 |            TABLE ACCESS BY LOCAL INDEX ROWID| RP_FEE_DETAIL_579           |  1 |    34 |   1   (0)|
|* 17 |             INDEX RANGE SCAN                | IDX_RP_FEE_INT_008_579      |  1 |       |   1   (0)|
|* 18 |          VIEW                               |                             |201K|  2947K|  58   (7)|
|  19 |           TABLE ACCESS FULL                 | SYS_TEMP_0FD9FC85A_54FF2F02 |201K|  2161K|  58   (7)|
|* 20 |        FILTER                               |                             |    |       |          |
|  21 |         NESTED LOOPS OUTER                  |                             |  1 |   136 |  71   (9)|
|* 22 |          HASH JOIN                          |                             |  1 |   102 |  70   (9)|
|* 23 |           TABLE ACCESS BY INDEX ROWID       | RP_PBOSS_DETAIL_579         |  1 |    87 |  10   (0)|
|  24 |            BITMAP CONVERSION TO ROWIDS      |                             |    |       |          |
|  25 |             BITMAP AND                      |                             |    |       |          |
|  26 |              BITMAP CONVERSION FROM ROWIDS  |                             |    |       |          |
|* 27 |               INDEX RANGE SCAN              | IDX_RPPD_004_579            |624 |       |   2   (0)|
|  28 |              BITMAP CONVERSION FROM ROWIDS  |                             |    |       |          |
|* 29 |               INDEX RANGE SCAN              | IDX_RPPD_003_579            |624 |       |   6   (0)|
|* 30 |           VIEW                              |                             |201K|  2947K|  58   (7)|
|  31 |            TABLE ACCESS FULL                | SYS_TEMP_0FD9FC85A_54FF2F02 |201K|  2161K|  58   (7)|
|  32 |          PARTITION RANGE ITERATOR           |                             |  1 |    34 |   1   (0)|
|* 33 |           TABLE ACCESS BY LOCAL INDEX ROWID | RP_FEE_DETAIL_579           |  1 |    34 |   1   (0)|
|* 34 |            INDEX RANGE SCAN                 | IDX_RP_FEE_INT_008_579      |  3 |       |   1   (0)|
|* 35 |        FILTER                               |                             |    |       |          |
|  36 |         NESTED LOOPS OUTER                  |                             |  1 |   147 |  64  (10)|
|* 37 |          HASH JOIN                          |                             |  1 |   113 |  63  (10)|
|* 38 |           TABLE ACCESS BY INDEX ROWID       | RP_PBOSS_DETAIL_579         |  1 |    72 |   2   (0)|
|  39 |            NESTED LOOPS                     |                             |  1 |    98 |   3   (0)|
|  40 |             TABLE ACCESS BY INDEX ROWID     | RP_CANCEL_DETAIL_579        |  1 |    26 |   1   (0)|
|* 41 |              INDEX RANGE SCAN               | IDX_RPCD_009_579            |  1 |       |   1   (0)|
|* 42 |             INDEX RANGE SCAN                | IDX_RPPD_010_579            |  2 |       |   1   (0)|
|* 43 |           VIEW                              |                             |201K|  2947K|  58   (7)|
|  44 |            TABLE ACCESS FULL                | SYS_TEMP_0FD9FC85A_54FF2F02 |201K|  2161K|  58   (7)|
|  45 |          PARTITION RANGE ITERATOR           |                             |  1 |    34 |   1   (0)|
|* 46 |           TABLE ACCESS BY LOCAL INDEX ROWID | RP_FEE_DETAIL_579           |  1 |    34 |   1   (0)|
|* 47 |            INDEX RANGE SCAN                 | IDX_RP_FEE_INT_008_579      |  3 |       |   1   (0)|
-----------------------------------------------------------------------------------------------------------

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

  10 - filter(TO_DATE(:SYS_B_11,:SYS_B_12)<=TO_DATE(:SYS_B_13,:SYS_B_14)+TO_NUMBER(:SYS_B_15))
  11 - access("A"."CUSTOMER_ORDER_ID"="CC"."CUSTOMER_ORDER_ID")
  13 - filter("A"."ORG_ID"=TO_NUMBER(:SYS_B_38) AND "A"."PROD_CATALOG_ID"=TO_NUMBER(:SYS_B_37) 
              AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_44)
              AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_45) AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_46) 
              AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_47) AND
              "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_48) AND ("A"."OFFER_TYPE"<>:SYS_B_41 
              OR "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_39) AND
              "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_40) OR "A"."EXPIRE_DATE">=TO_DATE(:SYS_B_42,:SYS_B_43)))
  14 - access("A"."OP_ID"=:SYS_B_16 AND "A"."DONE_DATE">=TO_DATE(:SYS_B_11,:SYS_B_12) AND
              "A"."DONE_DATE"<=TO_DATE(:SYS_B_13,:SYS_B_14)+TO_NUMBER(:SYS_B_15))
  16 - filter("A"."OFFER_ORDER_ID"="B"."OFFER_ORDER_ID"(+))
  17 - access("A"."DONE_DATE"="B"."DONE_DATE"(+))
       filter("B"."DONE_DATE"(+)>=TO_DATE(:SYS_B_11,:SYS_B_12) 
              AND "B"."DONE_DATE"(+)<=TO_DATE(:SYS_B_13,:SYS_B_14)+TO_NUMBER(:SYS_B_15))
  18 - filter("CC"."RECEIVE_TYPE"=:SYS_B_17)
  20 - filter(TO_DATE(:SYS_B_18,:SYS_B_19)<=TO_DATE(:SYS_B_20,:SYS_B_21)+TO_NUMBER(:SYS_B_22))
  22 - access("A"."CUSTOMER_ORDER_ID"="CC"."CUSTOMER_ORDER_ID")
  23 - filter("A"."ORDER_STATE"=:SYS_B_25 AND "A"."PROD_CATALOG_ID"=TO_NUMBER(:SYS_B_37) AND
              "A"."COMPLETE_DATE">=TO_DATE(:SYS_B_18,:SYS_B_19) 
              AND "A"."COMPLETE_DATE"<=TO_DATE(:SYS_B_20,:SYS_B_21)+TO_NUMBER(:SYS_B_22) AND
              "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_44) AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_45) 
              AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_46) AND
              "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_47) AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_48) 
              AND ("A"."OFFER_TYPE"<>:SYS_B_41 OR
              "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_39) AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_40) 
              OR "A"."EXPIRE_DATE">=TO_DATE(:SYS_B_42,:SYS_B_43)))
  27 - access("A"."OP_ID"=:SYS_B_23)
  29 - access("A"."ORG_ID"=TO_NUMBER(:SYS_B_38))
  30 - filter("CC"."RECEIVE_TYPE"=:SYS_B_24)
  33 - filter("A"."OFFER_ORDER_ID"="B"."OFFER_ORDER_ID"(+))
  34 - access("A"."DONE_DATE"="B"."DONE_DATE"(+))
  35 - filter(TO_DATE(:SYS_B_29,:SYS_B_30)<=TO_DATE(:SYS_B_31,:SYS_B_32)+TO_NUMBER(:SYS_B_33))
  37 - access("A"."CUSTOMER_ORDER_ID"="CC"."CUSTOMER_ORDER_ID")
  38 - filter("A"."ORDER_STATE"=:SYS_B_36 AND "A"."ORG_ID"=TO_NUMBER(:SYS_B_38) 
              AND "A"."PROD_CATALOG_ID"=TO_NUMBER(:SYS_B_37) AND
              "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_44) AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_45) 
              AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_46) AND
              "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_47) AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_48) 
              AND ("A"."OFFER_TYPE"<>:SYS_B_41 OR
              "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_39) AND "A"."BUSINESS_ID"<>TO_NUMBER(:SYS_B_40) 
              OR "A"."EXPIRE_DATE">=TO_DATE(:SYS_B_42,:SYS_B_43)))
  41 - access("C"."OP_ID"=:SYS_B_34 AND "C"."CANCEL_DATE">=TO_DATE(:SYS_B_29,:SYS_B_30) AND
              "C"."CANCEL_DATE"<=TO_DATE(:SYS_B_31,:SYS_B_32)+TO_NUMBER(:SYS_B_33))
  42 - access("A"."CUSTOMER_ORDER_ID"="C"."CUSTOMER_ORDER_ID")
  43 - filter("CC"."RECEIVE_TYPE"=:SYS_B_35)
  46 - filter("A"."OFFER_ORDER_ID"="B"."OFFER_ORDER_ID"(+))
  47 - access("A"."DONE_DATE"="B"."DONE_DATE"(+))

虽然效率不是好很多，单对于报表库来说，更改后此SQL效率已经提升2倍多。补充：下面的几个sql和第一个都是类似的，只是不同地市的不同表，sql完全一致，所以搞定一个，其它的就ok。

出埃及
关注
专栏目录
Sql profiles-->使用sql tuning生成sql profile
gumengkai的博客
03-23 427
一. sqlprofile理解 sql profile可以为某一sql语句提供除了系统统计信息、对象(表和索引等)统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器选择更适合的执行计划。 使用sql profile的目的： a. 锁定或者说是稳定执行计划 b. 在不能修改应用中的sql的情况下使sql语句按照执行的执行计划运行。 相较于outline，sql pr
SQL Tuning set -- 从游标获取SQL
文档搬运工
03-20 555
原文地址：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/managing-sql-tuning-sets.html#GUID-91D1B886-A6D7-40B8-93D5-112B8C6E6AFE 其实，这个话题是一道OCM考试题，只是考试的时候，考场环境会提供已经做好的sql set ，只需要数据泵...
Oracle SQL tuning - Tips for writing more efficient SQL
有梦就别怕痛
04-19 177
1.Rewrite complex subqueries with temporary tables - Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with...
深入了解SQL Tuning Advisor
liujinwei2005的专栏
02-10 1367
1、前言：一直以来SQL调优都是DBA比较费力的技术活，而且很多DBA如果没有从事过开发的工作，那么调优更是一项头疼的工作，即使是SQL调优很厉害的高手，在SQL调优的过程中也要不停的分析执行计划、加HINT、分析统计信息等等。从ORACLE 10G开始，数据库采取了很多智能化的管理工作，其中SQL优化器(SQL Tuning Advisor：STA)，大大的提高了DBA进行SQL优化的效率； 2、原理介绍： When SQL statements are executed by the Orac.
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1) SQL调整运行状况检查脚本
u010692693的专栏
11-08 793
PURPOSE 目的 Download the SQL Tuning Health-Check Script 下载脚本 Download the SQLHC Script Here (.zip archive). 或者 https://pan.baidu.com/s/1WWSjI2vCQfonX_kBhZ_Bfg What is the SQL Tuning Healt...
Flink SQL --- 窗口聚合
最新发布
chbxw
10-29 1201
flinksql-- 窗口聚合
flink-sql-client提交sql脚本文件
chenzuoli的博客
10-22 4922
标题: flink-sql-client提交sql脚本文件 日期: 2021-10-22 22:11:34 标签: [flink,sql-client] 分类: flink 我们知道，sql-client.sh可以提供给我们一个sql交互界面，让我们没执行一个sql，就可以看到执行结果，也可以交互式查询表的结果。 其实，我们也可以通过sql-client提交sql脚本，我们来看下。 ./bin/sql-client.sh -h 对应的帮助参数: (base) [chenzuoli@chenzuolis.
Oracle 11g AUTO SQL TUNING 遇到ORA-04030错误
myzrl的博客
08-24 387
今天遇到ORA-04030错误： 01 Mon Jun 10 22:05:19 2019 ...
oracle health check,ORACLE SQL Tuning Health-Check(SQLHC)
weixin_36480255的博客
04-11 288
一条sql语句的性能主要依赖于好的物理结构,准确的系统统计数据,准确的对象统计数据,合理的查询优化器参数,合理的系统参数.这些数据也就是CBO计算的基本参数,SQLHC(文档 ID 1366133.1)正是为用户提供sql的这些数据,来辅助sql调优.特别适合无法在客户环境中调优的情况,SQLHC虽然没有SQLT(文档 ID 215187.1)更强大,但不需要在采集的库上建用户,会被更多的客户所接...
Automatic SQL Tuning - SQL Profiles. [ID 271196.1]
csdn198008的博客
12-12 121
Applies to: Oracle Server - Enterprise Edition - Version 10.1.0.2 and laterInformation in this documen...
Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition
04-07
Reading this book and using SQL helps you learn to tune even the most complex SQL, and you'll learn to do it quickly, without the huge learning curve usually associated with tuning as a whole. ...
Oracle SQL Tuning with Oracle SQLTXPLAIN(Apress,2013)
04-12
Using this simple tool you'll learn how to tune even the most complex SQL, and you'll learn to do it quickly, without the huge learning curve usually associated with tuning as a whole. Firmly based ...
SQL Server 2017 Query Performance Tuning 5th Edition
09-05
This book covers the latest in performance optimization features and techniques and is current with SQL Server 2017. If your queries are not running fast enough and you’re tired of phone calls from ...
Oracle - SQL调整顾问（SQL tuning advisor）、SQL访问顾问（SQL Access Advisor）
LawssssCat的博客
01-18 546
cache Manager 里面
SQ优化之Nested Loops
05-16 3262
开发给的sql，跑得慢，需要优化，sql如下： SELECT/*+INDEX(N IND1)*/ n.CNTR_N as cntrNoForAutitLog, n.CNTR_N as containerNumber, n.CNTR_TYPE_C as containerType, n.LENGTH_Q as containerLength, n.STRUCTURE_STATUS_C as
Oracle SQL Profile
11-19 1811
本篇文章来简单看下如何使用sql profile来绑定正确的执行计划，10gR2出现这个功能还是蛮实用的，当然11g可以使用baseline(我之前写过一篇文章)， 当然我觉得这两种都挺好。 我们可能经常会遇到一些大表比如上T的，走错执行计划，比如全表扫描，那系统基本处于Hang的状态，那么这时候收集统计信息可能会很慢， 即使你采样的比例设置的很小。所以使用profile和baseline是
sql执行计划错误之cache buffers chain
08-03 1073
分享个小案例： 今天某个库出现了cache buffers chain，最近应用没啥变更，怎么会突然出现呢，当然latch:cache buffers chain的作用是db cache中Find data很重要的latch，不管逻辑读，物理读(也要经历逻辑读),如果link或者unlink一个buffer到不同的Hash Bucket，再或者pin，unpin一个buffer，都要获得相关bu
SQL优化之in and Exsits
05-16 838
这个SQL是帮网友优化的，很简单： 30s返回结果： select * from B11QUA_three where project_id='TJWQSZ' and type='04' and times='初检' and suggestion='通过' and sid not in (select isnull(c
Oracle SQL Tuning：优化数据库的图形化教程与实战步骤
Oracle SQL Tuning 是一款强大的数据库优化工具，它作为Quest公司出品的Quest Central软件的一部分，旨在提高Oracle、DB2和SQL Server数据库的性能。SQL Tuning for SQL Server模块专注于SQL语句的优化，通过对比和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值