Mengelola statistik dengan DBMS_STATS - PolarDB - Alibaba Cloud Documentation Center

PolarDB for PostgreSQL (Oracle-Compatible) menyediakan paket DBMS_STATS untuk mengumpulkan statistik waktu proses, membuat cadangan statistik, dan memulihkannya. Anda dapat menjadwalkan pencadangan statistik serta memodifikasi statistik tabel untuk mengarahkan pengoptimal ke rencana eksekusi yang diinginkan.

Prasyarat

Hubungi kami untuk menggunakan fitur paket DBMS_STATS.

Persiapkan data uji

Catatan

Data uji ini hanya berlaku untuk contoh operasi dalam topik ini.

Buat skema bernama dbms_stats_schema.
```
CREATE SCHEMA dbms_stats_schema;
```

Buat tabel bernama dbms_stats_test.

CREATE TABLE dbms_stats_schema.dbms_stats_test(id int);

Buat indeks.

CREATE INDEX dbms_stats_index on dbms_stats_schema.dbms_stats_test(id);

Masukkan data.

INSERT INTO dbms_stats_schema.dbms_stats_test values (generate_series(1,10000));

DBMS_STATS.GATHER_SCHEMA_STATS

Fungsi ini mengumpulkan dan membuat cadangan statistik tingkat skema.

Sintaks

DBMS_STATS.GATHER_SCHEMA_STATS (
        ownname          VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang akan dianalisis.	Ya

Example

Kumpulkan dan buat cadangan statistik tingkat skema:

CALL DBMS_STATS.GATHER_SCHEMA_STATS('dbms_stats_schema');

Kueri riwayat cadangan statistik:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_TABLE_STATS

Gunakan fungsi ini untuk mengumpulkan dan membuat cadangan statistik tingkat tabel.

Sintaks

DBMS_STATS.GATHER_TABLE_STATS (
        ownname          VARCHAR2, 
        tabname          VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang memiliki tabel target.	Ya
tabname	Nama tabel target.	Ya

Example

Kumpulkan dan buat cadangan statistik tingkat tabel:

CALL DBMS_STATS.GATHER_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

Kueri riwayat cadangan statistik:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_DATABASE_STATS

Fungsi ini mengumpulkan dan membuat cadangan statistik database.

Sintaks

DBMS_STATS.GATHER_DATABASE_STATS ();

Example

Kumpulkan dan buat cadangan statistik database:

CALL DBMS_STATS.GATHER_DATABASE_STATS();

Kueri riwayat cadangan statistik:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_INDEX_STATS

Fungsi ini mengumpulkan dan membuat cadangan statistik indeks.

Sintaks

DBMS_STATS.GATHER_INDEX_STATS (
        ownname          VARCHAR2, 
        indname          VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang memiliki indeks yang akan dianalisis.	Ya
indname	Nama indeks yang akan dianalisis.	Ya

Example

Untuk mengumpulkan dan membuat cadangan statistik tingkat indeks, jalankan pernyataan berikut:

CALL DBMS_STATS.GATHER_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index');

Untuk mengkueri riwayat cadangan statistik, jalankan kueri berikut:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.GATHER_COLUMN_STATS

Fungsi ini mengumpulkan dan membuat cadangan statistik untuk kolom tertentu.

Sintaksis

DBMS_STATS.GATHER_COLUMN_STATS (
        ownname         VARCHAR2,
        tablename       VARCHAR2,
        attname         TEXT
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang berisi tabel tersebut.	Ya
tablename	Nama tabel yang berisi kolom tersebut.	Ya
attname	Nama kolom yang akan dikumpulkan statistiknya.	Ya

Example

Kumpulkan dan buat cadangan statistik untuk kolom tertentu:

CALL DBMS_STATS.GATHER_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

Untuk mengkueri riwayat cadangan statistik, jalankan kueri berikut:

SELECT * FROM polar_dbms_stats.backup_history;

DBMS_STATS.RESTORE_SCHEMA_STATS

Fungsi ini memulihkan statistik untuk skema tertentu.

Sintaks

DBMS_STATS.RESTORE_SCHEMA_STATS (
        ownname                VARCHAR2, 
        as_of_timestamp        TIMESTAMP WITH TIME ZONE
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Menentukan skema yang akan dipulihkan statistiknya.	Ya
as_of_timestamp	Titik waktu untuk memulihkan statistik.	Ya

Example

Pulihkan statistik untuk skema tertentu:

SELECT DBMS_STATS.RESTORE_SCHEMA_STATS('dbms_stats_schema',time) FROM polar_dbms_stats.backup_history WHERE unit='s';

Kueri statistik yang telah dipulihkan:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.RESTORE_DATABASE_STATS

Fungsi ini memulihkan statistik database.

Sintaks

DBMS_STATS.RESTORE_DATABASE_STATS (
        as_of_timestamp        TIMESTAMP WITH TIME ZONE
    );

Parameters

Parameter	Deskripsi	Wajib
as_of_timestamp	Timestamp yang menentukan titik pemulihan.	Ya

Example

Contoh berikut memulihkan statistik database:

SELECT DBMS_STATS.RESTORE_DATABASE_STATS(time) FROM polar_dbms_stats.backup_history WHERE unit='d';

Contoh berikut mengkueri statistik yang telah dipulihkan:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.RESTORE_TABLE_STATS

Fungsi ini memulihkan statistik untuk tabel tertentu.

Sintaks

DBMS_STATS.RESTORE_TABLE_STATS (
        ownname                   VARCHAR2, 
        tabname                   VARCHAR2, 
        as_of_timestamp           TIMESTAMP WITH TIME ZONE
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang berisi tabel tersebut.	Ya
tabname	Nama tabel.	Ya
as_of_timestamp	Timestamp untuk memulihkan statistik.	Ya

Example

Contoh berikut memulihkan statistik untuk tabel:

SELECT DBMS_STATS.RESTORE_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', time) FROM polar_dbms_stats.backup_history WHERE unit='t';

Untuk mengkueri statistik setelah dipulihkan, jalankan kueri berikut:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.RESTORE_COLUMN_STATS

Memulihkan statistik untuk kolom tertentu.

Sintaks

DBMS_STATS.RESTORE_COLUMN_STATS (
        ownname         TEXT,
        tablename       TEXT,
        attname         TEXT,
        as_of_timestamp timestamp with time zone
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema.	Ya
tabname	Nama tabel.	Ya
attname	Nama kolom.	Ya
as_of_timestamp	Timestamp untuk memulihkan statistik.	Ya

Example

Kueri berikut memulihkan statistik untuk kolom tertentu:

SELECT DBMS_STATS.RESTORE_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id', time) FROM polar_dbms_stats.backup_history WHERE unit='c';

Untuk memverifikasi statistik yang telah dipulihkan, jalankan kueri berikut:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema%';

DBMS_STATS.PURGE_STATS

Menghapus cadangan statistik yang dibuat sebelum timestamp tertentu.

Sintaks

DBMS_STATS.PURGE_STATS (
        before_timestamp timestamp
    );

Parameters

Parameter	Deskripsi	Wajib
before_timestamp	Timestamp batas untuk menghapus backup statistik.	Ya

Example

Hapus cadangan statistik yang dibuat sebelum timestamp tertentu:

SELECT DBMS_STATS.PURGE_STATS(time) FROM polar_dbms_stats.backup_history WHERE unit='c';

DBMS_STATS.SET_TABLE_STATS

Menetapkan statistik untuk tabel tertentu.

Catatan

Jika statistik belum dikumpulkan untuk tabel yang ditentukan, jalankan gather_schema_stats atau gather_table_stats sebelum memanggil set_table_stats.

Sintaks

DBMS_STATS.SET_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2, 
        numrows       NUMBER   DEFAULT NULL, 
        numblks       NUMBER   DEFAULT NULL
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang berisi tabel tersebut.	Ya
tabname	Nama tabel.	Ya
numrows	Jumlah baris dalam tabel.	Tidak
numblks	Jumlah blok dalam tabel.	Tidak

Example

CALL DBMS_STATS.SET_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', 1234, 4321);

DBMS_STATS.GET_TABLE_STATS

Mengambil statistik untuk tabel tertentu.

Sintaks

DBMS_STATS.GET_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2, 
        numrows       OUT NUMBER, 
        numblks       OUT NUMBER
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang berisi tabel tersebut.	Ya
tabname	Nama tabel.	Ya
numrows	Jumlah baris dalam tabel.	Ya
numblks	Jumlah blok dalam tabel.	Ya

Example

Contoh berikut mengambil statistik tabel:

DECLARE
    numrows integer;
    numblks integer;
BEGIN
    CALL DBMS_STATS.GET_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test', numrows, numblks);
    raise notice '%', numrows;
    raise notice '%', numblks;
END;

DBMS_STATS.SET_INDEX_STATS

Fungsi ini menetapkan statistik untuk indeks.

Sintaksis

DBMS_STATS.SET_INDEX_STATS (
        ownname       VARCHAR2,
        indname       VARCHAR2,
        numrows       NUMBER   DEFAULT NULL, 
        numblks       NUMBER   DEFAULT NULL
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang berisi indeks tersebut.	Ya
indname	Nama indeks.	Ya
numrows	Jumlah baris dalam indeks.	Tidak
numblks	Jumlah blok dalam indeks.	Tidak

Example

Contoh berikut menetapkan statistik untuk indeks:

CALL DBMS_STATS.SET_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index', 2345, 5432);

DBMS_STATS.GET_INDEX_STATS

Mengambil statistik untuk indeks tertentu.

Sintaks

DBMS_STATS.GET_INDEX_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2, 
        numrows       OUT NUMBER, 
        numblks       OUT NUMBER
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang memiliki indeks tersebut.	Ya
tabname	Nama indeks.	Ya
numrows	Jumlah baris dalam indeks.	Ya
numblks	Jumlah blok data dalam indeks.	Ya

Example

Contoh ini menunjukkan cara mengambil statistik untuk indeks:

DECLARE
    numrows integer;
    numblks integer;
BEGIN
    CALL DBMS_STATS.GET_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index', numrows, numblks);
    raise notice '%', numrows;
    raise notice '%', numblks;
END;

DBMS_STATS.GET_COLUMN_STATS

Fungsi ini mengambil statistik untuk kolom tertentu.

Sintaks

DBMS_STATS.GET_COLUMN_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2, 
        colname       VARCHAR2, 
        distcnt OUT NUMBER, 
        nullcnt OUT NUMBER, 
        avgclen OUT NUMBER
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema.	Ya
tabname	Nama tabel.	Ya
colname	Nama kolom.	Ya
distcnt	Jumlah nilai unik dalam kolom.	Ya
nullcnt	Jumlah nilai null dalam kolom.	Ya
avgclen	Panjang rata-rata kolom.	Ya

Example

Contoh berikut menunjukkan cara mengambil statistik untuk kolom:

DECLARE
    distcnt integer;
    nullcnt integer;
    avgclen integer;
BEGIN
    CALL DBMS_STATS.GET_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id', distcnt, nullcnt, avgclen);
    raise notice '%', distcnt;
    raise notice '%', nullcnt;
    raise notice '%', avgclen;
END;

DBMS_STATS.LOCK_TABLE_STATS

Mengunci statistik tabel untuk tabel tertentu.

Sintaks

DBMS_STATS.LOCK_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2 
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema tabel tersebut.	Ya
tabname	Nama tabel.	Ya

Example

Untuk mengunci statistik tabel, jalankan perintah berikut:

CALL DBMS_STATS.LOCK_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

Untuk melihat statistik yang terkunci, jalankan pernyataan berikut:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname = 'dbms_stats_schema.dbms_stats_test';

DBMS_STATS.UNLOCK_TABLE_STATS

Prosedur ini membuka kunci statistik tabel.

Sintaks

DBMS_STATS.UNLOCK_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2 
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang berisi tabel tersebut.	Ya
tabname	Nama tabel.	Ya

Example

Contoh ini membuka kunci statistik tabel:

CALL DBMS_STATS.UNLOCK_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

Jalankan kueri berikut untuk memeriksa statistik yang terkunci:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname = 'dbms_stats_schema.dbms_stats_test';

DBMS_STATS.LOCK_SCHEMA_STATS

Fungsi ini mengunci statistik untuk skema tertentu.

Sintaks

DBMS_STATS.LOCK_SCHEMA_STATS (
        ownname       VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang akan dikunci.	Ya

Example

Untuk mengunci statistik untuk skema:

CALL DBMS_STATS.LOCK_SCHEMA_STATS('dbms_stats_schema');

Untuk memeriksa statistik yang terkunci, jalankan kueri berikut:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema.%';

DBMS_STATS.UNLOCK_SCHEMA_STATS

Fungsi ini membuka kunci statistik skema tertentu.

Sintaksis

DBMS_STATS.UNLOCK_SCHEMA_STATS (
        ownname       VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang akan dibuka kuncinya.	Ya

Example

Untuk membuka kunci statistik skema:

CALL DBMS_STATS.UNLOCK_SCHEMA_STATS('dbms_stats_schema');

Untuk memeriksa statistik yang terkunci, jalankan kueri berikut:

SELECT count(*) FROM polar_dbms_stats.relation_stats_locked WHERE relname LIKE 'dbms_stats_schema.%';

DBMS_STATS.LOCK_COLUMN_STATS

Fungsi ini mengunci statistik untuk kolom tertentu.

Sintaks

DBMS_STATS.LOCK_COLUMN_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2, 
        attname       VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang berisi kolom tersebut.	Ya
tabname	Tabel yang berisi kolom tersebut.	Ya
attname	Nama kolom yang akan dikunci.	Ya

Example

Contoh ini mengunci statistik untuk kolom:

CALL DBMS_STATS.LOCK_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

DBMS_STATS.UNLOCK_COLUMN_STATS

Fungsi ini membuka kunci statistik untuk kolom tertentu.

Sintaks

DBMS_STATS.UNLOCK_COLUMN_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2, 
        attname       VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang berisi kolom tersebut.	Ya
tabname	Nama tabel yang berisi kolom tersebut.	Ya
attname	Nama kolom yang akan dibuka kuncinya.	Ya

Example

Contoh berikut membuka kunci statistik untuk kolom tertentu:

CALL DBMS_STATS.UNLOCK_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

DBMS_STATS.DELETE_TABLE_STATS

Prosedur ini menghapus statistik tabel.

Syntax

DBMS_STATS.DELETE_TABLE_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang memiliki tabel tersebut.	Ya
tabname	Nama tabel.	Ya

Example

Hapus statistik tabel:

CALL DBMS_STATS.DELETE_TABLE_STATS('dbms_stats_schema', 'dbms_stats_test');

Periksa statistik tabel setelah penghapusan:

SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname = 'dbms_stats_schema.dbms_stats_test';

DBMS_STATS.DELETE_COLUMN_STATS

Prosedur ini menghapus statistik kolom.

Sintaks

DBMS_STATS.DELETE_COLUMN_STATS (
        ownname       VARCHAR2, 
        tabname       VARCHAR2,
        attname       VARCHAR2
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang berisi kolom tersebut.	Ya
tabname	Nama tabel yang berisi kolom tersebut.	Ya
attname	Nama kolom yang akan dihapus statistiknya.	Ya

Example

Contoh berikut menghapus statistik kolom:

CALL DBMS_STATS.DELETE_COLUMN_STATS('dbms_stats_schema', 'dbms_stats_test', 'id');

DBMS_STATS.DELETE_SCHEMA_STATS

Prosedur ini menghapus statistik yang ada untuk skema tertentu.

Sintaks

DBMS_STATS.DELETE_SCHEMA_STATS (
        ownname       VARCHAR2 
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Nama skema yang statistiknya akan dihapus.	Ya

Example

Contoh berikut menghapus statistik skema:

CALL DBMS_STATS.DELETE_SCHEMA_STATS('dbms_stats_schema');

Untuk memverifikasi bahwa statistik telah dihapus, jalankan kueri berikut:

SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname LIKE 'dbms_stats_schema.%';

DBMS_STATS.DELETE_INDEX_STATS

Menghapus statistik untuk indeks.

Sintaks

DBMS_STATS.DELETE_INDEX_STATS (
        ownname       VARCHAR2,
        indname       VARCHAR2 
    );

Parameters

Parameter	Deskripsi	Wajib
ownname	Skema yang memiliki indeks tersebut.	Ya
indname	Nama indeks.	Ya

Example

Hapus statistik indeks:

CALL DBMS_STATS.DELETE_INDEX_STATS('dbms_stats_schema', 'dbms_stats_index');

Kueri statistik setelah penghapusan:

SELECT count(*) FROM polar_dbms_stats.relation_stats_backup WHERE relname LIKE 'dbms_stats_schema.%';