正德厚生,臻于至善

OracleEBS

缓解焦虑v$session

xuwanxin1987阅读(6)

set lines 200 pages 50000
col event for a50
col sql_id for a20
col final_blocking_session for 9999999999999
col program for a45
col module for a35
select sid,serial#,event,sql_id,s.final_blocking_session,module,program from v$session S where status='ACTIVE' and type='USER';
set lines 180 pages 9999
col event for a50
col sql_id for a20
col final_blocking_session for 9999999999999
col program for a30
col module for a20
select sid,serial#,event,sql_id,s.final_blocking_session,module,program from v$session S where status='ACTIVE' and type='USER';

使用coe_load_sql_profile做替换绑定-20230202

xuwanxin1987阅读(10)

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;

sqlt

xuwanxin1987阅读(17)

https://www.sohu.com/a/397946746_411876?trans=000014_bdss_dkmwzacjP3p:CP=

https://blog.csdn.net/qq_41490913/article/details/105002574

https://developer.aliyun.com/article/985143

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1677588.1

SQLT快速绑定执行计划

SQL PROFILE可以使用SQLT工具快速绑定,SQL PROFILE就是对SQL增加了一系列HINTS,好处是不需要改写SQL,可以在数据库里直接管理。

对于COE工具SQL PROFILE绑定有两类:

直接绑定:针对执行计划经常突变的,历史中有好的执行计划,当前走的执行计划差,直接绑定即可。
替换绑定:针对执行计划一直较差,没有好的执行计划作为参考,可通过添加hints让其走好的执行计划,然后通过coe工具手动修改文件或coe_load_sql_profile或者编写存储过程绑定到好的执行计划上。
注意:如果SQL没有绑定变量,则通过coe_xfr_sql_profile生成的文件需要修改force_match=>true,手动编写存储过程或者coe_load_sql_profile做替换绑定的也需要修改force_match=>true,以让所有SQL结构相同(字面量条件不同)的SQL都绑定上好的执行计划。

下面分别说说这两种绑定方式:
1)使用coe_xfr_sql_profile脚本直接绑定
SQL> @./sqlt/utl/coe_xfr_sql_profile.sql

2)使用coe_load_sql_profile做替换绑定
SQL> @./sqlt/utl/coe_load_sql_profile.sql

不同sqlid之间替换执行计划,原sql_id:74mj7z7rgg6v9 新sql_id:0z3jnsu3ks2dc plan_hash_value:2211254330

imp SYSTEM/<system_passwordd>@UAT 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;
/

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

force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

force_match的默认值为false,表示只有在sql文本完全一致的情况下才会应用sql_profile,这种情况下只要目标sql的sql文本发生一点改动,原来的profile将失去作用
force_match的改为true,就相当于目标sql的where条件中的具体的输入值用绑定变量替换了
启用force_match=>true选项,需要先删除原来的profile

NAME	DESCRIPTION	TYPE	STATUS	FORCE_MATCHING
coe_0z3jnsu3ks2dc_2211254330	coe 0z3jnsu3ks2dc 2211254330 14921512880661520025 14513873519789741543	MANUAL	ENABLED	YES
74MJ7Z7RGG6V9_2211254330	ORIGINAL:74MJ7Z7RGG6V9 MODIFIED:0Z3JNSU3KS2DC PHV:2211254330 SIGNATURE:5419765659455457243 CREATED BY COE_LOAD_SQL_PROFILE.SQL	MANUAL	ENABLED	NO

74MJ7Z7RGG6V9_2211254330
sql_profile_74mj7z7rgg6v9

exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'sql_profile_74mj7z7rgg6v9'); 

ORIGINAL:74MJ7Z7RGG6V9 MODIFIED:0Z3JNSU3KS2DC PHV:2211254330 SIGNATURE:5419765659455457243 CREATED BY COE_LOAD_SQL_PROFILE.SQL

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%';

declare
  v_hint     sys.sqlprof_attr;
  v_sql_text clob;
begin
  select sql_fulltext
    into v_sql_text
    from gv$sql
   where sql_id = '74mj7z7rgg6v9'
     and rownum = 1;
  select extractvalue(value(d), '/hint') as outline_hints bulk collect into v_hint
  from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval
from gv$sql_plan where sql_id ='0z3jnsu3ks2dc' and plan_hash_value = 2211254330
 and other_xml is not null)) d;
  dbms_sqltune.import_sql_profile(v_sql_text,
                                  v_hint,
                                  'sql_profile_74mj7z7rgg6v91',
                                  force_match                => true,
                                  replace                    => true);
end;

declare
  v_hint     sys.sqlprof_attr;
  v_sql_text clob;
begin
  select sql_fulltext
    into v_sql_text
    from gv$sql
   where sql_id = '74mj7z7rgg6v9'
     and rownum = 1;
  select extractvalue(value(d), '/hint') as outline_hints bulk collect into v_hint
  from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval
from gv$sql_plan where sql_id ='f93tbqjhjqhz5' and plan_hash_value = 2211254330
 and other_xml is not null)) d;
  dbms_sqltune.import_sql_profile(v_sql_text,
                                  v_hint,
                                  'sql_profile_74mj7z7rgg6v91',
                                  force_match                => true,
                                  replace                    => true);
end;

2494034627

prod_pfile_20230131

xuwanxin1987阅读(19)

PRODCDB1.__data_transfer_cache_size=0
PRODCDB2.__data_transfer_cache_size=0
PRODCDB1.__db_cache_size=472983273472
PRODCDB2.__db_cache_size=473520144384
PRODCDB1.__inmemory_ext_roarea=0
PRODCDB2.__inmemory_ext_roarea=0
PRODCDB1.__inmemory_ext_rwarea=0
PRODCDB2.__inmemory_ext_rwarea=0
PRODCDB1.__java_pool_size=4294967296
PRODCDB2.__java_pool_size=3758096384
PRODCDB1.__large_pool_size=1073741824
PRODCDB2.__large_pool_size=536870912
PRODCDB1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PRODCDB2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PRODCDB1.__pga_aggregate_target=107374182400
PRODCDB2.__pga_aggregate_target=107374182400
PRODCDB1.__sga_target=549755813888
PRODCDB2.__sga_target=549755813888
PRODCDB1.__shared_io_pool_size=536870912
PRODCDB2.__shared_io_pool_size=536870912
PRODCDB1.__shared_pool_size=69256347648
PRODCDB2.__shared_pool_size=69793218560
PRODCDB1.__streams_pool_size=1073741824
PRODCDB2.__streams_pool_size=1073741824
PRODCDB1.__unified_pga_pool_size=0
PRODCDB2.__unified_pga_pool_size=0
*._b_tree_bitmap_plans=FALSE#MP
*._disable_actualization_for_grant=TRUE#MP
*._fast_full_scan_enabled=FALSE#MP
*._like_with_bind_as_equality=TRUE#MP
*._optimizer_autostats_job=FALSE#MP Turn off automatic statistics.
*._optimizer_cbqt_or_expansion='OFF'#Doc ID2747498.1
*._optimizer_gather_stats_on_conventional_dml=FALSE#MP
*._optimizer_use_stats_on_conventional_dml=FALSE#MP
*._pdb_name_case_sensitive=TRUE#MP
*._px_cdb_view_enabled=FALSE#Doc ID 2662860.1
*._sort_elimination_cost_ratio=5#MP
*._system_trig_enabled=TRUE#MP
*._trace_files_public=FALSE#prod11g
*._use_realfree_heap=TRUE#prod11g
*.aq_tm_processes=10#prod11g
*.audit_file_dest='/u01/app/oracle/admin/PRODCDB/adump'
*.audit_trail='none'#prod11g
*.cluster_database=TRUE#MP
*.compatible='19.0.0'#MP
*.CONTAINER_DATA='CURRENT_DICTIONARY'#Doc ID2900744.1
*.control_files='+DATA/PRODCDB/CONTROLFILE/controlfile_prodcdb01.ctl','+ARCH/PRODCDB/CONTROLFILE/controlfile_prodcdb02.ctl'
*.cursor_sharing='EXACT'#MP
*.db_block_checking='FALSE'#prod11g
*.db_block_checksum='TRUE'#prod11g
*.db_block_size=8192#MP
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDOMEM1'
*.db_create_online_log_dest_2='+REDOMEM2'
*.db_files=5120#prod11g
*.db_flashback_retention_target=2880
*.db_name='PRODCDB'
*.db_recovery_file_dest='+ARCH'
*.db_recovery_file_dest_size=1024G
*.diagnostic_dest='/u01/app/oracle'#prod11g
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODCDBXDB)'
*.dml_locks=10000# Database locks. #prod11g
*.enable_goldengate_replication=TRUE#prod11g
*.enable_pluggable_database=true
*.event='10946 trace name context forever, level 8454144'#MP
PRODCDB1.instance_number=1
PRODCDB2.instance_number=2
*.job_queue_processes=16#prod11g
PRODCDB1.local_listener='PRODCDB1_LOCAL' #ip
PRODCDB2.local_listener='PRODCDB2_LOCAL' #ip
*.LOG_ARCHIVE_DEST_1='LOCATION=+ARCH'#prod11g
*.log_archive_format='%t_%s_%r.dbf'#Doc ID 396009.1
*.log_buffer=10485760#Doc ID 396009.1
*.log_checkpoint_interval=100000#Doc ID 396009.1
*.log_checkpoint_timeout=1200#Doc ID 396009.1
*.log_checkpoints_to_alert=TRUE#Doc ID 396009.1
*.max_dump_file_size='20480'#Limit default trace file size to 20 MB.
*.nls_comp='BINARY'#MP
*.nls_date_format='DD-MON-RR'#MP
*.nls_language='AMERICAN'#11g
*.nls_length_semantics='BYTE'#MP
*.nls_numeric_characters='.,'#prod11g
*.nls_sort='BINARY'#MP
*.nls_territory='AMERICA'#prod11g
*.open_cursors=6000#prod11g
*.optimizer_adaptive_plans=TRUE#MP
*.optimizer_adaptive_statistics=FALSE#MP
*.optimizer_real_time_statistics=FALSE#MP
*.optimizer_secure_view_merging=FALSE#MP
*.parallel_degree_policy='MANUAL'#MP
*.parallel_force_local=TRUE#MP
*.parallel_max_servers=128# Max. value should be 2 x no. of CPUs.
*.parallel_min_servers=0
*.pga_aggregate_limit=0#MP
*.pga_aggregate_target=100G
*.processes=8000
*.recyclebin='off'#MP
*.remote_listener='PROD_REMOTE' #ip
*.remote_login_passwordfile='exclusive'
*.result_cache_max_size=600M#Doc ID 396009.1
*.sec_case_sensitive_logon=FALSE
*.service_names='PRODCDB'
*.session_cached_cursors=5000
*.sessions=16016
*.sga_max_size=512G
*.sga_target=512G
*.shared_pool_reserved_size=5G
*.shared_pool_size=50G
*.standby_file_management='AUTO'
*.target_pdbs=1
*.temp_undo_enabled=FALSE
PRODCDB2.thread=2
PRODCDB1.thread=1
*.undo_management='AUTO'#MP
PRODCDB2.undo_tablespace='UNDOTBS2'
PRODCDB1.undo_tablespace='UNDOTBS1'
*.workarea_size_policy='AUTO'#MP

coe_xfr_sql_profile.sql

xuwanxin1987阅读(20)

SPO coe_xfr_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.5.5 2013/03/01 carlos.sierra $
REM
REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM   This script generates another that contains the commands to
REM   create a manual custom SQL Profile out of a known plan from
REM   memory or AWR. The manual custom profile can be implemented
REM   into the same SOURCE system where the plan was retrieved,
REM   or into another similar TARGET system that has same schema
REM   objects referenced by the SQL that generated the known plan.
REM
REM PRE-REQUISITES
REM   1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM   1. SQL_ID (required)
REM   2. Plan Hash Value for which a manual custom SQL Profile is
REM      needed (required). A list of known plans is presented.
REM      You may choose from list provided or enter a valid phv
REM      from a version of the SQL modified with Hints.
REM
REM EXECUTION
REM   1. Connect into SQL*Plus as user with access to data dictionary.
REM      Do not use SYS.
REM   2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM      plan hash value (parameters can be passed inline or until
REM      requested).
REM
REM EXAMPLE
REM   # sqlplus system
REM   SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
REM   SQL> START coe_xfr_sql_profile.sql;
REM
REM NOTES
REM   1. For possible errors see coe_xfr_sql_profile.log
REM   2. If SQLT is installed in SOURCE, you can use instead:
REM      sqlt/utl/sqltprofile.sql
REM   3. Be aware that using DBMS_SQLTUNE requires a license for
REM      Oracle Tuning Pack.
REM   4. Use a DBA user but not SYS.
REM   5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1"
REM      Then you may consider this change (only in a test and disposable system):
REM      create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500);
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id = '&1';
PRO
WITH
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
 WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL
 UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
 WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
       SUM(elapsed_time)/SUM(executions) avg_et_secs
  FROM gv$sql
 WHERE sql_id = TRIM('&&sql_id.')
   AND executions > 0
 GROUP BY
       plan_hash_value ),
a AS (
SELECT plan_hash_value,
       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  FROM dba_hist_sqlstat
 WHERE sql_id = TRIM('&&sql_id.')
   AND executions_total > 0
 GROUP BY
       plan_hash_value )
SELECT p.plan_hash_value,
       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  FROM p, m, a
 WHERE p.plan_hash_value = m.plan_hash_value(+)
   AND p.plan_hash_value = a.plan_hash_value(+)
 ORDER BY
       avg_et_secs NULLS LAST;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
PRO
PRO Values passed to coe_xfr_sql_profile:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SQL_ID         : "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
SET TERM OFF ECHO ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;

-- trim parameters
COL sql_id NEW_V sql_id FOR A30;
COL plan_hash_value NEW_V plan_hash_value FOR A30;
SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;

VAR sql_text CLOB;
VAR sql_text2 CLOB;
VAR other_xml CLOB;
EXEC :sql_text := NULL;
EXEC :sql_text2 := NULL;
EXEC :other_xml := NULL;

-- get sql_text from memory
DECLARE
  l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
  FOR i IN (SELECT DISTINCT piece, sql_text
              FROM gv$sqltext_with_newlines
             WHERE sql_id = TRIM('&&sql_id.')
             ORDER BY 1, 2)
  LOOP
    IF :sql_text IS NULL THEN
      DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
      DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
    END IF;
    -- removes NUL characters
    l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
    -- adds a NUL character at the end of each line
    DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
  END LOOP;
  -- if found in memory then sql_text is not null
  IF :sql_text IS NOT NULL THEN
    DBMS_LOB.CLOSE(:sql_text);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
    :sql_text := NULL;
END;
/

SELECT :sql_text FROM DUAL;

-- get sql_text from awr
DECLARE
  l_sql_text VARCHAR2(32767);
  l_clob_size NUMBER;
  l_offset NUMBER;
BEGIN
  IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
    SELECT sql_text
      INTO :sql_text2
      FROM dba_hist_sqltext
     WHERE sql_id = TRIM('&&sql_id.')
       AND sql_text IS NOT NULL
       AND ROWNUM = 1;
  END IF;
  -- if found in awr then sql_text2 is not null
  IF :sql_text2 IS NOT NULL THEN
    l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0);
    l_offset := 1;
    DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
    DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
    -- store in clob as 64 character pieces plus a NUL character at the end of each piece
    WHILE l_offset < l_clob_size
    LOOP
      IF l_clob_size - l_offset > 64 THEN
        l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' ');
      ELSE -- last piece
        l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' ');
      END IF;
      DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
      l_offset := l_offset + 64;
    END LOOP;
    DBMS_LOB.CLOSE(:sql_text);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
    :sql_text := NULL;
END;
/

SELECT :sql_text2 FROM DUAL;
SELECT :sql_text FROM DUAL;

-- validate sql_text
SET TERM ON;
BEGIN
  IF :sql_text IS NULL THEN
    RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  END IF;
END;
/
SET TERM OFF;

-- get other_xml from memory
BEGIN
  FOR i IN (SELECT other_xml
              FROM gv$sql_plan
             WHERE sql_id = TRIM('&&sql_id.')
               AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
               AND other_xml IS NOT NULL
             ORDER BY
                   child_number, id)
  LOOP
    :other_xml := i.other_xml;
    EXIT; -- 1st
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
    :other_xml := NULL;
END;
/

-- get other_xml from awr
BEGIN
  IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
    FOR i IN (SELECT other_xml
                FROM dba_hist_sql_plan
               WHERE sql_id = TRIM('&&sql_id.')
                 AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
                 AND other_xml IS NOT NULL
               ORDER BY
                     id)
    LOOP
      :other_xml := i.other_xml;
      EXIT; -- 1st
    END LOOP;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
    :other_xml := NULL;
END;
/

-- get other_xml from memory from modified SQL
BEGIN
  IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
    FOR i IN (SELECT other_xml
                FROM gv$sql_plan
               WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
                 AND other_xml IS NOT NULL
               ORDER BY
                     child_number, id)
    LOOP
      :other_xml := i.other_xml;
      EXIT; -- 1st
    END LOOP;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
    :other_xml := NULL;
END;
/

-- get other_xml from awr from modified SQL
BEGIN
  IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
    FOR i IN (SELECT other_xml
                FROM dba_hist_sql_plan
               WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
                 AND other_xml IS NOT NULL
               ORDER BY
                     id)
    LOOP
      :other_xml := i.other_xml;
      EXIT; -- 1st
    END LOOP;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
    :other_xml := NULL;
END;
/

SELECT :other_xml FROM DUAL;

-- validate other_xml
SET TERM ON;
BEGIN
  IF :other_xml IS NULL THEN
    RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  END IF;
END;
/
SET TERM OFF;

-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
SET SERVEROUT ON SIZE UNL FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
DECLARE
  l_pos NUMBER;
  l_clob_size NUMBER;
  l_offset NUMBER;
  l_sql_text VARCHAR2(32767);
  l_len NUMBER;
  l_hint VARCHAR2(32767);
BEGIN
  DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
  DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.4.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
  DBMS_OUTPUT.PUT_LINE('REM   carlos.sierra@oracle.com');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
  DBMS_OUTPUT.PUT_LINE('REM   coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
  DBMS_OUTPUT.PUT_LINE('REM   This script is generated by coe_xfr_sql_profile.sql');
  DBMS_OUTPUT.PUT_LINE('REM   It contains the SQL*Plus commands to create a custom');
  DBMS_OUTPUT.PUT_LINE('REM   SQL Profile for SQL_ID &&sql_id. based on plan hash');
  DBMS_OUTPUT.PUT_LINE('REM   value &&plan_hash_value..');
  DBMS_OUTPUT.PUT_LINE('REM   The custom SQL Profile to be created by this script');
  DBMS_OUTPUT.PUT_LINE('REM   will affect plans for SQL commands with signature');
  DBMS_OUTPUT.PUT_LINE('REM   matching the one for SQL Text below.');
  DBMS_OUTPUT.PUT_LINE('REM   Review SQL Text and adjust accordingly.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
  DBMS_OUTPUT.PUT_LINE('REM   None.');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
  DBMS_OUTPUT.PUT_LINE('REM   SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('REM NOTES');
  DBMS_OUTPUT.PUT_LINE('REM   1. Should be run as SYSTEM or SYSDBA.');
  DBMS_OUTPUT.PUT_LINE('REM   2. User must have CREATE ANY SQL PROFILE privilege.');
  DBMS_OUTPUT.PUT_LINE('REM   3. SOURCE and TARGET systems can be the same or similar.');
  DBMS_OUTPUT.PUT_LINE('REM   4. To drop this custom SQL Profile after it has been created:');
  DBMS_OUTPUT.PUT_LINE('REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
  DBMS_OUTPUT.PUT_LINE('REM   5. Be aware that using DBMS_SQLTUNE requires a license');
  DBMS_OUTPUT.PUT_LINE('REM      for the Oracle Tuning Pack.');
  DBMS_OUTPUT.PUT_LINE('REM   6. If you modified a SQL putting Hints in order to produce a desired');
  DBMS_OUTPUT.PUT_LINE('REM      Plan, you can remove the artifical Hints from SQL Text pieces below.');
  DBMS_OUTPUT.PUT_LINE('REM      By doing so you can create a custom SQL Profile for the original');
  DBMS_OUTPUT.PUT_LINE('REM      SQL but with the Plan captured from the modified SQL (with Hints).');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
  DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;');
  DBMS_OUTPUT.PUT_LINE('REM');
  DBMS_OUTPUT.PUT_LINE('DECLARE');
  DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
  DBMS_OUTPUT.PUT_LINE('h       SYS.SQLPROF_ATTR;');
  DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS');
  DBMS_OUTPUT.PUT_LINE('BEGIN');
  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);');
  DBMS_OUTPUT.PUT_LINE('END wa;');
  DBMS_OUTPUT.PUT_LINE('BEGIN');
  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);');
  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);');
  DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.');
  DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),');
  DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.');
  l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0);
  l_offset := 1;
  WHILE l_offset < l_clob_size
  LOOP
    l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset);
    IF l_pos > 0 THEN
      l_len := l_pos - l_offset;
    ELSE -- last piece
      l_len := l_clob_size - l_pos + 1;
    END IF;
    l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset);
    /* cannot do such 3 replacement since a line could end with a comment using "--"
    l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP
    l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP
    l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP
    */
    l_offset := l_offset + l_len + 1;
    IF l_len > 0 THEN
      IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN
        l_sql_text := '['||l_sql_text||']';
      ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN
        l_sql_text := '{'||l_sql_text||'}';
      ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN
        l_sql_text := '<'||l_sql_text||'>';
      ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN
        l_sql_text := '('||l_sql_text||')';
      ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN
        l_sql_text := '"'||l_sql_text||'"';
      ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN
        l_sql_text := '|'||l_sql_text||'|';
      ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN
        l_sql_text := '~'||l_sql_text||'~';
      ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN
        l_sql_text := '^'||l_sql_text||'^';
      ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN
        l_sql_text := '@'||l_sql_text||'@';
      ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN
        l_sql_text := '#'||l_sql_text||'#';
      ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN
        l_sql_text := '%'||l_sql_text||'%';
      ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN
        l_sql_text := '$'||l_sql_text||'$';
      ELSE
        l_sql_text := CHR(96)||l_sql_text||CHR(96);
      END IF;
      DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');');
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);');
  DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
  DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
  FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
                   SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
              FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
  LOOP
    l_hint := i.hint;
    WHILE NVL(LENGTH(l_hint), 0) > 0
    LOOP
      IF LENGTH(l_hint) <= 500 THEN
        DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
        l_hint := NULL;
      ELSE
        l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
        DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
        l_hint := '   '||SUBSTR(l_hint, l_pos);
      END IF;
    END LOOP;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
  DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
  DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);');
  DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
  DBMS_OUTPUT.PUT_LINE('sql_text    => sql_txt,');
  DBMS_OUTPUT.PUT_LINE('profile     => h,');
  DBMS_OUTPUT.PUT_LINE('name        => ''coe_&&sql_id._&&plan_hash_value.'',');
  DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''',');
  DBMS_OUTPUT.PUT_LINE('category    => ''DEFAULT'',');
  DBMS_OUTPUT.PUT_LINE('validate    => TRUE,');
  DBMS_OUTPUT.PUT_LINE('replace     => TRUE,');
  DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);');
  DBMS_OUTPUT.PUT_LINE('END;');
  DBMS_OUTPUT.PUT_LINE('/');
  DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
  DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
  DBMS_OUTPUT.PUT_LINE('PRINT signature');
  DBMS_OUTPUT.PUT_LINE('PRINT signaturef');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
  DBMS_OUTPUT.PUT_LINE('SPO OFF;');
  DBMS_OUTPUT.PUT_LINE('PRO');
  DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
END;
/
SPO OFF;
SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;
SET SERVEROUT OFF;
PRO
PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
PRO on TARGET system in order to create a custom SQL Profile
PRO with plan &&plan_hash_value linked to adjusted sql_text.
PRO
UNDEFINE 1 2 sql_id plan_hash_value
CL COL
PRO
PRO COE_XFR_SQL_PROFILE completed.

DBA_HIST SQL

xuwanxin1987阅读(24)

可以通过以下三个视图查看历史执行计划:
DBA_HIST_SQL_PLAN
DBA_HIST_SQLSTAT
DBA_HIST_SNAPSHOT

DBA_HIST_SQLTEXT 视为SQL的松散超集
MOS Note 1580764.1提供了一个脚本和一些关于检查给定SQL_ID的性能历史的有用信息,但最终它只是加入 dba_hist_sqlstat 和 dba_hist_snapshot

EBS clear log file

xuwanxin1987阅读(25)

rm -rf *ora_10*
rm -rf *ora_20*
rm -rf *ora_30*
rm -rf *ora_40*
rm -rf *ora_50*
rm -rf *ora_60*
rm -rf *ora_70*
rm -rf *ora_80*
rm -rf *ora_90*
rm -rf *ora_11*
rm -rf *ora_12*
rm -rf *ora_13*
rm -rf *ora_14*
rm -rf *ora_15*
rm -rf *ora_16*
rm -rf *ora_17*
rm -rf *ora_18*
rm -rf *ora_19*
rm -rf *ora_21*
rm -rf *ora_22*
rm -rf *ora_23*
rm -rf *ora_24*
rm -rf *ora_25*
rm -rf *ora_26*
rm -rf *ora_27*
rm -rf *ora_28*
rm -rf *ora_29*
rm -rf *ora_31*
rm -rf *ora_32*
rm -rf *ora_33*
rm -rf *ora_34*
rm -rf *ora_35*
rm -rf *ora_36*
rm -rf *ora_37*
rm -rf *ora_38*
rm -rf *ora_39*
rm -rf *ora_41*
rm -rf *ora_42*
rm -rf *ora_43*
rm -rf *ora_44*
rm -rf *ora_45*
rm -rf *ora_46*
rm -rf *ora_47*
rm -rf *ora_48*
rm -rf *ora_49*
rm -rf *ora_51*
rm -rf *ora_52*
rm -rf *ora_53*
rm -rf *ora_54*
rm -rf *ora_55*
rm -rf *ora_56*
rm -rf *ora_57*
rm -rf *ora_58*
rm -rf *ora_59*
rm -rf *ora_61*
rm -rf *ora_62*
rm -rf *ora_63*
rm -rf *ora_64*
rm -rf *ora_65*
rm -rf *ora_66*
rm -rf *ora_67*
rm -rf *ora_68*
rm -rf *ora_69*
rm -rf *ora_71*
rm -rf *ora_72*
rm -rf *ora_73*
rm -rf *ora_74*
rm -rf *ora_75*
rm -rf *ora_76*
rm -rf *ora_77*
rm -rf *ora_78*
rm -rf *ora_79*
rm -rf *ora_81*
rm -rf *ora_82*
rm -rf *ora_83*
rm -rf *ora_84*
rm -rf *ora_85*
rm -rf *ora_86*
rm -rf *ora_87*
rm -rf *ora_88*
rm -rf *ora_89*
rm -rf *ora_91*
rm -rf *ora_92*
rm -rf *ora_93*
rm -rf *ora_94*
rm -rf *ora_95*
rm -rf *ora_96*
rm -rf *ora_97*
rm -rf *ora_98*
rm -rf *ora_99*
rm -rf cdmp*
rm -rf *.trc *.trm

Sizing the PGA in Oracle

xuwanxin1987阅读(42)

--How To Determine PGA , Olap Page Pool Hit / Miss Ratio and Size by User(Doc ID 353211.1)
select gvs.username, gvs.sid,
       round(pga_used_mem/1024/1024,2)||' MB' pga_used,
       round(pga_max_mem/1024/1024,2)||' MB' pga_max,
       round(pool_size/1024/1024,2)||' MB' olap_pp,
       round(100*(pool_hits-pool_misses)/pool_hits,2)||'%' olap_ratio
  from gv$process gvp, gv$session gvs, gv$aw_calc gva
 where session_id=gvs.sid and addr = paddr;
--Sizing the PGA in Oracle 19c - How to Account for the MGA Size (Doc ID 2808761.1)
show parameter pga
set lines 200 pages 50000;
col resource_name for a40;
select * from gv$resource_limit where resource_name='processes';

set lines 200 pages 50000;
col resource_name for a40;
select max(max_utilization) from gv$resource_limit where resource_name='processes';

col max_pga for 99999999.9
select value/1024/1024 max_pga from v$pgastat where name='maximum PGA allocated' minus
     select value/1024/1024 max_pga from v$pgastat where name='MGA allocated (under PGA)';
select ((select value/1024/1024 max_pga from v$pgastat where name='maximum PGA allocated' minus select value/1024/1024 max_pga from v$pgastat where name='MGA allocated (under PGA)') + ((select max(max_utilization) from gv$resource_limit where resource_name='processes') * 5)) * 1.1 PGA_MB from dual;

Oracle Database 11g 中的 ASM 限制

xuwanxin1987阅读(49)

如果 COMPATIBLE.ASM 或者 COMPATIBLE.RDBMS 小于12.1:
最大单盘2T,整体空间20PB
如果 COMPATIBLE.ASM 或者 COMPATIBLE.RDBMS 大于12.1:
单盘最大4PB,整体可以320EB

ASM - Scalability and Limits (Doc ID 370921.1)
ORA-15196 WITH ASM DISKS LARGER THAN 2TB (Doc ID 736891.1)

ASM 强制实施下列限制:
存储系统中包含 63 个磁盘组
存储系统中包含 10,000 个 ASM 磁盘
每个 ASM 磁盘的最大存储空间为 4 PB
每个存储系统的最大存储空间为 40 EB
每个磁盘组包含 1 百万个文件
最大文件大小取决于所使用的磁盘组的冗余类型:外部冗余为 140 PB(该值当前大于可能的数据库文件大小),正常冗余为 42 PB,高冗余为 15 PB。

注:在 Oracle Database 10g 中,外部冗余的最大 ASM 文件大小为 35 TB。
–Variable size extents
	* grows automatically with file size
–Benefits
	* Increase ASM file size
	* Reduce memory utilization in SGA
–100% automatic
–
–Note: RDBMS limits file size to 128TB
	* 63 disk groups
	* 10,000 ASM disks
	* 4 petabyte per ASM disk
	* 40 exabyte of storage
	* 1 million files per disk group
	* Maximum file size:
	* External redundancy: 140 PB
	* Normal redundancy: 42 PB
	* High redundancy: 15 PB
	
Understood your concern,You can not add disk more than 2 TB as a asm disk.Please check with your os admin to break the 3 Tb disk in to 1.5 Tb and then use it as a ASM disk.Following are few points to consider,

ASM provides near unlimited capacity for future growth, but does have some storage limits.
For example, ASM has the following limits on the number of disk groups, disks, and files:
1.) 63 disk groups in a storage system
2.) 10,000 ASM disks in a storage system
3.) 1 million files for each disk group

Without any Oracle Exadata Storage, ASM has these storage limits:

1.) 2 terabytes (TB) maximum storage for each ASM disk
2.) 20 petabytes (PB) maximum for the storage system
With all Oracle Exadata Storage, ASM has these storage limits:
3.) 4 PB maximum storage for each ASM disk
4.) 40 exabytes (EB) maximum for the storage system

The maximum size limit of a disk group equals the maximum disk size multiplied by the maximum number of disks in a disk group (10,000).The maximum number of disks across all disk groups is 10,000. The 10,000 disks can be in
one disk group or distributed across a maximum of 63 disk groups. This is a limitation on the number of ASM disks, not necessarily the number of spindles. A storage array could group multiple spindles into a LUN that is used as a single ASM disk. However ASM is currently limited to 2 TB in a single disk unless using Oracle Exadata storage.

File size limits are dependent on the value of the disk group compatibility attributes. ASM supports file sizes greater than 128 TB in any redundancy mode when the COMPATIBLE.RDBMS disk group attribute is set greater than10.1. If COMPATIBLE.RDBMS is set to 10.1, the file size limits are less. For example, with COMPATIBLE.RDBMS equal to 10.1 and the AU size equal to 1 MB, ASM file size limits are:

1.) External redundancy: 16 TB
2.) Normal redundancy: 5.8 TB
3.) High redundancy: 3.9 TB

ebs version check

xuwanxin1987阅读(61)

----------------------------------------- 数据库模块列表
set lines 200 pages 50000
col comp_id format a10
col comp_name format a35
col version format a15
col status format a15
select comp_id,comp_name, version, status from dba_registry;
----------------------------------------- nodes check 
spool fnd_nodes
set lines 200 pages 50000
col node_name format a15
col server_id format a8
col server_address format a15
col platform_code format a4
col webhost format a25
col domain format a20
col virtual_ip format a12
select
node_id,
platform_code,
support_db D,
support_cp C,
support_admin A,
support_forms F,
support_web W,
node_name,
server_id,
server_address,
domain,
webhost,
virtual_ip
from
apps.fnd_nodes
order by node_id;

----------------------------------------- version check 
select b.bug_number, b.language, to_char(b.LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS') Last_update from apps.ad_bugs b where bug_number='&a'; 

select * from apps.icx_parameters;
登录每台机器执行命令( !date;id;pwd;hostname -f;echo $ORACLE_SID;echo $TWO_TASK):

!date;id;pwd;hostname -f;echo $ORACLE_SID;echo $TWO_TASK
SELECT name,open_mode,dbid FROM v$database;
SELECT node_name,support_cp,support_forms,support_web,support_admin,support_db FROM apps.fnd_nodes;
SELECT release_name,creation_date FROM apps.fnd_product_groups;
SELECT instance_name,status,parallel,archiver,version FROM v$instance;
SELECT nls_language,language_code,installed_flag FROM apps.fnd_languages WHERE installed_flag in ('I','B');

R12 new: SELECT bug_number,language,creation_date FROM apps.ad_bugs where bug_number='&a';
11i old:SELECT bug_number,creation_date FROM apps.ad_bugs where bug_number='&a';
11i old:select release_name from fnd_product_groups;

select patch_level from fnd_product_installations where application_id = 50;

SELECT application_id,status,patch_level FROM apps.fnd_product_installations where PATCH_LEVEL like '%AD%';
SELECT application_id,status,patch_level FROM apps.fnd_product_installations where application_id=2031;

/autofs/upgrade/ohsupg/bin/check_module.sh -module PA

select decode(support_db,'Y','DB: ','MT: ')||lower(host)||'.'||lower(domain) name from apps.fnd_nodes where node_name <> 'AUTHENTICATION' order by node_name;

set line 300
col host for a30
col domain for a30
col webhost for a30
select node_name, host, domain, webhost, SUPPORT_DB, SUPPORT_CP, SUPPORT_FORMS, SUPPORT_WEB, SUPPORT_ADMIN from apps.fnd_nodes where NODE_NAME<>'AUTHENTICATION';

NODE_NAME HOST DOMAIN WEBHOST S S S S S
------------------------------ ------------------------------ ------------------------------ ------------------------------ - - - - -
C1-ZJCWEBS1 c1-zjcwebs1 dehetu.com c1-zjcwebs1.dehetu.com N Y Y Y Y
RAC1 rac1 dehetu.com Y N N N N
RAC2 rac2 dehetu.com Y N N N N
C1-ZJCWEBS2 c1-zjcwebs2 dehetu.com c1-zjcwebs2.dehetu.com N Y Y Y Y

----------------------------------------- E-Business Suite Version
select instance_name, release_name, host_name,
startup_time, version
from applsys.fnd_product_groups, v$instance;

----------------------------------------- Instance Node Details
select substr(node_name, 1, 20) node_name, node_mode, server_address, substr(host, 1, 15) host,
substr(domain, 1, 20) domain, substr(support_cp, 1, 3) cp, substr(support_web, 1, 3) web,
substr(support_admin, 1, 3) ADMIN, substr(support_forms, 1, 3) FORMS,
substr(SUPPORT_DB, 1, 3) db, substr(VIRTUAL_IP, 1, 30) virtual_ip
from apps.fnd_nodes;

----------------------------------------- WF components
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a16
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from apps.FND_CONCURRENT_QUEUES_VL fcq,apps.fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) order by COMPONENT_STATUS,STARTUP_MODE,COMPONENT_NAME;
 
----------------------------------------- Check Invalids
--按照用户
break on report
compute sum of INVALIDS on report
SELECT owner,count(object_name) INVALIDS from dba_objects where status ='INVALID' group by owner;

--按照用户及对象类型
break on report
compute sum of INVALIDS on report
SELECT owner,object_type, COUNT(*) INVALIDS from dba_objects where status ='INVALID' group by owner,object_type;

pre
create table apps.invalids_<rfc_num> as (select object_name,owner from dba_objects where status='INVALID');

post
SELECT owner,object_name,object_type from dba_objects where status ='INVALID' and object_name not in(SELECT object_name from apps.invalids_<RFC NUM>); 

-----------------------------------------表大小
--注,仅表数据的大小,不含索引、分区、LOB类型
select sum(bytes)/1024/1024 "表大小(M)" from dba_segments where segment_name='AP_SUPPLIER_SITES_ALL';

select Segment_Name,Sum(bytes)/1024/1024 From dba_Extents Group By Segment_Name having Segment_Name='AP_SUPPLIER_SITES_ALL';

select sum(bytes)/1024/1024/1024 GB from dba_temp_files /*临时文件大小*/
union
select sum(bytes)/1024/1024/1024 GB from dba_data_files /*数据文件大小*/
union
select sum(bytes)/1024/1024/1024 GB from dba_segments; /*对象大小*/

--used_mb存储空间使用大小
column name format a20
column free_mb format 999,999,999
select name,to_char(sysdate,'YYYY-MM-DD HH24:MI') SYSTEM_DATETIME from v$database;
select (select name from v$database) db_name,name diskgroup_name,total_mb,free_mb,(total_mb-free_mb) used_mb,round(free_mb/total_mb*100,1) FREE_Uti_PCT from v$asm_diskgroup order by 4;

-----------------------------------------
shutdown immediate
startup upgrade

@?/rdbms/admin/utlirp     --invalid all the objects 
@?/rdbms/admin/utlprp 16  --compile all the invalids
select count(*) from dba_objects where status = 'INVALID';

----------------------------------------- Compile new invalids
Ans:
alter package <package_name> compile ;
alter package <packae_name> compile body;
alter view <view_name> compile; 

@?/rdbms/admin/utlrp =>compile all the invalids
@?/rdbms/admin/utlprp 8 =>compile all the invalids(并行度8)

exec utl_recomp.recomp_parallel(8);

--exec dbms_utility.compile_schema('APPS'); 太慢

SQL> select owner,object_name,object_type from dba_objects where status ='INVALID' and object_name not in(select object_name from apps.mafify_invalids);

OWNER OBJECT_NAME OBJECT_TYPE
--------------- ------------------------------ -------------------
APPS XLA_BALANCES_CALC_PKG PACKAGE BODY
APPS XLA_BALANCES_PKG PACKAGE BODY

SQL> alter package XLA_BALANCES_PKG compile body;
SQL> alter package XLA_BALANCES_CALC_PKG compile body;

alter procedure <owner>.<procedure_name> compile;
alter function <owner>.<function_name> compile;
alter view <owner>.<view_name> compile;
alter package <owner>.<package_name> compile;
alter package <owner>.<package_name> compile body;
alter materialized view <owner>.<Package_name> compile;

spool compile.sql
set lines 200 pages 50000 
select 'alter PROCEDURE '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='PROCEDURE'
union
select 'alter FUNCTION '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='FUNCTION'
union
select 'alter VIEW '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='VIEW'
union
select 'alter TRIGGER '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='TRIGGER'
union 
select 'alter package '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='PACKAGE'
union
select 'alter package '||OWNER||'.'||OBJECT_NAME||' compile body;'from dba_objects where status='INVALID' and OBJECT_TYPE='PACKAGE BODY'
union
select 'alter MATERIALIZED VIEW '||OWNER||'.'||OBJECT_NAME||' compile;'from dba_objects where status='INVALID' and OBJECT_TYPE='MATERIALIZED VIEW'; 
spool off

@?/rdbms/admin/utlprp 16
select count(*) from dba_objects where status = 'INVALID';

alter session set nls_date_format ='yyyy-mm-dd HH24:MI:SS';
set lines 200 pages 50000 
col object_name format a30
col owner format a20
select owner, object_name, object_type,status,last_ddl_time from dba_objects where status = 'INVALID' order by last_ddl_time desc;

@?/rdbms/admin/utlrp
select count(*) from dba_objects where status = 'INVALID';

联系我们

登录

找回密码

注册