Menggunakan pengoptimalan berbasis histori

Panduan ini menjelaskan cara mengaktifkan, menonaktifkan, dan menganalisis pengoptimalan berbasis histori untuk kueri.

Tentang pengoptimalan berbasis histori

Pengoptimalan berbasis histori menggunakan informasi dari eksekusi kueri serupa yang sudah selesai untuk menerapkan pengoptimalan tambahan dan lebih meningkatkan performa kueri seperti waktu slot yang digunakan dan latensi kueri. Misalnya, saat Anda menerapkan pengoptimalan berbasis histori, eksekusi kueri pertama mungkin memerlukan waktu 60 detik, tetapi eksekusi kueri kedua mungkin hanya memerlukan waktu 30 detik jika pengoptimalan berbasis histori diidentifikasi. Proses ini berlanjut hingga tidak ada pengoptimalan tambahan yang dapat ditambahkan.

Berikut adalah contoh cara kerja pengoptimalan berbasis histori dengan BigQuery:

Jumlah eksekusi	Waktu slot kueri yang digunakan	Catatan
1	60	Eksekusi asli.
2	30	Pengoptimalan berbasis histori pertama diterapkan.
3	20	Pengoptimalan berbasis histori kedua diterapkan.
4	21	Tidak ada pengoptimalan berbasis histori tambahan yang akan diterapkan.
5	19	Tidak ada pengoptimalan berbasis histori tambahan yang akan diterapkan.
6	20	Tidak ada pengoptimalan berbasis histori tambahan yang akan diterapkan.

Pengoptimalan berbasis histori hanya diterapkan jika ada keyakinan tinggi bahwa akan ada dampak yang menguntungkan terhadap performa kueri. Selain itu, jika pengoptimalan tidak meningkatkan performa kueri secara signifikan, pengoptimalan tersebut akan dicabut dan tidak digunakan dalam eksekusi kueri tersebut pada masa mendatang.

Peran dan izin

• Untuk mengaktifkan atau menonaktifkan pengoptimalan berbasis histori, Anda harus memiliki izin yang diperlukan untuk membuat konfigurasi default BigQuery, lalu Anda harus menggunakan pernyataan ALTER PROJECT untuk mengaktifkan pengoptimalan berbasis histori. Setelah Anda mengaktifkan pengoptimalan berbasis histori, semua tugas dalam project tersebut akan menggunakan pengoptimalan berbasis histori, terlepas dari pengguna yang membuat tugas tersebut. Untuk mempelajari lebih lanjut izin yang diperlukan untuk konfigurasi default, lihat Izin yang diperlukan untuk konfigurasi default. Untuk mengaktifkan pengoptimalan berbasis histori, lihat Mengaktifkan pengoptimalan berbasis histori.

• Untuk meninjau pengoptimalan berbasis histori untuk tugas menggunakan tampilan INFORMATION_SCHEMA.JOBS, Anda harus memiliki peran yang diperlukan. Untuk mengetahui informasi selengkapnya, lihat Peran yang diperlukan untuk tampilan INFORMATION_SCHEMA.JOBS.

Mengaktifkan pengoptimalan berbasis histori

Pengoptimalan berbasis histori umumnya tersedia dan di-deploy secara bertahap. Untuk mengaktifkan pengoptimalan berbasis histori secara manual untuk project Anda, sertakan parameter default_query_optimizer_options = 'adaptive=on' dalam pernyataan ALTER PROJECT atau ALTER ORGANIZATION. Contoh:

ALTER PROJECT PROJECT_NAME
SET OPTIONS (
  `region-LOCATION.default_query_optimizer_options` = 'adaptive=on'
);

Ganti kode berikut:

• PROJECT_NAME: nama project
• LOCATION: lokasi project

Catatan: Setelah mengaktifkan pengoptimalan berbasis histori, Anda dapat mengabaikan pesan peringatan berikut dengan aman, yang ditampilkan untuk pernyataan ALTER PROJECT atau ALTER ORGANIZATION yang berhasil: ALTER PROJECT succeeded. Please make sure no existing queries depend on the old defaults (such as the default time zone) or else these queries will be broken.

Menonaktifkan pengoptimalan berbasis histori

Untuk menonaktifkan pengoptimalan berbasis histori dalam project, sertakan parameter default_query_optimizer_options = 'adaptive=off' dalam pernyataan ALTER PROJECT atau ALTER ORGANIZATION. Contoh:

ALTER PROJECT PROJECT_NAME
SET OPTIONS (
  `region-LOCATION.default_query_optimizer_options` = 'adaptive=off'
);

Ganti kode berikut:

• PROJECT_NAME: nama project
• LOCATION: lokasi project

Catatan: Setelah menonaktifkan pengoptimalan berbasis histori, Anda dapat mengabaikan pesan peringatan berikut dengan aman, yang ditampilkan untuk pernyataan ALTER PROJECT atau ALTER ORGANIZATION yang berhasil: ALTER PROJECT succeeded. Please make sure no existing queries depend on the old defaults (such as the default time zone) or else these queries will be broken.

Meninjau pengoptimalan berbasis histori untuk tugas

Untuk meninjau pengoptimalan berbasis histori untuk tugas, Anda dapat menggunakan kueri SQL atau panggilan metode REST API.

SQL

Anda dapat menggunakan kueri untuk mendapatkan pengoptimalan berbasis histori untuk tugas. Kueri harus menyertakan INFORMATION_SCHEMA.JOBS_BY_PROJECT dan nama kolom query_info.optimization_details.

Pada contoh berikut, detail pengoptimalan ditampilkan untuk tugas yang disebut sample_job. Jika tidak ada pengoptimalan berbasis histori yang diterapkan, NULL akan dihasilkan untuk optimization_details:

SELECT
  job_id,
  query_info.optimization_details
FROM `PROJECT_NAME.region-LOCATION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'sample_job'
LIMIT 1;

Hasilnya akan terlihat seperti berikut:

-- The JSON in optimization_details has been formatted for readability.
/*------------+-----------------------------------------------------------------*
 | job_id     | optimization_details                                            |
 +------------+-----------------------------------------------------------------+
 | sample_job | {                                                               |
 |            |   "optimizations": [                                            |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "web_sales.web_date,RIGHT"         |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "catalog_sales.catalog_date,RIGHT" |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "semi_join_reduction": "store_sales.store_date,RIGHT"     |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "join_commutation": "web_returns.web_item"                |
 |            |     },                                                          |
 |            |     {                                                           |
 |            |       "parallelism_adjustment": "applied"                       |
 |            |     },                                                          |
 |            |   ]                                                             |
 |            | }                                                               |
 *------------+-----------------------------------------------------------------*/

API

Untuk mendapatkan detail pengoptimalan tugas, Anda dapat memanggil metode jobs.get.

Dalam contoh berikut, metode jobs.get menampilkan detail pengoptimalan (optimizationDetails) dalam respons lengkap:

{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "sample_job"
  }
}

Hasilnya akan terlihat seperti berikut:

-- The unrelated parts in the full response have been removed.
{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "sample_job",
    "location": "US"
  },
  "statistics": {
    "query": {
      "queryInfo": {
        "optimizationDetails": {
          "optimizations": [
            {
              "semi_join_reduction": "web_sales.web_date,RIGHT"
            },
            {
              "semi_join_reduction": "catalog_sales.catalog_date,RIGHT"
            },
            {
              "semi_join_reduction": "store_sales.store_date,RIGHT"
            },
            {
              "join_commutation": "web_returns.web_item"
            },
            {
              "parallelism_adjustment": "applied"
            }
          ]
        }
      }
    }
  }
}

Memperkirakan dampak pengoptimalan berbasis histori

Untuk memperkirakan dampak pengoptimalan berbasis histori, Anda dapat menggunakan contoh kueri SQL berikut untuk mengidentifikasi kueri project dengan estimasi peningkatan terbesar terhadap waktu eksekusi.

  WITH
    jobs AS (
      SELECT
        *,
        query_info.query_hashes.normalized_literals AS query_hash,
        TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS elapsed_ms,
        IFNULL(
          ARRAY_LENGTH(JSON_QUERY_ARRAY(query_info.optimization_details.optimizations)) > 0,
          FALSE)
          AS has_history_based_optimization,
      FROM region-LOCATION.INFORMATION_SCHEMA.JOBS_BY_PROJECT
      WHERE EXTRACT(DATE FROM creation_time) > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    ),
    most_recent_jobs_without_history_based_optimizations AS (
      SELECT *
      FROM jobs
      WHERE NOT has_history_based_optimization
      QUALIFY ROW_NUMBER() OVER (PARTITION BY query_hash ORDER BY end_time DESC) = 1
    )
  SELECT
    job.job_id,
    100 * SAFE_DIVIDE(
      original_job.elapsed_ms - job.elapsed_ms,
      original_job.elapsed_ms) AS percent_execution_time_saved,
    job.elapsed_ms AS new_elapsed_ms,
    original_job.elapsed_ms AS original_elapsed_ms,
  FROM jobs AS job
  INNER JOIN most_recent_jobs_without_history_based_optimizations AS original_job
    USING (query_hash)
  WHERE
    job.has_history_based_optimization
    AND original_job.end_time < job.start_time
  ORDER BY percent_execution_time_saved DESC
  LIMIT 10;

Hasil kueri sebelumnya mirip dengan berikut ini jika pengoptimalan berbasis histori diterapkan:

  /*--------------+------------------------------+------------------+-----------------------*
   |    job_id    | percent_execution_time_saved | new_execution_ms | original_execution_ms |
   +--------------+------------------------------+------------------+-----------------------+
   | sample_job1  |           67.806850186245114 |             7087 |                 22014 |
   | sample_job2  |           66.485800412501987 |            10562 |                 31515 |
   | sample_job3  |           63.285605271764254 |            97668 |                266021 |
   | sample_job4  |           61.134141726887904 |           923384 |               2375823 |
   | sample_job5  |           55.381272089713754 |          1060062 |               2375823 |
   | sample_job6  |           45.396943168036479 |          2324071 |               4256302 |
   | sample_job7  |           38.227031526376024 |            17811 |                 28833 |
   | sample_job8  |           33.826608962725111 |            66360 |                100282 |
   | sample_job9  |           32.087813758311604 |            44020 |                 64819 |
   | sample_job10 |           28.356416319483539 |            19088 |                 26643 |
   *--------------+------------------------------+------------------+-----------------------*/

Hasil kueri ini hanyalah estimasi dampak pengoptimalan berbasis histori. Banyak faktor yang dapat memengaruhi performa kueri, termasuk tetapi tidak terbatas pada ketersediaan slot, perubahan data dari waktu ke waktu, definisi tampilan atau UDF, dan perbedaan nilai parameter kueri.

Jika hasil kueri contoh ini kosong, berarti tidak ada tugas yang telah menggunakan pengoptimalan berbasis histori, atau semua kueri dioptimalkan lebih dari 30 hari yang lalu.

Kueri ini dapat diterapkan ke metrik performa kueri lainnya seperti total_slot_ms dan total_bytes_billed. Untuk mengetahui informasi selengkapnya, lihat skema untuk INFORMATION_SCHEMA.JOBS.