ให้ฉันชี้แจงทันทีว่าฉันจะอธิบายโดยใช้ตัวอย่างการใช้ยูทิลิตี้ฟรี OraDeveloper Studio ทำไม เพราะมันเป็นไปไม่ได้ที่จะทำเช่นนี้ด้วยคำถามธรรมดาๆ และไม่มีเวลาหรือความปรารถนาที่จะคิดออก เนื่องจากมีวิธีที่ง่ายกว่า

แล้วนี่มีไว้เพื่ออะไรล่ะ? ฉันจะอธิบายให้คุณฟัง ตัวอย่างที่เป็นรูปธรรมเนื่องจากฉันถูกบังคับให้ทำการเพิ่มประสิทธิภาพ

ภารกิจคือการโหลดข้อมูลนับหมื่นแถวลงในฐานข้อมูล สำหรับแต่ละแถว คุณต้องค้นหาข้อมูลเพิ่มเติมในฐานข้อมูลก่อนด้วยแบบสอบถามที่ค่อนข้างยุ่งยาก (4 ตารางผ่านการรวม)
ปัญหาคือการโหลด 15,000 แถวใช้เวลา 8-9 ชั่วโมง เนื่องจากตามเงื่อนไขของงานจึงจำเป็นต้องโหลดบ่อยครั้ง ไม่ใช่ทุกๆ ห้าปี... โดยทั่วไปเราจำเป็นต้องทำให้เวลาอยู่ในระดับที่ยอมรับได้

ฉันทำอะไรลงไป?
1. ฉันพบว่าเป็นตัวเลือกที่ช้าลง (ข้อมูลถูกแทรกและอัปเดตในตารางที่มีแถวจำนวนมากและบางตารางไม่มีทั้งดัชนีหรือคีย์ - ดังนั้นจึงมีข้อสงสัยเกี่ยวกับข้อบกพร่องของการเลือก) .
2. ตรวจสอบการมีอยู่ของดัชนีในฟิลด์ที่ใช้โดยคำขอ เพิ่มอันที่หายไป
3.ขอความช่วยเหลือจากผู้รู้.

ผู้รู้แนะนำให้เราวิเคราะห์แผนการดำเนินการสืบค้นและอธิบายวิธีดำเนินการใน OraDev
สร้างหน้าต่างแบบสอบถามใหม่ (Ctrl+N) เราคัดลอกคำขอของเราลงไป กด Alt+G เลือกอันที่มีอยู่หรือสร้างอันหนึ่ง ตารางใหม่วางแผน.
หลังจากดำเนินการแล้ว แผนผังแผนดำเนินการจะปรากฏขึ้น มันไม่ง่ายเลยที่จะเข้าใจด้วยตัวเองและไม่มีครึ่งลิตร

เราสนใจอะไรเกี่ยวกับต้นไม้ต้นนี้? เราสนใจโหนด (ขั้นตอน) ที่ระบุต้นทุนจำนวนมากของขั้นตอน คุณสามารถดูราคาขั้นตอนได้ในคุณสมบัติขั้นตอน (ฉันมีหน้าต่างคุณสมบัติเปิดอยู่เสมอ ดังนั้นฉันจึงต้องเลือกเท่านั้น) ขั้นตอนที่ถูกต้อง- คุณอาจต้องเลือกคุณสมบัติโดยคลิกขวาที่ขั้นตอนนั้น) เรามองหาขั้นตอนที่ช้า (เราไม่ได้คำนึงถึงโหนดบนสุดซึ่งเป็นรากของแผนผังแผน - ราคารวมของคำขอจะถูกระบุไว้ที่นั่น และเรารู้อยู่แล้วว่าปัญหาอยู่ในคำขอนี้) พบมัน? ตอนนี้เรามาดูกันว่าขั้นตอนใช้งานได้กับตารางใด เขตข้อมูลใด และจำนวนแถวใด ซึ่งอยู่ในคุณสมบัติและชื่อของขั้นตอน เราดูแล้วคิดว่าทำไมที่นี่ถึงช้าจัง?
ตัวอย่างเช่น ขั้นตอนหนึ่งใช้ได้กับ 4000 เรคคอร์ด แทนที่จะเป็น 1 หรือ 3 เรคคอร์ด (ไม่ใช่หลักพัน) โดยหลักการแล้วสิ่งนี้ไม่ควรเกิดขึ้น - ฉันจำกัดตัวอย่างอย่างแม่นยำเพื่อเลือกจากช่วงที่ต้องการ ไม่ใช่จากกองขยะที่ไม่จำเป็น เมื่อดูเงื่อนไขการเข้าร่วมอย่างละเอียดแล้ว ฉันสังเกตเห็นว่าฉันพลาดฟิลด์ใดฟิลด์หนึ่งไป ฉันเพิ่มช่องลงในคำขอแล้วทุกอย่างก็เข้าที่ ราคาคำขอ (เต็ม) ลดลงจาก 531 เป็น 6 :)

ขอบคุณสหายที่ทำรังและตรวจจับเพื่อขอความช่วยเหลือ

ป.ล. ขออภัยที่ไม่ได้รวมภาพหน้าจอ มันจะชัดเจนมากขึ้นสำหรับพวกเขา แต่... เนื่องจากข้อมูลบางอย่างเป็นความลับ 80% จะต้องถูกปกปิด และอีกครั้งก็จะไม่ชัดเจน
พี.พี.เอส. เวลาโหลดโดยรวมลดลงอย่างมาก ใช้เวลา 12 นาทีในการโหลดข้อมูล 17.5 พันแถวลงในฐานข้อมูล เทียบกับ 8-9 ชั่วโมง... คุณเข้าใจทุกอย่างด้วยตัวเองแล้ว

5 คำตอบ

เอาต์พุต EXPLAIN PLAN เป็นเอาต์พุตการดีบักของเครื่องมือเพิ่มประสิทธิภาพ คำสั่งของออราเคิล- COST คือผลลัพธ์สุดท้ายของเครื่องมือเพิ่มประสิทธิภาพต้นทุน (CBO) ซึ่งมีเป้าหมายเพื่อเลือกแผนที่เป็นไปได้จำนวนมากที่ควรใช้ในการเรียกใช้แบบสอบถาม CBO จะคำนวณต้นทุนสัมพัทธ์สำหรับแต่ละแผน จากนั้นเลือกแผนที่มีต้นทุนต่ำที่สุด

(หมายเหตุ: ในบางกรณี CBO ไม่มีเวลาเพียงพอที่จะประเมินแผนที่เป็นไปได้ทั้งหมด ในกรณีนี้ CBO จะเลือกแผนที่มีต้นทุนต่ำสุดที่พบเท่านั้น)

โดยทั่วไป ปัจจัยที่ใหญ่ที่สุดอย่างหนึ่งในการสืบค้นที่ช้าคือจำนวนแถวที่อ่านเพื่อให้บริการการสืบค้น (หรือบล็อกที่แม่นยำยิ่งขึ้น) ดังนั้นต้นทุนจะขึ้นอยู่กับจำนวนแถวที่ต้องอ่านค่าประมาณของเครื่องมือเพิ่มประสิทธิภาพ .

ตัวอย่างเช่น สมมติว่าคุณมีคำถามต่อไปนี้:

เลือก emp_id จากพนักงานโดยที่ months_of_service = 6;

(คอลัมน์ months_of_service มีข้อจำกัด NOT NULL และมีดัชนีปกติอยู่ด้วย)

มีแผนหลักสองแผนที่เครื่องมือเพิ่มประสิทธิภาพสามารถเลือกได้จากที่นี่:

• แผน 1: อ่านแถวทั้งหมดจากตาราง "พนักงาน" สำหรับการตรวจสอบว่าภาคแสดงเป็นจริงหรือไม่ (months_of_service=6)
• แผน 2: อ่านดัชนีโดยที่ months_of_service=6 (ซึ่งส่งผลให้เกิดชุดของ ROWID) จากนั้นเข้าถึงตารางตาม ROWID ที่ส่งคืน

สมมติว่าตาราง "พนักงาน" มี 1,000,000 (1 ล้าน) แถว ลองจินตนาการเพิ่มเติมว่าค่าสำหรับ months_of_service อยู่ในช่วง 1 ถึง 12 และด้วยเหตุผลบางประการมีการกระจายอย่างเท่าเทียมกัน

ราคา แผน 1ซึ่งรวมถึง FULL SCAN จะมีค่าใช้จ่ายในการอ่านแถวทั้งหมดในตารางพนักงานซึ่งมีค่าประมาณ 1,000,000 แต่เนื่องจาก Oracle สามารถอ่านบล็อกโดยใช้การอ่านหลายบล็อกได้ ต้นทุนจริงจึงลดลง (ขึ้นอยู่กับวิธีกำหนดค่าฐานข้อมูลของคุณ) - ตัวอย่างเช่น สมมติว่าจำนวนตัวอย่างที่มีหลายบล็อกคือ 10 - ต้นทุนโดยประมาณของบล็อกทั้งหมด การสแกนจะเป็น 1,000,000/10 ; ต้นทุนทั้งหมด = 100,000.

ราคา แผน 2ซึ่งรวมถึง INDEX RANGE SCAN และการค้นหาตารางโดย ROWID จะทำให้สแกนดัชนีและค่าใช้จ่ายในการเข้าถึงตารางโดยใช้ ROWID ฉันจะไม่พิจารณาว่าการสแกนดัชนีช่วงจะมีค่าใช้จ่ายเท่าใด แต่ลองจินตนาการว่าค่าใช้จ่ายในการสแกนดัชนีช่วงคือ 1 ต่อแถว เราคาดว่าจะพบการจับคู่ 1 ใน 12 ครั้ง ดังนั้นค่าใช้จ่ายในการสแกนดัชนีคือ 1,000,000/12 = 83,333; บวกกับค่าใช้จ่ายในการเข้าถึงตาราง (สมมติว่ามีการอ่าน 1 บล็อกต่อการเข้าถึง เราไม่สามารถใช้การอ่านแบบหลายบล็อกได้ที่นี่) = 83.333; ต้นทุนรวม = 166,666.

อย่างที่คุณเห็น ค่าใช้จ่ายของแผน 1 (การสแกนแบบเต็ม) น้อยกว่าค่าใช้จ่ายของแผน 2 (การตรวจสอบดัชนี + การเข้าถึงแถว) ซึ่งหมายความว่า CBO จะเลือกการสแกนแบบเต็ม

หากสมมติฐานที่ทำโดยเครื่องมือเพิ่มประสิทธิภาพนั้นถูกต้อง ที่จริงแล้วแผน 1 จะดีกว่าและมีประสิทธิภาพมากกว่าแผน 2 มาก ซึ่งหักล้างความเชื่อที่ว่าการสแกนแบบเต็มนั้น "แย่เสมอ"

ผลลัพธ์จะค่อนข้างแตกต่างหากเป้าหมายของเครื่องมือเพิ่มประสิทธิภาพคือ FIRST_ROWS(n) แทนที่จะเป็น ALL_ROWS - ในกรณีนี้ เครื่องมือเพิ่มประสิทธิภาพต้องการแผน 2 เนื่องจากมักจะส่งคืนสองสามแถวแรกเร็วกว่า โดยต้นทุนของการมีประสิทธิภาพน้อยลงสำหรับการค้นหาทั้งหมด .

CBO สร้างแผนผังการตัดสินใจโดยการประมาณต้นทุนของแต่ละเส้นทางการดำเนินการที่เป็นไปได้สำหรับแต่ละคำขอ ต้นทุนกำหนดโดยพารามิเตอร์ CPU_cost หรือ I/O_cost ที่ตั้งค่าบนอินสแตนซ์ และ CBO จะประมาณการต้นทุนให้ดีที่สุดเท่าที่จะทำได้ด้วยสถิติที่มีอยู่ของตารางและดัชนีที่จะใช้ในการสืบค้น คุณไม่ควรปรับแต่งคำขอของคุณโดยอิงจากต้นทุนเพียงอย่างเดียว ต้นทุนช่วยให้คุณเข้าใจว่าทำไมเครื่องมือเพิ่มประสิทธิภาพถึงทำในสิ่งที่มันทำ หากไม่มีต้นทุน คุณจะเข้าใจได้ว่าทำไมเครื่องมือเพิ่มประสิทธิภาพจึงเลือกแผนที่ทำ มากกว่า ต้นทุนต่ำไม่ได้หมายถึงการร้องขอที่เร็วขึ้น มีหลายครั้งที่สิ่งนี้เป็นจริง และก็จะมีหลายครั้งที่มันผิด ต้นทุนจะขึ้นอยู่กับตารางสถิติของคุณและหากไม่ถูกต้อง ต้นทุนก็จะไม่ถูกต้อง

เมื่อตั้งค่าแบบสอบถาม คุณควรดูจำนวนสมาชิกและจำนวนแถวของแต่ละขั้นตอน พวกเขาสมเหตุสมผลไหม? เครื่องมือเพิ่มประสิทธิภาพถือว่าถูกต้องหรือไม่? สตริงส่งคืนอย่างถูกต้องหรือไม่ หากข้อมูลไม่ปรากฏอย่างถูกต้อง เป็นไปได้มากว่าเครื่องมือเพิ่มประสิทธิภาพไม่มีข้อมูลที่เหมาะสมซึ่งจำเป็นต่อการตัดสินใจที่ถูกต้อง อาจเป็นเพราะสถิติที่ล้าสมัยหรือหายไปในตารางและดัชนี รวมถึงสถิติ cpu วิธีที่ดีที่สุดคืออัปเดตสถิติเมื่อกำหนดค่าการสืบค้นของคุณเพื่อให้ได้รับประโยชน์สูงสุดจากเครื่องมือเพิ่มประสิทธิภาพ การรู้วงจรของคุณยังช่วยได้มากในการตั้งค่า รู้ว่าเมื่อใดที่เครื่องมือเพิ่มประสิทธิภาพได้เลือกอย่างแท้จริง การตัดสินใจที่ไม่ดีและชี้ไปที่ วิธีที่ถูกต้องด้วยคำแนะนำเล็กน้อย คุณก็ประหยัดเวลาได้

การที่คุณกล่าวถึง "FULL" บ่งบอกว่าข้อความค้นหากำลังสแกนแบบเต็มหน้าจอเพื่อค้นหาข้อมูลของคุณ ในบางกรณีนี่เป็นเรื่องปกติ ไม่เช่นนั้นจะเป็นตัวบ่งชี้รายการการจัดทำดัชนี/แบบสอบถามที่ไม่ถูกต้อง

ตามกฎทั่วไป คุณต้องการให้การสืบค้นของคุณใช้คีย์เพื่อให้ Oracle สามารถค้นหาข้อมูลที่คุณต้องการโดยการเข้าถึงแถวจำนวนน้อยที่สุด สักวันหนึ่งคุณจะสามารถเข้าถึงสถาปัตยกรรมของตารางของคุณได้ หากต้นทุนยังคงสูงเกินไป คุณอาจต้องคิดถึงวิธีปรับแต่งการออกแบบวงจรให้มีประสิทธิภาพดีขึ้น

ในระยะหลังนี้ เวอร์ชันของออราเคิล COST แสดงถึงเวลาที่เครื่องมือเพิ่มประสิทธิภาพรอแบบสอบถาม โดยแสดงเป็นหน่วยของเวลาที่ใช้ในการอ่านหนึ่งบล็อก

ดังนั้น หากการอ่านหนึ่งบล็อกใช้เวลา 2 มิลลิวินาที และค่าใช้จ่ายแสดงเป็น "250" คำขออาจใช้เวลา 500 มิลลิวินาทีจึงจะเสร็จสมบูรณ์

เครื่องมือเพิ่มประสิทธิภาพจะคำนวณต้นทุนตามจำนวนการอ่านบล็อกเดียวและหลายบล็อกโดยประมาณ รวมถึงการใช้ CPU ของแผน อย่างหลังมีประโยชน์มากในการลดต้นทุนโดยการดำเนินการบางอย่างก่อนผู้อื่น เพื่อหลีกเลี่ยงต้นทุน CPU ที่สูง

สิ่งนี้ทำให้เกิดคำถามว่าเครื่องมือเพิ่มประสิทธิภาพรู้ได้อย่างไรว่าการดำเนินการใช้เวลานานเท่าใดจึงจะเสร็จสมบูรณ์ เวอร์ชันล่าสุด Oracle อนุญาตให้คุณสร้างคอลเลกชันของ "สถิติระบบ" ซึ่งไม่ควรสับสนกับสถิติตารางหรือดัชนีอย่างแน่นอน สถิติของระบบคือการวัดประสิทธิภาพ ฮาร์ดแวร์สำคัญอย่างยิ่ง:

• ใช้เวลานานเท่าใดในการอ่านหนึ่งบล็อก
• การอ่านแบบมัลติทาสกิ้งใช้เวลานานเท่าใด?
• การอ่านหลายบล็อกมีขนาดใหญ่เพียงใด (มักจะแตกต่างจากค่าสูงสุดที่เป็นไปได้เนื่องจากขนาดตารางน้อยกว่าค่าสูงสุดและเหตุผลอื่นๆ)
• ประสิทธิภาพของซีพียู

ตัวเลขเหล่านี้อาจแตกต่างกันอย่างมากขึ้นอยู่กับสภาพแวดล้อมการทำงานของระบบ และสามารถจัดเก็บสถิติที่แตกต่างกันสำหรับการดำเนินการ "OLTP ในเวลากลางวัน" และการดำเนินการ "การรายงานเป็นระยะทุกคืน" และสำหรับ "การรายงานสิ้นเดือน" ได้ หากคุณต้องการ

เมื่อพิจารณาจากชุดสถิติเหล่านี้ แผนนี้การดำเนินการสืบค้นสามารถประมาณค่าใช้จ่ายได้ในสภาพแวดล้อมการทำงานที่แตกต่างกัน ซึ่งอาจสนับสนุนให้ใช้การสแกนตารางแบบเต็มในบางกรณี หรือการสแกนดัชนีในบางกรณี

ต้นทุนไม่เหมาะ แต่เครื่องมือเพิ่มประสิทธิภาพจะดีขึ้นในการตรวจสอบตัวเองในแต่ละรุ่น และสามารถตอบสนองต้นทุนจริงเทียบกับต้นทุนโดยประมาณที่ต้องยอมรับ โซลูชั่นที่ดีที่สุดในอนาคต. มันยังทำให้การคาดการณ์ยากอีกด้วย

โปรดทราบว่าไม่จำเป็นต้องมีค่าใช้จ่าย เวลาติดผนังเนื่องจากการดำเนินการค้นหาแบบขนานจะใช้เวลารวมหลายเธรด

ใน Oracle เวอร์ชันเก่า ต้นทุนของการดำเนินการ CPU จะถูกละเว้น และต้นทุนสัมพัทธ์ของการอ่านบล็อกเดียวและหลายบล็อกได้รับการแก้ไขอย่างมีประสิทธิภาพตามพารามิเตอร์เริ่มต้น

คุณยังสามารถสืบค้น v $sql และ v $session เพื่อรับสถิติเกี่ยวกับคำสั่ง SQL ซึ่งจะมีตัววัดโดยละเอียดสำหรับทรัพยากร การกำหนดเวลา และการดำเนินการทุกประเภท

มันเหมือนกับตะปูในรองเท้าที่คุณชื่นชอบ คุณสามารถเดินได้ แต่บ่อยครั้งมากขึ้นเรื่อยๆ ที่คุณพบว่าตัวเองต้องการอยู่กับที่หรือมอบหมายงานให้ผู้อื่นมากขึ้นเรื่อยๆ ความไม่สะดวกเล็กๆ น้อยๆ ไม่เพียงแต่ทำให้งานของเราช้าลง แต่ยังลดแรงจูงใจ แทรกแซงกระบวนการ และลดคุณภาพของผลลัพธ์อีกด้วย และถ้าคุณพบเพื่อนที่สอนให้คุณเอาค้อนตอกตะปูนั้น คุณจะไม่เพียงรู้สึกขอบคุณเขาสำหรับความช่วยเหลือของเขาเท่านั้น แต่คุณยังจะได้ช่วยเหลือผู้อื่นด้วยตัวเองอีกด้วย ช่วยพวกเขาจากอุปสรรคเล็กๆ น้อยๆ แต่น่ารำคาญมาก นี่คือเหตุผลที่คุณต้องสื่อสาร แบ่งปันไม่เพียงแต่ความรู้เชิงลึกและใกล้ชิดในฟอรัมและบนเว็บไซต์เช่น Habr แต่ยังรวมไปถึงเคล็ดลับง่ายๆ และ "เคล็ดลับเล็กๆ น้อยๆ" ของคุณ

เช่นเดียวกับข้อความใด ๆ แบบสอบถามและโปรแกรมใน SQL สามารถสร้างได้ในที่ใดก็ได้ โปรแกรมแก้ไขข้อความ- แต่ถ้าคุณเป็นมืออาชีพ คุณทำงานกับ SQL บ่อยครั้ง ดังนั้นการเน้นไวยากรณ์และการจัดรูปแบบโค้ดอัตโนมัติจะไม่เพียงพอสำหรับคุณอีกต่อไป โดยเฉพาะอย่างยิ่งหากคุณต้องสลับระหว่าง DBMS เดียวกันเวอร์ชันต่างๆ หรือ แพลตฟอร์มที่แตกต่างกันดีบีเอ็มเอส.

เมื่อเร็ว ๆ นี้ฉันได้พูดคุยกับผู้เชี่ยวชาญชั้นนำคนหนึ่ง ออราเคิล DBMS- เขาเล่าถึงสิ่งที่น่าสนใจมากมายเกี่ยวกับการทำงานกับแผนการดำเนินการค้นหา รุ่นที่แตกต่างกัน DBMS นี้และไม่ลังเลเลยที่จะบอกทุกคนเกี่ยวกับเครื่องมือและเทคนิคที่เขาใช้ และให้คำแนะนำเล็กๆ น้อยๆ ที่เป็นประโยชน์ ฉันแปลบทความหนึ่งในบล็อกของเขาและอยากจะแจ้งให้ Khabravchan สนใจ แม้ว่าจะใช้เทคนิคที่อธิบายไว้เพื่อทำงานกับ Oracle แต่ตอนนี้ฉันใช้วิธีการเดียวกันนี้กับ MS SQL และ Sybase ได้สำเร็จ

เรียกใช้แบบสอบถามการดำเนินการ และแท็บแผนแบบสอบถามจะปรากฏขึ้น โดยเต็มไปด้วยแผนการดำเนินการ


วางเคอร์เซอร์ของเมาส์บนโหนดใดๆ ในไดอะแกรมและเพิ่มเติม ข้อมูลที่เป็นประโยชน์เกี่ยวข้องกับขั้นตอนการดำเนินการนี้จากแผนแบบสอบถาม!
ตามค่าเริ่มต้น Rapid SQL จะแสดงแผนการดำเนินการแบบกราฟิก ฉันออกมาจากโลกเก่าของการเพิ่มประสิทธิภาพ…. ฉันชอบเวอร์ชันข้อความมากกว่าดังนั้นฉันจึงคลิก ปุ่มขวาเลื่อนเมาส์ไปที่หน้าต่างพร้อมแผนและเลือก "ดูเป็นข้อความ"
ฉันต้องการเห็นข้อความคำขอและแผนพร้อมกัน


มันง่ายที่จะทำ คุณเห็นแท็บหน้าต่าง ISQL ที่ด้านล่างของหน้าต่างหลักหรือไม่ ขั้นแรก เราต้องกำหนดค่า Rapid SQL เพื่อให้แสดงแผนในหน้าต่างแยกต่างหาก


คลิกปุ่มตัวเลือก (วงกลมสีแดงซ้าย) จากนั้นตั้งค่าตัวเลือก 'ไม่ได้แนบ' สำหรับหน้าต่างผลลัพธ์ ซึ่งจะสร้างแท็บแยกกันสองแท็บที่ด้านล่างของ Rapid SQL เมื่อเรียกใช้แบบสอบถาม เพียงลากหน้าต่างนี้ข้างแท็บเล็กน้อย แล้วสี่เหลี่ยมผืนผ้าจะปรากฏขึ้นในตำแหน่งที่คุณสามารถย้ายหน้าต่างนี้ได้
หรือคุณสามารถใช้รายการหน้าต่างไทล์จากเมนูหลักของโปรแกรม

และอีกอย่างหนึ่ง: ทั้งหมดนี้ใช้งานได้ใน DBArtisan ซึ่งเป็นโซลูชันสำหรับผู้ดูแลระบบฐานข้อมูล

มันเหมือนกับตะปูในรองเท้าที่คุณชื่นชอบ คุณสามารถเดินได้ แต่บ่อยครั้งมากขึ้นเรื่อยๆ ที่คุณพบว่าตัวเองต้องการอยู่กับที่หรือมอบหมายงานให้ผู้อื่นมากขึ้นเรื่อยๆ ความไม่สะดวกเล็กๆ น้อยๆ ไม่เพียงแต่ทำให้งานของเราช้าลง แต่ยังลดแรงจูงใจ แทรกแซงกระบวนการ และลดคุณภาพของผลลัพธ์อีกด้วย และถ้าคุณพบเพื่อนที่สอนให้คุณเอาค้อนตอกตะปูนั้น คุณจะไม่เพียงรู้สึกขอบคุณเขาสำหรับความช่วยเหลือของเขาเท่านั้น แต่คุณยังจะได้ช่วยเหลือผู้อื่นด้วยตัวเองอีกด้วย ช่วยพวกเขาจากอุปสรรคเล็กๆ น้อยๆ แต่น่ารำคาญมาก นี่คือเหตุผลที่คุณต้องสื่อสาร แบ่งปันไม่เพียงแต่ความรู้เชิงลึกและใกล้ชิดในฟอรัมและบนเว็บไซต์เช่น Habr แต่ยังรวมไปถึงเคล็ดลับง่ายๆ และ "เคล็ดลับเล็กๆ น้อยๆ" ของคุณ

เช่นเดียวกับข้อความอื่นๆ คิวรีและโปรแกรมใน SQL สามารถสร้างได้ในโปรแกรมแก้ไขข้อความใดๆ แต่ถ้าคุณเป็นมืออาชีพ คุณทำงานกับ SQL บ่อยครั้ง ดังนั้นการเน้นไวยากรณ์และการจัดรูปแบบโค้ดอัตโนมัติจะไม่เพียงพอสำหรับคุณอีกต่อไป โดยเฉพาะอย่างยิ่งหากคุณต้องสลับระหว่าง DBMS เวอร์ชันเดียวกันหรือแพลตฟอร์ม DBMS ที่แตกต่างกัน

เมื่อเร็วๆ นี้ ฉันบังเอิญได้พูดคุยกับผู้เชี่ยวชาญชั้นนำของ Oracle DBMS เขาเล่าถึงสิ่งที่น่าสนใจมากมายเกี่ยวกับการทำงานกับแผนดำเนินการสืบค้นใน DBMS เวอร์ชันต่างๆ และไม่ลังเลเลยที่จะบอกทุกคนเกี่ยวกับเครื่องมือและเทคนิคที่เขาใช้ และให้คำแนะนำเล็กๆ น้อยๆ ที่เป็นประโยชน์ ฉันแปลบทความหนึ่งในบล็อกของเขาและอยากจะแจ้งให้ Khabravchan สนใจ แม้ว่าจะใช้เทคนิคที่อธิบายไว้เพื่อทำงานกับ Oracle แต่ตอนนี้ฉันใช้วิธีการเดียวกันนี้กับ MS SQL และ Sybase ได้สำเร็จ

เรียกใช้แบบสอบถามการดำเนินการ และแท็บแผนแบบสอบถามจะปรากฏขึ้น โดยเต็มไปด้วยแผนการดำเนินการ


วางเมาส์เหนือโหนดใดๆ ในไดอะแกรมและข้อมูลที่เป็นประโยชน์เพิ่มเติมที่เกี่ยวข้องกับขั้นตอนการดำเนินการนั้นจากแผนการสืบค้นจะปรากฏขึ้น!
ตามค่าเริ่มต้น Rapid SQL จะแสดงแผนการดำเนินการแบบกราฟิก ฉันออกมาจากโลกเก่าของการเพิ่มประสิทธิภาพ…. ฉันชอบเวอร์ชันข้อความมากกว่า ดังนั้นฉันจึงคลิกขวาในหน้าต่างแผนและเลือก "ดูเป็นข้อความ"
ฉันต้องการเห็นข้อความคำขอและแผนพร้อมกัน


มันง่ายที่จะทำ คุณเห็นแท็บหน้าต่าง ISQL ที่ด้านล่างของหน้าต่างหลักหรือไม่ ขั้นแรก เราต้องกำหนดค่า Rapid SQL เพื่อให้แสดงแผนในหน้าต่างแยกต่างหาก


คลิกปุ่มตัวเลือก (วงกลมสีแดงซ้าย) จากนั้นตั้งค่าตัวเลือก 'ไม่ได้แนบ' สำหรับหน้าต่างผลลัพธ์ ซึ่งจะสร้างแท็บแยกกันสองแท็บที่ด้านล่างของ Rapid SQL เมื่อเรียกใช้แบบสอบถาม เพียงลากหน้าต่างนี้ข้างแท็บเล็กน้อย แล้วสี่เหลี่ยมผืนผ้าจะปรากฏขึ้นในตำแหน่งที่คุณสามารถย้ายหน้าต่างนี้ได้
หรือคุณสามารถใช้รายการหน้าต่างไทล์จากเมนูหลักของโปรแกรม

และอีกอย่างหนึ่ง: ทั้งหมดนี้ใช้งานได้ใน DBArtisan ซึ่งเป็นโซลูชันสำหรับผู้ดูแลระบบฐานข้อมูล

การปรับแต่งประสิทธิภาพของฐานข้อมูล Oracle 11g

วิธีอ่านแผนการสืบค้น

แผนการดำเนินการคืออะไร?

แผนการดำเนินการคือผลลัพธ์ของเครื่องมือเพิ่มประสิทธิภาพที่แปลเป็นภาษาของกลไกการดำเนินการ โดยจะสั่งกลไกดำเนินการเกี่ยวกับการดำเนินการที่ต้องดำเนินการเพื่อให้ได้ข้อมูลที่ร้องขอโดยนิพจน์อย่างรวดเร็วและมีประสิทธิภาพที่สุด

นิพจน์ EXPLAIN PLAN จะบันทึกแผนการดำเนินการที่เลือกโดยเครื่องมือเพิ่มประสิทธิภาพเพื่อดำเนินการนิพจน์ เช่น SELECT, UPDATE, DELETE และ INSERT ขั้นตอนแผนการดำเนินการไม่ได้ดำเนินการตามลำดับที่ระบุไว้ในแผน มีความสัมพันธ์ระหว่างแม่ลูกระหว่างขั้นตอน ทรีสตริงต้นทางเป็นพื้นฐานของแผนการดำเนินการ ประกอบด้วยข้อมูลต่อไปนี้:

• การเรียงลำดับตารางที่อ้างอิงโดยคำสั่ง
• วิธีการเข้าถึงสำหรับแต่ละตารางที่ระบุในคำสั่ง
• วิธีการรวมสำหรับตาราง ใช้โดยตัวดำเนินการรวมในนิพจน์
• การดำเนินการข้อมูล เช่น การกรอง การเรียงลำดับ หรือการรวมกลุ่ม

นอกเหนือจากแผนผังสตริงต้นทาง (หรือแผนผังกระแสข้อมูลเข้าแล้ว) การดำเนินการแบบขนาน) ตารางแผนประกอบด้วยข้อมูลต่อไปนี้:

• ข้อมูลการเพิ่มประสิทธิภาพ เช่น ต้นทุนและจำนวนนับของการดำเนินการแต่ละรายการ
• ข้อมูลพาร์ติชัน เช่น ชุดของพาร์ติชันที่เข้าถึง
• ข้อมูลที่เกิดขึ้นพร้อมกัน เช่น วิธีการกระจายการดำเนินการเข้าร่วม

ผลลัพธ์ของ EXPLAIN PLAN สามารถช่วยคุณระบุได้ว่าเครื่องมือเพิ่มประสิทธิภาพกำลังเลือกแผนการดำเนินการเฉพาะหรือไม่ เช่น การใช้ลูปแบบซ้อน

ฉันจะหาแผนการดำเนินการได้ที่ไหน?

มีหลายวิธีในการรับแผนการดำเนินการสำหรับนิพจน์ในฐานข้อมูล โดยวิธีที่นิยมมากที่สุดมีดังต่อไปนี้:

• คำสั่ง EXPLAIN PLAN ช่วยให้คุณสามารถดูแผนการดำเนินการที่เครื่องมือเพิ่มประสิทธิภาพสามารถใช้เพื่อดำเนินการนิพจน์ได้ คำสั่งนี้มีประโยชน์มากเนื่องจากจะสร้างแผนการดำเนินการและเขียนลงในตารางชื่อ PLAN_TABLE โดยไม่ต้องจัดเก็บคำสั่ง SQL
• V$SQL_PLAN ให้ความสามารถในการดูแผนการดำเนินการสำหรับเคอร์เซอร์ที่เพิ่งดำเนินการ ข้อมูลที่จัดเก็บไว้ใน V$SQL_LAN นั้นคล้ายคลึงกับข้อมูลที่สร้างโดยคำสั่ง EXPLAIN PLAN มาก อย่างไรก็ตาม Explain Plan จะแสดงแผนการดำเนินการที่เป็นไปได้ และ V$SQL_PLAN จะจัดเก็บแผนของการสืบค้นที่ดำเนินการแล้ว
• V$SQL_PLAN_MONITOR มีสถิติการตรวจสอบระดับแผนสำหรับแต่ละคำสั่ง SQL ที่พบใน V$SQL_MONITOR แต่ละบรรทัดที่อยู่ใน V$SQL_PLAN_MONITOR สอดคล้องกับการดำเนินการตามแผนการดำเนินการเฉพาะ
• เฟรมเวิร์ก AWR และแผนการดำเนินการจัดเก็บ Statspack สำหรับ SQL ที่เรียกว่าบ่อยที่สุด แผนถูกวางไว้ในมุมมอง dBA_HIST_SQL_PLAN หรือ STATS$SQL_PLAN
• แผนการดำเนินการและแหล่งที่มาของแถวยังถูกเขียนเพื่อติดตามไฟล์ที่สร้างโดย DBMS_MONITOR
• SQL Management Base เป็นส่วนหนึ่งของพจนานุกรมข้อมูลที่จัดเก็บไว้ในพื้นที่ตาราง SYSAUX ข้อมูลบันทึกเกี่ยวกับการดำเนินงาน ประวัติแผนการดำเนินการ และบรรทัดอ้างอิงจะถูกเก็บไว้ที่นี่ เช่นเดียวกับโปรไฟล์สำหรับคำสั่ง SQL
• เหตุการณ์การวินิจฉัย 10053 ซึ่งใช้ในการบันทึกการคำนวณตัวเพิ่มประสิทธิภาพต้นทุน ยังสามารถสร้างแผนการดำเนินการแบบสอบถามได้อีกด้วย
• ตั้งแต่เวอร์ชัน 10.2 เมื่อคุณดัมพ์สถานะกระบวนการ แผนการดำเนินการจะรวมอยู่ในไฟล์การติดตามที่สร้างขึ้นด้วย

การดูแผนการดำเนินการ

หากคุณรันคำสั่ง EXPLAIN PLAN ใน SQL*Plus คุณสามารถเลือกข้อมูลจากตาราง PLAN_TABLE และดูแผนการดำเนินการที่สร้างขึ้นได้ ที่สุด ด้วยวิธีง่ายๆดูแผนการดำเนินการคือการใช้แพ็คเกจ DBMS_XPLAIN แพ็คเกจ DBMS_XPLAIN มีห้าฟังก์ชันที่ใช้ได้:

• จอแสดงผล: ใช้สำหรับเอาต์พุตที่จัดรูปแบบของแผนการดำเนินการ
• DISPLAY_AWR: ใช้สำหรับเอาต์พุตแผนที่จัดรูปแบบแล้ว การดำเนินการ SQLนิพจน์ที่เก็บไว้ในที่เก็บ AWR
• DISPLAY_CURSOR: ใช้สำหรับเอาต์พุตที่จัดรูปแบบแล้วของแผนการดำเนินการจากเคอร์เซอร์ที่โหลดใดๆ
• DISPLAY_SQL_PLAN_BASELINE: ใช้สำหรับเอาต์พุตที่จัดรูปแบบของแผนการดำเนินการตั้งแต่หนึ่งแผนขึ้นไปสำหรับคำสั่ง SQL ที่ระบุโดยส่วนหัว
• DISPLAY_SQLSET: ใช้สำหรับเอาต์พุตที่จัดรูปแบบของแผนการดำเนินการที่จัดเก็บไว้ในชุดการปรับแต่ง SQL

ประโยชน์ของการใช้แพ็คเกจ DBMS_XPLAIN คือช่วยให้คุณสามารถดูแผนการดำเนินการที่จัดรูปแบบของคำสั่ง SQL ได้ โดยไม่คำนึงถึงแหล่งที่มา

คำสั่งอธิบายแผน

• คำสั่ง EXPLAIN PLAN ใช้เพื่อสร้างแผนการดำเนินการแบบสอบถาม
• เมื่อสร้างแผนแล้ว สามารถดูได้โดยการสืบค้นข้อมูลจากตาราง PLAN_TABLE

PLAN TABLE ถูกสร้างขึ้นโดยอัตโนมัติเป็นตารางชั่วคราวส่วนกลาง ซึ่งต่อมาผู้ใช้ทุกคนจะนำไปใช้ในการจัดเก็บแผนการดำเนินการ คุณสามารถสร้าง PLAN TABLE ของคุณเองได้โดยใช้สคริปต์ $ORACLE_HOME/rdbms/admin/utlxplan.sql หากจำเป็นต้องมีหน่วยเก็บข้อมูลระยะยาวของแผนการดำเนินการ

อธิบายโครงสร้างการบังคับบัญชาแผน

คำสั่ง EXPLAIN PLAN จะแทรกแถวลงใน PLAN TABLE สำหรับแต่ละขั้นตอนของแผนปฏิบัติการ

ตัวอย่างคำสั่งอธิบายแผน

คำสั่งนี้แทรกแผนการดำเนินการสำหรับนิพจน์ลงใน PLAN TABLE และเพิ่มแท็ก demo01 สำหรับการอ้างอิงในภายหลัง

มีหลายวิธีในการรับแผนปฏิบัติการ วิธีการข้างต้นใช้คำสั่ง EXPLAIN PLAN คำสั่งนี้สร้างแผนการดำเนินการสำหรับคำสั่ง SQL โดยไม่ต้องดำเนินการ และวางผลลัพธ์ไว้ใน PLAN TABLE PLAN TABLE แสดงถึงโครงสร้างต้นไม้ที่คุณสามารถส่งคืนแผนการดำเนินการสำหรับนิพจน์โดยใช้คอลัมน์ ID และ PARENT_ID และส่วนคำสั่ง CONNECT BY ในเลือกนิพจน์

การส่งออกเนื้อหาของ PLAN TABLE

ตัวอย่างด้านบนใช้ปุ่ม ALL สำหรับฟังก์ชัน DBMS_XPLAIN.DISPLAY ซึ่งช่วยให้คุณสามารถดูทั้งหมดได้ ข้อมูลที่มีอยู่เกี่ยวกับแผนปฏิบัติการซึ่งจัดเก็บไว้ใน PLAN TABLE ข้อสรุปนี้นอกเหนือจากข้อมูลมาตรฐานแล้วยังมี ข้อมูลเพิ่มเติมเช่นข้อมูล PROJECTION, ALIAS และ REMOTE SQL หากมีการกระจายการดำเนินการ

เพื่อการควบคุมข้อมูลเอาต์พุตที่แม่นยำยิ่งขึ้น สามารถใช้สิ่งต่อไปนี้: พารามิเตอร์ที่สำคัญ- แต่ละ คำหลักเพิ่มบล็อกแยกต่างหากในการส่งออกข้อมูลจาก PLAN TABLE คำหลักจะต้องคั่นด้วยลูกน้ำหรือช่องว่าง:

• ROWS (หากเหมาะสม) จะแสดงจำนวนแถวที่คาดว่าจะถูกนับโดยเครื่องมือเพิ่มประสิทธิภาพ
• ROWS (หากเหมาะสม) จะแสดงจำนวนไบต์ที่คาดว่าจะถูกนับโดยเครื่องมือเพิ่มประสิทธิภาพ
• ค่าใช้จ่าย หากเหมาะสมก็แสดงต้นทุนสันนิษฐานว่าคำนวณโดยเครื่องมือเพิ่มประสิทธิภาพ
• พาร์ติชั่น หากเหมาะสม จะแสดงการละทิ้ง patrician โดยเครื่องมือเพิ่มประสิทธิภาพ
• PARALLEL หรือเหมาะสม แสดงข้อมูล PX (methodการกระจายข้อมูลและข้อมูลเกี่ยวกับคิวการเข้าถึงตาราง)
• ภาคแสดงหรือเหมาะสมจะแสดงข้อมูลเกี่ยวกับภาคแสดง
• PROJECTION หรือเหมาะสมก็แสดงส่วนนี้การคาดการณ์

การใช้ Explain Plan ใน SQL Developer

ติดตามอัตโนมัติ

เมื่อแสดงออกวิ่ง ใน SQL*Plus หรือ SQL Developer คุณสามารถรับแผนการดำเนินการและสถิติการดำเนินการของนิพจน์ได้โดยอัตโนมัติ รายงานจะถูกสร้างขึ้นโดยอัตโนมัติหลังจากดำเนินการประเภทใดๆ เช่น SELECT, INSERT, UPDATE และ DELETE ข้อมูลนี้สามารถใช้เพื่อวินิจฉัยและปรับแต่งประสิทธิภาพของคำสั่ง SQL

หากต้องการใช้ AUTOTRACE จะต้องสร้าง PLAN TABLE ในฐานข้อมูล และผู้ใช้ที่ดำเนินการ AUTOTRACE จะต้องได้รับบทบาท PLUSTRACE บทบาทPLUSTRACE ถูกสร้างและออกให้กับบทบาท DBA โดยใช้สคริปต์$ORACLE_HOME/sqlplus/admin/plustrce.sql

ไวยากรณ์ออโต้เทรซ

คุณสามารถดำเนินการ Autotrace ได้โดยใช้ไวยากรณ์ที่แสดงในภาพด้านบน ตัวเลือกต่อไปนี้ยังสามารถใช้งานได้:

• OFF ปิดใช้งานการใช้การติดตาม
• ON เปิดใช้งานการกำหนดเส้นทางอัตโนมัติ
• TRACE เปิดใช้งานการติดตามอัตโนมัติและระงับเอาต์พุต SQL
• EXPLAIN แสดงแผนการดำเนินการแต่ไม่แสดงสถิติ
• สถิติ แสดงสถิติโดยไม่มีแผนการดำเนินการ

ตัวอย่างการใช้ AUTOTRACE

ติดตามอัตโนมัติ: สถิติ

สถิติที่เซิร์ฟเวอร์บันทึกไว้เมื่อดำเนินการนิพจน์จะสะท้อนถึงปริมาณ ทรัพยากรระบบใช้ในการดำเนินการนิพจน์และรวมถึงตัวบ่งชี้หลักดังต่อไปนี้:

• โทรซ้ำ- ปริมาณ โทรซ้ำสร้างขึ้นบนฝั่งไคลเอ็นต์และเซิร์ฟเวอร์ ฐานข้อมูลออราเคิลรองรับตารางที่ใช้สำหรับการประมวลผลภายใน เมื่อ Oracle Database จำเป็นต้องเปลี่ยนแปลงตารางเหล่านี้ ระบบจะสร้างขึ้น ตัวดำเนินการภายใน SQL ซึ่งจะทำให้เกิดการเรียกซ้ำ
• ได้รับบล็อก db- จำนวนครั้งที่ร้องขอบล็อก CURRENT
• ได้รับอย่างสม่ำเสมอ- จำนวนครั้งที่ร้องขอการดำเนินการอ่านบล็อกข้อมูลแบบรวม
• การอ่านทางกายภาพ- จำนวนบล็อกข้อมูลที่อ่านจากดิสก์ ตัวเลขนี้แสดงถึงผลรวมของค่าต่างๆ ฟิสิคัลอ่านโดยตรงและอ่านทั้งหมดจากบัฟเฟอร์แคช
• ทำซ้ำขนาด- จำนวนการทำซ้ำที่สร้างขึ้นทั้งหมดในบล็อก
• ไบต์ที่ส่งผ่าน SQL*Net ไปยังไคลเอนต์- จำนวนไบต์ทั้งหมดที่ถ่ายโอนไปยังไคลเอนต์จากกระบวนการเบื้องหลัง
• ไบต์ที่ได้รับผ่าน SQL*Net จากไคลเอ็นต์- จำนวนไบต์ทั้งหมดที่ได้รับจากไคลเอ็นต์ Oracle*Net
• SQL*Net ไปกลับ/จากไคลเอ็นต์- จำนวนข้อความ Oracle NET ทั้งหมดที่ส่งและรับจากไคลเอ็นต์
• เรียงลำดับ (หน่วยความจำ)- จำนวนการดำเนินการเรียงลำดับที่เสร็จสมบูรณ์ในหน่วยความจำและไม่จำเป็นต้องเขียนลงดิสก์
• เรียงลำดับ (ดิสก์)- จำนวนการดำเนินการเรียงลำดับที่ต้องมีการดำเนินการดิสก์อย่างน้อยหนึ่งครั้ง
• แถวที่ประมวลผลแล้ว- จำนวนแถวที่ประมวลผลระหว่างการดำเนินการ

db_block_getsแสดงการดำเนินการอ่านของบล็อกปัจจุบันจากฐานข้อมูล ได้รับอย่างสม่ำเสมอ- สิ่งเหล่านี้คือการดำเนินการอ่านบล็อกที่ต้องเป็นไปตามหมายเลข SCN ที่ระบุ การอ่านทางกายภาพแสดงบล็อกการอ่านจากดิสก์ db_block_getsและ ได้รับอย่างสม่ำเสมอ- ตัวบ่งชี้ทางสถิติที่มีการติดตามอย่างต่อเนื่อง ควรจะต่ำเมื่อเทียบกับจำนวนแถวที่ดึงข้อมูล การเรียงลำดับจะดำเนินการในหน่วยความจำ ไม่ใช่บนดิสก์

ติดตามอัตโนมัติโดยใช้ SQL*Developer

ดู V$SQL_PLAN

มุมมองนี้แสดงแผนการดำเนินการสำหรับเคอร์เซอร์ที่ยังอยู่ในแคชไลบรารี ข้อมูลที่จัดเก็บไว้ในมุมมองนี้คล้ายกับข้อมูลจาก PLAN TABLE หลายประการ อย่างไรก็ตาม V$SQL_PLAN มีแผนการดำเนินการสำหรับนิพจน์ที่ได้ดำเนินการแล้ว แผนการดำเนินการที่สร้างโดย EXPLAIN PLAN อาจแตกต่างจากแผนการดำเนินการจริงที่จัดเก็บไว้ในเคอร์เซอร์ สิ่งนี้เกิดขึ้นเนื่องจากพารามิเตอร์เซสชันและค่าตัวแปร BIND อาจแตกต่างจากค่าปัจจุบัน

อีกมุมมองที่มีประโยชน์: V$SQL_PLAN_STATISTICSซึ่งจัดเตรียมสถิติการดำเนินการสำหรับการดำเนินการแต่ละรายการในแผนการดำเนินการของเคอร์เซอร์แคชแต่ละตัว อีกหนึ่งไอเดียที่มีประโยชน์ V$SQL_PLAN_STATISTIC_ALLรวมข้อมูลการดำเนินการจาก V$SQL_PLAN_STATISTICSและ V$SQL_WORKAREA พร้อมจัดเก็บแผนปฏิบัติการไว้ V$SQL_PLAN.

คำอธิบายของคอลัมน์หลักของมุมมอง V$SQL_PLAN

มุมมองมีคอลัมน์เดียวกันกับ PLAN TABLE และคอลัมน์เพิ่มเติมอีกสองสามคอลัมน์ คอลัมน์ที่อยู่ใน PLAN TABLE และมีค่าเหมือนกัน:

• ที่อยู่
• HASH_VALUE

PLAN_HASH_VALUE เป็นคอลัมน์ที่มีการแสดงตัวเลขของแผนนิพจน์ SQL สำหรับเคอร์เซอร์ ด้วยการเปรียบเทียบค่าของคอลัมน์ PLAN_HASH_VALUE คุณสามารถระบุได้อย่างรวดเร็วว่าแผนการดำเนินการสำหรับนิพจน์เดียวกันมีการเปลี่ยนแปลงโดยไม่ต้องเปรียบเทียบทีละแถวหรือไม่

ดู V$SQL_PLAN_STATISTICS

มุมมอง V$SQL_PLAN_STATISTICS มีให้ สถิติปัจจุบันโดยการดำเนินการสำหรับแต่ละการดำเนินการในแผนการดำเนินการ เช่น จำนวนแถวที่ประมวลผลหรือเวลาดำเนินการ สถิติทั้งหมดจะถูกสะสม ยกเว้นจำนวนแถว ตัวอย่างเช่น สถิติเกี่ยวกับการรวมตารางอาจรวมการดำเนินการรวมตาราง 3 รายการ สถิติที่เก็บไว้ใน V$SQL_PLAN_STATISTICS พร้อมใช้งานสำหรับเคอร์เซอร์ที่คอมไพล์ด้วยพารามิเตอร์การเริ่มต้น STATISTICS_LEVEL = ALL หรือใช้คำแนะนำของเครื่องมือเพิ่มประสิทธิภาพ GATHER_PLAN_STATISTICS

มุมมอง V$SQL_STATISTICS_ALL มีสถิติการใช้งานหน่วยความจำสำหรับแถวต้นทางทั้งหมดที่ใช้หน่วยความจำ SQL (การเรียงลำดับหรือการรวม HASH) มุมมองนี้จะรวมข้อมูลที่จัดเก็บไว้ในมุมมอง V$SQL_PLAN เข้ากับสถิติการดำเนินการจากมุมมอง V$SQL_PLAN_STATISTICS และ V$SQL_WORKAREA .

ความสัมพันธ์ระหว่างมุมมองประสิทธิภาพไดนามิกที่สำคัญ

V$SQLAREAแสดงสถิติสำหรับพื้นที่ SQL ที่แบ่งใช้และมีหนึ่งบรรทัดสำหรับแต่ละพื้นที่ สตริง SQLการแสดงออก มุมมองนี้จัดเตรียมสถิติเกี่ยวกับคำสั่ง SQL ที่ถูกแยกวิเคราะห์แล้ว ในหน่วยความจำ และพร้อมที่จะดำเนินการ:

• SQL_ID- ตัวระบุ SQL ของเคอร์เซอร์พาเรนต์ในแคชไลบรารี
• VERSION_COUNTจำนวนเคอร์เซอร์ลูกที่มีอยู่ในแคชสำหรับเคอร์เซอร์พาเรนต์ที่กำหนด

V$SQLเก็บสถิติบนพื้นที่ SQL ที่ใช้ร่วมกันและมีหนึ่งแถวสำหรับแต่ละนิพจน์ SQL ที่สืบทอดมาจากนิพจน์ SQL หลัก:

• ที่อยู่แสดงถึงที่อยู่ส่วนหัวของเคอร์เซอร์พาเรนต์สำหรับเคอร์เซอร์ที่กำหนด
• HASH_VALUE-ค่าของนิพจน์พาเรนต์ในแคชไลบรารี
• SQL_ID- ตัวระบุ SQL ของเคอร์เซอร์พาเรนต์ในแคชไลบรารี
• PLAN_HASH_VALUE- ตัวเลข มุมมอง SQLวางแผนสำหรับเคอร์เซอร์นี้
• CHILD_NUMBER- หมายเลขเคอร์เซอร์ลูก

สถิติที่จัดเก็บไว้ใน V$SQL จะได้รับการอัปเดตเมื่อมีการดำเนินการคำสั่ง SQL อย่างไรก็ตาม สำหรับนิพจน์ที่ใช้เวลานาน ข้อมูลในมุมมองจะได้รับการอัปเดตทุกๆ ห้าวินาที ซึ่งทำให้สามารถประเมินผลกระทบด้านประสิทธิภาพการทำงานของแบบสอบถามที่ใช้เวลานานในขณะที่กำลังทำงานอยู่

V$SQL_PLANมีข้อมูลแผนการดำเนินการสำหรับเคอร์เซอร์ลูกแต่ละตัวที่โหลดลงในแคชของไลบรารี คอลัมน์ ที่อยู่, HASH_VALUEและ CHILD_NUMBERสามารถใช้เชื่อมต่อกับ V$SQLสำหรับคำจำกัดความของเคอร์เซอร์ลูกในภายหลัง

V$SQL_PLAN_สถิติจัดทำสถิติการดำเนินการระดับแถวต้นทางสำหรับเคอร์เซอร์ลูกแต่ละตัวคอลัมน์ ที่อยู่, HASH_VALUE สามารถใช้รวมกับมุมมองได้ V$SQLAREAเพื่อกำหนดผู้ปกครองเคอร์เซอร์.คอลัมน์ ที่อยู่, HASH_VALUEและ CHILD_NUMBERสามารถใช้เชื่อมต่อกับV$SQLเพื่อกำหนดเคอร์เซอร์ลูก

V$SQL_PLAN_สถิติ_ทั้งหมด มีสถิติการใช้งานหน่วยความจำสำหรับแถวต้นทางทั้งหมดที่ใช้หน่วยความจำ SQL (เรียงลำดับหรือรวม HASH) มุมมองนี้จะรวบรวมข้อมูลที่จัดเก็บไว้ในมุมมอง V$SQL_PLANพร้อมสถิติการดำเนินการจากการดู V$SQL_PLAN_STATISTICSและ V$SQL_WORKAREA.

V$SQL_WORKAREAจัดทำสถิติเกี่ยวกับพื้นที่ทำงานที่เกี่ยวข้องกับกระบวนการ การทำงานของ SQLการแสดงออก แต่ละคำสั่ง SQL ที่จัดเก็บไว้ในพูลที่ใช้ร่วมกันมีเคอร์เซอร์ลูกตั้งแต่หนึ่งตัวขึ้นไป ซึ่งมีข้อมูลเกี่ยวกับเคอร์เซอร์ที่จัดเก็บไว้ใน V$SQL. V$SQL_WORKAREAมีข้อมูลเกี่ยวกับพื้นที่ทำงานทั้งหมดที่จำเป็นโดยเคอร์เซอร์ลูกเหล่านี้

V$SQL_WORKAREAสามารถเชื่อมต่อกับ V$SQLAREA (ที่อยู่, HASH_VALUE)และด้วย V$SQL ( ที่อยู่, HASH_VALUE,CHILD_NUMBER).

เมื่อใช้การนำเสนอนี้ คุณจะได้รับคำตอบสำหรับคำถามต่อไปนี้:

• 10 อันดับพื้นที่ทำงานที่ต้องการมากที่สุดปริมาณหน่วยความจำสำหรับแคช
• สำหรับพื้นที่ทำงานที่ทำงานในโหมดอัตโนมัติพื้นที่ทำงานใช้งานอยู่กี่เปอร์เซ็นต์ ปริมาณสูงสุดหน่วยความจำ?

V$SQLSTATSแสดงสถิติประสิทธิภาพพื้นฐานสำหรับเคอร์เซอร์ SQL โดยแต่ละแถวจะแสดงข้อมูลที่รวมข้อความนิพจน์ SQL และแผนการดำเนินการ SQL (รวมกัน SQL_IDและ PLAN_HASH_VALUE- คอลัมน์ใน V$SQLSTATSเหมือนกัน, V$SQLและ V$SQLAREA- อย่างไรก็ตามการนำเสนอ V$SQLSTATSแตกต่างจาก V$SQLและ V$SQLAREAความเร็วในการประมวลผล ความสามารถในการปรับขนาด ระยะเวลาจัดเก็บข้อมูลที่ยาวนาน (สามารถจัดเก็บข้อมูลทางสถิติในมุมมองได้ แม้ว่าเคอร์เซอร์จะถูกไล่ออกจากพูลที่ใช้ร่วมกันแล้วก็ตาม)

ตัวอย่างการสืบค้นข้อมูลจากมุมมอง V$SQL_PLAN

คุณสามารถสอบถามข้อมูลจากมุมมองได้ V$SQL_PLANโดยใช้ฟังก์ชัน DBMS_XPLAIN.DISPLAY_CURSOR()เพื่อแสดงนิพจน์ปัจจุบันหรือที่ดำเนินการล่าสุด (ดังแสดงในตัวอย่าง) คุณสามารถส่งผ่านค่า SQL_IDเป็นพารามิเตอร์เพื่อรับแผนการดำเนินการ ได้รับการแสดงออก. SQL_ID - SQL_IDนิพจน์ที่เก็บไว้ในแคชเคอร์เซอร์ คุณสามารถรับค่าที่สอดคล้องกันได้โดยการสืบค้นข้อมูลจากคอลัมน์ SQL_IDวี V$SQLและ V$SQLAREA- หรือคุณสามารถเลือกได้ PREV_SQL_IDสำหรับเซสชั่นเฉพาะจาก V$เซสชัน- ตามค่าเริ่มต้น ตัวเลือกนี้จะไม่ถูกระบุ ซึ่งในกรณีนี้แผนที่จะจัดเก็บไว้ในเคอร์เซอร์ที่ดำเนินการครั้งล่าสุดจะปรากฏขึ้น

• ไอโอสแตท: สมมติว่ากระบวนการดำเนินการ SQL รวบรวมสถิติพื้นฐานสำหรับแผนการดำเนินการ STATISTICS_LEVEL ถูกตั้งค่าเป็น ALL หรือใช้ HINT GATHER_PLAN_STATISTICS) รูปแบบนี้จะแสดงสถิติ I/O สำหรับทุกคน เมื่อระบุทั้งหมด (หรือเฉพาะรายการสุดท้าย เมื่อระบุ LAST) การดำเนินการของเคอร์เซอร์
• บันทึกความจำ: ถือว่าใช้แล้ว ควบคุมอัตโนมัติ PGA (พารามิเตอร์ pga_aggregate_target ถูกตั้งค่าเป็นค่าที่ไม่ใช่ศูนย์) รูปแบบนี้ช่วยให้คุณสามารถแสดงสถิติการใช้งานหน่วยความจำ ประเภทนี้สถิติใช้ได้กับการดำเนินการที่ใช้หน่วยความจำมากเท่านั้น เช่น HASH Join การเรียงลำดับ หรือตัวดำเนินการบิตแมปบางตัว
• ออลสแตท: คำพ้องความหมายสำหรับ "IOSTATS เมมสแตท"
• ล่าสุด: ตามค่าเริ่มต้น สถิติแผนการดำเนินการจะแสดงสำหรับการดำเนินการเคอร์เซอร์ทั้งหมด เมื่อใช้คีย์เวิร์ด LAST คุณสามารถดูสถิติของแผนที่สร้างขึ้นตั้งแต่ครั้งล่าสุดที่มีการดำเนินการได้

มุมมอง AWR ที่สำคัญ

คุณสามารถดูข้อมูล AWR ใน Enterprise Manager หรือโดยการสร้างรายงาน AWR และคุณยังสามารถเข้าถึงมุมมองประสิทธิภาพแบบไดนามิกต่อไปนี้ที่จัดเก็บข้อมูล AWR:

• V$ACTIVE_SESSION_HISTORY- มุมมองนี้แสดงข้อมูลเกี่ยวกับกิจกรรมเซสชันล่าสุด อัปเดตทุกวินาที
• มุมมองเมตริก V$ นำเสนอข้อมูลเมตริกเพื่อติดตามประสิทธิภาพของระบบ คุณสามารถดูรายการมุมมองเมทริกได้โดยการเข้าถึงมุมมอง V$METRICGROUP
• มุมมอง DBA_HIST มีข้อมูลประวัติที่จัดเก็บไว้ในฐานข้อมูล มุมมองกลุ่มนี้รวมถึง:

1. DBA_HIST_ACTIVE_SESS_HISTORYมีเนื้อหาของประวัติเซสชันที่ใช้งานอยู่ซึ่งเลือกจากหน่วยความจำตามกิจกรรมของระบบล่าสุด
2. DBA_HIST_BASELINEมีข้อมูลเกี่ยวกับบรรทัดอ้างอิงที่เก็บไว้ในฐานข้อมูล
3. DBA_HIST_DATABASE_INSTANCEมีข้อมูลเกี่ยวกับสภาพแวดล้อมฐานข้อมูล
4. DBA_HIST_SNAPSHOTมีข้อมูลเกี่ยวกับสแน็ปช็อตที่จัดเก็บไว้ในระบบ
5. DBA_HIST_SQL_PLANมีข้อมูลเกี่ยวกับแผนการดำเนินการ
6. DBA_HIST_WR_CONTROLมีข้อมูลเกี่ยวกับการตั้งค่า AWR

การขอข้อมูลจาก AWR

การสร้างรายงานสำหรับเฉพาะSQLจากพื้นที่เก็บข้อมูล AWR

การตรวจสอบ SQL

เครื่องมือ การตรวจสอบ SQLใช้งานได้ตามค่าเริ่มต้นเมื่อมีตัวเลือก สถิติ_LEVELตั้งค่าเป็นค่า ทั่วไปหรือ ทั้งหมด- หากต้องการใช้ ของเครื่องดนตรีชิ้นนี้คุณต้องตั้งค่าพารามิเตอร์ด้วย CONTROL_MANAGEMENT_PACK_ACCESSในความหมาย วินิจฉัย+ปรับแต่งเพื่อเปิดใช้งานแพ็คเกจการวินิจฉัยและการกำหนดค่า

ตามค่าเริ่มต้น การตรวจสอบ SQL จะถูกเปิดใช้งานโดยอัตโนมัติเมื่อมีการดำเนินการคำสั่ง SQL ในแบบคู่ขนานหรือเมื่อใช้เวลา CPU หรือ I/O มากกว่าห้าวินาทีระหว่างการดำเนินการครั้งเดียว

มีคำแนะนำเกี่ยวกับเครื่องมือเพิ่มประสิทธิภาพสองประการในการเปิดใช้งานหรือปิดใช้งานการตรวจสอบ SQL สำหรับนิพจน์อย่างชัดเจน - เฝ้าสังเกตและ NO_MONITOR.

คุณสามารถติดตามสถิติการดำเนินการคำสั่ง SQL โดยใช้มุมมอง V$SQL_MONITORและ V$SQL_PLAN_MONITOR.

หลังจากเปิดใช้งานการตรวจสอบคำสั่ง SQL ในมุมมองประสิทธิภาพแบบไดนามิก V$SQL_MONITORเพิ่มข้อมูลที่จำเป็นในการติดตามตัววัดประสิทธิภาพหลัก เช่น เวลาดำเนินการ, เวลา CPU, จำนวนการอ่านและเขียน, เวลาแฝง I/O และตัววัดเวลาแฝงอื่น ๆ สถิตินี้อัปเดตแบบเรียลไทม์ระหว่างการดำเนินการ SQL ตามค่าเริ่มต้น - ทุกวินาที หลังจากดำเนินการเสร็จสิ้น ข้อมูลเกี่ยวกับการดำเนินการจะถูกจัดเก็บไว้ในมุมมอง V$SQL_MONITORอีกนาทีหนึ่งหลังจากนั้นจึงนำออก

ตัวอย่างรายงานการตรวจสอบ SQL

ใน ในตัวอย่างนี้จะถือว่าคุณกำลังเลือกข้อมูลจากตาราง SALES และเรียกใช้ SQL Monitoring ในเซสชันอื่น

ฟังก์ชัน DBMS_SQLTUNE.REPORT_SQL_MONITOR สามารถยอมรับพารามิเตอร์หลายตัวที่จำกัดหรือขยายระดับรายละเอียดของรายงาน โดยใช้พารามิเตอร์เพิ่มเติม คุณยังสามารถระบุรูปแบบเอาต์พุตของรายงาน (TEXT, HTML หรือ XML) ตามค่าเริ่มต้น รายงานจะถูกสร้างขึ้นเป็นข้อความ รูปแบบ

หากต้องการระบุการดำเนินการสองรายการของคำสั่ง SQL เดียวกันโดยไม่ซ้ำกัน จะมีการสร้างคีย์คอมโพสิตที่เรียกว่าคีย์การดำเนินการ กุญแจดอกนี้ประกอบด้วยแอตทริบิวต์สามรายการ ซึ่งแต่ละแอตทริบิวต์อ้างอิงถึงคอลัมน์ใน V$SQL_MONITOR:

• SQL_ID
• ตัวระบุที่สร้างขึ้นภายในเพื่อให้แน่ใจว่าได้รับ คีย์หลักไม่ซ้ำกันจริงๆ (SQL_EXEC_ID)
• การประทับเวลาเริ่มต้นการดำเนินการนิพจน์ (SQL_EXEC_START)

การตีความแผนปฏิบัติการ

บทสรุป อธิบายแผนคือการแสดงโครงสร้างแบบแผนผังของแผนปฏิบัติการแบบตาราง แต่ละขั้นตอน (บรรทัดในแผนปฏิบัติการหรือโหนดในแผนผัง) แสดงถึงแหล่งข้อมูลแถว
ลำดับของโหนดภายใต้ parrent แสดงลำดับการดำเนินการของโหนดในระดับนั้น หากสองขั้นตอนอยู่ในระดับเดียวกัน ขั้นตอนแรกตามลำดับจะถูกดำเนินการก่อน
ในรูปแบบต้นไม้ ใบไม้ทางด้านซ้ายในแต่ละระดับของต้นไม้จะระบุจุดที่การดำเนินการเริ่มต้นขึ้น
ขั้นตอนแผนการดำเนินการไม่ได้ดำเนินการตามลำดับที่มีการกำหนดหมายเลข มีความสัมพันธ์แบบแม่ลูกระหว่างขั้นตอนต่างๆ
ใน PLAN_TABLE และ V$SQL_PLAN องค์ประกอบที่สำคัญเพื่อให้ได้โครงสร้างต้นไม้คือคอลัมน์ ID, PARRENT_ID และ POSITION ในไฟล์การติดตาม คอลัมน์เหล่านี้สอดคล้องกับฟิลด์ id, pid และ pos ตามลำดับ
วิธีหนึ่งในการอ่านแผนปฏิบัติการคือการแปลงเป็นกราฟที่มีโครงสร้างแบบต้นไม้ คุณสามารถเริ่มต้นจากด้านบน รายการที่มี ID=1 คือจุดสูงสุดของแผนผัง นี่เป็นจริงสำหรับการดำเนินการที่มีค่า parrent_id หรือ pid เป็น 1
หากต้องการแสดงแผนเป็นต้นไม้ ให้ทำดังต่อไปนี้:

1. นำ ID ที่มีค่าต่ำสุดมาวางไว้ที่ด้านบนของแผนผัง
2. ระบุแถวที่มี PID (รหัสพาเรนต์) เท่ากับค่านี้
3. วางไว้บนต้นไม้ด้านล่าง บันทึกผู้ปกครองตามค่า POS จากน้อยไปหามากจากซ้ายไปขวา
4. เมื่อพบ ID หลักทั้งหมดแล้ว ให้เลื่อนลงไปหนึ่งระดับไปยัง ID ถัดไป และทำซ้ำขั้นตอนนี้ โดยค้นหาแถวใหม่ที่มี PID เดียวกัน

สิ่งแรกที่ต้องกำหนดในแผนการดำเนินการคือโหนดใดที่จะถูกดำเนินการก่อน วิธีการที่แสดงในรูปอธิบายวิธีการทำเช่นนี้ แต่บางครั้งในแผนการดำเนินการที่ซับซ้อน การทำเช่นนี้เป็นเรื่องยากและยังยากที่จะทำตามขั้นตอนทั้งหมดจนจบ แผนที่ซับซ้อนไม่แตกต่างจากองค์ประกอบที่เรียบง่ายยกเว้นจำนวนขั้นตอน เช่นเดียวกับพวกเขา กฎง่ายๆ- คุณสามารถซ่อนขั้นตอนในแผนของคุณที่ไม่ใช้ทรัพยากรจำนวนมากได้ตลอดเวลา
วิธีมาตรฐานในการตีความแผนปฏิบัติการ:

1. เริ่มต้นที่ด้านบน
2. เลื่อนการดำเนินการลงจนกว่าคุณจะไปถึงการดำเนินการที่สร้างข้อมูลแต่ไม่กินอะไรเลย นี่คือจุดเริ่มต้นของการดำเนินการ
3. ดูการดำเนินงานของลูกที่ผู้ปกครองรายนี้มี การดำเนินการย่อยจะดำเนินการดังนี้
4. เลื่อนขึ้นไปบนแผนภูมิจนกว่าธุรกรรมทั้งหมดจะได้รับการตรวจสอบ

วิธีการมาตรฐานสำหรับการตีความแผนผังแผนปฏิบัติการคือ:

1. เริ่มต้นที่ด้านบน
2. เลื่อนลงและไปทางซ้ายตามต้นไม้จนกว่าคุณจะไปถึงโหนดด้านซ้าย ซึ่งจะดำเนินการก่อน
3. ดูลูก ๆ ของโหนดนี้ เด็กเหล่านี้จะถูกประหารชีวิตต่อไป
4. หลังจากดำเนินการลูกแล้ว การดำเนินการหลักจะดำเนินการต่อไป
5. ตอนนี้ หลังจากปฏิบัติการนี้และลูกหลานทั้งหมดเสร็จสิ้น ให้ย้ายขึ้นไปบนต้นไม้และดูลูกหลานของปฏิบัติการชุดดั้งเดิมและผู้ปกครอง ก็ดำเนินการตามหลักการเดียวกัน
6. นำทางขึ้นไปบนแผนภูมิจนกว่าธุรกรรมทั้งหมดจะได้รับการตรวจสอบ

การตีความแผนปฏิบัติการ: ตัวอย่างที่ 1

รูปด้านบนแสดงการตีความแผนการดำเนินการสำหรับนิพจน์ ข้อความค้นหาที่แสดงในรูปจะพยายามค้นหาพนักงานที่มีเงินเดือนแตกต่างจากตารางเงินเดือน แบบสอบถามจะเลือกข้อมูลจากสองตารางและรวมแบบสอบถามย่อยตามการเลือกจากตารางอื่นเพื่อตรวจสอบระดับเงินเดือน
ลองดูลำดับการดำเนินการสำหรับคำขอนี้ จากรูปนี้และรูปก่อนหน้า ลำดับการดำเนินการจะเป็นดังนี้: 3-5-4-2-6-1:

• 3: การดำเนินการตามแผนจะเริ่มต้นด้วยการสแกนตาราง EMP แบบเต็ม (ID=3)
• 5: แถวจะถูกส่งผ่านไปยังขั้นตอนควบคุมการวนซ้ำแบบซ้อน (ID=2) ซึ่งใช้เพื่อค้นหาแถวในดัชนี PK_DEPT (ID=5)
• 4: ROWID ของแถวที่ได้รับหลังจากการสแกน PK_DEPT จะถูกนำมาใช้เพื่อรับข้อมูลส่วนที่เหลือจากตาราง DEPT (ID=4)
• 2: ID=2 กระบวนการรวมลูปแบบซ้อนจะดำเนินต่อไปจนกว่าจะเสร็จสมบูรณ์
• 6: หลังจากที่ ID=2 ได้ประมวลผลแถวต้นทางทั้งหมดสำหรับการเข้าร่วมแล้ว จะมีการดำเนินการสแกนตาราง SALGRADE (ID=6) แบบเต็ม
• 1: ข้อมูลที่ได้รับหลังจากดำเนินการ ID=6 จะถูกนำมาใช้เพื่อกรองแถวจาก ID=2 และ ID=6

กระบวนการลูกดำเนินการก่อนกระบวนการหลัก แม้ว่าโครงสร้างการเชื่อมต่อจะต้องถูกสร้างขึ้นก่อนที่กระบวนการลูกจะดำเนินการ บางทีวิธีที่ง่ายที่สุดในการอธิบายลำดับการดำเนินการคือในการดำเนินการรวม NESTED LOOPS ด้วย ID=2 ลูกสองคน (ID=3 และ ID=4 (พร้อมกับลูก ๆ ของพวกเขา)) จะต้องดำเนินการให้เสร็จสิ้นก่อนที่ ID=2 จะเป็น ดำเนินการ

การตีความแผนปฏิบัติการ: ตัวอย่างที่ 2

แบบสอบถามนี้จะส่งคืนชื่อ ชื่อแผนก และที่อยู่ของพนักงานที่มีแผนกตั้งอยู่ในซีแอตเทิลและมีผู้จัดการ

เพื่อความสะดวกในการจัดรูปแบบ จึงได้เพิ่มหมายเลขอื่นลงในรูปภาพ คอลัมน์ด้านซ้ายแสดงถึง ID และคอลัมน์ด้านขวาแสดงถึง PID แผนการดำเนินการแบบสอบถามแสดงการดำเนินการ NESTED LOOP JOIN สองรายการ เราตีความแผนการดำเนินการตามวิธีการที่นำเสนอข้างต้น:

1. เริ่มจากด้านบนกันก่อน ไอดี=0
2. เราลงไปดำเนินการจนกว่าเราจะไปถึงการดำเนินการที่สร้างข้อมูล แต่ไม่ได้ใช้อะไรเลย ใน ในกรณีนี้,ID 0,1,2 และ 3 กำลังใช้ข้อมูล ID=4 เป็นการดำเนินการแรกจากด้านบนที่ไม่ใช้ทรัพยากร แต่สร้างข้อมูล นี่คือแหล่งข้อมูลแรก INDEX RANGE SCAN จะส่งคืน ROWID ของแถวที่จะใช้ส่งคืนข้อมูลจากตาราง LOCATIONS (ID=3)
3. ลองดูพี่น้องของแหล่งที่มาแถวนี้ซึ่งอยู่ในระดับเดียวกับในแผนผัง พี่น้องในระดับเดียวกันมี ID=3 และ ID=5 ID = 5 มีลูก - ID = 6 ซึ่งจะถูกดำเนินการก่อนหน้านั้น นี่คือการดำเนินการ INDEX RANGE SCAN บนดัชนีอื่น โดยส่งคืน ROWID ที่จะนำไปใช้ดึงข้อมูลจากตาราง DEPARTMENTS ในระหว่างการดำเนินการ ID=5 ในภายหลัง
4. หลังจากที่การดำเนินการลูกดำเนินการแล้ว การควบคุมจะถูกโอนไปยังบรรพบุรุษ การดำเนินการถัดไปจะเป็น NESTED LOOPS ที่มี ID=2 เพื่อรวมข้อมูลที่ได้รับก่อนหน้านี้
5. ตอนนี้การปฏิบัติการของผู้ปกครองและลูกหลานทั้งหมดเสร็จสิ้นแล้ว เราก็ปีนขึ้นไปบนต้นไม้และดูว่าการดำเนินงานของผู้ปกครองมีพี่น้องในระดับเดียวกันหรือไม่ ID=2 อยู่ในระดับเดียวกับการดำเนินการ ID=7 ซึ่งมีรหัสย่อย=8 เด็กคนนี้จะถูกประหารชีวิตก่อน INDEX UNIQUE SCAN จะถูกดำเนินการเพื่อรับ ROWID ของแถว ซึ่งจะใช้ในการดึงข้อมูลจากตาราง EMPLOYEES ในการดำเนินการ ID=7
6. เราย้ายไปยังระดับที่สูงขึ้นหลังจากการดำเนินการทั้งหมดในระดับปัจจุบันและการดำเนินการที่สืบทอดได้รับการประมวลผลแล้ว การดำเนินการสุดท้ายที่จะดำเนินการคือการรวม NESTED LOOPS เข้ากับ ID=1 หลังจากนั้นผลลัพธ์จะถูกส่ง ID=0
7. ลำดับการดำเนินการมีดังนี้ 4-3-6-5-2-8-7-1-0

ด้านล่างคือ คำอธิบายโดยละเอียดแผนการดำเนินการ:

ขั้นแรก วนซ้ำซ้อนภายในจะถูกดำเนินการโดยใช้ LOCATIONS เป็นตารางนำหน้า โดยใช้การเข้าถึงดัชนีในคอลัมน์ CITY การดำเนินการนี้ได้ผลเพราะคุณกำลังมองหาแผนกในซีแอตเทิลเท่านั้น

ผลลัพธ์ถูกรวมเข้ากับตาราง DEPARTMENTS โดยใช้ดัชนีในคอลัมน์ LOCATION_ID สำหรับการเข้าร่วม ผลลัพธ์ของการดำเนินการรวมครั้งแรกคือแหล่งข้อมูลชั้นนำสำหรับลูปที่ซ้อนกันที่สอง

การดำเนินการรวมครั้งที่สองจะตรวจสอบดัชนีบนคอลัมน์ EMPLOYEE_ID ของตาราง EMPLOYEES การดำเนินการนี้สามารถดำเนินการได้เนื่องจากระบบทราบ (ตั้งแต่การดำเนินการเข้าร่วมครั้งแรก) ID ของผู้จัดการแผนกทั้งหมดในซีแอตเทิล ในกรณีนี้ UNIQUE SCAN จะดำเนินการเนื่องจากการสแกนจะดำเนินการในดัชนีคีย์หลัก

ขั้นแรก ระบบแฮชตาราง T3 ลงในหน่วยความจำ (ID=3)

จากนั้นระบบจะแฮชตาราง T1 ลงในหน่วยความจำ (ID=5)

จากนั้นการสแกนตาราง T2 (ID-6) จะเริ่มต้นขึ้น

ระบบจะดึงแถวจาก T2 และตรวจสอบ T1 (T1.i=T2.i)

หากเส้นยังคงอยู่ ระบบจะค้นหาใน T3 (T1.i=T3.i)

หากแถวยังคงอยู่ ระบบจะส่งต่อไปยังการดำเนินการถัดไป

ปัญหาของระบบ ค่าสูงสุดจากผลลัพธ์ชุดที่แล้ว

ลำดับการดำเนินการมีดังนี้: 3-5-6-4-2-1

ลำดับการรวม: T1-T2-T3

การอ่านแผนการดำเนินการที่ครอบคลุมเพิ่มเติม

แผนผังที่แสดงในภาพด้านซ้ายสร้างขึ้นจากการสืบค้นจากพจนานุกรมข้อมูล มันยาวมาก ดังนั้นจึงเป็นเรื่องยากมากที่จะใช้วิธีการตีความก่อนหน้านี้เพื่อค้นหาการผ่าตัดครั้งแรก

คุณสามารถย่อโครงร่างให้สั้นลงเพื่อให้อ่านง่ายขึ้นได้ รูปทางด้านขวาแสดงแผนการดำเนินการเดียวกัน แต่ย่อให้สั้นลงเท่านั้น ดังแสดงในรูป ขั้นตอนนี้ทำได้ง่ายโดยใช้ Enterprise manager หรือ SQL*Developer ดังที่เห็นในรูปแผนดังกล่าวครอบคลุมถึงการดำเนินการรวมสองสาขาเข้าด้วยกัน ความรู้เกี่ยวกับพจนานุกรมข้อมูลช่วยให้เราเข้าใจว่าทั้งสองสาขาสอดคล้องกับพื้นที่ตารางที่จัดการโดยพจนานุกรมและจัดการในเครื่อง ความรู้เกี่ยวกับฐานข้อมูลช่วยให้คุณเข้าใจว่าฐานข้อมูลไม่มีพื้นที่ตารางที่จัดการโดยพจนานุกรม ทางนี้หากมีปัญหาก็อยู่ในสาขาที่ 2 ครับ เพื่อยืนยันสมมติฐานของคุณ คุณต้องดูข้อมูลแผนและสถิติการดำเนินการของแหล่งที่มาแต่ละแถวเพื่อกำหนดส่วนของแผนที่ใช้ทรัพยากรมากที่สุด จึงต้องขยายสาขาที่พบปัญหา หากต้องการใช้วิธีนี้ คุณต้องใช้สถิติการดำเนินการเพิ่มเติม ซึ่งสามารถพบได้ในมุมมอง V$SQL_PLAN_STATISTICS หรือในรายงาน tkprof ที่สร้างจากไฟล์การติดตาม ตัวอย่างเช่น tkprof จะรวมเวลาที่การดำเนินการหลักแต่ละรายการใช้ในการดำเนินการ บวกกับเวลาดำเนินการของการดำเนินการย่อยทั้งหมด

ภาพรวมแผนการดำเนินการ

เมื่อคุณตั้งค่านิพจน์ SQL ในสภาพแวดล้อม OLTP เป้าหมายคือการเริ่มต้นด้วยตารางที่มีการใช้ตัวกรองหัวกะทิสูงสุด ซึ่งหมายความว่าแถวจำนวนน้อยลงจะถูกส่งผ่านไปยังการดำเนินการถัดไป ถ้าขั้นตอนถัดไปคือการรวม จะถือว่าแถวน้อยลงที่จะเข้าร่วม คุณต้องแน่ใจว่าเส้นทางการเข้าถึงข้อมูลมีความเหมาะสมที่สุด เมื่อคุณตรวจสอบแผนการดำเนินการ โปรดทราบสิ่งต่อไปนี้:

• แผนได้รับการออกแบบเพื่อให้ใช้ตัวกรองที่ดีที่สุดกับตารางนำ
• ลำดับการรวมได้รับการออกแบบเพื่อให้จำนวนแถวน้อยที่สุดถูกส่งผ่านไปยังขั้นตอนถัดไป (นั่นคือ ลำดับการรวมควรไปที่ตัวกรองที่ดีที่สุดที่ยังไม่ได้ใช้)
• วิธีการรวมจะตรงกับจำนวนแถวที่ถูกส่งไปเพื่อเข้าร่วม ตัวอย่างเช่น การรวม NESTED LOOP โดยใช้ดัชนีอาจไม่เหมาะสมเมื่อมีการส่งคืนแถวจำนวนมาก
• มุมมองถูกใช้อย่างมีประสิทธิภาพ ดูสิ เลือกรายการเพื่อกำหนดว่าจะต้องเข้าถึงมุมมองที่ใด
• ไม่มีการดำเนินการผลิตภัณฑ์คาร์ทีเซียนของตาราง (แม้จะมีตารางเล็กก็ตาม)
• แต่ละตารางอ่านได้อย่างมีประสิทธิภาพ: พิจารณา เพรดิเคต SQLนิพจน์ที่เกี่ยวข้องกับจำนวนแถวในตาราง ดูรายละเอียดการดำเนินการที่น่าสงสัย เช่น การสแกนตารางแบบเต็มสำหรับตารางที่มี จำนวนมากสตริงที่มีอยู่ในภาคแสดง

การสแกนตารางแบบเต็มสามารถใช้งานได้อย่างมีประสิทธิภาพสำหรับตารางขนาดเล็กหรือสำหรับแอปพลิเคชัน ประเภทเฉพาะเข้าร่วม (เช่น HASH JOIN) สำหรับแถวที่ส่งคืน

หากเงื่อนไขใดๆ ข้างต้นไม่เหมาะสม คุณจะต้องเขียน SQL ใหม่หรือตรวจสอบดัชนีที่มีอยู่ในตาราง

แผนการดำเนินการเองไม่สามารถให้ข้อมูลเกี่ยวกับประสิทธิภาพของการดำเนินการแบบสอบถามได้ ตัวอย่างเช่น ผลลัพธ์ของ EXPLAIN PLAN อาจแสดงการใช้ดัชนี แต่ไม่ได้หมายความว่านิพจน์นั้นมีประสิทธิภาพ บางครั้งการใช้ดัชนีอาจไม่มีประสิทธิภาพมากนัก

เพื่อกำหนด แผนการที่เหมาะสมที่สุดการดำเนินการ คุณจะต้องได้รับแผนเริ่มต้น จากนั้นจึงปรับให้เหมาะสมเพื่อการดำเนินการที่เหมาะสมที่สุดโดยใช้การทดสอบ ในขณะที่เปลี่ยนแผนการดำเนินการ คุณต้องตรวจสอบการใช้ทรัพยากรระบบ