DBA NAVIGATOR

🧱 Steps to Purge SQL Plan from the Shared Pool In some cases, after assigning an SQL profile for a particular SQL_ID, it may not be used as expected. This guide will help you ensure that the SQL profile is being used and, if necessary, purge the SQL plan from the shared pool to force the system to reoptimize and apply the assigned SQL profile. 🔧 Step-by-Step Instructions 1️⃣ Verify if the SQL Profile is Being Used Before purging any SQL plan, you should first check if the assigned SQL profile is being used. Run the following query to check: select distinct p.name sql_profile_name, s.sql_id from dba_sql_profiles p, DBA_HIST_SQLSTAT s where p.name = s.sql_profile and sql_id = '5dj81jnf8t03a'; -- Replace with your SQL_ID If the profile is being used, the output will display the associated SQL profile name. Alternatively, you can use the DBMS_XPLAN function to check the SQL profile: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('...

🧱 How to Run SQL Tuning Advisor (STA) in Oracle The SQL Tuning Advisor (STA) is a powerful tool in Oracle that helps database administrators identify and resolve performance issues in SQL statements. This guide provides a step-by-step approach to using STA effectively. In this guide, we'll walk you through a clean and safe method to create a new tuning task, execute it, check its status, and view the recommendations. 🔧 Step-by-Step Instructions 1️⃣ Create the Tuning Task for the Given SQL_ID DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => 'abc123xyz7890', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => 'abc123xyz7890_tuning_task', description => 'Tuning task for statement abc123xyz7890'); ...