שימוש באופטימיזציות שמבוססות על היסטוריה

במדריך הזה מוסבר איך להפעיל, להשבית ולנתח אופטימיזציות מבוססות-היסטוריה של שאילתות.

מידע על אופטימיזציות שמבוססות על היסטוריה

אופטימיזציות שמבוססות על היסטוריה משתמשות במידע מביצועים שכבר הושלמו של שאילתות דומות כדי להחיל אופטימיזציות נוספות ולשפר עוד יותר את ביצועי השאילתות, כמו זמן השימוש במשבצת וחביון השאילתה. לדוגמה, כשמחילים אופטימיזציה שמבוססת על היסטוריה, הביצוע הראשון של השאילתה עשוי להימשך 60 שניות, אבל הביצוע השני של השאילתה עשוי להימשך רק 30 שניות אם זוהתה אופטימיזציה שמבוססת על היסטוריה. התהליך הזה נמשך עד שלא נותרו אופטימיזציות נוספות להוספה.

הדוגמה הבאה ממחישה איך אופטימיזציות מבוססות-היסטוריה פועלות עם BigQuery:

מספר הביצועים	זמן המשבצת של השאילתה שנצרך	הערות
1	60	הביצוע המקורי.
2	30	האופטימיזציה הראשונה שמבוססת על היסטוריה.
3	20	בוצעה אופטימיזציה שנייה על סמך היסטוריית הנתונים.
4	21	אין אופטימיזציות נוספות שמבוססות על היסטוריה שאפשר להחיל.
5	19	אין אופטימיזציות נוספות שמבוססות על היסטוריה שאפשר להחיל.
6	20	אין אופטימיזציות נוספות שמבוססות על היסטוריה שאפשר להחיל.

אופטימיזציות שמבוססות על היסטוריה מופעלות רק כשיש סבירות גבוהה שהן ישפרו את ביצועי השאילתה. בנוסף, אם אופטימיזציה לא משפרת באופן משמעותי את ביצועי השאילתה, היא מבוטלת ולא נעשה בה שימוש בהרצות עתידיות של אותה שאילתה.

תפקידים והרשאות

• כדי להפעיל או להשבית אופטימיזציות שמבוססות על היסטוריה, צריכות להיות לכם ההרשאות הנדרשות ליצירת הגדרות ברירת מחדל של BigQuery, ואז תצטרכו להשתמש בהצהרת ALTER PROJECT כדי להפעיל אופטימיזציות שמבוססות על היסטוריה. אחרי שמפעילים אופטימיזציות מבוססות-היסטוריה, כל המשימות בפרויקט הזה משתמשות באופטימיזציות מבוססות-היסטוריה, בלי קשר למשתמש שיצר את המשימה. מידע נוסף על ההרשאות הנדרשות להגדרות ברירת מחדל זמין במאמר ההרשאות הנדרשות להגדרות ברירת מחדל. כדי להפעיל אופטימיזציות שמבוססות על היסטוריה, אפשר לעיין במאמר בנושא הפעלת אופטימיזציות שמבוססות על היסטוריה.

• כדי לבדוק את האופטימיזציות שמבוססות על היסטוריה של משימה באמצעות התצוגה INFORMATION_SCHEMA.JOBS, צריך להיות לכם התפקיד הנדרש. מידע נוסף זמין במאמר בנושא התפקיד הנדרש לתצוגה של INFORMATION_SCHEMA.JOBS.

הפעלת אופטימיזציות שמבוססות על היסטוריה

אופטימיזציות שמבוססות על היסטוריה מופעלות כברירת מחדל. אם השבתתם אופטימיזציות שמבוססות על היסטוריה בפרויקט או בארגון, אתם יכולים להפעיל מחדש אופטימיזציות שמבוססות על היסטוריה באופן ידני. לשם כך, צריך לכלול את הפרמטר default_query_optimizer_options = 'adaptive=on' בהצהרה ALTER PROJECT או ALTER ORGANIZATION. לדוגמה:

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

מחליפים את מה שכתוב בשדות הבאים:

• ‫PROJECT_NAME: שם הפרויקט
• ‫LOCATION: המיקום שבו המערכת תנסה להשתמש באופטימיזציות מבוססות-היסטוריה כדי להריץ את המשימות

הערה: אחרי שמפעילים אופטימיזציות שמבוססות על היסטוריה, אפשר להתעלם בבטחה מההודעה הבאה, שמוצגת לכל הצהרת ALTER PROJECT או ALTER ORGANIZATION מוצלחת: 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.

השבתת אופטימיזציות שמבוססות על היסטוריה

כדי להשבית את האופטימיזציות שמבוססות על היסטוריה בפרויקט, צריך לכלול את הפרמטר default_query_optimizer_options = 'adaptive=off' בהצהרה ALTER PROJECT או ALTER ORGANIZATION. לדוגמה:

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

מחליפים את מה שכתוב בשדות הבאים:

• ‫PROJECT_NAME: שם הפרויקט
• ‫LOCATION: המיקום שבו המערכת לא תנסה להשתמש באופטימיזציות מבוססות-היסטוריה כדי להציג מודעות

הערה: אחרי שמשביתים את האופטימיזציות שמבוססות על היסטוריה, אפשר להתעלם בבטחה מההודעה הבאה, שמוצגת לכל הצהרת ALTER PROJECT או ALTER ORGANIZATION מוצלחת: 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.

בדיקת אופטימיזציות מבוססות-היסטוריה למשרה

כדי לעיין באופטימיזציות מבוססות-היסטוריה של עבודה, אפשר להשתמש בשאילתת SQL או בהפעלת method של API בארכיטקטורת REST.

SQL

אפשר להשתמש בשאילתה כדי לקבל את האופטימיזציות שמבוססות על היסטוריה של עבודה. השאילתה צריכה לכלול את INFORMATION_SCHEMA.JOBS_BY_PROJECT ואת שם העמודה query_info.optimization_details.

בדוגמה הבאה, פרטי האופטימיזציה מוחזרים עבור משימה בשם sample_job. אם לא בוצעה אופטימיזציה על סמך היסטוריה, המערכת תפיק את NULL עבור 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;

התוצאות אמורות להיראות כך:

-- 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

כדי לקבל את פרטי האופטימיזציה של משימה, אפשר לקרוא לשיטה jobs.get.

בדוגמה הבאה, המתודה jobs.get מחזירה את פרטי האופטימיזציה (optimizationDetails) בתגובה המלאה:

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

התוצאות אמורות להיראות כך:

-- 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"
            }
          ]
        }
      }
    }
  }
}

הערכת ההשפעה של אופטימיזציות שמבוססות על היסטוריה

כדי להעריך את ההשפעה של אופטימיזציות שמבוססות על היסטוריה, אפשר להשתמש בשאילתת ה-SQL לדוגמה הבאה כדי לזהות שאילתות של פרויקטים עם השיפור המשוער הגדול ביותר בזמן הביצוע.

  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;

התוצאה של השאילתה הקודמת דומה לתוצאה הבאה אם בוצעו אופטימיזציות שמבוססות על היסטוריה:

  /*--------------+------------------------------+------------------+-----------------------*
   |    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 |
   *--------------+------------------------------+------------------+-----------------------*/

תוצאות השאילתה הזו הן רק אומדן של ההשפעה של אופטימיזציה שמבוססת על היסטוריה. יש הרבה גורמים שיכולים להשפיע על הביצועים של שאילתות, כולל, בין היתר, זמינות של משבצות, שינויים בנתונים לאורך זמן, הגדרות של תצוגות או של פונקציות מוגדרות על ידי המשתמש (UDF), והבדלים בערכים של פרמטרים של שאילתות.

אם התוצאה של שאילתת הדוגמה הזו ריקה, זה אומר שאף עבודה לא השתמשה באופטימיזציות מבוססות-היסטוריה, או שכל השאילתות עברו אופטימיזציה לפני יותר מ-30 יום.

אפשר להחיל את השאילתה הזו על מדדי ביצועים אחרים של שאילתות, כמו total_slot_ms ו-total_bytes_billed. מידע נוסף זמין בסכימה של INFORMATION_SCHEMA.JOBS.