8/0123, 10:53 PM Document 262687.1 CCopytight (6) 2028, Oade Al rights reserved, Orade Confidential Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor (Doc ID 262687.1) Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database - Enterprise Edition - Version 10.2.0.1 and later Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later Information in this document applies to any platform. the This document provides an introduction to the use of the SQL TUNING ADVISOR (STA) DBMS_SQLTUNE package. DBAs and Support Analysts NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. ‘The SQL Tuning Advisor automates the entire SQL tuning process replacing manual SQL tuning. It analyzes candidate SQL statements, and executes a complete analysis of the statements including: + Determining stale or missing statistics + Determining better execution plans + Detecting better access paths and objects required to satisfy them (indexes, materialized views) + Restructuring SQL While the primary interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control, the advisor can also be controlled with procedures in the DBMS_SQLTUNE package. Output can be queried via the various advisory views in SQL*Plus. To use these procedures the user must have been granted the DBA role and the ADVISOR privilege (if using the SQL. ‘Tuning Advisor in Oracle Enterprise Manages, the user must have been granted the select_catalog_role role). Running SQL Tuning Advisor using the DBMS_SQLTUNE package is a two-step process: 1, Create a SQL tuning task 2, Execute a SQL tuning task NOTE: You can run SQL. Tuning Advisor from the command line using $ORACLE_HOME/rdbms/admin/saltrpt.sql Example: This example is based on the "SH" account executing the various tasks. To allow the "SH" user to both create task and execute it user "SH" needs to be granted proper access: hitpsIsupportoracle.comfepmosifaces/DacumentDisplay’_adct-state=19czqhlw00_1928id=262687.1 188/0123, 10:53 PM Document 262687.1 comect / as sysoa [GRANT EXECUTE ON DBRS_SQLTUNE TO SH; ‘The example presented makes use of a table called SALES, residing in the SH schema. The table Is not analyzed. + Create a SQL tuning task You can create tuning tasks from the following ‘SQL statement selected by SQL identifier from the cursor cache ‘SQL Tuning Set containing multiple statements Text of a single SQL statement SQL statement selected by SQL identifier from the Automatic Workload Repository. You can find examples how to do this in the following manual: Oracle Database Online Documentation 12c Release 1 (12.1) Database Administration Database PL/SQL Packages and Types Reference Section 154 DBMS_SQLTUNE http://docs.oracle.com, 121 /ARPL tun, htm#ARPLS73098 ‘To use a create a task for a particular SQL statement, you can use the followin: name VARCIAR2( 38); mycsaltent s+ ‘SELECT * II FROM sales * || ‘WHERE prod_sd = 10 AND * || “cust_id = 208°; ry t25K_ame = OBNS_SQLTUNE.CREATE_TUNING.TASK( sel_tert => my saitert, sone Cownensive', pane => 'TEST_sqi_tuning task’, Gesctiption => ‘Task t3 tune 3 query on a specified PRODUCT"); or us salts aeciare ask VARCHAR (4; > “Setrekinn23")s Parameter explanation: © User_name: User under which the CREATE_TUNING_TASK function analyzes the SQL statement. © Scope: COMPREHENSIVE. This means that the advisor also performs SQL. Profiling analysis, © Time_limit: Time in seconds that the function can run. ‘The CREATE_TUNING_TASK function simply creates the task and returns either the task name that you have provided or generates a unique task name. This task name is the identifier by which you execute it (or perform ther procedures upon it). To view the task names associated with a specific owner, you can run the followin + Execute the tuning task.: une. beecute tuning task (task nane => “TEST sql_tuning. task’); hitpsIsupportoracle.comfepmosifaces/DacumentDisplay’_adct-state=19czqhlw00_1928id=262687.1 288/0123, 10:53 PM Document 262687.1 + Check the status of the task using following query: + View the Recommendations set long 68536 fet Linestze 12 we.report_tuning_task(‘TEST_sa_tuning_task') from duals ‘The output of above will be lke this: (8¥S_SQLTUNE, REPORT_TUNING_TASK('TEST_SOL_TUMING_TASK") Tuning Task Nane TEST sei_tuning task Tine Lintt(seconds): 62 22/08/2088 23:27:49 (2/04/2004 23:38:29, Sai 10 7 Sommraypanere _DNS_SQLTUNE REPORT_TUNING_IASK(TEST_SOL_TUNIMG_TASK") SQL Text: SELECT * FROW sales WHERE prog_id = 30 9D cust_sd = 200 FINOINGS SECTION (5 Finaings) index vas not analyze. Aeconenaation (as_SQLTUNE.REPORT_TNING_TASK(TEST_SOL_TUMIMG TASK’) Consider collecting optinizen statistics for this, index execute otns_* reincex.stats(ownnane => “SH, indnane => SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views can also be queried to get this information. NOTE: it is possible for the SQL Tuning Advisor to return no recommendations for a particular SQL statement e.g. in cases where the plan Is already optimal or the Automatic Tuning Optimization mode cannot find a better plan. NOTE:271196.1 - Automatic SQL Tuning and SQL Profiles NOTE:2022194.1 - How to "Fix" a Slow Query Using SQL Advisory Through Grid Control Didn't find what you are looking for? hitpsIsupportoracle.comfepmosifaces/DacumentDisplay’_adct-state=19czqhlw00_1928id=262687.1 38