æ¬èšäºã¯ 2026 幎 8 æ 18 æ¥ ã«å
¬éãããã Characterizing SQL*Net latency in your application for Oracle Database@AWS migrations ãã翻蚳ãããã®ã§ãã Oracle Database@AWS (ODB@AWS) ã¯ãAWS ããŒã¿ã»ã³ã¿ãŒå
ã§ Oracle Cloud Infrastructure (OCI) ã管çãã Oracle Exadata ã€ã³ãã©ã¹ãã©ã¯ãã£ãå©çšã§ãããµãŒãã¹ã§ããODB@AWS ã¯ãAmazon Virtual Private Cloud (Amazon VPC) å
ã§çšŒåãã Amazon Elastic Compute Cloud (Amazon EC2)ã Amazon Elastic Container Service (Amazon ECS)ã Amazon Elastic Kubernetes Service (Amazon EKS) ãªã©ã® AWS ãµãŒãã¹ãšã®éã§ãäœã¬ã€ãã³ã·ãŒã®ãããã¯ãŒã¯æ¥ç¶ãæäŸããŸããã¢ããªã±ãŒã·ã§ã³ãã¹ããš Exadata éã® SQL*Net ãã©ãã£ãã¯ã¯ ODB Peering ãçµç±ããAmazon VPC ãš ODB ãããã¯ãŒã¯éã§ãã©ã€ããŒãã«ã«ãŒãã£ã³ã°ãããŸãã æ¬èšäºã§ã¯ãããŒã¿ããŒã¹ãšã¢ããªã±ãŒã·ã§ã³ã®ã¯ãŒã¯ããŒãã ODB@AWS ãžç§»è¡ããæºåãé²ããããã®ææ³ã玹ä»ããŸãããã®ææ³ã䜿ããšããªã³ãã¬ãã¹ã®ã¯ãŒã¯ããŒãã SQL*Net ã¬ã€ãã³ã·ãŒã«ææãã©ãããè©äŸ¡ãã ODB@AWS ã®ãã«ãã¯ã©ãŠãã¢ãŒããã¯ã㣠ã§å¢ãã SQL*Net ã¬ã€ãã³ã·ãŒã®åœ±é¿ãææ¡ã§ããŸããããã«ãODB@AWS ã®ç§»è¡åè£ãšãªãã¯ãŒã¯ããŒãã«ãããæ¥ç¶ãšãããã€ã®èæ
®äºé
ã解説ããŸãã ODB@AWS ç§»è¡ã§ SQL*Net ã¬ã€ãã³ã·ãŒã®è©äŸ¡ãéèŠãªçç± ODB@AWS ãžã®ç§»è¡ã§ã¯ãã¯ãŒã¯ããŒãã SQL*Net ã¬ã€ãã³ã·ãŒã®å¢å ã«ææãã©ãããéèŠãªæ€èšãã€ã³ãã«ãªããŸããæ¥µããŠçæé (ããŒã¿ããŒã¹åŠçæéããµãããªç§) ã§ãé »ç¹ (æ¯ç§æ°ååã®å®è¡) ã«ããã€å°æ°ã®ã»ãã·ã§ã³ã«éäžããŠå®è¡ããã SQL æãå«ãã¯ãŒã¯ããŒãã¯ãSQL*Net ã©ãŠã³ãããªããã¬ã€ãã³ã·ãŒãæ°çŸãã€ã¯ãç§å¢ããã ãã§ã圱é¿ãåããã¢ããªã±ãŒã·ã§ã³ããã©ãŒãã³ã¹ã«çŽæ¥é¿ãå¯èœæ§ããããŸããæ¬èšäºã®ææ³ã䜿ãã°ãã¢ããªã±ãŒã·ã§ã³ã®ã¯ãŒã¯ããŒãããã® 3 ã€ã®ç¹æ§ãæã€ãã©ããã倿ãã該åœããå Žåã¯ç§»è¡åã«åœ±é¿ãå®éåã§ããŸãã 1 ã€ã®ååã¯åžžã«æãç«ã¡ãŸããã¯ã©ã€ã¢ã³ããããã¯ãŒã¯ã®ã¬ã€ãã³ã·ãŒãäžãã£ãŠã¢ããªã±ãŒã·ã§ã³ããã©ãŒãã³ã¹ãæªåããããšã¯ãããŸããããå¿
ãæ¹åããããã§ããããŸãããOracle 㯠SQL*Net ã®ã©ãŠã³ãããªãããæžããããã®ã¬ã€ãã³ã¹ãäœå幎ãåããå
¬éããŠããŸããã¹ãã¢ãããã·ãŒãžã£ãé
åãã§ããããã«ã¯åŠçã¯ãåŠçããµãŒããŒåŽã«ç§»ããããŸãšããŠå®è¡ãããããããšã§ãã¯ã©ã€ã¢ã³ããšãµãŒããŒéã®ã©ãŠã³ãããªããåæ°ãåæžããŸããOracle èªèº«ã® Net Services ããã¥ã¡ã³ãã«ã次ã®èšè¿°ããããŸãã ããããã¯ãŒã¯ãè¶ããã©ãŠã³ãããªããã®åæ°ãæžããããã¢ããªã±ãŒã·ã§ã³ããã¥ãŒãã³ã°ããããšãããããã¯ãŒã¯ããã©ãŒãã³ã¹ãæ¹åããæåã®æ¹æ³ã§ããã ãã ããããããæé©åãæœããŠã SQL*Net ã¬ã€ãã³ã·ãŒã«åŒ·ã圱é¿ãããã¢ãžã¥ãŒã«ãã¢ããªã±ãŒã·ã§ã³ã«æ®ã£ãŠããå Žåã¯ãæ¬ææ³ã§ç§»è¡åã«ã¬ã€ãã³ã·ãŒæåºŠãç¹å®ããå®éåã§ããŸãã ææ³ã®æŠèŠ æ¬ææ³ã¯ Oracle ã®æšæºçãªããã©ãŒãã³ã¹ããŒã« (Automatic Workload Repository (AWR)ãActive Session HistoryãSQL Trace) ã䜿ããæ¬¡ã® 3 ã€ã®ãã§ãŒãºã§æ§æãããŸãã ãã§ãŒãº 1: AWR ã¹ã¯ãªãŒãã³ã°ã§åè£ãšãªã SQL æãç¹å®ããã ãã§ãŒãº 2: ãã§ãŒãº 1 ã§æãã£ãåè£ã® SQL æã SQL Execution Session Density (SESD) ã§æ€èšŒããã ãã§ãŒãº 3: SQL Trace ãš Client Request Elapsed Time (CRET) ã®åæã§ SQL*Net ã¬ã€ãã³ã·ãŒãžã®æåºŠãææ¡ããã ææ³ã®è©³çް Client Request Elapsed Time (CRET) ãšã¯ æ¬ææ³ã¯ãOracle ã®ã¢ã€ãã«åŸ
æ©ã€ãã³ãã SQL*Net ã¬ã€ãã³ã·ãŒæåºŠã®äž»èŠãªèšæž¬ææ®µã«äœ¿ãæ°ããã¢ãããŒãã§ãã察象ãšãªãã¢ã€ãã«åŸ
æ©ã¯ SQL*Net message from client (SNMFC) ã§ããé£ç¶ãã 2 ã€ã® SNMFC åŸ
æ©ã«æãŸããåºéããæ¬èšäºã§ã¯ Client Request Elapsed Time (CRET) ã€ãã³ããšåŒã³ãŸãã ã¯ã©ã€ã¢ã³ãã¢ããªã±ãŒã·ã§ã³ãã SQL*Net çµç±ã§ããŒã¿ããŒã¹ã€ã³ã¹ã¿ã³ã¹ã«éããã SQL ãªã¯ãšã¹ãã¯ãããããæ¬¡ã® 3 段éã®ã¿ã€ã ã©ã€ã³ããã©ããŸãã ã¯ã©ã€ã¢ã³ãæé + ã¯ã©ã€ã¢ã³ããããµãŒããŒãžã®ãããã¯ãŒã¯æé: ã¯ã©ã€ã¢ã³ãã¢ããªã±ãŒã·ã§ã³ã¯ååã®çµæãåŠçã (ãŸãã¯ã¢ã€ãã«ç¶æ
ã®ãŸãŸãšãªã)ãæ¬¡ã® SQL ãªã¯ãšã¹ãã SQL*Net çµç±ã§ããŒã¿ããŒã¹ãµãŒããŒãžéä¿¡ããŸããã¯ã©ã€ã¢ã³ãæéã«ã¯ãèšç®åŠçããŠã§ãå±€ãšã®ããåããGUI ã¢ããªã±ãŒã·ã§ã³ã人éã®å
¥åãåŸ
ã€ãããªã¢ã€ãã«æéãå«ãŸããŸãããã®æ®µéã®éãããŒã¿ããŒã¹ã»ãã·ã§ã³ã¯ãã£ã𿬡ã®ãªã¯ãšã¹ãã®å°çãåŸ
〠SNMFC åŸ
æ©ã®ç¶æ
ã«ãããŸãã ããŒã¿ããŒã¹åŠçæé: ããŒã¿ããŒã¹ãµãŒããŒããªã¯ãšã¹ããåãåããåŠçããå¿çãæºåããŸãã ãµãŒããŒããã¯ã©ã€ã¢ã³ããžã®ãããã¯ãŒã¯æé: ããŒã¿ããŒã¹ãµãŒããŒãçµæã»ãããŸãã¯å®äºã³ãŒãã SQL*Net çµç±ã§ã¯ã©ã€ã¢ã³ãã¢ããªã±ãŒã·ã§ã³ãžéä¿¡ããŸããéä¿¡çŽåŸãããŒã¿ããŒã¹ã»ãã·ã§ã³ã¯åã³ SNMFC åŸ
æ©ã«å
¥ããŸãã æ®µé 1 ãšæ®µé 3 ã®åèšãéããŒã¿ããŒã¹æé (Non-Database Time) ã§ããæ®µé 2 ã¯å®å
šã«ããŒã¿ããŒã¹å
éšã®åŠçã§ãããSQL*Net ã®ã¯ã©ã€ã¢ã³ããããã¯ãŒã¯ã¬ã€ãã³ã·ãŒãšã¯ç¡é¢ä¿ã§ãã CRET = Database Time + Client Time + SQL*Net RTT å³ 1: ã¯ã©ã€ã¢ã³ããªã¯ãšã¹ãã®ã¿ã€ã ã©ã€ã³ãé£ç¶ãã 2 ã€ã® message from client åŸ
æ©ã 1 ã€ã® CRET ã€ãã³ããæã CRET ææ³ã®çŽ¹ä» ãããã㯠CRET ææ³ãããã§ãŒãº 1 (AWR ã¹ã¯ãªãŒãã³ã°)ããã§ãŒãº 2 (SQL Execution Session Density)ããã§ãŒãº 3 (SQL Trace ãš CRET åæ) ã® 3 ã€ã«åããŠè©³ããèŠãŠãããŸãã ãã§ãŒãº 1: AWR ã¹ã¯ãªãŒãã³ã° (åè£ãšãªã SQL æã®ç¹å®) ãŸããã¬ã€ãã³ã·ãŒã«ææãšæãããã¢ããªã±ãŒã·ã§ã³åŠçãåããŠããæéãå«ã AWR ã¬ããŒããçšæããããããŒã«èšèŒãããçµéæéã確èªããŸããããšãã°æ¬¡ã®å³ã¯ã30 åéã察象ãšãã AWR ã¬ããŒãã®ããããŒã»ã¯ã·ã§ã³ã§ãã å³ 2: 30 åéã®ã¹ãããã·ã§ããæéã瀺ã AWR ã¬ããŒãã®ããã㌠ã¹ã¯ãªãŒãã³ã°ã®ç®çã¯ãé »ç¹ãã€çæéã§å®è¡ããã SQL æãã€ãŸãæ¯ç§æ°ååå®è¡ããããŒã¿ããŒã¹åŠçæéããã€ã¯ãç§ã¬ãã«ã® SQL æãç¹å®ããããšã§ããSQL Ordered by Executions ã»ã¯ã·ã§ã³ãéããäžäœã®å SQL æã«ã€ããŠæ¯ç§ãããã®å®è¡åæ° (ç·å®è¡åæ° Ã· AWR ã®çµéæé (ç§)) ãšå¹³åããŒã¿ããŒã¹åŠçæé (ç·çµéæé ÷ å®è¡åæ°) ãèšç®ããŸããããŒã¿ããŒã¹åŠçæéããµãããªç§ã§ããã€æ¯ç§æ°ååå®è¡ãããŠãã SQL æãã¬ã€ãã³ã·ãŒæåºŠã®åè£ã§ããåŠçæéãæ°ããªç§ã«åã¶ SQL æã¯ã1 åã®å®è¡ãããã®ããŒã¿ããŒã¹åŠçæéããå¢å åã®ãããã¯ãŒã¯æéã倧ããäžåããããäžè¬ã« SQL*Net ã¬ã€ãã³ã·ãŒã®å¢å ã蚱容ã§ããŸãã ãã§ãŒãº 1 ã¹ã¯ãªãŒãã³ã°ã®äŸ å®éã®äŸãèŠãŠã¿ãŸãã360 å (21,600 ç§) ã察象ãšãã AWR ã¬ããŒãã« SQL Ordered by Executions ã»ã¯ã·ã§ã³ãå«ãŸããŠããŸããæ¬¡ã®ã¹ã¯ãªãŒã³ã·ã§ããã¯ãã®ã»ã¯ã·ã§ã³ã®äžäœ 5 è¡ã§ãç¶ã衚ã¯åè¡ã®ãã§ãŒãº 1 åæçµæã§ãã å³ 3: å®è¡åæ°ã®å€ãäžäœ 5 ã€ã® SQL æã瀺ã AWR ã® SQL ordered by executions ã»ã¯ã·ã§ã³ SQL_ID çµéæé (ç§) å®è¡åæ° å®è¡åæ°/ç§ å¹³å DB æé è©äŸ¡ âŠf243rp 10,966 112,622,398 5,214/ç§ 97 µs åè£ããã§ãŒãº 2 ãžé²ãã âŠt4xvug 9,809 97,577,695 4,517/ç§ 101 µs åè£ããã§ãŒãº 2 ãžé²ãã âŠr5tj7z 15,302 57,889,149 2,680/ç§ 264 µs åè£ããã§ãŒãº 2 ãžé²ãã âŠr8qyvu 13,509 40,966,821 1,897/ç§ 330 µs åè£ããã§ãŒãº 2 ãžé²ãã âŠ9uyp0z 18,471 24,081,444 1,115/ç§ 767 µs åè£ã®å¯èœæ§ãããDB åŠçæéã¯ããªç§ã«è¿ã¥ããŠãããããŸã ãµãããªç§ããã§ãŒãº 2 ãžé²ãã ãã§ãŒãº 2: SQL Execution Session Density (SESD) ãã§ãŒãº 1 ã§ã¯ AWR ã䜿ããçæéãã€é »ç¹ã«å®è¡ãããåè£ã® SQL æãç¹å®ããŸãããã ããSQL Ordered by Executions ã»ã¯ã·ã§ã³ã®å®è¡åæ°ã¯å
šã»ãã·ã§ã³ãåç®ããã€ã³ã¹ã¿ã³ã¹å
šäœã®éèšå€ã§ãããã®å®è¡ã 1 ã»ãã·ã§ã³ã«ãããã®ã 1,000 ã»ãã·ã§ã³ã«ãããã®ãã¯ãAWR ããã¯ããããŸããããã§ãŒãº 3 ã«é²ãåã«ãåè£ã® SQL æã 1 ã€ããããã¯ããå°æ°ã®ã»ãã·ã§ã³ããå®è¡ãããŠããããšã確èªããŸãã èãæ¹ã¯ã·ã³ãã«ã§ãã1 åã®å®è¡ã«ããŒã¿ããŒã¹åŠçæé 100µs ãèŠãã SQL æã®å Žåã1 ã€ã®ã»ãã·ã§ã³ãæ¯ç§ 10,000 åå®è¡ããã±ãŒã¹ãšã100 ã»ãã·ã§ã³ãããããæ¯ç§ 100 åå®è¡ããã±ãŒã¹ã¯ãAWR äžã§ã¯åºå¥ã§ããŸãããã¬ã€ãã³ã·ãŒã«ææãªã®ã¯åè
ã ãã§ããå°æ°ã®ã»ãã·ã§ã³ã«å®è¡ãéäžããŠããç¶æ
㯠High SESD ã§ããã§ãŒãº 3 ã®åæå¯Ÿè±¡ã«ãªããŸãã倿°ã®ã»ãã·ã§ã³ã«å®è¡ã忣ããŠããç¶æ
㯠Low SESD ã§ããã§ãŒãº 3 ã¯äžèŠã§ãã SESD ã¯ã該åœãã SQL_ID ãå®è¡ããŠããã»ãã·ã§ã³ã®ãŠããŒã¯æ°ã Active Session History ã«åãåãããã V$SQL ã® USERS_EXECUTING åãåç
§ããããããã¯ã¢ããªã±ãŒã·ã§ã³ã®åäœãææ¡ããŠããã¢ããªã±ãŒã·ã§ã³ãªãŒããŒã«ç¢ºèªããããšãã£ãæ¹æ³ã§å€æã§ããŸããHigh SESD ãªããã§ãŒãº 3 ã®åæã«é²ã¿ãLow SESD ãªãé²ã¿ãŸããã SQL Execution Session Density ã®è©³çް å
ã®ãã§ãŒãº 1 ã® AWR ã«åºãŠãã SQL_ID 52cn24qf243rp ã¯ãæ¯ç§ 5,214 åå®è¡ãããå¹³åããŒã¿ããŒã¹åŠçæé㯠97µs ã§ãããSESD ãè©äŸ¡ããçµæããã® SQL_ID ãå®è¡ããŠããã»ãã·ã§ã³ãããã 5 ã€ã ã£ããšããŸããåã»ãã·ã§ã³ã¯æ¯ç§ããã 1,043 åã®å®è¡ãæ
ãããšã«ãªããCRET ãµã€ã¯ã« 1 åãããã®å®è¡äºç®ã¯çŽ 959µs ã§ã (1,043 à 959µs = 1,000,000µs = 1 ç§)ãCRET ãµã€ã¯ã«æéã 959µs ãšçããããSQL*Net ã¬ã€ãã³ã·ãŒã®å€åãçžå¯Ÿçãªããã©ãŒãã³ã¹ã«å€§ãã圱é¿ããããŸããããã®åœ±é¿ãè©äŸ¡ããã®ããã§ãŒãº 3 ã§ãããã® SQL æã¯ãã§ãŒãº 3 ã®åæã«æé©ãªåè£ãšèšããŸããéã«ãåãæ¯ç§ 5,214 åã®å®è¡ã 200 ã»ãã·ã§ã³ã«åæ£ããŠããã°ãåã»ãã·ã§ã³ã®å®è¡ééã¯ããã 38ms ãšãªãããã§ãŒãº 3 ã®åæã¯äžèŠã§ãã ãã§ãŒãº 3: SQL Trace ãš CRET åæ ãããŸã§ã§ããã§ãŒãº 1 ã®ã¹ã¯ãªãŒãã³ã°ã§çæéãã€é »ç¹ã«å®è¡ããã SQL æãç¹å®ãããã§ãŒãº 2 ã® SESD åæã§ãã®å®è¡ãããå°æ°ã®ã»ãã·ã§ã³ã«éäžããŠããããšã確èªããŸãããæ®ãäœæ¥ã¯ã察象ã»ãã·ã§ã³ã® 1 ã€ããã¬ãŒã¹ããCRET ã€ãã³ãã®åæã§å®éã®ã¬ã€ãã³ã·ãŒæåºŠãå®éåããããšã§ãã SQL Trace ã®æå¹å åè£ã® SQL æãå®è¡äžã®ã»ãã·ã§ã³ãç¹å®ããåŸ
æ©ã€ãã³ãä»ãã§ãã¬ãŒã¹ãæå¹ã«ããŸãã EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => &sid, serial_num => &serial#, waits => TRUE, binds => FALSE); å®è¡ã®ä»£è¡šçãªãµã³ãã«ãååŸã§ãããããååãªæéãã¬ãŒã¹ãç¶ç¶ããŸãããã¬ãŒã¹ãçµãã£ããç¡å¹ã«ããŸãã EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => &sid, serial_num => &serial#); CRET ãã©ã°ã©ã: å®äŸ 次ã®å³ã¯ãSQL Trace ãã¡ã€ã«ããæãåºããå®éã® CRET ãã©ã°ã©ãã§ããé£ç¶ãã 2 ã€ã® SNMFC åŸ
æ© (èµ€ããã€ã©ã€ããããéšå) ããã©ã°ã©ããæãã§ããŸãã å³ 4: é£ç¶ãã 2 ã€ã® message from client åŸ
æ©ããã€ã©ã€ããã SQL Trace ã®æç² CRET ã®ç¶ç¶æéã¯ã2 ã€ã® SNMFC ã瀺ã tim= ã®å·®åã§ã1,297,258,033,879 ãã 1,297,258,032,801 ãåŒãã 1,078µs ã§ããåŸãã® SNMFC ã瀺ã ela= å€ (947µs) ãããã® CRET ãµã€ã¯ã«ã«ãããéããŒã¿ããŒã¹æéã«çžåœããŸããCRET ã®ç¶ç¶æéããéããŒã¿ããŒã¹æéãåŒããšããŒã¿ããŒã¹æéãæ±ãŸãã1,078 ãã 947 ãåŒãã 131µs ãšãªããŸãã CRET åæããŒã« 次㮠4 ã€ã® AWK ã¯ã³ã©ã€ããŒã CRET åæããŒã«ãæ§æããŸããããããåŠç察象ã®ãã¬ãŒã¹ãã¡ã€ã«åã第 1 åŒæ°ãšããŠåãåããå®è¡ã«ã¯ gawk(1) ãå¿
èŠã§ããã¯ã³ã©ã€ããŒã¯ SQL Trace ãã¡ã€ã«ãçŽæ¥åŠçãããããOracle ã®ã¯ã©ã€ã¢ã³ããœãããŠã§ã¢ã¯äžèŠã§ãã4 ã€ã®ã¯ã³ã©ã€ããŒãåºåããå
å®¹ã¯æ¬¡ã®ãšããã§ãã ã¯ã³ã©ã€ã㌠1 â CRET ãµããªãŒ: ç·çµéæéãCRET æ°ãCRET ã¬ãŒã (CRET/ç§)ã ã¯ã³ã©ã€ã㌠2 â SNMFC çµéæéã®ããŒã»ã³ã¿ã€ã«: éããŒã¿ããŒã¹æéã®ååž (MinãP50ãP90ãP99ãMax)ã ã¯ã³ã©ã€ã㌠3 â CRET çµéæéã®ããŒã»ã³ã¿ã€ã«: ã©ãŠã³ãããªããå
šäœã®æéååž (MinãP50ãP90ãP99ãMax)ã ã¯ã³ã©ã€ã㌠4 â ã¬ã€ãã³ã·ãŒåœ±é¿ã®äºæž¬: SQL*Net RTT ã 100µsã150µsã200µs å¢ããå Žåã®å®è¡æéã®å¢å äºæž¬ã ã¯ã³ã©ã€ã㌠1 â CRET ãµããªãŒ grep -i 'message from client' $1 | gawk '{p=match($0,/tim=[0-9]+/); t=substr($0,p+4,RLENGTH-4)+0; if(NR==1) s=t; e=t; n++} END{el=(e-s)/1000000; print sprintf("CRET Analysis | Elapsed: %.1f sec (%.2f min) | CRETs: %d | Rate: %.2f/sec", el, el/60, n-1, (n-1)/el)}' ã¯ã³ã©ã€ã㌠2 â SNMFC çµéæéã®ããŒã»ã³ã¿ã€ã« grep -i 'message from client' $1 | gawk '{p=match($0,/ela= *[0-9]+/); v=substr($0,p,RLENGTH); sub(/ela= */,"",v); val=v+0; if(NR==1) mn=val; if(val<mn) mn=val; a[NR]=val} END{n=asort(a); p50=a[int(n*0.5+0.5)];p90=a[int(n*0.9+0.5)];p99=a[int(n*0.99+0.5)]; mx=a[n]; print sprintf("SNMFC Elapsed (us): Min: %d | P50: %d | P90: %d | P99: %d | Max: %d", mn, p50, p90, p99, mx)}' ã¯ã³ã©ã€ã㌠3 â CRET çµéæéã®ããŒã»ã³ã¿ã€ã« grep -i 'message from client' $1 | gawk '{p=match($0,/tim=[0-9]+/); t=substr($0,p+4,RLENGTH-4)+0; if(NR>1){d=t-prev; if(NR==2) mn=d; if(d<mn) mn=d; c[NR-1]=d} prev=t} END{n=asort(c);p50=c[int(n*0.5+0.5)]; p90=c[int(n*0.9+0.5)];p99=c[int(n*0.99+0.5)]; mx=c[n]; print sprintf("CRET Elapsed (us): Min: %d | P50: %d | P90: %d | P99: %d | Max: %d", mn, p50, p90, p99, mx)}' ã¯ã³ã©ã€ã㌠4 â ã¬ã€ãã³ã·ãŒåœ±é¿ã®äºæž¬ grep -i 'message from client' $1 | gawk '{p=match($0,/tim=[0-9]+/); t=substr($0,p+4,RLENGTH-4)+0; if(NR==1) s=t; e=t; n++}END{crets=n-1; el=(e-s);print sprintf("Latency Impact: +100us: +%.0fs (+%.1f%%) | +150us: +%.0fs (+%.1f%%) | +200us: +%.0fs (+%.1f%%)", crets*100/1000000, (crets*100/el)*100,crets*150/1000000, (crets*150/el)*100,crets*200/1000000, (crets*200/el)*100)}' å®è·µäŸ: CRET åæã®åºå 次ã®ã¹ã¯ãªãŒã³ã·ã§ããã¯ãã¯ã©ã€ã¢ã³ãåŽã®å®è¡ãã¿ãŒã³ããŸã£ããç°ãªã 2 ã€ã®ãã¬ãŒã¹ãã¡ã€ã« (ãããã CRET ã€ãã³ã 10,000 ä»¶) ã«å¯ŸããŠã4 ã€ã®ã¯ã³ã©ã€ããŒãå®è¡ããäŸã§ããäžæ¹ã¯ã¿ã€ãã«ãŒããããäžæ¹ã¯ãªã¯ãšã¹ãã®åéã«ã¯ã©ã€ã¢ã³ãåŽã®åŠçåŸ
ã¡ã倧ããå
¥ããã¿ãŒã³ã§ããã¯ã³ã©ã€ããŒã®åºåãããããããã®ãã¿ãŒã³ã®ã¬ã€ãã³ã·ãŒæåºŠãå®éçã«ããããŸãã å³ 5: ã¿ã€ãã«ãŒãã®ãã¬ãŒã¹ãšã¯ã©ã€ã¢ã³ãé
å»¶ã®ãã¬ãŒã¹ã«å¯Ÿãã 4 ã€ã® CRET ã¯ã³ã©ã€ããŒã®åºå ã¯ã©ã€ã¢ã³ããã¿ã€ãã«ãŒããããã¬ãŒã¹ (ora_196424.trc) ãã®ãã¬ãŒã¹ã®å¯Ÿè±¡æé㯠7.0 ç§ã§ã10,000 ä»¶ã® CRET ã¯å¹³åã§æ¯ç§ 1,430 ä»¶ã§ããã1 ç§ã 1,430 ã§å²ããšãåäžã»ãã·ã§ã³ã®å®è¡äºç®ã¯ 699µs ã«ãªããŸããSNMFC ã® P50 ã§ãã 665µs ãéããŒã¿ããŒã¹æéã§ãããã«ã¯ SQL*Net RTT ãšã¯ã©ã€ã¢ã³ãæéã®äž¡æ¹ãå«ãŸããŸããåäžã»ãã·ã§ã³ã®å®è¡äºç® 699µs ã®ãã¡ 665µs ãéããŒã¿ããŒã¹æéãå ããŠãããSQL*Net RTT ã远å ããäœå°ã¯ã»ãŒãããŸãããã¯ã³ã©ã€ã㌠4 ã®åºåã«ãããšãããšãã° SQL*Net RTT ã« 150µs ãå ããå Žåãçµéæé 7 ç§ã«å¯Ÿã㊠2 ç§ (+21.5%) å¢ããŸãããã® SQL æãåŠçããŠããã»ãã·ã§ã³ã¯ãSQL*Net RTT ã¬ã€ãã³ã·ãŒã®ç®ç«ã£ãå¢å ã蚱容ã§ããŸããã ã¯ã©ã€ã¢ã³ãé
å»¶ã®ãããã¬ãŒã¹ (ora_255365.trc) ãã®ãã¬ãŒã¹ã«ã¯ 1,557.7 ç§ã®éã«çæããã 10,000 ä»¶ã® CRET ãå«ãŸããå¹³åã¯æ¯ç§ 6.42 ä»¶ã§ãããã ãã»ãã·ã§ã³ã®å®è¡ãã¿ãŒã³ã¯ããŒã¹ãçã§ããã®å¹³åå€ã¯å®æ
ã衚ããŠããŸãããSNMFC ã® P99 ã 4.17 ç§ã§ããããšãããæ°ç§åäœã®ã¢ã€ãã«æéãçæéã®éäžçãªåŠçã®åéã«æãŸã£ãŠãããæ¯ç§å¹³åã¯å®è¡ã®å¯åºŠãæž¬ãææšãšããŠæå³ãæããªããšããããŸãã1 ç§ã 6.42 ã§å²ããšãåäžã»ãã·ã§ã³ã®å®è¡äºç®ã¯ CRET 1 åãããçŽ 156ms ã«ãªããŸããSNMFC ã® P50 ã§ãã 888µs (0.888ms) ãéããŒã¿ããŒã¹æéã§ãSQL*Net RTT ãšã¯ã©ã€ã¢ã³ãæéã®äž¡æ¹ãå«ã¿ãŸãã156ms ã®å®è¡äºç®ã«å¯ŸããŠéããŒã¿ããŒã¹æé㯠1ms æªæºããå ããŠããããSQL*Net RTT ã®å¢å ã¯åé¡ã«ãªããŸãããã¯ã³ã©ã€ã㌠4 ã®åºåã«ãããšãããšãã° 10,000 ä»¶ã® CRET ã€ãã³ãããããã« SQL*Net RTT ã 200µs 远å ããŠãã26 åã®ãã¬ãŒã¹ã«å¯Ÿããå¢å ã¯ããã 2 ç§ã§ããçµéæéã®å¢å ç㯠0.1% ã§ãã CRET åæ: äž¡è
ã®æ¯èŒ ææš äŸ A: ã¿ã€ãã«ãŒã (ææ) äŸ B: ã¯ã©ã€ã¢ã³ãé
å»¶ (蚱容) CRET ç·æ° 10,000 10,000 CRET ã¬ãŒã 1,430.06 / ç§ 6.42 / ç§ SNMFC P50 (é DB æé) 665µs 888µs SNMFC P99 (é DB æé) 902µs 4,171,289µs (4.17 ç§) CRET P50 (ã©ãŠã³ãããªãã) 683µs 1,041µs CRET P99 (ã©ãŠã³ãããªãã) 923µs 4,171,440µs (4.17 ç§) ç·çµéæé 7.0 ç§ (0.12 å) 1,557.7 ç§ (25.96 å) +150µs RTT ã®åœ±é¿ +2 ç§ (+21.5%) +2 ç§ (+0.1%) è©äŸ¡ SQL*Net RTT ã¬ã€ãã³ã·ãŒã®å¢å ã蚱容ã§ããªã SQL*Net RTT ã¬ã€ãã³ã·ãŒã®å¢å ã蚱容ã§ãã å
šäœã®æµã Client Request Elapsed Time (CRET) ææ³ã¯ã3 ã€ã®ãã§ãŒãºã§æ®µéçã«çµã蟌ãã¹ã¯ãªãŒãã³ã°ããã»ã¹ã§ãããã§ãŒãº 1 ã§ã¯ AWR ã䜿ããããŒã¿ããŒã¹åŠçæéããµãããªç§ã§å®è¡é »åºŠã®é«ã SQL æãç¹å®ããŸãããã§ãŒãº 2 ã§ã¯ SQL Execution Session Density (SESD) ãè©äŸ¡ããå®è¡ã 1 ã€ãŸãã¯ããå°æ°ã®ã»ãã·ã§ã³ã«éäžããŠãããã倿ããŸãã倿°ã®ã»ãã·ã§ã³ã«åæ£ããŠããã°ããã® SQL æã¯å¯Ÿè±¡å€ã§ããéäžããŠããå Žåã¯ãã§ãŒãº 3 ã«é²ã¿ãHigh SESD ã®ã»ãã·ã§ã³ããã¬ãŒã¹ããããã§ããã¬ãŒã¹ãã¡ã€ã«ã«å¯Ÿã㊠CRET åæããŒã«ãå®è¡ããSQL*Net RTT ãããŸããŸãªå¹
ã§å¢å ããå Žåã®å®è¡æéãžã®åœ±é¿ãäºæž¬ããŸãã çµæã®è§£é æ¬ææ³ã§ã¯ãSQL*Net ã¬ã€ãã³ã·ãŒã®å¢å ãå®è¡æéã«äžãã圱é¿ãäºæž¬ããŸããç§»è¡ã®å€æã«ããã£ãŠã¯ãæ¬¡ã®æ çµã¿ãåºçºç¹ã«ããŠãã ããã äºæž¬ãããåœ±é¿ åé¡ ã¬ã€ãã³ã¹ < 5% 蚱容 (Tolerant) ã¯ãŒã¯ããŒãã¯äœã SQL*Net ã¬ã€ãã³ã·ãŒãå¿
èŠãšããŸãããODB@AWS ã«é©ããŠããŸãã 5% â 10% å¢ç (Borderline) ã¢ããªã±ãŒã·ã§ã³ãªãŒããŒãšã®åè°ãå¿
èŠã§ãã倿ã®åã«ãç§»è¡å
ãªãŒãžã§ã³ã§ã®å®éã® ODB@AWS RTT ãæž¬å®ããŠãã ããã > 10% ææ (Sensitive) æç¢ºãªããã©ãŒãã³ã¹äœäžãèŠèŸŒãŸããŸããRTT ã®æž¬å®ãç·©åçã®æ€èšãã¢ãŒããã¯ãã£ã®èŠçŽããå¿
èŠã§ãã ODB@AWS ç§»è¡åè£ã«ãããæ¥ç¶ãšãããã€ã®èæ
®äºé
次ã®ã¹ãããã«æ²¿ã£ãŠé²ããã°ãã¯ãŒã¯ããŒãã®é©åæ§ãè©äŸ¡ããODB@AWS ã§ SQL*Net ã¬ã€ãã³ã·ãŒãå¯èœãªéãäœãæããããŸãã ãŸã CRET ææ³ã§ã¯ãŒã¯ããŒããã¹ã¯ãªãŒãã³ã°ãããã¢ãŒããã¯ãã£ã決ããåã«ãå°ãªããšããã§ãŒãº 1 (AWR ã¹ã¯ãªãŒãã³ã°) ã宿œããé«éãã€é«é »åºŠã§ãå°æ°ã®ã»ãã·ã§ã³ã«éäžããŠãã (High SESD) SQL æãç¹å®ããŸãããã®åæã«ãã£ãŠãã¯ãŒã¯ããŒãã ODB@AWS ãããã€ã®æååè£ãªã®ããå
ã«ã¢ããªã±ãŒã·ã§ã³å±€ã®æé©åãå¿
èŠãªã®ãã倿ã§ããŸãã ã¢ããªã±ãŒã·ã§ã³å±€ãåäžã®å Žæã«é
眮ããçŽæ¥ ODB Peering ã確ç«ãããã¬ã€ãã³ã·ãŒã«ææãªã¢ããªã±ãŒã·ã§ã³ã³ã³ããŒãã³ãã¯ãODB ãããã¯ãŒã¯ãšåãã¢ãã€ã©ããªãã£ãŸãŒã³ã«ãããã€ããAZ éã®ã¬ã€ãã³ã·ãŒã«ããè² è·ãæé€ããŸããã¢ããªã±ãŒã·ã§ã³ã® VPC ãš ODB ãããã¯ãŒã¯ã®éã«ã¯çŽæ¥ ODB Peering ã確ç«ããŸããçŽæ¥ Peering ã«ãããAWS Transit Gateway ã AWS Cloud WAN ãçµç±ãããExadata ããŒã¿ããŒã¹ãŸã§ã®æçã®ãããã¯ãŒã¯çµè·¯ãåŸãããŸããæ¥ç¶ãã¿ãŒã³ã®è©³çްã¯ã Oracle Database@AWS ãããã¯ãŒã¯æ¥ç¶ãã¿ãŒã³ã®å®è£
ããåç
§ããŠãã ããã ODB@AWS 髿§èœãããã¯ãŒãã³ã° ãæå¹ã«ããã¬ã€ãã³ã·ãŒãæ€èšŒããã ODB@AWS 髿§èœãããã¯ãŒãã³ã° ãæå¹ã«ãããšãåäžã¢ãã€ã©ããªãã£ãŸãŒã³å
ã®ã¢ããªã±ãŒã·ã§ã³å±€ (EC2ãECSãEKS) ãš ODB@AWS ããŒã¿ããŒã¹ã®éã§ å®å®ãããµãããªç§ã® SQL*Net ã©ãŠã³ãããªããã¬ã€ãã³ã·ãŒ ãå®çŸã§ããŸãã髿§èœãããã¯ãŒãã³ã°ã¯ãèªåçã«ããããžã§ãã³ã°ããã EC2 ãã¬ã€ã¹ã¡ã³ãã°ã«ãŒãã䜿ããã¢ããªã±ãŒã·ã§ã³ã€ã³ã¹ã¿ã³ã¹ã Exadata ã€ã³ãã©ã¹ãã©ã¯ãã£ã®ç©ççã«è¿ãäœçœ®ã«é
眮ããŸããè¿œå æéã¯ããããŸãããæž¬å®ãšæ€èšŒã®æé ã¯ã Oracle Database@AWS ã®é«æ§èœãããã¯ãŒãã³ã°å
¥é ãã®ãMeasuring Network Latencyãã»ã¯ã·ã§ã³ãåç
§ããŠãã ããã ãŸãšã æ¬èšäºã§ã¯ãã¯ãŒã¯ããŒãã® SQL*Net ã¬ã€ãã³ã·ãŒç¹æ§ã ODB@AWS ã®ãã«ãã¯ã©ãŠããããã€ã¢ãŒããã¯ãã£ã«é©åãããã©ãããæ®µéçã«å€æããææ³ãšããŠãCRET ææ³ã玹ä»ããŸãããé«éãã€é«é »åºŠã§ãå°æ°ã®ã»ãã·ã§ã³ã«éäžããŠå®è¡ããã SQL æãåæããã°ãç§»è¡åã« SQL*Net ã¬ã€ãã³ã·ãŒãžã®æåºŠãææ¡ã§ããŸãã CRET ã®è©äŸ¡ã§ã¬ã€ãã³ã·ãŒã«ææãšå€æããã¯ãŒã¯ããŒãã«ã€ããŠã¯ãODB@AWS ã¢ãŒããã¯ãã£ãåè£ããå€ãåã«ãé
åãã§ããããã«ã¯åŠçãã¹ãã¢ãããã·ãŒãžã£ã䜿ã£ãŠ SQL*Net ã®ã©ãŠã³ãããªãããæžããããšãæ€èšããŠãã ããã èè
ã«ã€ã㊠Sameer Malik Sameer ã¯ãAWS ã®ããªã³ã·ãã« Solutions Architect ãšããŠããšã³ã¿ãŒãã©ã€ãºããŒã¿ããŒã¹ãšã¯ã©ãŠãããŒã¿ãã©ãããã©ãŒã ã®ã¢ããã€ãŒãŒã·ã§ã³ãæ
åœããŠããŸããOracleãExadataãPostgreSQLãAmazon AuroraãAmazon RDS ã«ç²Ÿéããè€éãªããŒã¿ããŒã¹ç°å¢ããææ°ã®ã¢ããªã±ãŒã·ã§ã³ã AI ãæ¯ããã¹ã±ãŒã©ãã«ã§å埩åã®é«ããã©ãããã©ãŒã ãžãšå€é©ããåãçµã¿ãæ¯æŽããŠããŸãã Kevin Closson Kevin ã¯ãAWS ã® RDS Commercial Engines ã°ã«ãŒãã«æå±ããããªã³ã·ãã«ããŒã¿ããŒã¹ãšã³ãžãã¢ã§ããããŒã¿ããŒã¹ãã©ãããã©ãŒã ã®ããã©ãŒãã³ã¹ã«ç²ŸéããAmazon RDS ãå«ãããŸããŸãªãã©ãããã©ãŒã ã® I/O æ§èœãã¹ãã«åºã䜿ãããŠãã SLOB (Silly Little Oracle Benchmark) ããŒã«ã®äœæè
ã§ãã ãã®èšäºã¯ Solutions Architect ã® ç¢æš èŠ ã翻蚳ããŸããã