AWR 快照未生成:
Error ORA-32701 'On Current SQL: insert into wrh$_sql_bind_metadata' (Doc ID 2226216.1)
1.检查AWR快照,发现部分快照未生成。
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
dmisync DMIS 41834 24 Jan 2024 00:00 1
41835 24 Jan 2024 01:00 1
41836 24 Jan 2024 02:00 1
41837 24 Jan 2024 03:00 1
41838 24 Jan 2024 04:00 1
41839 24 Jan 2024 05:00 1
41840 24 Jan 2024 06:00 1
41841 24 Jan 2024 07:00 1
41842 24 Jan 2024 08:01 1
41843 24 Jan 2024 09:00 1
41844 24 Jan 2024 10:00 1
41846 24 Jan 2024 12:00 1 --这里出现断点。
41850 25 Jan 2024 14:23 12.检查日志
alert.log
--可以发现有MMON进程挂起suspend 82800 seconds的告警
ALTER TABLE SYNCDATA.PM_USER_INFO DROP CONSTRAINT SYS_C0092000
Wed Jan 24 13:13:02 2024
ALTER TABLE SYNCDATA.LP_EVALUATION_OUTER_REPAIR MODIFY ZCBUS_SOURCE_OPTIME DATE NULL
Wed Jan 24 15:20:41 2024
Suspending MMON slave action kewrmafsa_ for 82800 seconds
--夯死约23小时。
--查看trace日志。
more dmisync_mmon_106932.trc
*** 2024-01-24 15:51:44.726
minact-scn master-status: grec-scn:0x0592.1bd25eb6 gmin-scn:0x0592.1bd25eb6 gcalc-scn:0x0592.1bd25eb6
minact-scn master-status: grec-scn:0x0592.1bdbffdc gmin-scn:0x0592.1bdbffdc gcalc-scn:0x0592.1bdbffdc
*** 2024-01-24 16:00:39.450
Unable to schedule a MMON slave at: Auto Flush Main 1
Slave action has been temporarily suspended
- Slave action had prior policy violations.
Unknown return code: 101
*** 2024-01-24 16:01:39.525
Unable to schedule a MMON slave at: Auto Flush Main 1
Slave action has been temporarily suspended
- Slave action had prior policy violations.
Unknown return code: 101
*** 2024-01-24 16:01:51.531
minact-scn master-status: grec-scn:0x0592.1be4ed73 gmin-scn:0x0592.1be4ed73 gcalc-scn:0x0592.1be4ed73
*** 2024-01-24 16:03:39.683
Unable to schedule a MMON slave at: Auto Flush Main 1
Slave action has been temporarily suspended
- Slave action had prior policy violations.
Unknown return code: 101
3.查看M00的子进程。
[oracle@dmis-prod-db-oracle11g trace]$ more dmisync_m000_56922.trc
Trace file /chac/app/oracle/diag/rdbms/dmis230/dmisync/trace/dmisync_m000_56922.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /chac/app/oracle/product/11.2.0
System name: Linux
Node name: dmis-prod-db-oracle11g
Release: 3.10.0-1160.el7.x86_64
Version: #1 SMP Mon Oct 19 16:18:59 UTC 2020
Machine: x86_64
VM name: VMWare Version: 6
Instance name: dmisync
Redo thread mounted by this instance: 1
Oracle process number: 104
Unix process pid: 56922, image: oracle@dmis-prod-db-oracle11g (M000)
*** 2023-12-25 20:38:32.840
*** SESSION ID:(5642.42087) 2023-12-25 20:38:32.840
*** CLIENT ID:() 2023-12-25 20:38:32.840
*** SERVICE NAME:(SYS$BACKGROUND) 2023-12-25 20:38:32.840
*** MODULE NAME:(MMON_SLAVE) 2023-12-25 20:38:32.840
*** ACTION NAME:(Auto-Flush Slave Action) 2023-12-25 20:38:32.840
DDE rules only execution for: ORA 12751
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
Executing ASYNC actions
----- START DDE Action: 'ORA_12751_DUMP' (Sync) -----
CPU time exceeded 300 seconds
Time limit violation detected at:
ksedsts()+465<-kspol_12751_dump()+145<-dbgdaExecuteAction()+1065<-dbgerRunAction()+109<-dbgerRunActions()+4134<-dbgexPhaseII()+1873<-dbgexProcessError()+2680<-dbgeExecuteForError()+88<-dbg
ePostErrorKGE()+2136<-dbkePostKGE_kgsf()+71<-kgeselv()+276<-ksesecl0()+162
<-ksucin()+147<-qerfxFetch()+5073<-qerjotRowProc()+359<-qerhjInnerProbeHashTable()+491<-qerfxFetch()+1106<-rwsfcd()+103<-qerhjFetch()+621<-qerjotFetch()+2025<-rwsfcd()+103<-qerltcFetch()+1
223<-insexe()+691<-opiexe()+5632<-kpoal8()+2380<-opiodr()+917<-kpoodrc()+36
<-rpiswu2()+1776<-kpoodr()+636<-upirtrc()+2436<-kpurcsc()+98<-kpuexec()+10790<-OCIStmtExecute()+39<-kewrose_oci_stmt_exec()+55<-kewrgwxf1_gwrsql_exft_1()+317<-kewrgwxf_gwrsql_exft()+496<-k
ewrews_execute_wr_sql()+52<-kewrftbs_flush_table_by_sql()+180<-kewrfabt_flush_attribute_table()+997
<-kewrfsb_flush_sqlbind()+34<-kewrft_flush_table()+397<-kewrftec_flush_table_ehdlcx()+766<-kewrftsq_flush_topsql()+768<-kewrft_flush_table()+397<-kewrftec_flush_table_ehdlcx()+766<-kewrfat
_flush_all_tables()+1406<-kewrfos_flush_onesnap()+170<-kewrfsc_flush_snapshot_c()+644
<-kewrafs_auto_flush_slave()+769<-kebm_slave_main()+586<-ksvrdp()+1766<-opirip()+674<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245Cur
rent Wait Stack:
Not in wait; last wait ended 14 min 50 sec ago
Wait State:
fixed_waits=0 flags=0x21 boundary=(nil)/-1
Session Wait History:
elapsed time of 14 min 50 sec since last wait
0: waited for 'db file sequential read'
file#=0x2e, block#=0x6125, blocks=0x1
wait_id=595 seq_num=596 snap_id=1
wait times: snap=0.000279 sec, exc=0.000279 sec, total=0.000279 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000523 sec of elapsed time
1: waited for 'db file sequential read'
file#=0x2, block#=0xfc60, blocks=0x1
wait_id=594 seq_num=595 snap_id=1
wait times: snap=0.000372 sec, exc=0.000372 sec, total=0.000372 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.304686 sec of elapsed time
2: waited for 'db file sequential read'
file#=0x2, block#=0x19cb, blocks=0x1
wait_id=593 seq_num=594 snap_id=1
wait times: snap=0.000390 sec, exc=0.000390 sec, total=0.000390 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.001525 sec of elapsed time
3: waited for 'control file sequential read'
file#=0x0, block#=0xb5, blocks=0x1
wait_id=592 seq_num=593 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
4: waited for 'control file sequential read'
file#=0x0, block#=0x12, blocks=0x1
wait_id=591 seq_num=592 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000001 sec of elapsed time
5: waited for 'control file sequential read'
file#=0x0, block#=0x10, blocks=0x1
wait_id=590 seq_num=591 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
6: waited for 'control file sequential read'
file#=0x0, block#=0x1, blocks=0x1
wait_id=589 seq_num=590 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000015 sec of elapsed time
7: waited for 'control file sequential read'
file#=0x0, block#=0xb5, blocks=0x1
wait_id=588 seq_num=589 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
8: waited for 'control file sequential read'
file#=0x0, block#=0x12, blocks=0x1
wait_id=587 seq_num=588 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000001 sec of elapsed time
9: waited for 'control file sequential read'
file#=0x0, block#=0x10, blocks=0x1
wait_id=586 seq_num=587 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
Sampled Session History of session 5642 serial 42087
---------------------------------------------------
The sampled session history is constructed by sampling
the target session every 1 second. The sampling process
captures at each sample if the session is in a non-idle wait,
an idle wait, or not in a wait. If the session is in a
non-idle wait then one interval is shown for all the samples
the session was in the same non-idle wait. If the
session is in an idle wait or not in a wait for
consecutive samples then one interval is shown for all
the consecutive samples. Though we display these consecutive
samples in a single interval the session may NOT be continuously
idle or not in a wait (the sampling process does not know).
The history is displayed in reverse chronological order.
sample interval: 1 sec, max history 120 sec
---------------------------------------------------
[120 samples, 20:36:32 - 20:38:31]
not in wait at each sample
----- END DDE Action: 'ORA_12751_DUMP' (SUCCESS, 9 csec) -----
----- END DDE Actions Dump (total 9 csec) -----
*** KEWROCISTMTEXEC - encountered error: (ORA-12751: cpu time or run time policy violation
)
*** SQLSTR: total-len=619, dump-len=240,
STR={insert into wrh$_sql_bind_metadata (snap_id, dbid, sql_id, name, position, dup_position, datatype, datatype_string, character_sid, precision, scale, max_length)
SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */ :lah_snap_}
DDE rules only execution for: ORA 12751
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
Executing ASYNC actions
----- START DDE Action: 'ORA_12751_DUMP' (Sync) -----
CPU time exceeded 300 seconds
Time limit violation detected at:
ksedsts()+465<-kspol_12751_dump()+145<-dbgdaExecuteAction()+1065<-dbgerRunAction()+109<-dbgerRunActions()+4134<-dbgexPhaseII()+1873<-dbgexProcessError()+2680<-dbgeExecuteForError()+88<-dbg
ePostErrorKGE()+2136<-dbkePostKGE_kgsf()+71<-kgeade()+351<-kgerelv()+140
<-kgerev()+34<-kserec1()+170<-OCIKSEC()+189<-kewrose_oci_stmt_exec()+292<-kewrgwxf1_gwrsql_exft_1()+317<-kewrgwxf_gwrsql_exft()+496<-kewrews_execute_wr_sql()+52<-kewrftbs_flush_table_by_sq
l()+180<-kewrfabt_flush_attribute_table()+997<-kewrfsb_flush_sqlbind()+34
<-kewrft_flush_table()+397<-kewrftec_flush_table_ehdlcx()+766<-kewrftsq_flush_topsql()+768<-kewrft_flush_table()+397<-kewrftec_flush_table_ehdlcx()+766<-kewrfat_flush_all_tables()+1406<-ke
wrfos_flush_onesnap()+170<-kewrfsc_flush_snapshot_c()+644<-kewrafs_auto_flush_slave()+769
<-kebm_slave_main()+586<-ksvrdp()+1766<-opirip()+674<-opidrv()+603<-sou2o()+103<-opimai_real()+250<-ssthrdmain()+265<-main()+201<-__libc_start_main()+245Current Wait Stack:
Not in wait; last wait ended 14 min 50 sec ago
Wait State:
fixed_waits=0 flags=0x21 boundary=(nil)/-1
Session Wait History:
elapsed time of 14 min 50 sec since last wait
0: waited for 'db file sequential read'
file#=0x2e, block#=0x6125, blocks=0x1
wait_id=595 seq_num=596 snap_id=1
wait times: snap=0.000279 sec, exc=0.000279 sec, total=0.000279 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000523 sec of elapsed time
1: waited for 'db file sequential read'
file#=0x2, block#=0xfc60, blocks=0x1
wait_id=594 seq_num=595 snap_id=1
wait times: snap=0.000372 sec, exc=0.000372 sec, total=0.000372 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.304686 sec of elapsed time
2: waited for 'db file sequential read'
file#=0x2, block#=0x19cb, blocks=0x1
wait_id=593 seq_num=594 snap_id=1
wait times: snap=0.000390 sec, exc=0.000390 sec, total=0.000390 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.001525 sec of elapsed time
3: waited for 'control file sequential read'
file#=0x0, block#=0xb5, blocks=0x1
wait_id=592 seq_num=593 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
4: waited for 'control file sequential read'
file#=0x0, block#=0x12, blocks=0x1
wait_id=591 seq_num=592 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000001 sec of elapsed time
5: waited for 'control file sequential read'
file#=0x0, block#=0x10, blocks=0x1
wait_id=590 seq_num=591 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
6: waited for 'control file sequential read'
file#=0x0, block#=0x1, blocks=0x1
wait_id=589 seq_num=590 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000015 sec of elapsed time
7: waited for 'control file sequential read'
file#=0x0, block#=0xb5, blocks=0x1
wait_id=588 seq_num=589 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
8: waited for 'control file sequential read'
file#=0x0, block#=0x12, blocks=0x1
wait_id=587 seq_num=588 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000001 sec of elapsed time
9: waited for 'control file sequential read'
file#=0x0, block#=0x10, blocks=0x1
wait_id=586 seq_num=587 snap_id=1
wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000000 sec of elapsed time
Sampled Session History of session 5642 serial 42087
---------------------------------------------------
The sampled session history is constructed by sampling
the target session every 1 second. The sampling process
captures at each sample if the session is in a non-idle wait,
an idle wait, or not in a wait. If the session is in a
non-idle wait then one interval is shown for all the samples
the session was in the same non-idle wait. If the
session is in an idle wait or not in a wait for
consecutive samples then one interval is shown for all
the consecutive samples. Though we display these consecutive
samples in a single interval the session may NOT be continuously
idle or not in a wait (the sampling process does not know).
The history is displayed in reverse chronological order.
sample interval: 1 sec, max history 120 sec
---------------------------------------------------
[121 samples, 20:36:32 - 20:38:32]
not in wait at each sample
----- END DDE Action: 'ORA_12751_DUMP' (SUCCESS, 0 csec) -----
----- END DDE Actions Dump (total 0 csec) -----
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWRAFM1: Error=13509 encountered by kewrfteh
*** KEWRAFS: Error=13509 encountered by Auto Flush Slave.
KEBM: MMON slave action policy violation. kewrmafsa_; viol=2; err=13509
到这里，我们可以确认 awr快照生成任务进程m00x发起 wrh$_sql_bind_metadata 表
的插入语句执行超时，导致AWR快照生成失败。
语句主要通过关联查询基表 x$kewrattrnew，x$kewrsqlidtab以及 v$sql_bind_capture，将结果插入到
wrh$_sql_bind_metadata 表
insert into wrh$_sql_bind_metadata
(snap_id,
dbid,
sql_id,
name,
position,
dup_position,
datatype,
datatype_string,
character_sid,
precision,
scale,
max_length)
SELECT /*+ ordered use_nl(bnd) index(bnd sql_id) */
:lah_snap_id,
:dbid,
bnd.sql_id,
name,
position,
dup_position,
datatype,
datatype_string,
character_sid,
precision,
scale,
max_length
FROM x$kewrattrnew new, x$kewrsqlidtab tab, v$sql_bind_capture bnd
WHERE new.str1_kewrattr = tab.sqlid_kewrsie
AND tab.sqlid_kewrsie = bnd.sql_id
AND tab.childaddr_kewrsie = bnd.child_address;
x$kewrattrnew是存放属性配置的表，
x$kewrsqlidtab是存放sqlid信息的表，
v$sql_bind_capture 是访问 v_$sql_bind_capture 视图的同义词
3.原因。
x$kqlfbc 基表是绑定变量的内存缓存表，而当数据库里面语句使用了大量的绑定变量，
会导致在访问该X$基表时需要检索内存里面大量的绑定变量信息，使执行效率非常缓慢
4.处理方法。
(1)收集基表x$kewrattrnew，x$kewrsqlidtab，确保语句的执行计划生成正确，
避免由于执行计划的错误，导致语句执行缓慢
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');
PL/SQL procedure successfully completed.
(2)重启数据库释放内存基表的数据
shu immediate
startup
(3)刷新shared_pool释放内存基表的数据
alter system flush shared_pool; 5.其他处理方法
查询使用绑定变量语句过多的SQL,可以发现多个语句使用了大量的绑定变量
---查询绑定变量过多的sql_id
select a.sql_id,count(POSITION)
from (select sql_id,POSITION
from v$sql_bind_capture
where rownum<100000) a
group by a.sql_id
order by 2;
---通过sql_id去确认sql语句
set linesize 400
set long 99999
set longc 99999
select sql_fulltext
from v$sql
where rownum<10;
--v$sql_bind_capture
--检查发现大量使用绑定变量。
例如:
update "SYNCDATA"."GCCAREVALUATEMAIN"
set "LOSSTYPE" = :1,
"LICENSENOA" = :2,
"LOSSNAME" = :3,
"INSURECARIND" = :4,
"ENGINENO" = :5,
"FRAMENO" = :6,
"VINNO" = :7,
"CARUSAGE" = :8,
"MODELCODE" = :9,
"BRANDNAME" = :10,
"SEATCOUNT" = :11,
"MANUFACTURINGYEAR" = :12,
"CAPACITY" = :13,
"RUNDISTANCE" = :14,
"USEYEARS" = :15,
"INSUREDIND" = :16,
"THIRDINSURERCODE" = :17,
"THIRDINSURER" = :18,
"THIRDREFERENCE" = :19,
"PERSONCODE" = :20,
"MEMBERNO" = :21,
"MEMBERREFERENCE" = :22,
"PERSONCNAME" = :23,
"PERSONENAME" = :24,
"PERSONTYPE" = :25,
"ITEMDETAILNO" = :26,
"ITEMDETAILCODE" = :27,
"ITEMDETAILLIST" = :28,
"LOSSCARNO" = :29,
"LICENSENOA2" = :30,
"LICENSENOB" = :31,
"SOLUTIONCODE" = :32,
"AREACODE" = :33,
"FIX EDINCOMEIND" = :34,
"JOBCODE" = :35,
"JOBNAME" = :36,
"SEX" = :37,
"AGE" = :38,
"BIRTHDATE" = :39,
"NATIONALITY" = :40,
"IDENTIFYTYPEA" = :41,
"IDENTIFYNOA" = :42,
"IDENTIFYTYPEB" = :43,
"IDENTIFYNOB" = :44,
"JOBUNIT" = :45,
"POSITION" = :46,
"TICCODE" = :47,
"ICCCODE" = :48,
"OCCUPATIONS" = :49,
"WORKPARTICULARS" = :50,
"DAMAGEDATE" = :51,
"PAYPERSONTYPE" = :52,
"CURRENCY" = :53,
"MONTHWORKDAYS" = :54,
"RESTIND" = :55,
"RESTWAGEIND" = :56,
"MONTHSTDWAGE" = :57,
"MONTHBONUS" = :58,
"ALLOWANCE" = :59,
"MONTHWAGE" = :60,
"HOSPITALCODE" = :61,
"HOSPITALNAME" = :62,
"SICKIND" = :63,
"FIRSTSICKDATE" = :64,
"INHOSPDATE" = :65,
"OUTHOSPDATE" = :66,
"RESTDATE" = :67,
"LEVELCODE" = :68,
"DIAGNOSISCODE" = :69,
"D IAGNOSISDETAIL" = :70,
"DOCTOR" = :71,
"OPERATIONCODE" = :72,
"OPERATIONDATE" = :73,
"SPECIALIST" = :74,
"ANAESTHETIST" = :75,
"REFERRALDATE" = :76,
"PHYSIOTHERAPYDATE" = :77,
"SICKLEAVEDAYS" = :78,
"SICKLEAVESTARTDATE" = :79,
"SICKLEAVEENDDATE" = :80,
"DEATHDATE" = :81,
"NURSEPERSONS" = :82,
"NURSEPERSONINFO" = :83,
"NURSEDAYS" = :84,
"DIAGNOSE" = :85,
"INJUREGRADE" = :86,
"INJURYSCOPEDESC" = :87,
"HOSPITALDAYS" = :88,
"CUREDAYS" = :89,
"CHANGEHOSPITAL" = :90,
"FLLOWHOSPREMARK" = :91,
"RELATEPERSONNO" = :92,
"COMPENSATEBACKFLAG" = :93,
"DOCSUGGESTDAYS" = :94,
"EVALUATENO" = :95,
"CURRENCY2" = :96,
"ORIGCURRENCY" = :97,
"EXORIGRATE" = :98,
"SUMPREDEFLOSS" = :99,
"EVALUATECODE" = :100,
"EVALUATENAME" = :101,
"EVALUATE1CODE" = :102,
"EVALUATE1NAME" = :103,
"DEFLOSSDATE" = :104,
"EVALUATEREMARK" = :105,
"SUMDEFLOSS" = :106,
"DEFLEVEL" = :107,
"UNDERWRITECODE" = :108,
"UNDERWRITENAME" = :109,
"UNDERWRITEENDDATE" = :110,
"VERIFYIND" = :111,
"UNDERWRITEIND" = :112,
"VERIFYOPINION" = :113,
"VERIFYREMARK" = :114,
"FIRSTDEFLOSS" = :115,
"WARPDEFLOSS" = :116,
"RETURNREASON" = :117,
"BACKCHECKREMARK" = :118,
"VERPAPPROVERCODE" = :119,
"VERPDATE" = :120,
"VERPOPINION" = :121,
"VERPREMARK" = :122,
"COMPENSATEBACKFLAG2" = :123,
"COMPENSATEOPINION" = :124,
"COMPENSATEBACKDATE" = :125,
"COMPENSATEAPPROVERCODE" = :126,
"OPERATORCODE" = :127,
"INPUTDATE" = :128,
"CLAIMISSUECOMPANY" = :129,
"HANDLERCODE" = :130,
"HANDLER1CODE" = :131,
"COMPANYCODE" = :132,
"STATUS" = :133,
"VALIDIND " = :134,
"REMARK" = :135,
"FLAG" = :136,
"EVALUATETYPE" = :137,
"EACHPAIDIND" = :138,
"NODUTYIND" = :139,
"INDEMNITYDUTYRATE" = :140,
"DEALTYPE" = :141,
"EVALUATEKINDTYPE" = :142,
"CLIENTNO" = :143,
"CLASSES" = :144,
"SICKCLASS" = :145,
"DISABILITYCLASS" = :146,
"DISABILITYLEVEL" = :147,
"RISKSCORE" = :148,
"RESURVEYFLAG" = :149,
"DISEASECODE" = :150,
"DISEASENAME" = :151,
"FRAUDFLAG" = :152,
"FRAUDTYPECODE" = :153,
"FRAUDTYPENAME" = :154,
"ISHOTSINCEDETONATION" = :155,
"ISWATERFLOODED" = :156,
"WATERFLOODEDLEVEL" = :157,
"INJURYPART" = :158,
"INJURYIDENTIFYCERTICODE" = :159,
"INJURYIDENTIFYNAME" = :160,
"FINALLYIND" = :161,
"INJUREPARTCODE" = :162,
"NONLITIGATIONCONCILIATIONFLAG" = :163,
"CREATORCODE" = :164,
"CREATETIME" = :165,
"U PDATERCODE" = :166,
"UPDATETIME" = :167,
"ISFROMSURVEYFLAG" = :168,
"REOPENTIMES" = :169,
"INJUREDAREA" = :170,
"EXPECTRESTDATE" = :171,
"EXPECTDISABILITYLEVEL" = :172,
"EXPECTDISABILITYNAME" = :173,
"DISABILITYNAME" = :174,
"DEATHREASON" = :175,
"PERSONEVALUATECOMEFROM" = :176,
"ISAUTOADJUS" = :177,
"ZCBUS_SOURCE_OPTIME" = :178,
"ZCBUS_TARGET_OPTIME" = :179
where "REGISTNO" = :180
and "TIMES" = :181
and "LOSSID" = :182;
insert into "SYNCDATA"."GUPOLICYCOPYMAIN"
("POLICYNO",
"ENDORSEQNO",
"ENDORNO",
"BUSINESSCHANNEL",
"BUSINESSSOURCE",
"BUSINESSTYPE",
"POLICYSORT",
"LANGUAGE",
"GROUPIND",
"INTERMEDIARYCODE",
"AGREEMENTNO",
"SOLUTIONCODE",
"CREDITPERIOD",
"INDICATIONNO",
"INDICATIONSEQNO",
"QUOTATIONNO",
"QUOTATIONSEQNO",
"HOLDCOVERNO",
"HOLDCOVERSEQNO",
"COVERNOTENO",
"COVERNOTESEQNO",
"AGENTREFERENCENO",
"RENEWALNO",
"REPLACEDPOLICYNO",
"PROPOSALNO",
"PRINTNO",
"PROMOTIONCODE",
"APPLICODE",
"APPLINAME",
"APPLIADDRESS",
"APPLIADDRTYPE",
"INSUREDCODE",
"INSUREDNAME",
"INSUREDADDRESS",
"INSUREDADDRTYPE",
"OPERATEDATE",
"RECEIVE DDATE",
"ISSUEDATE",
"CURRENCY",
"SUMINSURED",
"SUMGROSSPREMIUM",
"SUMNETPREMIUM",
"ARGUESOLUTION",
"MAINPORT",
"SUBPORT",
"OTHPOLICYNO",
"THEIRCOMPANYCODE",
"THEIRCOMPANYNAME",
"THEIRREFERENCE",
"ARBITORYNAME",
"INSTALLMENTNO",
"ENDORSETIMES",
"REGISTTIMES",
"CLAIMSTIMES",
"PRINTTIMES",
"ISSUECOMPANY",
"ISSUEPLACE",
"COMPANYCODE",
"SALESMANCODE",
"APPROVERCODE",
"OPERATORCODE",
"INPUTDATE",
"UWYEAR",
"UNDERWRITECODE",
"UNDERWRITENAME",
"UNDERWRITEENDDATE",
"UNDERWRITEIND",
"AUTORENEWIND",
"MULTIRISKIND",
"LASTMODIFIERCODE",
"LASTMODIFYDATE",
"FRONTINGRIPERCENT",
"FRONTINGRICOMMISSIONPERCENT",
"FRONTINGRIADMINFEEPERCENT ",
"FRONTINGRITAXPERCENT",
"RENEWIND",
"RENEWEDIND",
"SURRENDERIND",
"CANCELIND",
"ENDIND",
"LOSTIND",
"DEBTORIND",
"STATIND",
"SOURCEID",
"YEARPREMIUM",
"MIGRATEDCLIENTNO",
"MIGRATEDACCOUNTNUM",
"MIGRATEDPOLICYNO",
"CHANGEINSURED",
"CHANGEPREMIUM",
"CHANGESUBPREMIUM",
"CHANGEQUANTITY",
"CHANGENETPREMIUM",
"ENDORIND",
"REMARK",
"VALIDIND",
"FLAG",
"SUMUWPREMIUM",
"CHANGESUMUWPREMIUM",
"VISATYPE",
"IMAGEIND",
"PROPOSALPOLICYIND",
"INWARDREFERENCE",
"HENGSANGIND",
"DOMESTICIND",
"HISTORYIND",
"REPLACEPROPOSERIND",
"HANDLESTATUS",
"APPROVEENDDATE",
"DEALERCODE",
"CODIND",
"ACCEPTDATE",
"SPECIALACCEPTANCE ",
"RELATEDRISKCODE",
"CONTACTREMARK",
"AGRVALIDIND",
"OUTERUSERCODE",
"OUTERCOMPANYCODE",
"SERVICEPROJECTCODE",
"CHANNELDETAILCODE",
"PRODUCTCODE",
"PROBLEMIND",
"CANCELTIME",
"CHANNELFLAG",
"AGRICULTUREFLAG",
"HISTORYFLAG",
"CALCULATETYPE",
"BUSINESSIND",
"SALESCOMMISSIONERCODE",
"CMBPRINTIVCIND",
"BUSINESSMODE",
"APPLYNO",
"OLDCARPRINTFLAG",
"CHANNELTIP",
"COOPERATESITECODE",
"APPLYSERIALNO",
"COMINSUREIND",
"TEAMMANAGER",
"PIONEERCODE",
"COMPOLICYINFO",
"PREFEEFLAG",
"POASERIALNO",
"SURVEYIND",
"PRIIND",
"PRINTDATE",
"OUTERSUBCOMPANYCODE",
"DEPARTMENTCODE",
"BASKETIND",
"FLOWID",
"INTERSALESMANREGISTERNO",
"I NTERSALESMANCODE",
"SALESMANREGISTERNO",
"IMAGEATTACHIND",
"MONEYSUSPICIOUSIND",
"RELATETRADETYPE",
"BUSINESSGRADE",
"BUSINESSTYPESOURCE",
"RESOURCETYPE",
"BUSINESSGRADETWO",
"OTHBUSINESSNO",
"SHOWIND",
"APPROVALNO",
"APPROVALNO2",
"SUMADDTAX",
"SUMADDTAXRATE",
"SUMUWPRICETAXTOTAL",
"SUMPRICETAXTOTAL",
"CHANGEADDTAX",
"CHANGEPRICETAXTOTAL",
"CHANGEUWPRICETAXTOTAL",
"RECORDCODE",
"INTERBUSINESSIND",
"THIRDPLATFORM",
"CREATETIME",
"CREATORCODE",
"UPDATETIME",
"UPDATERCODE",
"GROUPNO",
"SYSTEMCODE",
"BURIEDCODE",
"ZCBUS_SOURCE_OPTIME",
"ZCBUS_TARGET_OPTIME",
"ZCBUS_INSERT_OPTIME")
values
(:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8,
:9,
:10,
:11,
:12,
:13,
:14,
:15,
:16,
:17,
:18,
:1 9,
:20,
:21,
:22,
:23,
:24,
:25,
:26,
:27,
:28,
:29,
:30,
:31,
:32,
:33,
:34,
:35,
:36,
:37,
:38,
:39,
:40,
:41,
:42,
:43,
:44,
:45,
:46,
:47,
:48,
:49,
:50,
:51,
:52,
:53,
:54,
:55,
:56,
:57,
:58,
:59,
:60,
:61,
:62,
:63,
:64,
:65,
:66,
:67,
:68,
:69,
:70,
:71,
:72,
:73,
:74,
:75,
:76,
:77,
:78,
:79,
:80,
:81,
:82,
:83,
:84,
:85,
:86,
:87,
:88,
:89,
:90,
:91,
:92,
:93,
:94,
:95,
:96,
:97,
:98,
:99,
:100,
:101,
:102,
:103,
:104,
:105,
:106,
:107,
:108,
:109,
:110,
:111,
:112,
:113,
:114,
:115,
:116,
:117,
:118,
:119,
:120,
:121,
:122,
:123,
:124,
:125,
:126,
:127,
:128,
:129,
:130,
:131,
:132,
:133,
:134,
:135,
:136,
:137,
:138,
:139,
:140,
:141,
:142,
:143,
:144,
:145,
:146,
:147,
:148,
:149,
:150,
:151,
:152,
:153,
:154,
:155,
:156,
:157,
:158,
:159,
:160,
:161,
:162,
:163,
:164,
:165,
:166,
:167,
:168,
:169,
:170,
:171,
:172,
:173,
:174,
:175,
:176,
:177,
:178,
:179,
:180,
:181,
:182,
:183);6.总结
SQL可以使用绑定变量,但是不要过度使用绑定变量。
AWR快照无法生成,同时出现,就是绑定变量过多，导致绑定变量基表过大，无法查询:
KEBM: MMON slave action policy violation.
Suspending MMON slave action kewrmafsa_ for 82800 seconds
Unable to schedule a MMON slave at: Auto Flush Main 1
Slave action has been temporarily suspended
- Slave action had prior policy violations.
建议适量使用绑定变量。
1008
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
