Anuroop Challa

Creating SQL Tuning Task with sql_id

Creating SQL Tuning Task with sql_id
=============================

First get sql_id of sql those who try to create task

Hope as dba you all know how get sql_id. and here i take below sql-id to create task

sql_id= 2w3y1nfj1uanf

Note: remember in below red color sql_id change as per your id.

Step1: - Create task:

SET serveroutput ON

DECLARE
v_tune_taskid VARCHAR2(100);
BEGIN
v_tune_taskid := dbms_sqltune.create_tuning_task (
sql_id => '2w3y1nfj1uanf',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 30,
task_name => 'sql_tuning_task_2w3y1nfj1uanf',
description => 'Tuning task sql_id 2w3y1nfj1uanf');
dbms_output.put_line('taskid = ' || v_tune_taskid);
END;
/

PL/SQL procedure successfully completed.

successful mean task created

Step2: - verify task as you created.

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2w3y1nfj1uanf';

Step3: Execute SQL Tuning task to get recommendation.

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_2w3y1nfj1uanf');

Step4: once complete run verify status of job.

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2w3y1nfj1uanf';

Step5: Run report to get info.

SET LINES 150
SET pages 50000
SET long 5000000
SET longc 5000000

SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_2w3y1nfj1uanf') AS recommendations FROM dual;

====================================================================
make below step recomdation task need to run or need if run recommendation
example jobs looks execute dbms_sqltune.accept_sql_profile
=====================================================================
Step6: drop task once finish your recomdation run .

BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_2w3y1nfj1uanf');
END;

/

SQL Tuning Task Creation ORA-13780: SQL statement does not exist.

SQL Tuning Task Creation ORA-13780: SQL statement does not exist.
=================================================

example:-

declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
sql_id => 'your sql_id',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 10800,
task_name => 'tuning task name',
description => 'tuning task description');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

declare
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125
ORA-06512: at "SYS.DBMS_SQLTUNE", line 655

solution :----

SELECT SNAP_ID FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='sql_id'
ORDER BY SNAP_ID;

===========now create task============

declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 12345,
end_snap => 12350,
sql_id => 'your sql_id',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 10800,
task_name => 'tuning task_name',
description => 'tuning task description');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/

===============hope it helps you================

ORA 700 [kskvmstatact: excessive swapping observed]

ORA 700 [kskvmstatact: excessive swapping observed]
========================================

One of my database down suddenly.
Reason: ORA-700 SWAP error, as the dump it's taking more that 10 minutes pmon killed the process and the database was down.

this database running with less memory .

Solution
alter system set "_kse_die_timeout"=XX or 0 if we want to disable the pmon killer during dumping .

note:
The error is documented in:
- Note:1919850.1 - 12c: Warning Message About Heavy Swapping Observation and ORA-700 [kskvmstatact: excessive swapping observed]

Because your swap memory has low values of swap in / swap out, this warning message can be safely ignored.

Given both the alert log message and ORA-700 are expected behavior for 12c, no action is required.

An enhancement request (ER) was created to change the threshold in future releases in
Bug 19495842 - CHANGE THRESHOLD IN THE SWAP WARNING IN THE ALERT_<SID>.LOG

=================
1 - Increase the timeout value to _kse_die_timeout to 360000 to increase the timeout to 1 hour or 180000 if you desired 30 minutes . That will stop PMON killing the process and db shutdown.

2 - Set "_kse_die_timeout"=0 (it is a dynamic parameter so can be set with alter system) but in this case If a process does get stuck spending ages dumping diagnostic data it would not be automatically killed after XX mins, this means that all the resources for the process still allocated and could cause other issues related to locking or hanging issue.
==================================

ORA-04030: out of process memory when trying to allocate 123416 bytes

ORA-04030: out of process memory when trying to allocate 123416 bytes
====================================================

we revive alert from database due to less memory. user run query consuming more.

It impacts other process to fail. This error causing lack of memory in pga and makes other process to fail.

You can also monitor the PGA usage and high consumption of the pga process with below queries during the error time.

=================================================

spool memory_results.txt append

set pages 999
select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual;

PROMPT
PROMPT PROCESS CONSUMING MORE PGA:
PROMPT -------------------------

set line 200
col logon for a10
col spid for a10
col username for a10
col osuer for a10
col machine for a10
col program for a15
col module for a10
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
SID, v$session.SERIAL#, v$process.SPID , ROUND(v$process.pga_used_mem/(1024*1024), 2) PGA_MB_USED,
v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--and status = 'ACTIVE'
--and v$session.sid = 97
--and v$session.username = 'SYSTEM'
--and v$process.spid = 24301
ORDER BY pga_used_mem DESC;

PROMPT
PROMPT List largest process:
PROMPT -------------------------

col user for a20
SELECT pid, spid, substr(username,1,20) "USER", program, pga_used_mem, pga_alloc_mem, pga_freeable_mem, pga_max_mem
FROM v$process
WHERE pga_alloc_mem = (SELECT max(pga_alloc_mem)
FROM v$process
WHERE program NOT LIKE '%LGWR%');

PROMPT
PROMPT Summation of PGA based on v$process:
PROMPT -------------------------

SELECT SUM(pga_alloc_mem)/1024/1024 AS "Mbytes allocated", SUM(PGA_USED_MEM)/1024/1024 AS "Mbytes used" FROM v$process;

PROMPT
PROMPT Summation of PGA memory based on V$SESSTAT:
PROMPT -------------------------

SELECT SUM(value)/1024/1024 AS Mbytes FROM v$sesstat s, v$statname n WHERE n.STATISTIC# = s.STATISTIC# AND n.name = 'session pga memory';

PROMPT
PROMPT PGASTATS
PROMPT -------------------------

SELECT SUBSTR(name,1,30), value, unit FROM v$pgastat;

PROMPT
PROMPT List all processes including PGA size from V$PROCESS.
PROMPT -------------------------

SET LINESIZE 120
SET PAGESIZE 120
COLUMN spid HEADING 'OSpid' FORMAT a8
COLUMN pid HEADING 'Orapid' FORMAT 999999
COLUMN sid HEADING 'Sess id' FORMAT 99999
COLUMN serial# HEADING 'Serial#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a8
COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 99,999,999,999
COLUMN pga_used_mem HEADING 'PGA used' FORMAT 99,999,999,999
COLUMN username HEADING 'oracleuser' FORMAT a12
COLUMN osuser HEADING 'OS user' FORMAT a12
COLUMN program HEADING 'Program' FORMAT a20

SELECT p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
p.pga_used_mem,
s.username,
s.osuser,
s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
AND p.background is null /* Remove if need to monitor background processes */
ORDER BY p.pga_alloc_mem DESC;

PROMPT
PROMPT Summation of PGA and SGA
PROMPT ----------

SELECT SUM(bytes)/1024/1024 AS Mbytes
FROM (SELECT value AS bytes
FROM v$sga
UNION ALL
SELECT value AS bytes
FROM v$sesstat s, v$statname n
WHERE n.STATISTIC# = s.STATISTIC#
AND n.name = 'session pga memory');

PROMPT
PROMPT PGA and SGA Memory hourly
PROMPT -----------------

SELECT dhs.instance_number, trunc(dhs.end_interval_time,'mi') Time,
pga.mem pga, sga.mem sga,(pga.mem+sga.mem) Total_MB
FROM
(SELECT snap_id,instance_number,round(sum(value)/1024/1024,4) mem
FROM DBA_HIST_PGASTAT
WHERE name = 'total PGA allocated'
GROUP BY snap_id,instance_number) pga,
(SELECT snap_id,instance_number,round(sum(bytes)/1024/1024,4) mem
FROM DBA_HIST_SGASTAT
GROUP BY snap_id,instance_number) sga,
dba_hist_snapshot dhs
WHERE dhs.snap_id=sga.snap_id
and dhs.instance_number=sga.instance_number
and dhs.snap_id=pga.snap_id
and dhs.instance_number=pga.instance_number
ORDER BY dhs.snap_id desc, dhs.instance_number

spool off
===================================================================

ORA-07445: exception encountered: core dump (from view execution)

ORA-07445: exception encountered: core dump
=====================================
Note: ORA-07445 please note that internal error all are not same depends on argument it vary.

this was one of the internal error we most cases, the best practice we follow was contact oracle support. and upload the file to them(even in starting trace file and alertlog verfiy and find the cause)

in my case cause was one our app team member created view and executed from toad.(he did his testing in production, so better you suggest them to test in test and qa)

solution:-
1. tune the sql and try to gather stats on objects.

2. incase if still first solution does not work follow below procedure set parameter to avoid.

alter session set "_simple_view_merging"=false;

in my case contacted the app team member applied suggestion number 1.

ADD/Remove UNDO tablespace in oracle database

ADD/Remove UNDO tablespace in oracle database:-
=====================================

every database having default undo tablespace, if you want to change it do follow below procedure.

1. first create new tablespace with new(as you want to create)
Example:-
CREATE UNDO TABLESPACE <NEW_NAME> DATAFILE '<datafile_location/name.dbf>' SIZE 1000M AUTOEXTEND OFF ONLINE RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

ALTER SYSTEM SET UNDO_TABLESPACE = <NEW_NAME>;
ALTER SYSTEM SET UNDO_RETENTION = <SET as per NEED>;

Once new one created, if you want drop old one follow steps below.
check any session already using old one with below sql

SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE';

you will get SID&Serial# if any in use, kill them if have or else you can proceed to drop old one.

To kill
SQL> alter system kill session 'SID,SERIAL#';

To drop
SQL> drop tablespace undotbs1 including contents and datafiles;

please help to share your suggestions, or share your experience on same kind of issue in comment session.
your sharing or suggestions will helps me to improve in my future posts.

Thanks for the visit!!!

ADD/Remove TEMP tablespace in oracle database

ADD/Remove TEMP tablespace in oracle database:-
=====================================

To add new Temp tablespace and change it as default do follow below procedure.

1. First create New temptablespace and change it to default temp.

CREATE TEMPORARY TABLESPACE <NEW_TEMP_NAME> TEMPFILE '<teampfile location.dbf' SIZE 1000M AUTOEXTEND OFF
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <NEW_TEMP_NAME>;

Remove the old temptable space , before remove you must check any session already in use or not.

to check use below query to get live session(SID&serial#) those who use old temp.

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

THEN kill session if any session in use.

alter system kill session 'SID_NUMBER, SERIAL#';

we can drop old temporary tablespace.
DROP TABLESPACE TEMP including contents and datafiles;

Be careful while doing this in production.

Thanks for your visit my blog, if you have any suggestion or question please comment below.