Drop accepted plan in SQL Advisory

Drop accepted plan in SQL Advisory

1. From SQL Advisory we accepted the plan

begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE);
end;
/

2. Check that SQL profile created is used

Set autotrace on
— run the SQL Query it will show execution plan which last line show you that plan is used or not and you get name of SQL Profile.

3. Check the sql profiles which is associated with SQL

select * from dba_sql_profiles;

4. Disable the SQL profile if you want

begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE_NAME','STATUS','DISABLED');
end;
/

5. Drop the SQL Profile

exec dbms_sqltune.drop_sql_profile('SQL_PROFILE_NAME');

Advertisements

SQL Tuning Advisory with direct SQL Query in Oracle

SQL Tuning Advisory with direct SQL Query in Oracle

We have Query which we want to run the SQL Advisory directly on Query.

Following are the steps to run query directly in SQL Advisory

1. Create a SQL tuning task defining SQL which need to be tuned
You need to enter the query and username of objects present.

DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
my_sqltext := 'SELECT name,job_title FROM EMP';
my_sqltext := my_sqltext || ' where emp_no=100';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task1');
END;
/

2. Execute the SQL tuning task

exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task1');

3. Fetch the Advisory report of SQL tuning

SET LONG 100000
SET PAGESIZE 200
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNING_TASK1') AS recommendations FROM dual;

4. Drop the task name if everything done or not needed more

exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'SQL_TUNING_TASK1');

5. Check if any tuning task already present

SELECT task_name, status FROM dba_advisor_log WHERe task_name = 'SQL_TUNING_TASK';

Failed parse elapsed time in AWR Oracle

Failed parse elapsed time in AWR Oracle

Failed parses occurred when someone attempts to run an invalid SQL statement which having syntax issues or object not available.
Example of alert log resembles to errors:
PARSE ERROR: ospid=1852, error=936 for statement: ORA-00936: missing expression (Syntax is wrong)
PARSE ERROR: ospid=644, error=942 for statement: ORA-00942: table or view does not exist ( object is missing)
PARSE ERROR: ospid=6428, error=6550 for statement: ORA-06550: Line 1, column 7: (error in pl/sql block)

In AWR report, one of database is showing the FAILED PARSE ELAPSED TIME statistic name is consuming CPU resources.

In AWR Report: You see the Time model statistics block

Time Model Statistics
Statistic Name 	            % OF DB TIME  % of Total CPU Time
sql execute elapsed time    49,470.54     52.59   
failed parse elapsed time	682.72         5.19
hard parse elapsed time	    508.37	       3.87

In Addm report if you found this error, it means Parse errors consuming lot of CPU in hard parsing

Finding: Hard Parse Due to Parse Errors
Hard parsing SQL statements that encountered parse errors was consuming
significant database time.

Solution
For tracing the failed parsing result in alert log from which application module its generated by help of enabling the event

1. Enable the trace at system level

alter system set events '10035 trace name context forever, level 1';

2. Check the alert log its getting error message with OSPID

PARSE ERROR: ospid=1852, error=936 for statement:
2018-12-09T22:42:36.914209-06:00
SELECT image_size, cap_location, perm_location, cap_file_ptr, perm_file_ptr FROM HR.SALES WHERE entity_no = '005028' date_captured = '10-Dec-2018';

3. Use above ospid value into the following query to Get the session detail and module name.

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,15) box,
substr(b.username,1,8) username,
substr(b.osuser,1,10) os_user,
substr(b.program,1,30) program,
substr(b.status,1,10) status
from v$session b, v$process a
where
b.paddr = a.addr and a.spid = '1852'
order by spid;

4. Disable the trace if you found the information

alter system set events '10035 trace name context off';

Note: Temporary Workaround
Oracle also provide temporary work around to use _cursor_features_enabled
parameter. Its default value is 2. It worked to reduce CPU overhead by saving failed parsing query in table SQLERROR$. For enable it you need to set value to 34. It effect on DB restart. It is not dynamic parameter.

-- Check current value
col Name for a25
col Value for a10
col Description for a30
select ksppinm "Name",ksppstvl "Value",ksppdesc "Description" from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm = '_cursor_features_enabled';

--Change to 34
alter system set "_cursor_features_enabled" = 34 scope=spfile;

Example from application team for trace module name
1. Execute the following syntax error command from other session:
Select * from dba_file;

2. In Alert Log getting the error because trace is enabled
PARSE ERROR: ospid=8392, error=942 for statement:
2018-12-13T03:27:21.710995-06:00
select * from dba_file

3. Check session detail as:

select
substr(a.spid,1,5) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,15) box,
substr(b.username,1,8) username,
substr(b.osuser,1,10) os_user,
substr(b.program,1,30) program,
substr(b.status,1,10) status
from v$session b, v$process a
where
b.paddr = a.addr and a.spid = '8392'
order by spid;

PID  SID SER#  BOX     USERNAME OS_USER PROGRAM     STATUS
---- --- ----- ------- -------- ------- ----------- ------
8392 422 28636 XXXX\XX SYS      SYSTEM  sqlplus.exe ACTIVE

Instance and User level tracing in Oracle

Instance and User level tracing in Oracle

Enable Instance level tracing in Oracle

-- Enable the trace at instance level
alter system set events '10046 trace name context forever,level 12';
-- Disable the trace at instance level
alter system set events '10046 trace name context off';

Initialization parameter setting for instance level tracing
You can defined event in init file for enable tracing at instance level.

-- Entry the parameter in init file and start the database
event="10046 trace name context forever,level 12"
-- Disable the trace at instance level
alter system set events '10046 trace name context off';

Enable user level tracing with Logon Trigger
When use connected with the database it make connection then logon trigger will fire automatic and enable the trace for that session.

CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/

Tracing the process SPID in Oracle

Tracing the process SPID in Oracle

1. Identified the process id if it present in alert log from any error or trace it from your self from SQL query with v$process view.

--- From alert log
WARNING: too many parse errors, count=1178 SQL hash=0xb94e2a38
PARSE ERROR: ospid=2776, error=918 for statement:
2018-12-03T16:59:31.252004-06:00

--From SQL Query trace yourself
select p.PID,p.SPID,s.SID from v$process p,v$session s
where s.paddr = p.addr and s.sid = &SESSION_ID;

2. Login sqlplus as sysdba and start the trace process

sqlplus / as sysdba
oradebug setospid XXXX; -- XXXX is process id
oradebug unlimit;
oradebug tracefile_name;
oradebug event 10046 trace name context forever,level 12;

Example:
sqlplus / as sysdba
oradebug setospid 2776;
oradebug unlimit;
oradebug tracefile_name;
oradebug event 10046 trace name context forever,level 12;

3. Wait for the issue to be reproduced.

Wait for error to occurred again.

4. Disable the trace process.

oradebug event 10046 trace name context off;
exit

Check the resource limit of session and processes in Oracle

Check the resource limit of session and processes in Oracle

With help of V$RESOURCE_LIMIT view, we can see the current utilization and MAX utilization of resources from last startup.

DESC V$RESOURCE_LIMIT

Name Type
--------------------- --------------
RESOURCE_NAME VARCHAR2(30)
CURRENT_UTILIZATION NUMBER
MAX_UTILIZATION NUMBER
INITIAL_ALLOCATION VARCHAR2(40)
LIMIT_VALUE VARCHAR2(40)

Meaning of columns:
RESOURCE_NAME: Name of the resource
CURRENT_UTILIZATION : Number of (resources, locks, or processes) currently being used
MAX_UTILIZATION : Maximum consumption of this resource since the last instance start-up
INITIAL_ALLOCATION : Initial allocation. This will be equal to the value specified for the resource in the initialization parameter file (UNLIMITED for infinite allocation).
LIMIT_VALUE : Unlimited for resources and locks. This can be greater than the initial allocation value (UNLIMITED for infinite limit)

Check session and process limit from Oracle Startup

select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name = 'sessions';

select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name = 'processes';

Check the history table of Resource Limit

select * from DBA_HIST_RESOURCE_LIMIT where resource_name in ('sessions','processes');

Get history of resource utilization with time

col snapshottime for a20
col resource_name for a20
select s.SNAP_ID,TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,resource_name,current_utilization,max_utilization,limit_value from DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
where s.snap_id = b.snap_id and resource_name in ('sessions') order by 2;

Find timing and snapshot of maximum session connected to database
Suppose my max utilization show me the value of 800 session connected then i want to check when & what time i am getting maximum session or my peak time of application.
Then i use the query to find the snapshot id and timing by defining current_utilization column in where clause to see that timing.

col snapshottime for a20
col resource_name for a20
select s.SNAP_ID,TO_CHAR(s.END_INTERVAL_TIME, 'DD-MON HH24:MI:SS') Snapshottime,resource_name,current_utilization,max_utilization,limit_value from DBA_HIST_SNAPSHOT s, DBA_HIST_RESOURCE_LIMIT b
where s.snap_id = b.snap_id and resource_name in ('sessions') and current_utilization > 600 order by 2;

Trace the SQL Query with SQL ID in Oracle

Trace the SQL Query with SQL ID in Oracle

1. Identify your SQL ID of SQL Query which need to trace.

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'SQL QUERY%';

2. Enable the trace of SQL with using SQLID in following statements:

-- Enable the events with replacing SQL_ID
alter system set events 'sql_trace [sql:] wait=true,bind=true';
alter system set events 'trace[sql_optimizer.*] [sql:]';

3. Run the SQL Query.

SQL Query which need to trace.

4. After executing the SQL Query, disable the trace.

-- to disable the events:
alter system set events 'sql_trace off';
alter system set events 'trace[sql_optimizer.*] off';

5. Get the trace file in alert log location.

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

Example of Tracing SQL Query:

1. Identify the SQL id for SQL;

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'select * from scott.emp%'

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- -----------------------
ggqns3c1jz86c 3956160932 select * from scott.emp;

2. Enable the trace for SQL ID : ggqns3c1jz86c

alter system set events 'sql_trace [sql:ggqns3c1jz86c] wait=true,bind=true';
alter system set events 'trace[sql_optimizer.*] [sql:ggqns3c1jz86c]';

3. Run the SQL Query.

select * from scott.emp

4. Disable the trace

alter system set events 'sql_trace off';
alter system set events 'trace[sql_optimizer.*] off';

5. Check the trace location and find the file having _ORA_SPID.trc in it like xe_ora_5864.trc

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
VALUE
----------------------------------------------
D:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace