Simple SQL tunning statistics

 EXPLAIN PLAN FOR

-- Your SQL query goes here;

SELECT * FROM your_table WHERE your_condition;

-- Display the execution plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Gather statistics for tables involved in the query

EXEC DBMS_STATS.GATHER_TABLE_STATS('your_schema', 'your_table', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

-- Create a SQL Tuning Task

DECLARE

  task_name VARCHAR2(30);

BEGIN

    task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

    sql_id => 'your_sql_id_here', 

    scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, 

    time_limit => 60);

    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);

END;

/

-- Display recommendations from the SQL Tuning Advisor

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('your_task_name_here') AS recommendations FROM DUAL;

DECLARE 

  l_sql_tune_task_id  VARCHAR2(100); 

BEGIN 

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 

                          sql_id      => '<sql_id>', 

                          scope       => DBMS_SQLTUNE.scope_comprehensive, 

                          time_limit  => 500, 

                          task_name   => 'tuning_task_name', 

                          description => 'Tuning task1 for tuning_task_name'); 

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 

END; 

/ 

 

 

 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tuning_task_name'); 

 

 

set long 65536 

set longchunksize 65536 

set linesize 100 

select dbms_sqltune.report_tuning_task('tuning_task_name') from dual;

In Oracle Database, the "EXPLAIN PLAN" statement is used to display the execution plan that the database optimizer has chosen for a SQL statement. The execution plan outlines the steps and operations that the database will perform to retrieve the requested data. If a query is running slow and its cost is high in the explain plan, it indicates that the optimizer has chosen a suboptimal plan for execution, resulting in poor performance.

Here are some common reasons for a high-cost execution plan:

1. Missing or Outdated Statistics: The optimizer relies on statistics about the tables and indexes involved in the query to make informed decisions. If these statistics are outdated or missing, the optimizer may choose an inefficient plan.

2. Complex Queries: Complex queries with multiple joins, subqueries, or large result sets can result in high costs. Optimizing such queries may involve restructuring them or creating appropriate indexes.

3. Lack of Indexes: If there are no indexes or if existing indexes are not being utilized efficiently, it can lead to full table scans and increased query costs. Adding or modifying indexes may improve performance.

4. Inefficient Joins: The optimizer may choose an inefficient join method, such as nested loops instead of hash joins, leading to increased query cost. Adjusting join orders or using hints may help in optimizing the plan.

5. Insufficient Hardware Resources: If the server doesn't have enough resources, such as CPU or memory, the database may choose plans that are less resource-intensive but slower. Adequate hardware resources should be provisioned.

6. Optimizer Parameters: The database optimizer has various parameters that control its behavior. In some cases, adjusting these parameters or using hints can influence the chosen execution plan.

To address these issues, you can take the following steps:

• Update statistics on tables and indexes.
• Review and optimize the query itself.
• Ensure that appropriate indexes are present and being used.
• Consider using hints to guide the optimizer.
• Monitor system resources and address any deficiencies.