Dev's Weblog

Reading time: 3 - 4 minutes

Today happed had a situation where i were called by my client and informed about slow generation of report.

Client was using some SQL which was selecting some data from few tables to generate reports.

I used the following query to find out how much it used to take in past.

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

As per the output i can see that SQL has changed the execution plan from 2PM on 29th JUL 10.

As per the Oracle documentation , i decided to use SQL Tuning Advisor

SQL Tuning Advisor has three steps.

1. Create Tuning Task    ( DBMS_SQLTUNE.CREATE_TUNING_TASK )
2. Execute Tuning Task  ( DBMS_SQLTUNE.EXECUTE_TUNING_TASK )
3. Report Tuning Task    ( DBMS_SQLTUNE.REPORT_TUNING_TASK )

I created SQL Tuning task using following script.

SET LONG 10000;
SET PAGESIZE 9999
SET LINESIZE 155
set verify off
col recommendations for a150
accept task_name -
       prompt 'Task_Name: '
DECLARE
ret_val VARCHAR2(4000);

BEGIN

ret_val := dbms_sqltune.create_tuning_task(task_name=>'&&Task_name', sql_id=>'&sql_id', time_limit=>&time_limit);

dbms_sqltune.execute_tuning_task('&&Task_name');

END;
/
SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations FROM dual;
undef task_name

Tuning Advisor's Primary recommendations are often to accept a SQL Profile but there may be other recommendations such as creating indexes, etc.

As per my past experience never ever accept recommendation blindly else you may be in problem.

SQL tuning advisor suggested SQL profile and i accepted it and it improved performance dramatically.

Script Source:

http://kerryosborne.oracle-guy.com/2008/09/sql-tuning-advisor/

Reading time: < 1 minute

[oracle@db1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db

[oracle@db1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@db1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName orcl.sysdbaonline.com -sysPassword oracle -systemPassword oracle -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS

Reading time: 1 - 2 minutes

Sometimes it is necessary to automatically trigger SQL trace. Automatically here means that code must be added somewhere.

The simplest approach is to create a logon trigger at the database level. To avoid enabling SQL trace for all users, I usually suggest creating a role (named sqltrace in the following example) and temporarily granting it only to the user utilized for the test.

CREATE ROLE sqltrace;

CREATE OR REPLACE TRIGGER enable_sqltrace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQLTRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/

So whenever user with sqltrace role logs in sql tracing will be started by above trigger.

Reading time: < 1 minute

I noticed that wordpress changed all the occurrence of apostrophes in my all posts.

I opened the source of page and i found all the apostrophes were changed to &#8216 surprisingly.

I had to modify wp-include/default-filters.php file to get my apostrophes working.

I had commented out following line.

#add_filter( 'the_content', 'wptexturize' );

after that it started working perfectly and i see apostrophes in source of page not &#8216.

Hurrey….

Reading time: 1 - 2 minutes

Sometimes it becomes very tough to find the relevant oracle trace file using session id in oracle.You can alter your session before setting an event to identify trace file quite easily but following method doesn't require alteration of session.

SELECT s.sid,
s.server,
lower(
CASE
WHEN s.server IN ('DEDICATED','SHARED') THEN
i.instance_name || '_' ||
nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' ||
p.spid || '.trc'
ELSE NULL
END
) AS trace_file_name
FROM v$instance i,
v$session s,
v$process p,
v$px_process pp,
v$shared_server ss
WHERE s.paddr = p.addr
AND s.sid = pp.sid (+)
AND s.paddr = ss.paddr(+)
AND s.type = 'USER'
ORDER BY s.sid;

Sample Output:

       SID SERVER    TRACE_FILE_NAME
---------- --------- --------------------------------------------------
     8    DEDICATED orcl_ora_17842.trc
    10   DEDICATED orcl_ora_17846.trc
    16   DEDICATED orcl_ora_17856.trc
    199 DEDICATED orcl_ora_17848.trc
    394 DEDICATED orcl_ora_17844.trc
    400 DEDICATED orcl_ora_17850.trc
    580 DEDICATED orcl_ora_17852.trc
    585 DEDICATED orcl_ora_17840.trc

8 rows selected.