Tag Archives: performance

Manage the SQL Profile in the Oracle Database

Manage the SQL Profile in the Oracle Database

SQL Profile is used by Optimizer for use more efficient execution plan for the SQL Query to make in accepted State. It is found in DBA_SQL_PROFILES view. It used to choose better plan by optimizer if the SQL profile plan is having low cost then optimizer use it.

Check the SQL Profile in the Database.

select name, type, status, sql_text from dba_sql_profiles;

Check the SQL Profile internal hint information

SELECT
a.name
,b.comp_data
FROM dba_sql_profiles a
,dbmshsxp_sql_profile_attr b
WHERE a.name = b.profile_name;

Create the SQL Profiles
Two ways:
1. Creating the SQL Profiles while running the manual SQL tuning task
For manually creating with SQL Tuning task follows the link:
SQL Tuning Advisory

2. Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher).
Check SQL profiles that have automatically been created having the value “AUTO” in the TYPE column of the DBA_SQL_PROFILES views

--Check
select name, type, status, sql_text from dba_sql_profiles;

-- Enable
exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

-- Disable
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');

Disable the SQL Profiles

select name, status from dba_sql_profiles;

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_789tfag56hjli0004',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/

Drop the SQL Profile

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_9824ryfg6f7d78653');

--Drop all profiles in a database, using PL/SQL block:
declare
cursor c1 is select name from dba_sql_profiles;
begin
for r1 in c1 loop
dbms_sqltune.drop_sql_profile(r1.name);
end loop;
end;
/

Advertisements

Join Method in explain plan of SQL Query

Join Method in explain plan of SQL Query

Optimizer is using the wrong join type, need to change the join method for increase the speed of the SQL Queries.
You can override the join type by placing the appropriate hint in the query. You can test the SQL Query performance by changing its Join Method like Nested loop to hash join etc.

Three Join Methods:
NESTED LOOPS
HASH
SORT MERGE

NESTED LOOPS Join
Use the USE_NL hint for using in SQL Query.
The nested loops join is consider best for joining small tables.
In a nested loops join, one table is known as driving table(outer table). For each row of the outer, each row in the inner table is searched for matching rows.

In the Example: Employee table is outer table and Department table is inner table:

SELECT /*+ use_nl(emp, dept) */ FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES emp JOIN DEPARTMENTS dept USING (deptno);

------------------------------------------------------
| Id | Operation                       | Name        |
------------------------------------------------------
| 0 | SELECT STATEMENT                 |             |
| 1 | NESTED LOOPS                     |             |
| 2 | NESTED LOOPS                     |             | 
| 3 | TABLE ACCESS FULL                | DEPARTMENTS |
| 4 | INDEX RANGE SCAN                 | EMP_DEP_IDX |
| 5 | TABLE ACCESS BY INDEX ROWID      | EMPLOYEES   |
------------------------------------------------------

HASH Join
Hash joins are used for joining large amounts of data or larger table. The smaller of the two tables is used by the optimizer to build a hash table on the join key between the two tables.
Use the USE_HASH hint for using in SQL Query.
In the example, the DEPT table is the smaller tablev and used to build the hash table:

SELECT /*+ use_hash(emp , dept) */ first_name, department_name
FROM employees emp JOIN departments dept USING (deptno);

-------------------------------------
| Id | Operation        | Name      |
-------------------------------------
| 0 | SELECT STATEMENT  |           |
| 1 | HASH JOIN         |           |
| 2 | TABLE ACCESS FULL | DEPT      |
| 3 | TABLE ACCESS FULL | EMPLOYEES |
-------------------------------------

SORT MERGE Join
Sort merge joins are also used to join a large data. Sort merge join is used when the join condition between the tables is not an equijoin.
Use the USE_MERGE hint for using in SQL Query.
In the following example, the input data from both tables is sorted on the join key, and then merged together.


select /*+ use_merge(emp, dept) */ first_name , emp.department_id
from hr.employees emp , hr.departments dept
where emp.department_id = dept.department_id and dept. department_id 20;

----------------------------------------------------
| Id | Operation                  | Name           |
----------------------------------------------------
| 0 | SELECT STATEMENT            |                |
| 1 | MERGE JOIN                  |                |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS    |
| 3 | INDEX FULL SCAN             | DEPARTMENTS_PK |
| 4 | SORT JOIN                   |                |
| 5 | TABLE ACCESS FULL           | EMPLOYEES      |
----------------------------------------------------

Package DBMS SQL MONITOR used for SQL Monitoring Oracle

Package DBMS SQL MONITOR used for SQL Monitoring performance issue

DBMS_SQL_MONITOR for real time monitoring the SQL Statements.
You will get the real time execution for the query.
For using DBMS_SQL_MONIOR, you need to the STATISTICS_LEVEL parameter to be set to ‘TYPICAL’ or ‘ALL’, and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to ‘DIAGNOSTIC+TUNING’.
It’s mainly available in enterprise edition.

Steps to use the DBMS_SQL_MONITOR package for monitoring the SQL Statements:

1. Start the process of Monitoring with following package

variable v_sql_mon_beg number;
begin
:v_sql_mon_beg := dbms_sql_monitor.begin_operation (dbop_name =>'QUERY1', dbop_eid => null , forced_tracking => 'Y');
END;
/
PRINT :v_sql_mon_beg
v_sql_mon
---------
1

2. Execute your SQL statement/load which need to be checked.

Select count(*) from ic.tran;

3. End the operation with following package:

Exec dbms_sql_monitor.end_operation (dbop_name => 'Query1' , dbop_eid => :v_sql_mon) ;

4. Fetch the monitoring report

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL E:\report1.html
SELECT DBMS_SQL_MONITOR.report_sql_monitor(
dbop_name => 'Query1',
type => 'HTML',
report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

Use of optimizer index cost adj Parameter in Oracle

Use of optimizer_index_cost_adj Parameter in Oracle

Optimizer_index_cost_adj parameter is an initialization parameter that can be very useful for SQL tuning. It is a numeric parameter with values from zero to 10,000 and a default value of 100.

General Setting for OLTP environment:

Alter system Set optimizer_index_caching=80 scope=both;
alter system set optimizer_index_cost_adj=25 scope=both;

OPTIMIZER_INDEX_COST_ADJ is used for choosing the access path selection to be more or less index over a full table scan. As value is making below 100 then it will go to choose index path instead of full table scan.
For OLTP systems, setting parameter to a smaller value (around 25) may result in performance gains as SQL statements change from large-table full-table scans to index range scans.

Note: Value 100 means optimizer has equal rights to choose from multiple path or index path.

A single block I/O as performed typically by an index range scan is measured via the “db file sequential read” wait event while the multi-block I/O as typically performed during a FTS is measured via the “db file scattered read” wait event.

For Better Result
In order to determine these wait events during a specific time period to perhaps better reflect typical loads during these times, one could also simply run a Statspack or an AWR report and look at the wait event section of the report.

Find optimal value from v$system_event with following formula: (it better to get from AWR or Statspack report)

Optimizer_index_cost_adj = (Avg waits DB file Sequential read/Avg waits DB file scattered read) * 100

alter session set optimizer_index_cost_adj = 50;

Note: For example, a setting of 50 makes the index access path look half as expensive as normal.

Following query give parameter value according to current waits:

- scattered read (full table scans) are fast at 13ms (c3)
- sequential reads (index probes) take much longer 86ms (c4)
- starting setting for optimizer_index_cost_adj at 36:
select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits)*100 c3,
b.total_waits /(a.total_waits + b.total_waits)*100 c4,
(b.average_wait / a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read';

C1      C2     C3  C4  C5
------- ------ --- --- ---
13,824  5,072  13  86  36   


We always expert scattered reads (full-table scans) to be far faster than sequential reads (index probes) because of Oracle sequential pre fetch (see db_file_multiblock_read_count):

select value from sys.v_$parameter where name = 'db_file_multiblock_read_count';

alter session set db_file_multiblock_read_count = 32768;

Another query to find the optimal value

set pages 80
set lines 130
col c1 heading 'Average Waits for|Full Scan Read I/O' format 999999.999
col c2 heading 'Average Waits for|Index Read I/O' format 999999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 999.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 999.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 99999
select a.snap_id "Snap",
sum(a.time_waited_micro)/sum(a.total_waits)/10000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/10000 c2,
(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c3,
(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c4,
(sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5
from
dba_hist_system_event a,
dba_hist_system_event b
where a.snap_id = b.snap_id
and a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read';

Cursor Mutex S wait event and too many child cursors in Oracle

Cursor Mutex S wait event and too many child cursors in Oracle

Mutexes are a lighter-weight and more granular concurrency mechanism. If one session is changing a data structure in memory,
then another session must wait to acquire the mutex before it can make a similar change.

The wait event: “Cursor: mutex S”. The mutex S is a serialization mutex for the cursor cache. Library cache mutex – X is held in exclusive mode by a session
It is involved, as soon as two sessions try to (hard/soft) parse the same statement in sense of SQL_IDs.
Cursor: mutex S wait occurs when Oracle is serializing parsing of multiple SQL statements.

We upgraded the database on 12.2 Environment from 11g, we are start getting

Oracle Wait: “”Cursor: mutex S”. The mutex S is a serialization mutex for the cursor cache.

You can check the child cursor having mainly update or insert then select in version count in AWR report

Top 10 Foreground Events by Total Wait Time

Event             Waits       Total Wait Time (sec)   Avg Wait   % DB time   Wait Class
cursor: mutex X   5,698,887   512.1K                  89.86ms    79.6        Concurrency 
DB CPU                         22.1K                              3.4   
cursor: mutex S   1,616,430    10.7K                   6.62ms     1.7        Concurrency
log file sync     546,786     2030.3                   3.71ms      .3        Commit 

It may be caused of version SQL present in database: ( check awr report for following section)

SQL ordered by Version Count

Version Count  Executions  SQL Id         SQL Module     SQL Text
4,096                      crxk85q87pug9                 SELECT COUNT(:"SYS_B_0") FROM ... 
1,426                      9gm72hfpbn449                 SELECT entity_no, date_capture... 
1,086                      6rkf3uzawjtwk      
659            118         79msc2b6tcv7a  StmtCOM64.exe  SELECT status, page_count, phy... 
608                        9ktt2cdn2cndn                 SELECT COUNT(:"SYS_B_0") FROM ... 
261                        1y4f4wtu63797                 WITH MONITOR_DATA AS (SELECT I... 
161            33          c7v3v03hs7528  w3wp.exe       SELECT IC.Tran.*, rowidtochar(... 

With peak hours, lot of update statement run the child cursor is increasing and causing performance issue.
On checking with v$sql immediately showed an select statement that has more than 1000 child cursors for the same SQL_ID,
it was mostly the culprit sql because creating new child cursors means expensive hard parsing,
and searching the list of existing children means CPU load as well.

You can check the child cursor count from the following queries:

Check Number of child cursor present in database:

select a.cursors, a.sql_id,b.sql_text
from
(
select count(*) as cursors, ssc.sql_id
from v$sql_shared_cursor ssc
group by ssc.sql_id
order by cursors desc
) a,
(
select sa.sql_id, sa.sql_text, sa.parsing_schema_name from v$sqlarea sa
) b
where a.sql_id=b.sql_id and b.parsing_schema_name = 'SCOTT';

The number of child cursors and the appearance of the “Cursor: mutex S” wait event is closely related,
a stable and well-working workaround was to flush out the particular cursor or flush out the shared pool
but shared pool flush sometime is expensive in Production environment.

Solution

1. Set the following paremeter may fixed the issue:

alter system set "_cursor_obsolete_threshold"=1024

Temporary solution
Scheduled a job in dbms_scheduler, running every 1 minute, executing dbms_shared_pool.purge for flushing the sql id cursors:

DECLARE
SQ_ADD VARCHAR2(100) := '';
BEGIN
execute immediate 'select address from v$sqlarea where sql_id = ''someSQLIDfoo''' into SQ_ADD;
dbms_shared_pool.purge (SQ_ADD||',123454321','C');
END;

3. Flush the shared pool if you are having large no of sql having multiple version count.

alter system flush shared_pool;

In my case 12.2 oracle, optimizer parameter disable fixed the issue. It reduced the version count sqls in awr report from next day.
May be the issue with optimizer adaptive plans in 12c, when i updated this parameter to false, then my version count is going down.
adaptive cursor sharing is playing.

alter system set optimizer_adaptive_plans=false scope=both;

Explain for child cursor:
On tracing the sql queries using bind variable at level 10046 traces, level 12.

found that the bind varialbe is treated as different in some case NULL value, some as varchar2 or in some as number data type.
if you have 10 column in where clause using 10 bind variable then it may lead to lot of production of child plans.

High CPU SQL in AWR EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS

High CPU consumption in AWR Report EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS()

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS dbms_job is used for Enterprise Manager (EM) Database Control in 10g and above versions of the database.
NOTE : The dbms job EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS is present in the database running EM Database Control and the note does not apply to EM Grid Control.

In AWR report it show large no of execution and consuming High CPU

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

On checking the dbms_jobs table:

select job, schema_user, last_date, next_date, interval, what from dba_jobs;

 JOB NEXT_DATE NEXT_SEC DESCRIPTION                                                                                                                                                  
---- --------- -------- ----------------------------------------                                                                                                                     
4001 29-AUG-17 18:43:01 wwv_flow_cache.purge_sessions(p_purge_se                                                                                                                     
4002 29-AUG-17 11:50:43 wwv_flow_mail.push_queue(wwv_flow_platfo                                                                                                                     
  23 29-AUG-17 11:41:43 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROC 

JOB SCHEMA_USER WHAT                                         INTERVAL
--- ----------- -------------------------------------------- -----------------------
 23 SYSMAN      EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); sysdate + 1 / (24 * 60)

Solution:

Option 1: If enterprise manager database control is not needed.
If EM database control is not used, we did not configured it then we removed the job which caused performance issue.
For Remove the job:

execute dbms_job.remove(21);

OR

exec sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> commit;

Option 2nd: If Enterprise Manager database console is needed.
For improve the performance we need to rebuild index and reorganise the related tables as below steps:

1. Stop the DB Console service on all the nodes (if it’s running)
2. Stop the EM jobs by logging in as SYSMAN and run:

exec emd_maintenance.remove_em_dbms_jobs;
commit;

3. Reorganise the table as SYS or SYSMAN:

CREATE TABLE temp_dep AS SELECT * FROM MGMT_METRIC_DEPENDENCY_DETAILS WHERE 1=2;
exec dbms_redefinition.start_redef_table ('SYSMAN','MGMT_METRIC_DEPENDENCY_DETAILS','temp_dep');
DROP MATERIALIZED VIEW temp_dep;

CREATE TABLE temp_avail AS SELECT * FROM MGMT_CURRENT_AVAILABILITY WHERE 1=2;
exec dbms_redefinition.start_redef_table ('SYSMAN','MGMT_CURRENT_AVAILABILITY','temp_avail');
DROP MATERIALIZED VIEW temp_avail;

4. Rebuild the index used:

ALTER INDEX SEVERITY_PRIMARY_KEY REBUILD;
ALTER INDEX CUR_AVAIL_PRIMARY_KEY REBUILD;
ALTER INDEX PK_MGMT_METRIC_DEP_DETAILS REBUILD;

5. Resubmit the EM jobs:

exec emd_maintenance.submit_em_dbms_jobs;
commit;

Extended statistics Performance issue Oracle 12c

Extended statistics Performance Issue in Oracle 12c

Extended Stats in Oracle 12c caused performance issue with following SQL executing multiple times. Find number of execution in Statspack report or AWR Report consuming lot of CPU:
select default$ from col$ where rowid=:1

On Checking,query is related with extended stats in 12c causing performance issue. In 12c, extended stats is created itself as optimizer needed.
Oracle 12c version create extended statistics automatically, it’s fixed in next 12.2 version. In oracle 11g, you have option to create extended statistics( column Group) manually.
you can follow link for manually created:
https://smarttechways.com/2017/01/06/extended-stats-in-oracle
 
In some cases, when we upgrade the database from oracle 11g to 12c.
We faced performance issue due to extended stats (column group ), So we manually disable it and drop the extended stats in database. Steps as following in blog:
 
On checking Statspack report of Oracle Database following query is consuming CPU.
select default$ from col$ where rowid=:1
 
Statspack Report:

SQL ordered by CPU DB/Inst: IC/ic Snaps: 8812-8823
-> Total DB CPU (s): 2,637
-> Captured SQL accounts for 15.8% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU

CPU CPU per Elapsd Old
Time(s)  Executions  Exec(s)  %Total  Time(s)  Buffer Gets  Hash Value
-------  ----------  -------  ------  -------  -----------  ----------
75.39    2,610,891   0.00     2.9     79.09    5,221,732    3360804353
select default$ from col$ where rowid=:1


Solution:

1. For stop using extended stats in Oracle 12c. You can disable the hidden parameter.
alter system set "_optimizer_enable_extended_stats"=FALSE scope=both;
 
2. You can also change cursor sharing parameter to “FORCE” if your application is using hard parsing(but not recommended)
alter session set cursor_sharing='force' scope=both;
 
3. You can drop the already created extended stats by following process:

Check the extended stats extension for owner and table:
SELECT extension_name, extension FROM dba_stat_extensions WHERE table_name = 'EMP' and owner ='SCOTT'
If you find the column group or extension present on table, you can take backup of stats and drop the extension (column group) from database by following package:
Note: copy paste extension column of above query to following command:
 
Drop Command:

exec dbms_stats.drop_extended_stats(ownname => 'SCOTT',tabname => 'EMP',extension => '("ENTITY_NO","ACCT_TYPE","DOC_TYPE")');

In my case, By deleting the extended stats will fixed the performance issue in Oracle 12c.
 
Scripts for drop all the table and schema extended stats

spool E:\stats.txt
SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
SET TRIMSPOOL ON
SET WRAP ON
set termout off
set verify off
set longchunksize 200000
set feedback off
SET HEADING Off
set echo off
SELECT 'exec dbms_stats.drop_extended_stats(ownname =>'''||owner ||''',tabname =>'''||table_name||''',extension => '''|| extension||''');' FROM dba_stat_extensions WHERE owner ='SCOTT';

For more detail, please refer following link:
https://magnusjohanssontuning.wordpress.com/2016/12/27/group-column-statistics-part-2