Oracle Query Performance issue and solved by accepting sql profile.
Overview of the issue
Oracle Query from application running was slower than the previous execution.
DB version is 12.1.0.2.0 running on Exadata 3rd compute node on Linux OS.
SQL_ID causing the slow performance => "g052r0ydnhn2z".
Step 1: Verified the details with below query.
SQL> select sid,serial#,inst_id,username ,logon_time ,status,sql_id,event from gv$session
where status='ACTIVE' and username='DW_STG';
4061 19594 3 DW_STG 04-jun-2019 14:21:23 ACTIVE g052r0ydnhn2z cell single block read request
Step 2: connected to PDB
SQL> sho pdbs
2 PDB$SEED READ ONLY NO
3 ORAPDW1 READ WRITE NO
SQL> alter session set container=ORAPDW1;
Step 3 : Ran the sqltrpt
SQL> @$ORACLE_HOME/rdbms/admin/sqltrpt
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: g052r0ydnhn2z
Sql Id specified: g052r0ydnhn2z
Note: removed some lines from output
Step 4:got the below recommendations
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
2 potentially better execution plans were found for this statement. Choose
one of the following SQL profiles to implement.
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_22924',
task_owner => 'SYS', replace => TRUE);
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_22924',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 96 will improve its response time
99.97% over the SQL profile plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 96.88% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
Step 5: Verified the execution plan section
Note: only cost value taken from the execution plan
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3894016934
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5019T| 2674P| | 18T (1)|999:59:59 |
|* 1 | HASH JOIN OUTER | | 5019T| 2674P| 585P| 18T (1)|999:59:59 |
--------------------------------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 2775156428
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5093M| 5578G| | 19M (1)| 00:51:19 |
|* 1 | HASH JOIN RIGHT OUTER | | 5093M| 5578G| 1879M| 19M (1)| 00:51:19 |
-----------------------------------------------------------------------------------------------------------------------
3- Using Parallel Execution
---------------------------
Plan hash value: 1670394672
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5093M| 7760G| 6401 (4)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
Step 6: Running sql profile
After checking the 2 recommendations, Taken the 1st recommendation over the
"parallel execution" and ran the below command
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_22924',
task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.
SQL>
Conclusion:
Query ran faster with out any issue.
See also
Troubleshooting queries on Oracle Database
Script to get CPU intensive queries in oracle
script to find load average on the OS Level
