一、查询CRMDB42实例的ASH裸数据
SQL> select sample_time,sample_id,count(*) 2 from gv$active_session_history 3 where sample_time between 4 to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and 5 to_date('&date2', 'yyyy-mm-dd hh24:mi:ss') 6 and inst_id in (select instance_number from v$instance) 7 group by sample_time,sample_id 8 order by sample_time,sample_id; Enter value for date1: 2020-01-07 10:00:00 old 4: to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and new 4: to_date('2020-01-07 10:00:00', 'yyyy-mm-dd hh24:mi:ss') and Enter value for date2: 2020-01-07 10:05:00 old 5: to_date('&date2', 'yyyy-mm-dd hh24:mi:ss') new 5: to_date('2020-01-07 10:05:00', 'yyyy-mm-dd hh24:mi:ss') SAMPLE_TIME SAMPLE_ID COUNT(*) --------------------------------------------------------------------------- ---------- ---------- 07-JAN-20 10.00.01.016 AM 107975664 125 07-JAN-20 10.00.02.046 AM 107975665 14 07-JAN-20 10.00.03.076 AM 107975666 14 07-JAN-20 10.00.04.116 AM 107975667 25 07-JAN-20 10.00.05.146 AM 107975668 19 07-JAN-20 10.00.06.176 AM 107975669 72 07-JAN-20 10.00.07.206 AM 107975670 136 07-JAN-20 10.00.08.246 AM 107975671 189 07-JAN-20 10.00.12.406 AM 107975672 240 07-JAN-20 10.00.13.436 AM 107975673 250 07-JAN-20 10.00.14.486 AM 107975674 260 07-JAN-20 10.00.15.516 AM 107975675 321 07-JAN-20 10.00.16.556 AM 107975676 377 07-JAN-20 10.00.17.596 AM 107975677 412 07-JAN-20 10.00.18.636 AM 107975678 411 07-JAN-20 10.00.19.706 AM 107975679 396 07-JAN-20 10.00.20.746 AM 107975680 397 07-JAN-20 10.00.21.786 AM 107975681 359 07-JAN-20 10.00.22.826 AM 107975682 329 07-JAN-20 10.00.23.866 AM 107975683 326 07-JAN-20 10.00.25.086 AM 107975684 335 07-JAN-20 10.00.26.126 AM 107975685 306 07-JAN-20 10.00.27.166 AM 107975686 314 07-JAN-20 10.00.28.206 AM 107975687 318 07-JAN-20 10.00.29.256 AM 107975688 321 07-JAN-20 10.00.30.296 AM 107975689 323 07-JAN-20 10.00.31.336 AM 107975690 341 07-JAN-20 10.00.32.376 AM 107975691 350 07-JAN-20 10.00.33.416 AM 107975692 354 07-JAN-20 10.00.34.466 AM 107975693 348 07-JAN-20 10.00.35.506 AM 107975694 331 07-JAN-20 10.00.36.546 AM 107975695 335 07-JAN-20 10.00.37.586 AM 107975696 321 07-JAN-20 10.00.38.626 AM 107975697 314 07-JAN-20 10.00.39.826 AM 107975698 306 07-JAN-20 10.00.40.866 AM 107975699 299 07-JAN-20 10.00.41.906 AM 107975700 298 07-JAN-20 10.00.42.946 AM 107975701 281 07-JAN-20 10.00.43.996 AM 107975702 271 07-JAN-20 10.00.45.036 AM 107975703 266 07-JAN-20 10.00.46.076 AM 107975704 259 07-JAN-20 10.00.47.116 AM 107975705 250 07-JAN-20 10.00.48.156 AM 107975706 242 07-JAN-20 10.00.49.206 AM 107975707 235 07-JAN-20 10.00.50.246 AM 107975708 233 07-JAN-20 10.00.51.276 AM 107975709 294 07-JAN-20 10.00.52.316 AM 107975710 357 07-JAN-20 10.00.53.356 AM 107975711 400 07-JAN-20 10.00.54.626 AM 107975712 395 07-JAN-20 10.00.55.666 AM 107975713 325 07-JAN-20 10.00.56.706 AM 107975714 289 07-JAN-20 10.00.57.746 AM 107975715 235 07-JAN-20 10.00.58.786 AM 107975716 216 07-JAN-20 10.00.59.836 AM 107975717 195 07-JAN-20 10.01.00.876 AM 107975718 191 07-JAN-20 10.01.01.916 AM 107975719 194 07-JAN-20 10.01.02.956 AM 107975720 184 07-JAN-20 10.01.04.006 AM 107975721 187 07-JAN-20 10.01.05.046 AM 107975722 198 07-JAN-20 10.01.06.086 AM 107975723 187 07-JAN-20 10.01.07.116 AM 107975724 192 07-JAN-20 10.01.08.156 AM 107975725 185 07-JAN-20 10.01.12.806 AM 107975726 180 07-JAN-20 10.01.13.846 AM 107975727 179 07-JAN-20 10.01.14.896 AM 107975728 183 07-JAN-20 10.01.15.926 AM 107975729 179 07-JAN-20 10.01.16.956 AM 107975730 178 07-JAN-20 10.01.17.986 AM 107975731 167 07-JAN-20 10.01.19.046 AM 107975732 160 07-JAN-20 10.01.20.076 AM 107975733 156 07-JAN-20 10.01.21.106 AM 107975734 157 07-JAN-20 10.01.22.136 AM 107975735 151 07-JAN-20 10.01.23.166 AM 107975736 139 07-JAN-20 10.01.24.366 AM 107975737 134 07-JAN-20 10.01.25.396 AM 107975738 126 07-JAN-20 10.01.26.426 AM 107975739 124 07-JAN-20 10.01.27.456 AM 107975740 126 07-JAN-20 10.01.28.486 AM 107975741 110 07-JAN-20 10.01.29.537 AM 107975742 110 07-JAN-20 10.01.30.567 AM 107975743 103 07-JAN-20 10.01.31.597 AM 107975744 101 07-JAN-20 10.01.32.627 AM 107975745 89 07-JAN-20 10.01.33.657 AM 107975746 87 07-JAN-20 10.01.34.707 AM 107975747 86 07-JAN-20 10.01.35.737 AM 107975748 77 07-JAN-20 10.01.36.767 AM 107975749 72 07-JAN-20 10.01.37.797 AM 107975750 67 07-JAN-20 10.01.38.837 AM 107975751 66 07-JAN-20 10.01.40.057 AM 107975752 57 07-JAN-20 10.01.41.087 AM 107975753 58 07-JAN-20 10.01.42.117 AM 107975754 50 07-JAN-20 10.01.43.147 AM 107975755 51 07-JAN-20 10.01.44.197 AM 107975756 41 07-JAN-20 10.01.45.227 AM 107975757 43 07-JAN-20 10.01.46.257 AM 107975758 32 07-JAN-20 10.01.47.287 AM 107975759 32 07-JAN-20 10.01.48.317 AM 107975760 18 07-JAN-20 10.01.49.367 AM 107975761 19 07-JAN-20 10.01.50.397 AM 107975762 11 07-JAN-20 10.01.51.427 AM 107975763 8 SQL> select * from (select sql_id, nvl(event,'ON CPU'), count(*) from gv$active_session_history where sample_time between to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and to_date('&date2', 'yyyy-mm-dd hh24:mi:ss') and inst_id in (select instance_number from v$instance) group by sql_id, event order by count(*) desc) where rownum <=10; Enter value for date1: 2020-01-07 10:00:00 old 5: to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and new 5: to_date('2020-01-07 10:00:00', 'yyyy-mm-dd hh24:mi:ss') and Enter value for date2: 2020-01-07 10:01:00 old 6: to_date('&date2', 'yyyy-mm-dd hh24:mi:ss') new 6: to_date('2020-01-07 10:01:00', 'yyyy-mm-dd hh24:mi:ss') SQL_ID NVL(EVENT,'ONCPU') COUNT(*) ------------- ---------------------------------------------------------------- ---------- 14wcvgb1rn4ft CSS initialization 9390 bdnkcpztcmfa2 enq: TX - allocate ITL entry 1853 6vu9pdn1kg9sf enq: TX - allocate ITL entry 1378 7hk3615ca5v88 enq: TX - allocate ITL entry 407 8wc9ccfqgncbk enq: TX - allocate ITL entry 393 ON CPU 195 log file sync 113 14wcvgb1rn4ft CSS operation: action 105 dup992hnmt2g7 enq: TX - allocate ITL entry 91 db file sequential read 72 10 rows selected. SAMPLE_TIME FINAL_INST FINAL_SID FINAL_SERI# FINAL_PROGRAM FINAL_SQL FINAL_EVT INST_ID WAIT_LVL_EVENT COUNT(*) --------------- ---------- --------- ----------- ----------------------------------- --------------- ------------------------------ ---------- ------------------------------ ---------- 20200107100001 2 132 13823 ddsserver@lbesdds034 (TNS V1-V3) latch: enqueue hash chains 2 enq: DX - contention 2 20200107100001 2 2266 64192 ddsserver@lbesdds025 (TNS V1-V3) gdxcgmcuk2w01 latch: enqueue hash chains 2 latch: enqueue hash chains 2 20200107100001 2 2385 57266 ddsserver@lbesdds028 (TNS V1-V3) gdxcgmcuk2w01 latch: enqueue hash chains 2 latch: enqueue hash chains 2 20200107100001 2 5547 63814 oracle@ncrmdb42 (LGWR) log file parallel write 2 log file sync 54 20200107100001 2 6232 56742 ddsserver@lbesdds036 (TNS V1-V3) c90a1waa2j260 ON CPU 2 latch: enqueue hash chains 2 20200107100003 2 5547 63814 oracle@ncrmdb42 (LGWR) log file parallel write 2 log file sync 2 20200107100004 2 5547 63814 oracle@ncrmdb42 (LGWR) log file parallel write 2 log file sync 2 20200107100006 2 5547 63814 oracle@ncrmdb42 (LGWR) log file parallel write 2 log file sync 2 20200107100012 2 673 38068 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 2295 8324 ddsserver@lbesdoc108 (TNS V1-V3) 0vtn1u4a13h5q SQL*Net more data from client 2 enq: TX - allocate ITL entry 3 20200107100012 2 4639 25491 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100012 2 4891 13181 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100012 2 6147 61014 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 9050 8672 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100012 2 9782 59787 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100012 2 17400 49685 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100012 2 15455 7548 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100012 2 17204 59951 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100012 2 17621 40065 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 17882 23656 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 18073 6724 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 18079 56890 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 18452 17715 ddsserver@lbesdds038 (TNS V1-V3) 9yk0vz7rt0f9y ON CPU 2 enq: TX - allocate ITL entry 2 20200107100012 2 18643 44819 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100012 2 19107 42316 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 19308 13081 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 21069 45370 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS operation: action 2 enq: TX - allocate ITL entry 2 20200107100012 2 21653 52824 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100012 2 22014 1581 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100012 2 22094 7671 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100012 2 22219 12754 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100012 2 22496 15025 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS operation: action 2 enq: TX - allocate ITL entry 2 20200107100012 2 23067 44716 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 2295 8324 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 4891 13181 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100013 2 5547 63814 oracle@ncrmdb42 (LGWR) ON CPU 2 log file sync 3 20200107100013 2 5865 20800 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100013 2 6223 57179 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 7843 60865 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100013 2 17900 26168 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100013 2 14990 18378 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100013 2 15455 7548 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 17621 40065 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS operation: query 2 enq: TX - allocate ITL entry 3 20200107100013 2 17882 23656 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100013 2 18073 6724 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100013 2 18450 61646 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 18719 57376 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 19291 49649 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 19576 11704 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 20404 4683 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100013 2 22014 1581 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100013 2 22197 22449 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100013 2 22492 55107 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100013 2 23985 55590 ddsserver@lbesdds032 (TNS V1-V3) 0vtn1u4a13h5q ON CPU 2 enq: TX - allocate ITL entry 2 20200107100014 2 1793 44811 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100014 2 4891 13181 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100014 2 5865 20800 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100014 2 6223 57179 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100014 2 7843 60865 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100014 2 14990 18378 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100014 2 15634 7179 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100014 2 17126 28085 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100014 2 19775 54949 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 6 20200107100014 2 20605 47226 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100014 2 22094 7671 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100015 2 973 64984 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100015 2 5547 63814 oracle@ncrmdb42 (LGWR) log file parallel write 2 log file sync 2 20200107100015 2 5865 20800 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100015 2 6020 12430 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100015 2 7263 56954 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 7 20200107100015 2 9050 8672 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS operation: query 2 enq: TX - allocate ITL entry 3 20200107100015 2 14990 18378 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100015 2 18614 17568 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100015 2 19004 61385 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100015 2 20605 47226 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100015 2 22197 22449 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100015 2 23908 41608 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100016 2 973 64984 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100016 2 4356 56328 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100016 2 5547 63814 oracle@ncrmdb42 (LGWR) ON CPU 2 log file sync 2 20200107100016 2 5865 20800 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100016 2 7263 56954 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100016 2 11956 22030 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100016 2 14991 50076 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100016 2 18432 53034 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100016 2 22197 22449 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100016 2 22404 41409 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100016 2 23247 32518 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100016 2 23413 43715 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100016 2 23908 41608 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100017 2 511 53767 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 7 20200107100017 2 757 4312 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100017 2 1150 60504 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100017 2 1156 3546 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100017 2 1441 10152 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100017 2 1908 19452 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100017 2 3407 21311 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100017 2 4356 56328 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100017 2 5547 63814 oracle@ncrmdb42 (LGWR) log file parallel write 2 log file sync 3 20200107100017 2 6520 6245 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100017 2 7146 46271 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100017 2 9910 30946 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100017 2 14500 34564 ddsserver@lbesdds044 (TNS V1-V3) 5va1trm92d5x9 ON CPU 2 enq: TX - allocate ITL entry 2 20200107100017 2 20800 5701 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100017 2 11321 17447 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100017 2 11956 22030 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 6 20200107100017 2 14584 32609 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100017 2 14775 45001 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100017 2 19098 46820 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100017 2 19215 9512 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100017 2 20404 4683 ddsserver@lbesdds025 (TNS V1-V3) 8kp39xr2fp92x ON CPU 2 enq: TX - allocate ITL entry 4 20200107100017 2 21928 30554 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100017 2 22404 41409 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100017 2 23413 43715 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100017 2 23908 41608 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 511 53767 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100018 2 1150 60504 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 1441 10152 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100018 2 1908 19452 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 2465 35187 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100018 2 4082 51761 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100018 2 5102 32654 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100018 2 5547 63814 oracle@ncrmdb42 (LGWR) ON CPU 2 log file sync 2 20200107100018 2 5752 39043 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100018 2 5931 53039 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 6426 35674 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 7065 23590 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100018 2 7146 46271 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 7454 12406 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100018 2 9408 55991 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 6 20200107100018 2 20800 5701 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100018 2 11020 60824 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 13092 11851 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 6 20200107100018 2 13634 57958 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 14489 41380 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 7 20200107100018 2 15730 39115 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100018 2 17030 60360 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100018 2 17211 55733 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100018 2 17614 46421 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 18731 56730 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 7 20200107100018 2 20996 47652 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 9 20200107100018 2 22285 39738 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 22385 24032 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100018 2 22404 41409 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100018 2 22586 52593 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100018 2 22776 9491 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 23413 43715 ddsserver@lbesdds044 (TNS V1-V3) 14wcvgb1rn4ft CSS operation: action 2 enq: TX - allocate ITL entry 3 20200107100018 2 23908 41608 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100018 2 24007 49673 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100019 2 511 53767 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100019 2 757 4312 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100019 2 1050 53724 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100019 2 1150 60504 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100019 2 1347 44663 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100019 2 1441 10152 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100019 2 1908 19452 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 6 20200107100019 2 2010 57096 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100019 2 2465 35187 ddsserver@lbesdoc108 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100019 2 3885 51427 ddsserver@lbesdds038 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100019 2 3888 61705 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100019 2 4150 11066 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100019 2 4356 56328 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4 20200107100019 2 5102 32654 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100019 2 5191 22074 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 7 20200107100019 2 5390 2479 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100019 2 5752 39043 ddsserver@lbesdds025 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 2 20200107100019 2 5931 53039 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 3 20200107100019 2 7065 23590 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 9 20200107100019 2 7241 45081 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 5 20200107100019 2 7454 12406 ddsserver@lbesdds032 (TNS V1-V3) 14wcvgb1rn4ft CSS initialization 2 enq: TX - allocate ITL entry 4复制
查看活动会话上升时间主要是CSS initialization等待，通过堵塞链表还发现CSS initialization等待进一步导致enq: TX - allocate ITL entry等待。
二、CSS initialization等待的SQL_ID 14wcvgb1rn4ft
SQL> select * from table(dbms_xplan.display_cursor('14wcvgb1rn4ft')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 14wcvgb1rn4ft, child number 0 ------------------------------------- INSERT INTO CONTEXT_DATA(VERSION_ID, CONTEXT_CODE, ENTRY_DATE, OBJECT_NAME, DATA_CONTENT, BE_ID, STATUS, CLOB_ID, CONTEXT_SEQ) VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9) --------------------------------------------------------- | Id | Operation | Name | Cost | --------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | | 1 | LOAD TABLE CONVENTIONAL | CONTEXT_DATA | | --------------------------------------------------------- Note ----- - cpu costing is off (consider enabling it) SQL> desc orders.CONTEXT_DATA; Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------------- -------- CONTEXT_SEQ NOT NULL NUMBER(20) CONTEXT_CODE NOT NULL NUMBER(20) OBJECT_NAME NOT NULL VARCHAR2(64) ENTRY_DATE NOT NULL DATE BE_ID NOT NULL NUMBER(10) PARTITION_ID NOT NULL NUMBER(8) DATA_CONTENT CLOB STATUS VARCHAR2(1) VERSION_ID NUMBER(4) CLOB_ID NUMBER(20) DBMS_METADATA.GET_DDL(UPPER('TABLE'),UPPER('CONTEXT_DATA'),UPPER('ORDERS')) -------------------------------------------------------------------------------- CREATE TABLE "ORDERS"."CONTEXT_DATA" ( "CONTEXT_SEQ" NUMBER(20,0) NOT NULL ENABLE, "CONTEXT_CODE" NUMBER(20,0) NOT NULL ENABLE, "OBJECT_NAME" VARCHAR2(64) NOT NULL ENABLE, "ENTRY_DATE" DATE NOT NULL ENABLE, "BE_ID" NUMBER(10,0) NOT NULL ENABLE, "PARTITION_ID" NUMBER(8,0) DEFAULT mod(to_char(sysdate,'DD'),3) NOT NULL ENABLE, "DATA_CONTENT" CLOB, "STATUS" VARCHAR2(1) DEFAULT 0, "VERSION_ID" NUMBER(4,0) DEFAULT 0, "CLOB_ID" NUMBER(20,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) PARTITION BY LIST ("BE_ID") SUBPARTITION BY HASH ("CONTEXT_SEQ") SUBPARTITIONS 1 (PARTITION "P_L_11" VALUES (11) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "D_CUST_01" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ( SUBPARTITION "P_L_11_H_1" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_2" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_3" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_4" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_5" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_6" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_7" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_8" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_9" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_10" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_11" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_12" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_13" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_14" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_15" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_16" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_17" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_18" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_19" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_20" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_21" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_22" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_23" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_24" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_25" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_26" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_27" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_28" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_29" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_30" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_31" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS , SUBPARTITION "P_L_11_H_32" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_01" ) TABLESPACE "D_CUST_01" NOCOMPRESS ) , PARTITION "P_L_99" VALUES (99) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "D_CUST_02" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ( SUBPARTITION "P_L_99_H_1" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_2" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_3" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_4" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_5" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_6" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_7" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_8" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_9" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_10" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_11" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_12" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_13" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_14" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_15" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_16" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_17" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_18" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_19" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_20" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_21" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_22" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_23" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_24" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_25" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_26" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_27" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_28" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_29" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_30" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_31" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS , SUBPARTITION "P_L_99_H_32" LOB ("DATA_CONTENT") STORE AS SECUREFILE ( TABLESPACE "D_CUST_02" ) TABLESPACE "D_CUST_02" NOCOMPRESS ) )复制
三、关于CSS initialization等待
RAC（或者使用ASM的单实例）数据库环境下，当前台进程需要执行direct IO操作时就需要向CSSD进程进行注册，此时该前台进程发生CSS initialization等待。oracle 11g和12C后，CSS initialization等待事件的触发机智并没有改变，该event是一个direct IO的预期行为，任何前台进程在需要进行direct IO的情况下都必须进行一次CSS注册，之后就可以被运行direct IO操作。
LOB对象第一次操作时是需要进行direct IO，但是如果该LOB对象有cache则之后的操作无需进行direct IO，也不会出现CSS initialization等待。通常不建议对LOB字段频繁操作，如果确实需要使用LOB字段，需要使用cache特性，注意的是对LOB对象cache而不是对表进行cache。
四、优化建议：
1、减少LOB字段的调用（程序看看能否在数据库层面只存储URL，LOB对象存储在文件系统中，Oracle也是建议大对象不适宜存储在数据表，最好只存储URL在数据表）
2、将LOB字段修改为varchar（oracle 12c提供了扩展varchar，可以支持32k的varchar2，不过需要修upgrade方式重启数据库、修改max_string_size参数、运行相关脚本打开扩展varchar2特性）
12C中，varchar2,nvarchar2和raw类型从之前的4K扩展到32K，升级到12C后，参数max_string_size默认值是standard，即不改变varchar2、nvarchar2、raw数据类型的大小限制
SQL> show parameter max_string_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string STANDARD复制
开启"扩展数据类型"功能:
SQL> alter system set max_string_size=extended scope=both; alter system set max_string_size=extended scope=both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration SQL> shutdown immediate SQL> startup upgrade; SQL> alter system set max_string_size=extended scope=both; SQL> @$ORACLE_HOME/rdbms/admin/utl32k.sql复制
修改以后要执行以下脚本，升级后可能会有部分对象变得无效，需要重新编译下一无效对象
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql复制
升级以后再重启数据库
如果varchar2,nvarchar2和raw的大小超过4k，oracle内部会以LOB的方式存储（oracle内部自己维护，不建议用户直接操作）。
3、添加LOB字段的cache属性（LOB对象的segment大小太大，达到了几十GB，不适合放在db cache中）
MOS上文章Recommendation About LOB Columns (cache/no Cache) In WebCenter Sites Schemas (Doc ID 2168834.1)
APPLIES TO:
Oracle WebCenter Sites - Version 11.1.1.8.0 and later
Information in this document applies to any platform.
GOAL
In the WebCenter Sites schema there are a lot of tables with LOB columns, that could be candidates to be CACHED.
We are wondering, if are there any recommendations about change the status of LOB columns in the WebCenter Sites schema to be CACHED?
SOLUTION
There are no out-of-the box recommendations for LOB columns as development have not verified it internally.
You can discuss with your DBA and based of table statistics, tables DDL can be altered as desired. Following link describes the LOB column options
https://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm#CIHEBABG
CACHE / NOCACHE / CACHE READS
When creating tables that contain LOBs, use the cache options according to the guidelines in Table 5-3, “When to Use CACHE, NOCACHE, and CACHE READS”:
Table 5-3 When to Use CACHE, NOCACHE, and CACHE READS
Cache Mode Read Write
CACHE READS Frequently Once or occasionally
CACHE Frequently Frequently
NOCACHE (default) Once or occasionally Never
CACHE / NOCACHE / CACHE READS: LOB Values and Buffer Cache
CACHE: Oracle places LOB pages in the buffer cache for faster access.
NOCACHE: As a parameter in the STORE AS clause, NOCACHE specifies that LOB values are not brought into the buffer cache.
CACHE READS: LOB values are brought into the buffer cache only during read and not during write operations.
NOCACHE is the default for both SECUREFILE and BASICFILE LOBs.
Note:
Using the CACHE option results in improved performance when reading and writing data from the LOB column. However, it can potentially age other non-LOB pages out of the buffer cache prematurely.
Please test the changes in test environment thoroughly before implementing them in production environment. Any changes to the webcenter sites schema are considered as customization and will need to be maintained by the customer.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
2025年4月中国数据库流行度排行榜：OB高分复登顶，崖山稳驭撼十强
墨天轮编辑部
1897次阅读
2025-04-09 15:33:27
2025年3月国产数据库大事记
墨天轮编辑部
872次阅读
2025-04-03 15:21:16
2025年3月国产数据库中标情况一览：TDSQL大单622万、GaussDB大单581万……
通讯员
605次阅读
2025-04-10 15:35:48
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
553次阅读
2025-04-15 17:24:06
征文大赛 |「码」上数据库—— KWDB 2025 创作者计划启动
KaiwuDB
497次阅读
2025-04-01 20:42:12
数据库，没有关税却有壁垒
多明戈教你玩狼人杀
491次阅读
2025-04-11 09:38:42
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
475次阅读
2025-04-18 14:18:38
国产数据库需要扩大场景覆盖面才能在竞争中更有优势
白鳝的洞穴
466次阅读
2025-04-14 09:40:20
Oracle SQL 执行计划分析与优化指南
Digital Observer
451次阅读
2025-04-01 11:08:44
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
450次阅读
2025-04-08 09:12:48
