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.
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;

Configure and use of SQLT tool for Oracle

Configured and use of SQLT Reports

SQLT is a tool used to check the SQL statement and generate set of diagnostics files in HTML format. SQLT used to analyze a SQL Statement with help of SQL_ID and SQL_TEXT.
Setup of SQLT create two users: SQLTXPLAIN(unlock) and SQLTXADMIN(already locked).
 
Installing SQLT:
1. Download the SQLT software from oracle support.
 
2. Go to the directory SQLT\install folder.
cd SQLT\install
3. Connect with the database as sysdba
sqlplus / as sysdba
4. For setup SQLT run the following command:
SQL> START sqcreate.sql;
5. Verify the DBA_USERS view for user creation
SQL> select username,account_status from dba_users where username like 'SQLTX%';

USERNAME        ACCOUNT_STATUS
--------------  -----------------
SQLTXPLAIN      OPEN
SQLTXADMIN      EXPIRED & LOCKED


Uninstall steps for SQLT
1. Go to the directory SQL\install folder
cd SQLT\install
2. Connect with the database as sysdba
sqlplus / as sysdba
3. For uninstall the SQLT run the following command:
SQL> START sqdrop.sql
4. For check the DBA_USERS for SQLT user existence:
SQL> select username,account_status from dba_users where username like 'SQLTX%';
no row found

 
SQLT has 7 Methods to generate diagnostics report:
 
1.XTRACT Method is used if you have the SQL_ID or HASH_VALUE of the SQL statement, Mostly used method.
a) Go to directory SQLT\run
cd SQLT\run
b) Connect with database
sqlplus / as sysdba
c) Start the method
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
Example
SQL> START sqltxtract.sql 0k5uut65g6vb sqltxplain_password
 
2. XECUTE Method it’s executes the SQL statement which need to analyzed then it produce output report.If SQL query take long time to execute then chose XTRACT Method. For running need to create file for SQL query which used by method.
Note: For using this method you need to create a file which have SQL query . if SQL query is using bind variable then you need to declare it in file.
Note: For Insert/update /delete operation you need to create savepoint for transaction and rollbackup upto that savepoint
( So, if not having knowledge then please do not use this method)
a. Got to directory SQLT\run
cd SQLT\run
b. Connect with database
sqlplus / as sysdba
c. Start the method
SQL>START sqltxecute.sql [path]\scriptname.sql [sqltxplain_password]
Example
SQL>START sqltxecute.sql D:\script.sql sqltxplain_password
 
3. XTRXEC Method: used both method XTRACT and XECUTE.
XTRACT generated a SQL script with bind variable for XECUTE Method.
XTRACT take bind variable as peak value of execution plan.
a. Go to directory SQLT\run
cd SQLT\run
b. Connect with database
sqlplus / as sysdba>
c. Start the method
SQL> START sqltxtrxec.sql [sql_id]|[HASH_VALUE] [ssqltxplain_password]
Example:
SQL> START sqltxtrxec.sql 0k5uut65g6vb sqltxplain_password
 
4. XTRSBY Method is used to analyze SQL on DATA Guard or Stand by databases with SQL_ID or HASH VALUE. Create a database link of standby database from primary database. It’s used db_link in command
a. Go to directory sqlt\run
cd sqlt\run
b. Connect with primary database
sqlplus / as sysdba
c. Start the method:
SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK]
Example:
SQL> START sqltxtrsby.sql 0k5uut65g6vb sqltxplain_password db_link_name
 
5. XPLAIN Method is based on the EXPLAIN PLAN FOR command,
therefore it is blind to bind variables referenced by your SQL statement.For this method you need to create SQL text file.
Note: Use this method only if XTRACT or XECUTE are not possible.
a. Go to directory sqlt\run
cd sqlt\run
b. Connect with primary database
sqlplus / as sysdba
c. Start the method:
SQL>START sqltxplain.sql [path]filename [sqltxplain_password]
Example:
SQL> START sqltxplain.sql D:\sql1.sql sqltxplain_password
 
6. XPREXT Method used if you already used XTRACT method for faster execution of SQLT while disabling some features.
Check features disable with sqlt/run/sqltcommon11.sql
a. Go to directory sqlt\run
cd sqlt\run
b. Connect with database
sqlplus / as sysdba
c. Start the method:
SQL> START sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
Example:
SQL> START sqltxprext.sql 0k5uut65g6vb sqltxplain_password
 
7. XPREXC Method used if you already used XECUTE method for faster execution of SQLT while disabling some features.
Check features disable with sqlt\run\sqltcommon11.sql
a. Go to directory sqlt\run
cd sqlt\run
b. Connect with database
sqlplus / as sysdba
c. Start the method:
SQL> START [path]sqltxprexc.sql [path]scriptname [sqltxplain_password]
Example:
SQL>START sqltxprexc.sql D:\script1.sql sqltxplain_password