Archive for category TOI

Create a SQL Profile to let the Optimizer ignore hints in #Oracle

Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if  hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.

One method is to use alter session set “_optimizer_ignore_hints”=true; This will make the optimizer ignore all hints during that session  – also the useful ones, so maybe that is not desirable. The method I show here works on the statement level. The playground:

SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:04.92
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  7m2k0y4hy1ngh, child number 0
--------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3

Plan hash value: 1767991108

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |           |       |       |   139K(100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |           |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SALES     |    17M|   131M|   139K  (1)| 00:00:06 |
|   3 |    BITMAP CONVERSION TO ROWIDS       |           |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE        | SALES_BIX |       |       |            |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CHANNEL_ID"=3)

The index hint directs the optimizer here to use a bad plan that wouldn’t be used otherwise:

SQL> select max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:01.06
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  ahw4npmjpnu1k, child number 0
--------------------------------------
select max(amount_sold) from sales where channel_id=3

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       | 28396 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| SALES |    17M|   131M| 28396   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CHANNEL_ID"=3)

Now the remedy:

begin
 dbms_sqltune.import_sql_profile(
 name => 'MYPROFILE1',
 category => 'DEFAULT',
 sql_text => 'select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3',
 profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS')
                                 );
end;
/

PL/SQL procedure successfully completed.

SQL> select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
         1782.72

Elapsed: 00:00:01.05

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7m2k0y4hy1ngh, child number 0
-------------------------------------
select /*+ index (sales,sales_bix) */ max(amount_sold) from sales where channel_id=3

Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       | 28396 (100)|          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES |    17M|   131M| 28396   (1)| 00:00:02 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CHANNEL_ID"=3)

Note
-----

PLAN_TABLE_OUTPUT
----------------------------------------------------
   - SQL profile MYPROFILE1 used for this statement

This works for that SQL statement only without having to modify the application. The SQL profile can be removed like this:

SQL> exec dbms_sqltune.drop_sql_profile('MYPROFILE1')
PL/SQL procedure successfully completed.

All the above is not new, but still I think it might be worthwhile to mention it here for your reference, should you encounter some nasty hints once:-)

,

3 Comments

Another reason why you should use the Data Guard Broker for your #Oracle Standby

The Data Guard Broker is recommended for various reasons, this one is less obvious: It prevents a Split-Brain problem that may otherwise occur in certain situations. Let me show you:

[oracle@uhesse ~]$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - myconf

  Protection Mode: MaxAvailability
  Members:
  prima - Primary database
    physt - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

This is my setup with 12c, but the demonstrated behavior is the same with 11g already. I will cause a crash of the primary database now, without damaging any files – like a power outage on the primary site:

[oracle@uhesse ~]$ ps -ef | grep smon
oracle    6279     1  0 08:30 ?        00:00:00 ora_smon_prima
oracle    6786     1  0 08:32 ?        00:00:00 ora_smon_physt
oracle    7168  3489  0 08:43 pts/0    00:00:00 grep --color=auto smon
[oracle@uhesse ~]$ kill -9 6279

Don’t do that at home:-) Now the primary is gone, but of course I can failover to the standby:

[oracle@uhesse ~]$ dgmgrl sys/oracle@physt
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> failover to physt;
Performing failover NOW, please wait...
Failover succeeded, new primary is "physt"

So far so good, my end users can continue to work now on the new primary. But what happens when the power outage is over and the ex-primary comes back up again?

[oracle@uhesse ~]$ sqlplus sys/oracle@prima as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed May 18 08:47:30 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size		    2923920 bytes
Variable Size		  452985456 bytes
Database Buffers	  788529152 bytes
Redo Buffers		   13852672 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened

The DMON background process of the new primary communicates with the DMON on the ex-primary, telling it that there cannot be two primary databases within the same Data Guard Broker configuration! Try the same scenario without the broker and you will observe the ex-primary coming up until status OPEN. Just wanted to let you know:-)

5 Comments

FREE Webinar: Efficient techniques to create and maintain your #Oracle Standby Database

Join us with this FREE event on May 19, 13:00 CET and register now, because seats are limited.

DataGuard_Lunchtime_Webinar

I will be talking about

  • how to create and maintain a 12c Standby Database in the most efficient way
  • how to do switchover and failover
  • how to keep up client connectivity after role changes

These topics will be live demonstrated – positively no slide show reading.

After this major part of the event, we will briefly advertise our digital learning offerings that relate to Oracle Database Core Technology and how you may take advantage of them.

Hope to see YOU in the session:-)

1 Comment

Follow

Get every new post delivered to your Inbox.

Join 4,277 other followers

%d bloggers like this: