วิธีกู้คืนสถิติฐานข้อมูล Oracle เพื่อประสิทธิภาพที่เหมาะสมที่สุด

บทนำ

ในฐานะผู้ดูแลฐานข้อมูล (DBA) คุณอาจพบบ่อยครั้งที่การรวบรวมสถิติใหม่ทำให้ Optimizer ใช้แผนการดำเนินการที่ไม่เหมาะสม (suboptimal execution plans) ในกรณีเหล่านั้น คุณอาจต้องการกู้คืนสถิติจากช่วงเวลาที่ประสิทธิภาพดีกว่า

อย่างไรก็ตาม เวอร์ชันต่างๆ ของ Oracle Database มีการเปลี่ยนแปลงเล็กน้อยในวิธีการจัดการสถิติ:

• Oracle 10g เริ่มรักษาสถิติโดยอัตโนมัติเพื่อการกู้คืนที่ง่าย
• ใน 11.1 และใหม่กว่า Oracle นำเสนอวิธีที่ดีกว่า อนุญาตให้คุณเลื่อนการเผยแพร่สถิติ (defer statistics publishing)

เหตุผลที่เป็นไปได้สำหรับประสิทธิภาพสถิติที่แย่

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

ใน Oracle 10g และใหม่กว่า คุณสามารถกู้คืนสถิติได้โดยใช้แพ็กเกจ dbms_stats ซึ่งให้ตัวเลือกในการกู้คืนสถิติและส่งออกสถิติ

โดยค่าเริ่มต้น Oracle เก็บสถิติประวัติไว้ 31 วัน แต่คุณสามารถเปลี่ยนช่วงเวลานั้นได้

ภาพต่อไปนี้แสดงคำสั่ง SQL เพื่อแสดงช่วงเวลาการเก็บรักษาปัจจุบัน:

เพื่อเปลี่ยนช่วงเวลาการเก็บรักษา ให้รันคำสั่งต่อไปนี้ โดยที่ xx คือจำนวนวันที่คุณต้องการใช้:

SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)

คิวรีต่อไปนี้ช่วยให้คุณเข้าใจว่าสถิติประวัติใดที่คุณสามารถกู้คืนได้:

หมายเหตุ: ตัวอย่างนี้แสดงสถิติจากวันที่กล่าวมาข้างต้นและภายหลัง

การกู้คืนสถิติตาราง

ตัวอย่างนี้สาธิตวิธีกู้คืนสถิติตารางจากวันที่ก่อนหน้า

ขั้นแรก ระบุว่าสถิติใดมีอยู่โดยรันคำสั่งต่อไปนี้:

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name like 'MY_TABLE' and owner='MYSELF' order by 2;
TABLE_NAME STATS_UPDATE_TIME
--------------- --------------------------------------
MY_TABLE 20-DEC-19 05.32.26.887184 AM -05:00
MY_TABLE 20-DEC-19 10.10.19.361091 PM -05:00
MY_TABLE 21-DEC-19 05.32.14.475934 AM -05:00
MY_TABLE 21-DEC-19 10.10.18.725917 PM -05:00
MY_TABLE 22-DEC-19 10.10.17.841143 PM -05:00
MY_TABLE 23-DEC-19 05.32.56.168779 AM -05:00
MY_TABLE 23-DEC-19 10.10.23.633939 PM -05:00
MY_TABLE 24-DEC-19 05.32.14.082730 AM -05:00
MY_TABLE 24-DEC-19 10.10.21.712948 PM -05:00
MY_TABLE 25-DEC-19 05.32.13.710159 AM -05:00
MY_TABLE 25-DEC-19 10.10.17.836929 PM -05:00
MY_TABLE 26-DEC-19 05.32.14.545533 AM -05:00
MY_TABLE 26-DEC-19 10.10.12.808687 PM -05:00
MY_TABLE 27-DEC-19 05.32.13.779967 AM -05:00

คุณเห็นได้ว่า MY_TABLE ถูกวิเคราะห์หลายครั้งในวันที่ผ่านมา เพื่อกู้คืนสถิติตารางที่รวบรวมเมื่อ 21-DEC-19 10.10.18.725917 PM -05:00 ให้รันคำสั่งต่อไปนี้:

SQL> execute dbms_stats.restore_table_stats('MYSELF','MY_TABLE','21-DEC-19 10.10.18.725917 PM -05:00');
PL/SQL procedure successfully completed.

การกู้คืนสถิติสกีม่า

ตัวอย่างนี้สาธิตวิธีกู้คืนสถิติสกีม่าจากวันที่ก่อนหน้า

เพื่อกู้คืนสถิติสกีม่าของตารางที่รวบรวมเมื่อ 21-DEC-19 10.10.18.725917 PM -05:00 ให้รันคำสั่งต่อไปนี้:

SQL> exec dbms_stats.restore_schema_stats(ownname=>'MYSELF', AS_OF_TIMESTAMP=>'21-DEC-19 10.10.18.725917 PM -05:00');

เพื่อหาตัวเลือกสถิติสกีม่าที่มีอยู่สำหรับ AS_OF_TIMESTAMP ให้รันคำสั่งต่อไปนี้และเลือกวันที่ที่เหมาะสมสำหรับการกู้คืน:

select count(*), stats_update_time from dba_tab_stats_history where owner='MYSELF' group by stats_update_time;

กู้คืนสถิติอื่นๆ

คุณได้เห็นวิธีกู้คืนสถิติตารางและสกีม่าแล้ว รายการต่อไปนี้แสดงเอนทิตีทั้งหมดที่คุณสามารถกู้คืนสถิติก่อนหน้าได้:

• TABLE_STATS
• SCHEMA_STATS
• DATABASE_STATS
• DICTIONARY_STATS
• FIXED_OBJECTS_STATS
• SYSTEM_STATS

ส่งออกสถิติที่คุณต้องการเก็บ

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

1. สร้างตารางสถิติโดยรันคำสั่งคล้ายกับตัวอย่างต่อไปนี้:

   Exec dbms_stats.create_stat_table(ownname => 'MYSELF', stattab => 'MYSELF_STATS_', tblspace => '');

   ownname: ชื่อเจ้าของ stattab: ชื่อตารางภายใต้ผู้ใช้ MYSELF เพื่อสร้าง tblspace: tablespace ที่จะสร้างตารางนี้ภายใต้

2. ส่งออกสถิติในตารางข้างต้นโดยรันคำสั่งคล้ายกับตัวอย่างต่อไปนี้:

   exec dbms_stats.export_table_stats('SCHEMA1','TAB1',NULL,'STATS','TAG1_TAB1',TRUE);

   ตัวอย่าง:

   Exec dbms_stats.export_database_stats(statown => 'MYSELF', stattab => 'MYSELF_STATS');

สรุป

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

ใช้แท็บ Feedback เพื่อแสดงความคิดเห็นหรือถามคำถาม คุณยังสามารถแชทตอนนี้เพื่อเริ่มการสนทนาได้

เรียนรู้เพิ่มเติมเกี่ยวกับบริการฐานข้อมูลของเรา