Master Yavor from Tryavna

This is just another nasty bug. After the DST change this night, OEM 12c Cloud control started a real flood with notifications like this:

EM Event: Critical:ORADB – Row(3): Supplied date is in the future : now = Sun Oct 28 03:34:22 CET 2012 supplied value = Sun Oct 28 04:34:00 CET 2012

Or even worse

EM Event: Critical:ORADB – Row(1): Supplied date is in the future : now = Sun Oct 28 02:55:23 CEST 2012 supplied value = Sun Oct 28 02:55:20 CET 2012

There is nothing about this issue in My Oracle Support, of course. The only similar bug is Bug 14258529, but there is not fix nor workaround.

It looks like a restart of the agents fixes the issue. We have more than 20 minutes now without any errors.

Now I’m going back in the bed.

This is tested on 11.2.0.2

SQL is a great piece of software. Well, it needs an additional license, but can handle problems that are practically impossible for a human being.

The most often case is when something goes terribly wring on production and has to be fixed urgently. SQL tunning advisor have saved us so many times in such situations… It usually finds some very good solution (without the need for the DBA to understand what this bloody SQL statement does at all); and makes an SQL profile for us, so we do not need to patch the application code.

There is, however, one more scenario. Imagine a brave developer, creating a mighty statement with 97 lines in the execution plan. He have thought for many hours or days on the problem. Then he comes for assistance with the optimization of this statement. I cannot even understand what this statement does without loosing some hours! But SQL tunning advisor completes in minutes and usually gives some quite good tips. But how can you run it?

It easy to do it through the Grid/Cloud control – some clicks and this is it. But what is the database is not monitored by OEM CC (this is a DEV db)?

In fact, it is not that hard:

1. Create a task:

DECLARE
  v_task VARCHAR2(30);
  v_sql  CLOB;
BEGIN
  v_sql     := 'SELECT ... FROM ... WHERE ...';
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => v_sql, 
                                               user_name => 'HR', 
                                               scope => 'COMPREHENSIVE',
                                               time_limit => 3600, -- seconds
                                               task_name => 'tune_test2',
                                               description => 'Tune statement used for the new XYZ functionality.');
END;
/

2. Execute the task. This may take some time, depending on the complexity of the statement and the limit given in the former step:

exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test2');

3. Now let’s see the result

set long 90000 longchunksize 90000
set linesize 232 pagesize 9999
select dbms_sqltune.report_tuning_task('tune_test2') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_TEST2')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tune_test2
Tuning Task Owner  : HR
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status  : COMPLETED
Started at         : 10/05/2012 12:07:10
Completed at       : 10/05/2012 12:12:13

-------------------------------------------------------------------------------
Schema Name: HR
SQL ID     : ba6g55fakh01v
SQL Text   : SELECT ...

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 76.48%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2',
            task_owner => 'HR', replace => TRUE);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2801951464

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |    21 |  1302 | 30711   (4)| 00:06:09 |
...
|  97 |    TABLE ACCESS FULL                     | MY_TAB                      |     2 |    10 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
...
  95 - access("E"."TEST_ID"="T"."TEST_ID")

2- Original With Adjusted Cost
------------------------------

-------------------------------------------------------------------------------
Error: cannot fetch explain plan for object: 1
-------------------------------------------------------------------------------

3- Using SQL Profile
--------------------
Plan hash value: 3439552619

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |    21 |  1302 |  7223   (2)| 00:01:27 |
....
|  97 |    TABLE ACCESS FULL                     | MY_TAB                      |     2 |    10 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
....
  95 - access("E"."TEST_ID"="T"."TEST_ID")

(Yes, I really had to do this for a statement with 97 steps in the execution plan and 95 filters. Of course, I have removed the statement, plan and predicates here)

4. Now we can easily apply the plan. In my case I just wanted to see the advise. But you can never be 100% sure unless you run the whole statement. And even then 🙂

execute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2',  task_owner => 'HR', replace => TRUE);

Another interesting “feature” coming with the upgrade from 10g to 11g.

During the automated pre-upgrade tests we have noticed, the audit trail table grows rapidly and one part of the IO load is caused by inserts in SYS.AUD$.

We found the following Metalink note:
Huge/Large/Excessive Number Of Audit Records Are Being Generated In The Database [ID 1171314.1]

1.) Starting with Oracle 11g the BY SESSION clause is obsolete. This is documented in the Database Security Guide :

” The BY SESSION clause of the AUDIT statement now writes one audit record for every audited event. In previous releases, BY SESSION wrote one audit record for all SQL statements or operations of the same type that were executed on the same schema objects in the same user session. Now, both BY SESSION and BY ACCESS write one audit record for each audit operation. In addition, there are separate audit records for LOGON and LOGOFF events. ”

Because of the above change it is expected that the number of the audit records/files will grow considerably.
…

In the documentation we find the following amboguity:
Oracle® Database SQL Language Reference 11g Release 1 (11.1)

BY SESSION

In earlier releases, BY SESSION caused the database to write a single record for all SQL statements or operations of the same type executed on the same schema objects in the same session. Beginning with this release of Oracle Database, both BY SESSION and BY ACCESS cause Oracle Database to write one audit record for each audited statement and operation. BY SESSION continues to populate different values to the audit trail compared with BY ACCESS. If you specify neither clause, then BY SESSION is the default.

The same can be seen in Oracle® Database SQL Language Reference 11g Release 2 (11.2)

It does not get better in Oracle® Database Security Guide 11g Release 2 (11.2)

Benefits of Using the BY ACCESS Clause in the AUDIT Statement

By default, Oracle Database writes a new audit record for every audited event, using the BY ACCESS clause functionality. To use this functionality, either include BY ACCESS in the AUDIT statement, or if you want, you can omit it because it is the default. (As of Oracle Database 11g Release 2 (11.2.0.2), the BY ACCESS clause is the default setting.)

Oracle recommends that you audit BY ACCESS and not BY SESSION in your AUDIT statements. The benefits of using the BY ACCESS clause in the AUDIT statement are as follows:
…

To be honest, this sounds to me like “Errrr… something broke with this BY SESSION. We managed to make it work somehow, but now it acts almost like BY ACCESS”

So we had to redefine our audit policy before the upgrade. Fortunately most noaudit operations can be executed online. Only noaudit execute uses some nasty latches and we had to execute those during the downtime.
Here is one useful query about this:

select 'noaudit select on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'

  from DBA_OBJ_AUDIT_OPTS

 where sel like 'S/%'

   and owner in ('...application schemas...')

   and object_name not in ('...exclusions...')

union all

select 'noaudit execute on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'

  from DBA_OBJ_AUDIT_OPTS

 where exe like 'S/%'

   and owner in ('...application schemas...')

   and object_name not in ('...exclusions...')

union all

select 'noaudit update on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'

  from DBA_OBJ_AUDIT_OPTS

 where upd like 'S/%'

   and owner in ('...application schemas...')

   and object_name not in ('...exclusions...')

union all

select 'noaudit insert on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'

  from DBA_OBJ_AUDIT_OPTS

 where ins like 'S/%'

   and owner in ('...application schemas...')

   and object_name not in ('...exclusions...')

union all

select 'noaudit delete on ' || owner || '.' || object_name || ' WHENEVER SUCCESSFUL;'

  from DBA_OBJ_AUDIT_OPTS

 where del like 'S/%'

   and owner in ('...application schemas...')

   and object_name not in ('...exclusions...')

And one more, important: do not forget to check what is written in ALL_DEF_AUDIT_OPTS before the upgrade. If you do not do this, catupgrd will create thousands objects with inappropriate auditing.

We have the following task: Table1 contains the clients of the biggest grocery store in Munich, and Table2 all the fans of Bayern (Munich). The question is how many Bayern fans visit the grocery.
Let’s assume we have the columns First Name (FNAME), Last Name (LNAME), CITY, COUNTRY.

The easiest solution is:

select *

  from table2 t2

 where exists (select 1

          from table1 t1

         where t1.fname = t2.fname

           and t1.lname = t2.lname

           and t1.city = t2.city

           and t1.country = t2.country)

But this will give us the result if all names are written correctly. Which, unfortunately, happens rarely. For example Mr. Schmitt from the fans may be written as Schmidt in the grocery; Mrs Pavlovich may be Pavlovic; John Smith is not the same as John Smit, etc. We need something better.

There is one SQL function which returns a character string containing the phonetic representation a string. The function is soundex. The algorithm behind it is relatively simple and can be found here. Let’s try it:

select *

  from table2 t2

 where exists (select 1

          from table1 t1

         where soundex(t1.fname) = soundex(t2.fname)

           and soundex(t1.lname) = soundex(t2.lname)

           and t1.city = t2.city

           and t1.country = t2.country)

(to make the things simple, i have assumed the CITY and COUNTRY are written fine. We can use the same transformation on them)

But this gives us too many false positives. I do not think Marko Mjagkov is the same as Marica Moisejevs, but soundex returns the same – M620 M221. We need something else

There is one nice, relatively new package, named utl_match. In this package there are functions using two different algorithms facing this problem. The first one is developed in Russia by Vladimir Levenshtein in 1965 (the function EDIT_DISTANCE). The other seems to be developed in US, by Matthew Jaro and William Winkler, published in 1990. Let’s see what we can do using the latter:

select *

  from table2 t2

 where exists (select 1

          from table1 t1

         where utl_match.jaro_winkler(t1.fname, t2.fname) > 0.9

           and utl_match.jaro_winkler(t1.lname, t2.lname) > 0.9

           and t1.city = t2.city

           and t1.country = t2.country)

Hm, we have another problem – there is no effective way to speedup the query. If the grocery has 10 000 clients in Munich, and the football cub has 500 000 fans in the city, we will execute the function utl_match.jaro_winkler more than 5 000 000 000 times. It will, for example, try to compare Adolf with Gretta, which is nonsense. One cannot index this, or use another infrastructure tricks – only raw computing power will help. So it takes time.

And so, I decided to combine both approaches and came up with this:

select *

  from table2 t2

 where exists (select 1

          from table1 t1

         where soundex(t1.fname) = soundex(t2.fname)

           and soundex(t1.lname) = soundex(t2.lname)

           and utl_match.jaro_winkler(t1.fname, t2.fname) > 0.9

           and utl_match.jaro_winkler(t1.lname, t2.lname) > 0.9

           and t1.city = t2.city

           and t1.country = t2.country)

Here soundex limits the number of checks, although there may be many false positives. One can even do a function-based index on Soundex, or a MView.
Then utl_match.jaro_winkler gives us the most “similar” names. Believe it or not, this works fine – on tables with 20К and 2M rows (all from the same city), it completed in tens of seconds and returns mostly reasonable results. Of course, there may be many coincidences – there are many men in Munich named Hans Shmidt. But it is useful.

P.S. During the research we encountered PETER. We did not test it because it requires using extproc, which can potentially lead to security problem. But the product looks very promising.