正德厚生，臻于至善
        找回密码
当前位置：徐万新之路 Oracle 正文

使用coe_load_sql_profile做替换绑定-20230202

分类：Oracle / OracleEBS / DBA 阅读(397) 评论(0) 
exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_0z3jnsu3ks2dc_2211254330'); 

select * from gv$sql_plan where sql_id='74mj7z7rgg6v9';
select * from gv$sql_plan where sql_id='0z3jnsu3ks2dc';

select sql_id,sql_profile,plan_hash_value from gv$sql where sql_id='0z3jnsu3ks2dc';

select * from dba_sql_profiles where name like '%0z3jnsu3ks2dc%';

select sql_id,sql_profile,plan_hash_value from gv$sql where sql_id='74mj7z7rgg6v9';

select * from dba_sql_profiles where name like '%74mj7z7rgg6v9%';

select sql_id,sql_profile,plan_hash_value from gv$sql where sql_profile is not null;

select * from gv$sql where sql_id='0z3jnsu3ks2dc';
-------------------------------------------------------------------74mj7z7rgg6v9
SELECT ph.segment1,
       ph.po_header_id,
       ph.type_lookup_code,
       ph.displayed_field type,
       ph.vendor_name,
       ph.vendor_id,
       ph.full_name,
       'Vendor',
       ph.org_id,
       MO_GLOBAL.get_ou_name(ph.org_id) operating_unit
  FROM po_pos_val_trx_v ph
 WHERE (UPPER(SEGMENT1) LIKE :1 AND (SEGMENT1 LIKE :2 OR SEGMENT1 LIKE :3))
   AND (ph.type_lookup_code in ('BLANKET', 'STANDARD', 'PLANNED') AND
       rownum >= 1 AND ph.org_id = nvl(:4, ph.org_id) AND exists
        (SELECT 'Valid PO Shipments'
           FROM po_line_locations_trx_v poll
          WHERE ph.po_header_id = poll.po_header_id
            AND nvl(poll.approved_flag, 'N') = 'Y'
            AND nvl(poll.cancel_flag, 'N') = 'N'
            AND poll.ship_to_organization_id = nvl(:5, :6)))
 ORDER BY decode(rtrim(ph.segment1, '0123456789'), null, null, ph.segment1),
          decode(rtrim(ph.segment1, '0123456789'),
                 null,
                 to_number(ph.segment1),
                 null)
-------------------------------------------------------------------0z3jnsu3ks2dc
SELECT /*+optimizer_features_enable(default) */
 ph.segment1,
 ph.po_header_id,
 ph.type_lookup_code,
 ph.displayed_field type,
 ph.vendor_name,
 ph.vendor_id,
 ph.full_name,
 'Vendor',
 ph.org_id,
 MO_GLOBAL.get_ou_name(ph.org_id) operating_unit
  FROM po_pos_val_trx_v ph
 WHERE (UPPER(SEGMENT1) LIKE 'S1BD2022020701WBF1%' AND (SEGMENT1 LIKE 's1%' OR SEGMENT1 LIKE 'S1%'))
   AND (ph.type_lookup_code in ('BLANKET', 'STANDARD', 'PLANNED') AND
       rownum >= 1 AND ph.org_id = nvl(null, ph.org_id) AND exists
        (SELECT 'Valid PO Shipments'
           FROM po_line_locations_trx_v poll
          WHERE ph.po_header_id = poll.po_header_id
            AND nvl(poll.approved_flag, 'N') = 'Y'
            AND nvl(poll.cancel_flag, 'N') = 'N'
            AND poll.ship_to_organization_id = nvl('520', '520')))
 ORDER BY decode(rtrim(ph.segment1, '0123456789'), null, null, ph.segment1),
          decode(rtrim(ph.segment1, '0123456789'),
                 null,
                 to_number(ph.segment1),
                 null)

目标是想让74mj7z7rgg6v9走0z3jnsu3ks2dc的2211254330这个执行计划

[oracle@r122uatdb1 sqlt]$ export ORACLE_PDB_SID=UAT
[oracle@r122uatdb1 sqlt]$ sqlplus system/system_passwd

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 2 15:10:25 2023
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Thu Feb 02 2023 14:18:49 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> @./sqlt/utl/coe_load_sql_profile.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: 74mj7z7rgg6v9

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: 0z3jnsu3ks2dc

     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
          3342724567                 .346
          2211254330                 .416

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 2211254330

Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "74mj7z7rgg6v9"
MODIFIED_SQL_ID: "0z3jnsu3ks2dc"
PLAN_HASH_VALUE: "2211254330"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for modified SQL_ID &&modified_sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>
SQL>SET ECHO OFF;
0001 BEGIN_OUTLINE_DATA
0002 IGNORE_OPTIM_EMBEDDED_HINTS
0003 OPTIMIZER_FEATURES_ENABLE('19.1.0')
0004 DB_VERSION('19.1.0')
0005 ALL_ROWS
0006 OUTLINE_LEAF(@"SEL$1D218D57")
0007 UNNEST(@"SEL$31")
0008 UNNEST(@"SEL$32")
0009 OUTLINE_LEAF(@"SEL$722829E4")
0010 OUTLINE_LEAF(@"SET$DA09BD39_2")
0011 OUTLINE_LEAF(@"SEL$722829E3")
0012 OUTLINE_LEAF(@"SET$DA09BD39_1")
0013 OUTLINE_LEAF(@"SET$DA09BD39")
0014 OUTLINE_LEAF(@"SEL$E919FBA5")
0015 OR_EXPAND(@"SEL$EBD7B9EC" (1) (2))
0016 OUTLINE_LEAF(@"SEL$27")
0017 OUTLINE_LEAF(@"SEL$D908F4C4")
0018 MERGE(@"SEL$07C48A2D" >"SEL$15")
0019 MERGE(@"SEL$22" >"SEL$15")
0020 MERGE(@"SEL$43F09110" >"SEL$15")
0021 MERGE(@"SEL$5EC70623" >"SEL$15")
0022 MERGE(@"SEL$95423B40" >"SEL$15")
0023 MERGE(@"SEL$97CCBC9E" >"SEL$15")
0024 OUTLINE_LEAF(@"SET$1")
0025 OUTLINE_LEAF(@"SEL$1")
0026 OUTLINE(@"SEL$1D70030A")
0027 MERGE(@"SEL$F7A62D4D" >"SEL$28")
0028 OUTLINE(@"SEL$31")
0029 OUTLINE(@"SEL$32")
0030 OUTLINE(@"SEL$14")
0031 OUTLINE(@"SEL$EBD7B9EC")
0032 MERGE(@"SEL$07BDC5B4" >"SEL$2")
0033 MERGE(@"SEL$285A8194" >"SEL$2")
0034 MERGE(@"SEL$61262C81" >"SEL$2")
0035 MERGE(@"SEL$8A3193DA" >"SEL$2")
0036 MERGE(@"SEL$9" >"SEL$2")
0037 MERGE(@"SEL$ABDE6DFF" >"SEL$2")
0038 OUTLINE(@"SEL$15")
0039 OUTLINE(@"SEL$07C48A2D")
0040 MERGE(@"SEL$19" >"SEL$18")
0041 OUTLINE(@"SEL$22")
0042 OUTLINE(@"SEL$43F09110")
0043 MERGE(@"SEL$24" >"SEL$23")
0044 OUTLINE(@"SEL$5EC70623")
0045 MERGE(@"SEL$21" >"SEL$20")
0046 OUTLINE(@"SEL$95423B40")
0047 MERGE(@"SEL$26" >"SEL$25")
0048 OUTLINE(@"SEL$97CCBC9E")
0049 MERGE(@"SEL$17" >"SEL$16")
0050 OUTLINE(@"SEL$28")
0051 OUTLINE(@"SEL$F7A62D4D")
0052 MERGE(@"SEL$30" >"SEL$29")
0053 OUTLINE(@"SEL$2")
0054 OUTLINE(@"SEL$07BDC5B4")
0055 MERGE(@"SEL$4" >"SEL$3")
0056 OUTLINE(@"SEL$285A8194")
0057 MERGE(@"SEL$11" >"SEL$10")
0058 OUTLINE(@"SEL$61262C81")
0059 MERGE(@"SEL$13" >"SEL$12")
0060 OUTLINE(@"SEL$8A3193DA")
0061 MERGE(@"SEL$8" >"SEL$7")
0062 OUTLINE(@"SEL$9")
0063 OUTLINE(@"SEL$ABDE6DFF")
0064 MERGE(@"SEL$6" >"SEL$5")
0065 OUTLINE(@"SEL$18")
0066 OUTLINE(@"SEL$19")
0067 OUTLINE(@"SEL$23")
0068 OUTLINE(@"SEL$24")
0069 OUTLINE(@"SEL$20")
0070 OUTLINE(@"SEL$21")
0071 OUTLINE(@"SEL$25")
0072 OUTLINE(@"SEL$26")
0073 OUTLINE(@"SEL$16")
0074 OUTLINE(@"SEL$17")
0075 OUTLINE(@"SEL$29")
0076 OUTLINE(@"SEL$30")
0077 OUTLINE(@"SEL$3")
0078 OUTLINE(@"SEL$4")
0079 OUTLINE(@"SEL$10")
0080 OUTLINE(@"SEL$11")
0081 OUTLINE(@"SEL$12")
0082 OUTLINE(@"SEL$13")
0083 OUTLINE(@"SEL$7")
0084 OUTLINE(@"SEL$8")
0085 OUTLINE(@"SEL$5")
0086 OUTLINE(@"SEL$6")
0087 NO_ACCESS(@"SEL$1" "PH"@"SEL$1")
0088 PQ_FILTER(@"SEL$1" SERIAL)
0089 INDEX_RS_ASC(@"SEL$D908F4C4" "PO_HEADERS_ALL"@"SEL$26" "PO_HEADERS_N12")
0090 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$D908F4C4" "PO_HEADERS_ALL"@"SEL$26")
0091 INDEX_RS_ASC(@"SEL$D908F4C4" "PO_DOCUMENT_TYPES_ALL_TL"@"SEL$19" ("PO_DOCUMENT_TYPES_ALL_TL"."DOCUMENT_TYPE_CODE" "PO_DOCUMENT_TYPES_ALL_TL"."DOCUMENT_SUBTYPE" "PO_DOCUMENT_TYPES_ALL_TL"."ORG_ID" "PO_DOCUMENT_TYPES_ALL_TL"."LANGUAGE" "PO_DOCUMENT_TYPES_ALL_TL"."ZD_EDITION_NAME"))
0092 INDEX_RS_ASC(@"SEL$D908F4C4" "PO_DOCUMENT_TYPES_ALL_B"@"SEL$21" ("PO_DOCUMENT_TYPES_ALL_B"."DOCUMENT_TYPE_CODE" "PO_DOCUMENT_TYPES_ALL_B"."ORG_ID" "PO_DOCUMENT_TYPES_ALL_B"."DOCUMENT_SUBTYPE" "PO_DOCUMENT_TYPES_ALL_B"."ZD_EDITION_NAME"))
0093 INDEX_RS_ASC(@"SEL$D908F4C4" "PAP"@"SEL$22" ("PER_ALL_PEOPLE_F"."PERSON_ID" "PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE"))
0094 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$D908F4C4" "PAP"@"SEL$22")
0095 INDEX_RS_ASC(@"SEL$D908F4C4" "APS"@"SEL$15" ("AP_SUPPLIERS"."VENDOR_ID"))
0096 INDEX_RS_ASC(@"SEL$D908F4C4" "HP"@"SEL$15" ("HZ_PARTIES"."PARTY_ID"))
0097 INDEX_RS_ASC(@"SEL$D908F4C4" "FND_LOOKUP_VALUES"@"SEL$24" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."VIEW_APPLICATION_ID" "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."SECURITY_GROUP_ID" "FND_LOOKUP_VALUES"."LANGUAGE" "FND_LOOKUP_VALUES"."ZD_EDITION_NAME"))
0098 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$D908F4C4" "FND_LOOKUP_VALUES"@"SEL$24")
0099 INDEX(@"SEL$D908F4C4" "FND_LOOKUP_VALUES"@"SEL$17" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."VIEW_APPLICATION_ID" "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."SECURITY_GROUP_ID" "FND_LOOKUP_VALUES"."LANGUAGE" "FND_LOOKUP_VALUES"."ZD_EDITION_NAME"))
0100 LEADING(@"SEL$D908F4C4" "PO_HEADERS_ALL"@"SEL$26" "PO_DOCUMENT_TYPES_ALL_TL"@"SEL$19" "PO_DOCUMENT_TYPES_ALL_B"@"SEL$21" "PAP"@"SEL$22" "APS"@"SEL$15" "HP"@"SEL$15" "FND_LOOKUP_VALUES"@"SEL$24" "FND_LOOKUP_VALUES"@"SEL$17")
0101 USE_NL(@"SEL$D908F4C4" "PO_DOCUMENT_TYPES_ALL_TL"@"SEL$19")
0102 USE_NL(@"SEL$D908F4C4" "PO_DOCUMENT_TYPES_ALL_B"@"SEL$21")
0103 USE_NL(@"SEL$D908F4C4" "PAP"@"SEL$22")
0104 USE_NL(@"SEL$D908F4C4" "APS"@"SEL$15")
0105 USE_NL(@"SEL$D908F4C4" "HP"@"SEL$15")
0106 USE_NL(@"SEL$D908F4C4" "FND_LOOKUP_VALUES"@"SEL$24")
0107 USE_NL(@"SEL$D908F4C4" "FND_LOOKUP_VALUES"@"SEL$17")
0108 NLJ_BATCHING(@"SEL$D908F4C4" "FND_LOOKUP_VALUES"@"SEL$17")
0109 PUSH_SUBQ(@"SEL$27")
0110 NO_ACCESS(@"SEL$E919FBA5" "VW_ORE_2B0B1E0D"@"SEL$2B0B1E0D")
0111 INDEX_RS_ASC(@"SET$DA09BD39_1" "PO_HEADERS_ALL"@"SET$DA09BD39_1" ("PO_HEADERS_ALL"."CLM_DOCUMENT_NUMBER"))
0112 BATCH_TABLE_ACCESS_BY_ROWID(@"SET$DA09BD39_1" "PO_HEADERS_ALL"@"SET$DA09BD39_1")
0113 INDEX_RS_ASC(@"SET$DA09BD39_1" "PO_DOCUMENT_TYPES_ALL_TL"@"SET$DA09BD39_1" ("PO_DOCUMENT_TYPES_ALL_TL"."DOCUMENT_TYPE_CODE" "PO_DOCUMENT_TYPES_ALL_TL"."DOCUMENT_SUBTYPE" "PO_DOCUMENT_TYPES_ALL_TL"."ORG_ID" "PO_DOCUMENT_TYPES_ALL_TL"."LANGUAGE" "PO_DOCUMENT_TYPES_ALL_TL"."ZD_EDITION_NAME"))
0114 INDEX_RS_ASC(@"SET$DA09BD39_1" "PO_DOCUMENT_TYPES_ALL_B"@"SET$DA09BD39_1" ("PO_DOCUMENT_TYPES_ALL_B"."DOCUMENT_TYPE_CODE" "PO_DOCUMENT_TYPES_ALL_B"."ORG_ID" "PO_DOCUMENT_TYPES_ALL_B"."DOCUMENT_SUBTYPE" "PO_DOCUMENT_TYPES_ALL_B"."ZD_EDITION_NAME"))
0115 INDEX_RS_ASC(@"SET$DA09BD39_1" "PAP"@"SET$DA09BD39_1" ("PER_ALL_PEOPLE_F"."PERSON_ID" "PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE"))
0116 BATCH_TABLE_ACCESS_BY_ROWID(@"SET$DA09BD39_1" "PAP"@"SET$DA09BD39_1")
0117 INDEX_RS_ASC(@"SET$DA09BD39_1" "APS"@"SET$DA09BD39_1" ("AP_SUPPLIERS"."VENDOR_ID"))
0118 INDEX_RS_ASC(@"SET$DA09BD39_1" "HP"@"SET$DA09BD39_1" ("HZ_PARTIES"."PARTY_ID"))
0119 INDEX_RS_ASC(@"SET$DA09BD39_1" "FND_LOOKUP_VALUES_0002"@"SET$DA09BD39_1" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."VIEW_APPLICATION_ID" "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."SECURITY_GROUP_ID" "FND_LOOKUP_VALUES"."LANGUAGE" "FND_LOOKUP_VALUES"."ZD_EDITION_NAME"))
0120 BATCH_TABLE_ACCESS_BY_ROWID(@"SET$DA09BD39_1" "FND_LOOKUP_VALUES_0002"@"SET$DA09BD39_1")
0121 INDEX(@"SET$DA09BD39_1" "FND_LOOKUP_VALUES_0001"@"SET$DA09BD39_1" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."VIEW_APPLICATION_ID" "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."SECURITY_GROUP_ID" "FND_LOOKUP_VALUES"."LANGUAGE" "FND_LOOKUP_VALUES"."ZD_EDITION_NAME"))
0122 LEADING(@"SET$DA09BD39_1" "PO_HEADERS_ALL"@"SET$DA09BD39_1" "PO_DOCUMENT_TYPES_ALL_TL"@"SET$DA09BD39_1" "PO_DOCUMENT_TYPES_ALL_B"@"SET$DA09BD39_1" "PAP"@"SET$DA09BD39_1" "APS"@"SET$DA09BD39_1" "HP"@"SET$DA09BD39_1" "FND_LOOKUP_VALUES_0002"@"SET$DA09BD39_1" "FND_LOOKUP_VALUES_0001"@"SET$DA09BD39_1")
0123 USE_NL(@"SET$DA09BD39_1" "PO_DOCUMENT_TYPES_ALL_TL"@"SET$DA09BD39_1")
0124 USE_NL(@"SET$DA09BD39_1" "PO_DOCUMENT_TYPES_ALL_B"@"SET$DA09BD39_1")
0125 USE_NL(@"SET$DA09BD39_1" "PAP"@"SET$DA09BD39_1")
0126 USE_NL(@"SET$DA09BD39_1" "APS"@"SET$DA09BD39_1")
0127 USE_NL(@"SET$DA09BD39_1" "HP"@"SET$DA09BD39_1")
0128 USE_NL(@"SET$DA09BD39_1" "FND_LOOKUP_VALUES_0002"@"SET$DA09BD39_1")
0129 USE_NL(@"SET$DA09BD39_1" "FND_LOOKUP_VALUES_0001"@"SET$DA09BD39_1")
0130 NLJ_BATCHING(@"SET$DA09BD39_1" "FND_LOOKUP_VALUES_0001"@"SET$DA09BD39_1")
0131 PQ_FILTER(@"SET$DA09BD39_1" SERIAL)
0132 INDEX_RS_ASC(@"SET$DA09BD39_2" "PO_HEADERS_ALL"@"SET$DA09BD39_2" ("PO_HEADERS_ALL"."CLM_DOCUMENT_NUMBER"))
0133 BATCH_TABLE_ACCESS_BY_ROWID(@"SET$DA09BD39_2" "PO_HEADERS_ALL"@"SET$DA09BD39_2")
0134 INDEX_RS_ASC(@"SET$DA09BD39_2" "PO_DOCUMENT_TYPES_ALL_TL"@"SET$DA09BD39_2" ("PO_DOCUMENT_TYPES_ALL_TL"."DOCUMENT_TYPE_CODE" "PO_DOCUMENT_TYPES_ALL_TL"."DOCUMENT_SUBTYPE" "PO_DOCUMENT_TYPES_ALL_TL"."ORG_ID" "PO_DOCUMENT_TYPES_ALL_TL"."LANGUAGE" "PO_DOCUMENT_TYPES_ALL_TL"."ZD_EDITION_NAME"))
0135 INDEX_RS_ASC(@"SET$DA09BD39_2" "PO_DOCUMENT_TYPES_ALL_B"@"SET$DA09BD39_2" ("PO_DOCUMENT_TYPES_ALL_B"."DOCUMENT_TYPE_CODE" "PO_DOCUMENT_TYPES_ALL_B"."ORG_ID" "PO_DOCUMENT_TYPES_ALL_B"."DOCUMENT_SUBTYPE" "PO_DOCUMENT_TYPES_ALL_B"."ZD_EDITION_NAME"))
0136 INDEX_RS_ASC(@"SET$DA09BD39_2" "PAP"@"SET$DA09BD39_2" ("PER_ALL_PEOPLE_F"."PERSON_ID" "PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE"))
0137 BATCH_TABLE_ACCESS_BY_ROWID(@"SET$DA09BD39_2" "PAP"@"SET$DA09BD39_2")
0138 INDEX_RS_ASC(@"SET$DA09BD39_2" "APS"@"SET$DA09BD39_2" ("AP_SUPPLIERS"."VENDOR_ID"))
0139 INDEX_RS_ASC(@"SET$DA09BD39_2" "HP"@"SET$DA09BD39_2" ("HZ_PARTIES"."PARTY_ID"))
0140 INDEX_RS_ASC(@"SET$DA09BD39_2" "FND_LOOKUP_VALUES_0002"@"SET$DA09BD39_2" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."VIEW_APPLICATION_ID" "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."SECURITY_GROUP_ID" "FND_LOOKUP_VALUES"."LANGUAGE" "FND_LOOKUP_VALUES"."ZD_EDITION_NAME"))
0141 BATCH_TABLE_ACCESS_BY_ROWID(@"SET$DA09BD39_2" "FND_LOOKUP_VALUES_0002"@"SET$DA09BD39_2")
0142 INDEX(@"SET$DA09BD39_2" "FND_LOOKUP_VALUES_0001"@"SET$DA09BD39_2" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."VIEW_APPLICATION_ID" "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."SECURITY_GROUP_ID" "FND_LOOKUP_VALUES"."LANGUAGE" "FND_LOOKUP_VALUES"."ZD_EDITION_NAME"))
0143 LEADING(@"SET$DA09BD39_2" "PO_HEADERS_ALL"@"SET$DA09BD39_2" "PO_DOCUMENT_TYPES_ALL_TL"@"SET$DA09BD39_2" "PO_DOCUMENT_TYPES_ALL_B"@"SET$DA09BD39_2" "PAP"@"SET$DA09BD39_2" "APS"@"SET$DA09BD39_2" "HP"@"SET$DA09BD39_2" "FND_LOOKUP_VALUES_0002"@"SET$DA09BD39_2" "FND_LOOKUP_VALUES_0001"@"SET$DA09BD39_2")
0144 USE_NL(@"SET$DA09BD39_2" "PO_DOCUMENT_TYPES_ALL_TL"@"SET$DA09BD39_2")
0145 USE_NL(@"SET$DA09BD39_2" "PO_DOCUMENT_TYPES_ALL_B"@"SET$DA09BD39_2")
0146 USE_NL(@"SET$DA09BD39_2" "PAP"@"SET$DA09BD39_2")
0147 USE_NL(@"SET$DA09BD39_2" "APS"@"SET$DA09BD39_2")
0148 USE_NL(@"SET$DA09BD39_2" "HP"@"SET$DA09BD39_2")
0149 USE_NL(@"SET$DA09BD39_2" "FND_LOOKUP_VALUES_0002"@"SET$DA09BD39_2")
0150 USE_NL(@"SET$DA09BD39_2" "FND_LOOKUP_VALUES_0001"@"SET$DA09BD39_2")
0151 NLJ_BATCHING(@"SET$DA09BD39_2" "FND_LOOKUP_VALUES_0001"@"SET$DA09BD39_2")
0152 PQ_FILTER(@"SET$DA09BD39_2" SERIAL)
0153 INDEX(@"SEL$722829E4" "OA"@"SEL$722829E4" ("MO_GLOB_ORG_ACCESS_TMP"."ORGANIZATION_ID"))
0154 INDEX(@"SEL$722829E3" "OA"@"SEL$722829E3" ("MO_GLOB_ORG_ACCESS_TMP"."ORGANIZATION_ID"))
0155 INDEX(@"SEL$27" "OA"@"SEL$27" ("MO_GLOB_ORG_ACCESS_TMP"."ORGANIZATION_ID"))
0156 INDEX_RS_ASC(@"SEL$1D218D57" "POLL"@"SEL$29" ("PO_LINE_LOCATIONS_ALL"."PO_HEADER_ID" "PO_LINE_LOCATIONS_ALL"."PO_RELEASE_ID"))
0157 BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1D218D57" "POLL"@"SEL$29")
0158 INDEX(@"SEL$1D218D57" "POD"@"SEL$32" ("PO_DISTRIBUTIONS_ALL"."LINE_LOCATION_ID"))
0159 INDEX_RS_ASC(@"SEL$1D218D57" "POL"@"SEL$30" ("PO_LINES_ALL"."PO_LINE_ID"))
0160 INDEX(@"SEL$1D218D57" "POD"@"SEL$31" ("PO_DISTRIBUTIONS_ALL"."PO_LINE_ID"))
0161 LEADING(@"SEL$1D218D57" "POLL"@"SEL$29" "POD"@"SEL$32" "POL"@"SEL$30" "POD"@"SEL$31")
0162 USE_NL(@"SEL$1D218D57" "POD"@"SEL$32")
0163 USE_NL(@"SEL$1D218D57" "POL"@"SEL$30")
0164 USE_NL(@"SEL$1D218D57" "POD"@"SEL$31")
0165 END_OUTLINE_DATA
dropping staging table "STGTAB_SQLPROF_74MJ7Z7RGG6V9"
creating staging table "STGTAB_SQLPROF_74MJ7Z7RGG6V9"
packaging new sql profile into staging table "STGTAB_SQLPROF_74MJ7Z7RGG6V9"

PROFILE_NAME
------------------------------
74MJ7Z7RGG6V9_2211254330
SQL>REM
SQL>REM SQL Profile
SQL>REM ~~~~~~~~~~~
SQL>REM
SQL>SELECT signature, name, category, type, status
  2    FROM dba_sql_profiles WHERE name = :name;

           SIGNATURE NAME                                                                                                                             CATEGORY                                              TYPE    STATUS
-------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------- --------
 5419765659455457243 74MJ7Z7RGG6V9_2211254330                                                                                                         DEFAULT                                               MANUAL  ENABLED
SQL>SELECT description
  2    FROM dba_sql_profiles WHERE name = :name;

DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORIGINAL:74MJ7Z7RGG6V9 MODIFIED:0Z3JNSU3KS2DC PHV:2211254330 SIGNATURE:5419765659455457243 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;

****************************************************************************
* Enter SYSTEM password to export staging table STGTAB_SQLPROF_74mj7z7rgg6v9
****************************************************************************

Export: Release 19.0.0.0.0 - Production on Thu Feb 2 15:11:05 2023
Version 19.16.0.0.0

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

Password: system_passwd

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table   STGTAB_SQLPROF_74MJ7Z7RGG6V9          1 rows exported
Export terminated successfully without warnings.

If you need to implement this Custom SQL Profile on a similar system,
import and unpack using these commands:

imp SYSTEM file=STGTAB_SQLPROF_74mj7z7rgg6v9.dmp tables=STGTAB_SQLPROF_74mj7z7rgg6v9 ignore=Y

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => '74MJ7Z7RGG6V9_2211254330',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_74mj7z7rgg6v9',
staging_schema_owner => 'SYSTEM' );
END;
/

  adding: coe_load_sql_profile_74mj7z7rgg6v9.log (deflated 85%)
  adding: STGTAB_SQLPROF_74mj7z7rgg6v9.dmp (deflated 91%)
  adding: coe_load_sql_profile.log (deflated 62%)

deleting: coe_load_sql_profile.log

coe_load_sql_profile completed.
SQL>exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@r122uatdb1 sqlt]$ unset ORACLE_PDB_SID
[oracle@r122uatdb1 sqlt]$

在生产环境：
1.导入sql_profile
unzip coe_load_sql_profile_74mj7z7rgg6v9.zip
cd coe_load_sql_profile_74mj7z7rgg6v9
imp system/system_passwd@PROD file=STGTAB_SQLPROF_74mj7z7rgg6v9.dmp tables=STGTAB_SQLPROF_74mj7z7rgg6v9 ignore=Y

2.UNPACK_STGTAB_SQLPROF
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => '74MJ7Z7RGG6V9_2211254330',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_74mj7z7rgg6v9',
staging_schema_owner => 'SYSTEM' );
END;
/

3.drop table system.STGTAB_SQLPROF_74mj7z7rgg6v9
drop table system.STGTAB_SQLPROF_74mj7z7rgg6v9 purge;
赞(0) 打赏
未经允许不得转载：徐万新之路 » 使用coe_load_sql_profile做替换绑定-20230202

相关推荐

评论 抢沙发

取消

1-Friends

2-Oracle

3-Database

4-OS

5-Security

6-Cloud

7-Other

热门标签

暂无标签！

热门文章

最新评论

置顶推荐

网站统计

  • 日志总数：635
  • 评论总数：20
  • 标签总数：0
  • 页面总数：3
  • 分类总数：90
  • 链接总数：92
  • 用户总数：12
  • 最后更新：2024-11-19
2024 年 11 月
一 二 三 四 五 六 日
 1234
567891011
12131415161718
19202122232425
262728293031  

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫

微信扫一扫

登录

找回密码

注册