![]() ![]() SELECT DBMS_SQLTUNE.report_tuning_task('5dkrnbx1z8gcb_tuning_task_1') AS recommendations FROM dual Ģ. Task_name => '5dkrnbx1z8gcb_tuning_task_1',ĭescription => 'Tuning task for statement 5dkrnbx1z8gcb') ĭBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id) ĮXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5dkrnbx1z8gcb_tuning_task_1') ![]() Scope => DBMS_SQLTUNE.scope_comprehensive, L_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( Run sql tuning advisor for sql_id=5dkrnbx1z8gcb set long 1000000000 In this example, first we will run SQL tuning advisor against an sql_id, and then will do different operations on sql profile.ġ. MERGE statements (the update or insert operations) INSERT statements (only with a SELECT clause)ĬREATE TABLE statements (only with the AS SELECT clause) SQL PROFILE can be applied to below statements. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. NOTE – It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. i.e when we run SQL tuning advisor against a query, The tuning optimizer creates a profile for an SQL statement called SQL profile which consists additional statistical information about that statement, and it gives the recommendation to accept that profile. SQL profile is managed through SQL_TUNE package of SQL tuning advisor. SQL profile is a collection of additional statistical information stored in the data dictionary that helps the optimizer to generate the best plan for the query. Lets today go through details about sql profile. You might have heard of SQL profile and SQL baselines in performance tuning chapters. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
June 2023
Categories |