October 2012 – Master Yavor from Tryavna

EM Event: Critical:DB – Row(3): Supplied date is in the future

Oracle Comments Off

Oct 282012

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.

Too many databases for one server

Oracle Comments Off

Oct 142012

OK, the server is not that bad. He could handle the load. We are talking about a Dev server, after all. It runs Dev databases.

The problem is, it runs about 30 Dev. At every given time only 5-6 (or even 1-2) are active. But it gets much worse when the time comes for the automatic statistic gathering. Then the server is knocked down to the knees. All 30 or so DBs start fighting for the disks – and the disks cannot handle the massive requests. Then the ASM get severely overloaded and even sops responding for a few seconds. In some extreme cases some of the databases get terminated:

ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 4 Serial number: 1
ASMB (ospid: 8290): terminating the instance due to error 15064

One more detail: all those 30 databases are created as a copy of one “master” database. Whatever you put in the master – sooner or later gets populated to all the rest. Every developer or a team can recreate his team DB as a current copy of the master. Or a copy of other team DB, but this happens rarely. So, if out master DB knows the statistics have to be gathered at 22:00, sooner or later all Dev databases start gathering stats at 22:00. This is, in fact, the most resource-intensive thing that can happen to the server.

So hare’s what I’ve done:

create or replace procedure p_scramble_stats_start as
  v_start_hour char(1);
  v_start_min char(2);
BEGIN
  -- disable
  DBMS_SCHEDULER.DISABLE(name => '"SYS"."MONDAY_WINDOW"', force => TRUE);
  DBMS_SCHEDULER.DISABLE(name => '"SYS"."TUESDAY_WINDOW"', force => TRUE);
  DBMS_SCHEDULER.DISABLE(name => '"SYS"."WEDNESDAY_WINDOW"', force => TRUE);
  DBMS_SCHEDULER.DISABLE(name => '"SYS"."THURSDAY_WINDOW"', force => TRUE);
  DBMS_SCHEDULER.DISABLE(name => '"SYS"."FRIDAY_WINDOW"', force => TRUE);
  DBMS_SCHEDULER.DISABLE(name => '"SYS"."SATURDAY_WINDOW"', force => TRUE);
  DBMS_SCHEDULER.DISABLE(name => '"SYS"."SUNDAY_WINDOW"', force => TRUE);
  -- "choose" the time
  select to_char(ora_hash(instance_name, 6)), to_char(ora_hash(instance_name, 59))
    into v_start_hour, v_start_min
    from v$instance;
  -- set
  DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."MONDAY_WINDOW"',
                               attribute => 'REPEAT_INTERVAL',
                               value     => 'freq=daily;byday=MON;byhour='||v_start_hour||';byminute='||v_start_min||';bysecond=0');
  DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."TUESDAY_WINDOW"',
                               attribute => 'REPEAT_INTERVAL',
                               value     => 'freq=daily;byday=TUE;byhour='||v_start_hour||';byminute='||v_start_min||';bysecond=0');
  DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."WEDNESDAY_WINDOW"',
                               attribute => 'REPEAT_INTERVAL',
                               value     => 'freq=daily;byday=WED;byhour='||v_start_hour||';byminute='||v_start_min||';bysecond=0');
  DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."THURSDAY_WINDOW"',
                               attribute => 'REPEAT_INTERVAL',
                               value     => 'freq=daily;byday=THU;byhour='||v_start_hour||';byminute='||v_start_min||';bysecond=0');
  DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."FRIDAY_WINDOW"',
                               attribute => 'REPEAT_INTERVAL',
                               value     => 'freq=daily;byday=FRI;byhour='||v_start_hour||';byminute='||v_start_min||';bysecond=0');
  DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."SATURDAY_WINDOW"',
                               attribute => 'REPEAT_INTERVAL',
                               value     => 'freq=daily;byday=SAT;byhour='||v_start_hour||';byminute='||v_start_min||';bysecond=0');
  DBMS_SCHEDULER.SET_ATTRIBUTE(name      => '"SYS"."SUNDAY_WINDOW"',
                               attribute => 'REPEAT_INTERVAL',
                               value     => 'freq=daily;byday=SUN;byhour='||v_start_hour||';byminute='||v_start_min||';bysecond=0');
  -- enable
  DBMS_SCHEDULER.ENABLE(name => '"SYS"."MONDAY_WINDOW"');
  DBMS_SCHEDULER.ENABLE(name => '"SYS"."TUESDAY_WINDOW"');
  DBMS_SCHEDULER.ENABLE(name => '"SYS"."WEDNESDAY_WINDOW"');
  DBMS_SCHEDULER.ENABLE(name => '"SYS"."THURSDAY_WINDOW"');
  DBMS_SCHEDULER.ENABLE(name => '"SYS"."FRIDAY_WINDOW"');
  DBMS_SCHEDULER.ENABLE(name => '"SYS"."SATURDAY_WINDOW"');
  DBMS_SCHEDULER.ENABLE(name => '"SYS"."SUNDAY_WINDOW"');
END;

This procedure picks a random hour an minute (from 00:00 to 6:59) based in the current instance name. Then sets all maintenance windows (used for statistics gathering) to be open at that time. In our case the statistics gathering needs 5 to 15 minutes, so that’s OK.

I have done a scheduler job on all DBS job, which runs this procedure every evening at 22:00. This way, no matter which database is copied, the stats gathering time is changed the same day.

This solved the problem for us.

Creating a manual SQL tunning task

Oracle Comments Off

Oct 132012

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);