Dev's Weblog

Reading time: 1 - 2 minutes

Problem:

======

During relinking operation OUI will fail and in $ORACLE_HOME/install/make.log Ayou will find following error.

/u01/app/dev/Middleware/agent11g/lib/libnnz11.so: could not read symbols: Invalid operation

collect2: ld returned 1 exit status

make[1]: *** [/u01/app/dev/Middleware/agent11g/sysman/lib/emdctl] Error 1

make[1]: Leaving directory `/u01/app/dev/Middleware/agent11g/sysman/lib'

When you start to install with ./runInstaller, run in another terminal window (as root)
ls $ORACLE_HOME/sysman/lib/ins_emagent.mk

At first this will produce an error, as the installer wont have created this file yet.
Once the file exists, do:

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk
Search for the line
$(MK_EMAGENT_NMECTL)
Change it to:
$(MK_EMAGENT_NMECTL) -lnnz11

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