Search

OakieTags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

Oakies Blog Aggregator

12c Parallel Execution New Features: Parallel FILTER Subquery Evaluation - Part 1: Introduction

12c introduces another interesting new Parallel Execution feature - the parallel evaluation of FILTER subqueries. In pre-12c FILTER subqueries always had to be evaluated in the Query Coordinator. This had several consequences, in particular the data driving the FILTER subquery always had to flow through the Query Coordinator, and hence represented a forced serial execution part of a parallel execution plan. This limitation also meant that depending on the overall plan shape the parallel plan was possibly decomposed into multiple DFO trees, leading to other side effects I've outlined in some of my other publications already.In 12c now the FILTER subquery can be evaluated in the Parallel Slaves, and the driving data no longer needs to be processed in the Query Coordinator. However, the resulting plan shape can be a little bit confusing. Let's have a look at a simple example:


create table t_1
compress
as
select /*+ use_nl(a b) */
rownum as id
, rpad('x', 100) as filler
from
(select /*+ cardinality(1e5) */ * from dual
connect by
level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_1', method_opt=>'for all columns size 1')

alter table t_1 parallel 4;

create index t_1_idx on t_1 (id) invisible;

explain plan for
select /*+
--optimizer_features_enable('11.2.0.4')
*/ count(*) from
t_1 t
where exists (select /*+ no_unnest */ null from t_1 where t.id = t_1.id);

-- 11.2.0.4 plan shape with index invisible
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 440M (2)| 04:47:04 | | | |
| 1 | SORT AGGREGATE | | 1 | 6 | | | | | |
|* 2 | FILTER | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM)| :TQ20000 | 2000K| 11M| 221 (1)| 00:00:01 | Q2,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 2000K| 11M| 221 (1)| 00:00:01 | Q2,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T_1 | 2000K| 11M| 221 (1)| 00:00:01 | Q2,00 | PCWP | |
| 7 | PX COORDINATOR | | | | | | | | |
| 8 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 6 | 222 (2)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 9 | PX BLOCK ITERATOR | | 1 | 6 | 222 (2)| 00:00:01 | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL| T_1 | 1 | 6 | 222 (2)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------

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

2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_1" "T_1" WHERE "T_1"."ID"=:B1))
10 - filter("T_1"."ID"=:B1)

-- 12.1.0.2 plan shape with index invisible
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1588M (2)| 17:14:09 | | | |
| 1 | SORT AGGREGATE | | 1 | 6 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 6 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 6 | | | Q1,00 | PCWP | |
|* 5 | FILTER | | | | | | Q1,00 | PCWC | |
| 6 | PX BLOCK ITERATOR | | 2000K| 11M| 221 (1)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T_1 | 2000K| 11M| 221 (1)| 00:00:01 | Q1,00 | PCWP | |
|* 8 | TABLE ACCESS FULL | T_1 | 1 | 6 | 798 (2)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------

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

5 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_1" "T_1" WHERE "T_1"."ID"=:B1))
8 - filter("T_1"."ID"=:B1)

-- 11.2.0.4 plan shape with index visible
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 5973K (1)| 00:03:54 | | | |
| 1 | SORT AGGREGATE | | 1 | 6 | | | | | |
|* 2 | FILTER | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | |
| 4 | PX SEND QC (RANDOM)| :TQ10000 | 2000K| 11M| 221 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 5 | PX BLOCK ITERATOR | | 2000K| 11M| 221 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T_1 | 2000K| 11M| 221 (1)| 00:00:01 | Q1,00 | PCWP | |
|* 7 | INDEX RANGE SCAN | T_1_IDX | 1 | 6 | 3 (0)| 00:00:01 | | | |
----------------------------------------------------------------------------------------------------------------

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

2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_1" "T_1" WHERE "T_1"."ID"=:B1))
7 - access("T_1"."ID"=:B1)

-- 12.1.0.2 plan shape with index visible
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 5973K (1)| 00:03:54 | | | |
| 1 | SORT AGGREGATE | | 1 | 6 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 6 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 6 | | | Q1,00 | PCWP | |
|* 5 | FILTER | | | | | | Q1,00 | PCWC | |
| 6 | PX BLOCK ITERATOR | | 2000K| 11M| 221 (1)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T_1 | 2000K| 11M| 221 (1)| 00:00:01 | Q1,00 | PCWP | |
|* 8 | INDEX RANGE SCAN | T_1_IDX | 1 | 6 | 3 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------

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

5 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_1" "T_1" WHERE "T_1"."ID"=:B1))
8 - access("T_1"."ID"=:B1)

I've included two variations of the setup, one without available index for evaluating the FILTER subquery and one with index.The pre-12c plan shape without index makes the former limitation particularly obvious: The FILTER operator is above the PX COORDINATOR and marked serial, and the table scan in the FILTER subquery gets parallelized as separate DFO tree (indicated among others by the two PX COORDINATOR operators), which means that each time this separate DFO tree starts, a separate set of Parallel Slave will be allocated/deallocated, adding possibly a lot of overhead to a probably already inefficient execution plan anyway - assuming the FILTER subquery needs to be evaluated many times.In 12c the FILTER operator is marked parallel and the need for a separate DFO tree is gone. What might be confusing with this plan shape is that the operations of the FILTER subquery are not marked parallel. In my opinion this is misleading and should actually be marked parallel, because at runtime the operations will be performed by the Parallel Slaves, and in case of a Full Table Scan each slave will run the entire full table scan (so no PX ITERATOR for dividing the scan into chunks / granules), which is comparable to what happens when a parallel Nested Loop join runs or the new PQ_REPLICATE feature gets used - and in those cases the operations are marked parallel:


-- 11.2.0.4 / 12.1.0.2 plan shape with index invisible
-- and subquery unnested using NL SEMI join
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 442M (2)| 04:48:03 | | | |
| 1 | SORT AGGREGATE | | 1 | 12 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 12 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 12 | | | Q1,00 | PCWP | |
| 5 | NESTED LOOPS SEMI | | 2000K| 22M| 442M (2)| 04:48:03 | Q1,00 | PCWP | |
| 6 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T_1 | 2000K| 11M| 221 (1)| 00:00:01 | Q1,00 | PCWP | |
|* 8 | TABLE ACCESS FULL | T_1 | 2000K| 11M| 796 (2)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------

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

8 - filter("T"."ID"="T_1"."ID")

Summary

So the operators of the FILTER subquery can now be run in the slaves, which is the main point of this feature, although being represented in a confusing way in the execution plan. Avoiding the potential decomposition into multiple DFO trees is another possible side effect. Decreased query duration should be possible if the evaluation of the FILTER subquery requires significant time and can now be run in the Parallel Slaves instead of serial execution through the Query Coordinator.Note that depending on the plan shape and SQL features used, it's still possible that 12c reverts to the old serial FILTER subquery evaluation plan shape, so the new feature doesn't get used always.There is more to say about this feature. In the next part of this instalment I'll focus on the different distribution methods possible with the new parallel FILTER operator - there is a new PQ_FILTER hint that allows controlling the distribution, but there are also some interesting points to make about how the optimizer seems to make its choice which distribution method to use automatically. In the examples shown here there's no separate distribution for the FILTER, by the way, but this can look differently, as I'll show in the next part.

OTN Tour of Latin America 2015 (Southern Leg)

ace-directorI put out a brief video a few days ago (re-uploaded today to fix typos) about my participation in the OTN Tour of Latin America (2015). I’ll be on the southern leg this year. Sorry to those countries who make up the northern leg. I will be back soon I hope.

Anyway, the southern leg of the tour shapes up like this.

  • 3/4 August Uruguay UYOUG
  • 5/6 August Argentina AROUG
  • 8 August Brazil GUOB
  • 10 August Chile CLOUG
  • 12 August Peru PEOUG

I’m looking forward to seeing everyone. See you soon!

After the Peru leg, the wife and I will be going off to see Machu Picchu.

Cheers

Tim…


OTN Tour of Latin America 2015 (Southern Leg) was first posted on July 26, 2015 at 1:41 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

The Queen of Social Media Mean

Unlike other forms of communication, social media can be a real gray area for rules on what dictates freedom of speech and how you have to endure with those that may not be pleasant to interact with.  I’m often told what etiquette some feel it necessary and that I should just disengage from some people online.

The truth is, not everyone gets social media and many just don’t understand boundaries or how to communicate productively when online.  You, as a member of the online community, have the right to choose who you communicate with and when it’s time to NOT communicate with another person online.  Although I often keep this type of post to Linked in, I decided to discuss this here, as I think it’s a question that many ask themselves when they run into social media disagreements-

When do I draw a line and how do I justify enough is enough?

Luckily, most people are busy with their day that they just don’t care what other people are posting.  I think the most disconcerting are those that think so deeply about what other’s have posted in a 140 character limit that it completely consumes their day and yes, they are out there.

You MUST Respond to Me

I’ve blogged about data I’ve gathered that demonstrated how the more interaction and attention you attain on social media, the more often negative reactions, (abusive incidents) will occur from certain personality types.  I also noticed that there were those that weren’t abusive or destructive, but were negative in their interaction.  Others on social media will recognize this type- you see their handle come up on your feed and think, “Sigh….what now….”  You are hesitant to look at their response to something you’ve posted, as you know when you do, you rarely feel good afterwards.  Their comments almost consistently have a “but” in them.  “I liked your post, BUT….” “The topic is good, but…” If you react, they think you’re sensitive to criticism when the truth is, their goal is meant to dismiss your post that you’ve taken the time to share.  These types commonly show up in your social media feed at the same time as abusive types when you analyze social media data.  Many online categories will class these posts along with abusive types as trolls, but I’ve chosen to categorize them with the title of the “pokers”.

drwhopoke

Why do I call them this?  Remember when you were a little kid and you had that one friend or more likely, a sister or brother that sat next to you in the back seat of the car and would poke you in the arm for the whole ride?  I haven’t met a parent yet that hasn’t gone through the, “He’s touching me!!” fight with their kids.

Enough Already!

The goal of the poker is the same as the sibling in the back seat of the car- distraction from something that’s bothering them.  Most often, they’re satiating their insecurities or frustrations by distracting you and/or your followers by responding to your posts.  Now we all remember what happened to that annoying friend or sibling that poked you repeatedly after a period of time being poked, right?

dont_poke_me_by_lonely_shadow_mutt

Once you’ve hit your breaking point and react, the response from the poker is one of amazement and often offense-

offended1

How could you insult them and think that they would act in such a way? How could you think they had anything but the most honest and best intentions?  They really don’t understand why you’ve suddenly reacted so negatively after they’ve responded this way on so many of your other posts over a period of time.  They weren’t hurting you, they just made a comment!

 

Don’t Get Mean, Get Logical

The fact is, this type of “troll” can be damaging to you and your personal brand.  This type of distraction can impact your productivity, as well as impact any social media work you are performing for your brand or the company you represent.  If you’ve engaged them and asked them to cease and they don’t stop, then there is only one course of action:

Block.

Disconnect.

Unfriend.

Now many of my connections and friends have been surprised when after blocking people we’re both connected to, they’ll send me a private message and ask, “Did you really just block XXX?  He says you blocked him!”  The percentage of those I’ve blocked, (no matter if it is publicly known that I’ve blocked them or kept private) of someone else having an issue with the “poker” after me is currently 100%.  Great percentage folks, congratulations!

Here’s the secondary reason for blocking, disconnecting and/or unfriending.  My feed is obviously causing the poker to be agitated.  The poker reacts when I’m either recognized for speaking, contributions or experienced a high amount of interaction in social media.  This level of social media activity is bringing out their insecurities, (just as it would with an online bully, but the poker isn’t reacting to the degree a bully would, which is why I do offer one request for them to stop and choose a different path of interaction with me.)  The added benefit of disconnecting and unfriending removes your social media contributions from their feed, providing an “out of sight, out of mind” benefit.  Removing as much interaction with this person as possible, you’re doing both parties a favor.

flabbergasted

Nothing to See Here

Once you remove yourself from this person’s view, don’t be surprised if they end up choosing a new target to “poke”.  It’s almost unheard of that this type won’t require some new person to use as an outlet to “poke” at.  By not engaging outside of a single warning to cease and desist, you’ll save yourself from continuing to be a target and can focus on your goals.

Finally, for those who’ve been blocked, unfriended or disconnected from my social media profiles.  I really don’t have any personal feelings one way or the other towards you. Let’s be honest-  my husband, my children and my job mean a lot to me, but you don’t.  My social media work is part of my professional life and this is a professional decision.  If you are derailing me from my professional goal and if I decide I’m causing you agitation and in response you react negatively, yes, I’m going to disengage from you.  It’s the logical thing to do and I wish more would take that step when this type of behavior happens online.

Habits-Castle2

Don’t email me and ask why I blocked you or act offended.  I don’t respond to my emotions, I respond to data and recognize that by accepting or engaging your behavior, your problem will then become mine.  Also, don’t ask my connections why you’ve been blocked.  They rarely, if ever know why I’ve blocked you, but are less surprised that you’ve alienated someone.  Accept that it’s for both our benefits and move on.

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [The Queen of Social Media Mean], All Right Reserved. 2015.

APEX 5.0.1 : We’re all patched up!

apexAPEX 5.0.1 was released about a week ago. I started to patch some stuff straight away. We were already on APEX 5.0 across the board, so we didn’t need to do any full installations, just patches.

During the patching I noticed we were getting some issues with supposed misconfiguration of static files. After clearing my browser cache, the message went away, so I tweeted this.

“Regarding APEX 5.0.1 patch. Clear your browser cache, or you may get that static files message, even though config is correct. :) #orclapex

Practically before I hit return, Patrick Wolf came back with this.

“@oraclebase Thanks for letting us know. I have filed bug# 21463521 and fixed it for 5.0.2″

We’re a week on now and all our APEX installations are happily running 5.0.1. We had no issues with the upgrades and no problems with the apps.

We are small fry where APEX is concerned, so don’t take this as the green light to upgrade everything yourself. I’m just saying we had no problems with it, which is pretty cool. If APEX is a strategic environment for you, you will probably need to do a lot more testing than us. :)

Cheers

Tim…


APEX 5.0.1 : We’re all patched up! was first posted on July 24, 2015 at 4:32 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

MobaXterm 8.0

command-promptI just noticed MobaXterm 8.0 was released a few days go.

Downloads and changelog available in the usual places.

Happy unzipping!

Cheers

Tim…


MobaXterm 8.0 was first posted on July 24, 2015 at 3:19 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

AUDIT_SYS_OPERATIONS defaults to TRUE in #Oracle 12c

A small but remarkable change in Oracle Database 12c is the default value of AUDIT_SYS_OPERATIONS has changed to TRUE now. In other words, all actions done by the superuser sys are being audited now by default!

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 24 15:23:10 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,value from v$spparameter where isspecified='TRUE';

NAME                                     VALUE
---------------------------------------- --------------------------------------------------
memory_target                            1073741824
control_files                            /u01/app/oracle/oradata/prima/control01.ctl
db_block_size                            8192
compatible                               12.1.0.2
db_recovery_file_dest                    /u02/fra
db_recovery_file_dest_size               2147483648
undo_management                          auto
undo_tablespace                          undotbs1
remote_login_passwordfile                exclusive
db_name                                  prima
diagnostic_dest                          /u01/app/oracle

11 rows selected.


SQL> show parameter sys_oper

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     TRUE
SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

SQL> show parameter audit_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/rdbms/audit
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit

[oracle@uhesse audit]$ cat prima_ora_6204_20150724152310753136143795.aud
Audit file /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/prima_ora_6204_20150724152310753136143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      uhesse
Release:        3.8.13-68.2.2.el7uek.x86_64
Version:        #2 SMP Tue May 12 14:38:58 PDT 2015
Machine:        x86_64
Instance name: prima
Redo thread mounted by this instance: 1
Oracle process number: 41
Unix process pid: 6204, image: oracle@uhesse (TNS V1-V3)

Fri Jul 24 15:23:10 2015 +02:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2113606181'
[Output shortened...]
Fri Jul 24 15:23:56 2015 +02:00
LENGTH : '185'
ACTION :[31] 'select count(*) from scott.dept'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2113606181'

Something you might need to know as a DBA, don’t you think? :-)

Tagged: 12c New Features, security

Subquery Factoring (9)

Several years go (eight to be precise) I wrote a note suggesting that Oracle will not materialize a factored subquery unless it is used at least twice in the main query. I based this conclusion on a logical argument about the cost of creating and using a factored subquery and, at the time, I left it at that. A couple of years ago I came across an example where even with two uses of a factored subquery Oracle still didn’t materialize even though the cost of doing so would reduce the cost of the query – but I never got around to writing up the example, so here it is:


create table t1
as
select
        object_id, data_object_id, created, object_name, rpad('x',1000) padding
from
        all_objects
where
        rownum <= 10000
;

exec dbms_stats.gather_table_stats(user,'T1')

explain plan for
with gen as (
        select /*+ materialize */ object_id, object_name from t1
)
select
        g1.object_name,
        g2.object_name
from
        gen g1,
        gen g2
where
        g2.object_id = g1.object_id
;

select * from table(dbms_xplan.display);

You’ll notice that my original table has very wide rows, but my factored subquery selects a “narrow” subset of those rows. My target is to have an example where doing a tablescan is very expensive but the temporary table holding the extracted data is much smaller and cheaper to scan.

I’ve included a materialize hint in the SQL above, but you need to run the code twice, once with, and once without the hint. Here are the two plans – unhinted first:


============================
Unhinted - won't materialize
============================

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 |   468K|   428   (2)| 00:00:03 |
|*  1 |  HASH JOIN         |      | 10000 |   468K|   428   (2)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |   234K|   214   (2)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T1   | 10000 |   234K|   214   (2)| 00:00:02 |
---------------------------------------------------------------------------

==================================
Hinted to materialize - lower cost
==================================

--------------------------------------------------------------------------------------------------------- 
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT           |                            | 10000 |   585K|   227   (2)| 00:00:02 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6664_9DAAEB7 |       |       |            |          | 
|   3 |    TABLE ACCESS FULL       | T1                         | 10000 |   234K|   214   (2)| 00:00:02 | 
|*  4 |   HASH JOIN                |                            | 10000 |   585K|    13   (8)| 00:00:01 | 
|   5 |    VIEW                    |                            | 10000 |   292K|     6   (0)| 00:00:01 | 
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6664_9DAAEB7 | 10000 |   234K|     6   (0)| 00:00:01 | 
|   7 |    VIEW                    |                            | 10000 |   292K|     6   (0)| 00:00:01 | 
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6664_9DAAEB7 | 10000 |   234K|     6   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Clearly the optimizer isn’t considering the costs involved. If I add the predicate “where object_id > 0″ (which identifies ALL the rows in the table), materialization occurs unhinted (with the same costs reported as for the hinted plan above. My tentative conclusion is that the transformation is a heuristic one that follows the rule “two or more appearances of the subquery and some indication of row selection in the subquery rowsource”. (In fact if the rowsource is “select * from pipeline_function” the requirement for subsetting doesn’t seem to apply.)

The plans above came from 11.2.0.4 but I got the same result, with a slight difference in costs, in 12.1.0.2. It’s worth pointing out that despite Oracle apparently ignoring the costs when deciding whether or not to materialize, it still seems to report self-consistent values after materialization: the 227 for the plan above is the 214 for creating the temporary table plus the 13 for deriving the hash join of the two copies of the temporary table.

ORA-15410: Disks in disk group do not have equal size

12.1.0.2 ASM introduced a new feature which might prevent you from adding dissimilar size disks into a normal or high redundancy disk groups. The relevant MOS note is Doc ID 1938950.1.

Unfortunately I've found that some information in this note requires further clarification.

COMPATIBLE.ASM

The note suggests that the check is only enforced when the Disk Group has 'COMPATIBLE.ASM'='12.1.0.2' attribute set. This is incorrect and can be easily demonstrated:





SQL> select name, compatibility from v$asm_diskgroup;

NAME COMPATIBILITY
------------------------------ --------------------
DATA 11.2.0.3.0

SQL> alter diskgroup data add disk '/dev/sdc';
alter diskgroup data add disk '/dev/sdc'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15410: Disks in disk group DATA do not have equal size.

As you can see my Disk Group has 'COMPATIBLE.ASM'='11.2.0.3.0' which did not prevent it from getting ORA-15410.

What happens if you have disks of dissimilar sizes already in the Disk Group?

Let's say you've upgraded from a previous release and your normal/high redundancy disk group had disks of dissimilar sizes in it already. What happens when you try to add a disk after the upgrade to 12.1.0.2 ASM? Some of the questions that immediately jump to mind:

  • Will it always rise an error because the existing disks have different sizes forcing you to make everything of the same size first?
  • Will it let you add a disk as long as the size is the same to one of the disks you already have?
  • Will it let you add a disk with a new size?

Here I have a 12.1.0.2 normal redundancy Disk Group with two disks of dissimilar size:

SQL> select dg.name group_name,
dg.type,
dg.compatibility,
d.name disk_name,
d.total_mb
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number=d.group_number
order by d.disk_number;

GROUP_NAME TYPE COMPATIBILITY DISK_NAME TOTAL_MB
---------- ------ -------------------- ---------- ----------
DATA NORMAL 12.1.0.2.0 DATA_0000 4096
DATA NORMAL 12.1.0.2.0 DATA_0001 5120

Let's add a third disk of some other size:

SQL> alter diskgroup data add disk '/dev/sdd' size 2g;

Diskgroup altered.

SQL> select dg.name group_name,
dg.type,
dg.compatibility,
d.name disk_name,
d.total_mb
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number=d.group_number
order by d.disk_number;

GROUP_NAME TYPE COMPATIBILITY DISK_NAME TOTAL_MB
---------- ------ -------------------- ---------- ----------
DATA NORMAL 12.1.0.2.0 DATA_0000 4096
DATA NORMAL 12.1.0.2.0 DATA_0001 5120
DATA NORMAL 12.1.0.2.0 DATA_0002 2048

As you can see, as long as you have disks of dissimilar sizes already in the disk group, the check appears to be ignored. I can resize the disks as well:

SQL> alter diskgroup data resize disk DATA_0002 size 4g;

Diskgroup altered.

SQL> select dg.name group_name,
dg.type,
dg.compatibility,
d.name disk_name,
d.total_mb
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number=d.group_number
order by d.disk_number; 2 3 4 5 6 7 8

GROUP_NAME TYPE COMPATIBILITY DISK_NAME TOTAL_MB
---------- ------ -------------------- ---------- ----------
DATA NORMAL 12.1.0.2.0 DATA_0000 4096
DATA NORMAL 12.1.0.2.0 DATA_0001 5120
DATA NORMAL 12.1.0.2.0 DATA_0002 4096

Now what happens if I drop DATA_0001 thus making all disks to be of equal size?

SQL> alter diskgroup data drop disk DATA_0001;

Diskgroup altered.

SQL> alter diskgroup data add disk '/dev/sdc';
alter diskgroup data add disk '/dev/sdc'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15410: Disks in disk group DATA do not have equal size.

It appears that the moment the Disk Group got all disks of the same size the check got enforced.

Conclusion

Based on the tests I've demonstrated above the rules for ORA-15410 on 12.1.0.2 ASM appears to be:

  • If all the disks in a Disk Group are of the same size then the check is enforced, regardless of what 'COMPATIBLE.ASM' value is.
  • If a Disk Group contains disks of different sizes then the check is ignored, regardless of what 'COMPATIBLE.ASM' value is.

(keeping in mind that the check can only happen for normal/high redundancy Disk Groups)









WordPress 4.2.3 : Hurry up and … let it fix itself…

WordPress 4.2.3wordpress has been released.

It contains fixes for some pretty nasty stuff. Usually, the updates have to be manually triggered for a day or so before the auto-update feature picks them up. I was on the blog this morning and there were no “manually triggered auto-updates” available, so it looks like this one has been pushed straight out, which probably makes sense.

By the time you’ve read this you are probably up to date already, but if not, get on your blog and give it a nudge. :)

You can see the changelog here.

Cheers

Tim…


WordPress 4.2.3 : Hurry up and … let it fix itself… was first posted on July 23, 2015 at 1:28 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Emergency Monitoring, Real-Time ADDM, Compare Period ADDM, and Active Session History (ASH) Analytics

My recent dalliance with YouTube (141 subscribers and growing! :) ) has left the blog feeling a little bit unloved of late, but then why write when you can waffle in the car? :)

Anyway, the 12c learning train keeps on rolling. I’ve recently put the following articles live.

These are all listed as 12c new features in the 1Z0-060 “Upgrade to Oracle Database 12c” OCP upgrade exam, which I find a bit odd. Two of them are EM12c features, not database features. The other two are existing EM12c features that are now available directly from the database, but I can’t see myself ever using them on the server when it is much easier to do it from Cloud Control. Whatever! :)

Getting close to the end of the OCP syllabus now… I’ll soon have to consider sitting the exam…

Cheers

Tim…


Emergency Monitoring, Real-Time ADDM, Compare Period ADDM, and Active Session History (ASH) Analytics was first posted on July 23, 2015 at 12:00 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.