Search

OakieTags

Who's online

There are currently 0 users and 35 guests online.

Recent comments

Oakies Blog Aggregator

2016 DBAKevlar Year in Review

How was 2016 for me?

It was a surprisingly busy year-  blogging, speaking, working and doing.

I posted just under 100 posts to my blog this year.  After I changed jobs, the “3 per week” quickly declined to “4 per month” after I was inundated with new challenges and the Delphix learning curve.  That will change for 2017, along with some new initiatives that are in the works, so stay tuned.

For 2016, the most popular posts and pages for my website followed a similar trend from the last year.  My emulator for RPI is still a popular item and I have almost as many questions on RPI as I do WIT-  Raspberry Pi is everywhere and you’ll see a regained momentum from me with some smart home upgrades.

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/01/Screen-Shot-2... 278w" sizes="(max-width: 337px) 100vw, 337px" data-recalc-dims="1" />

My readers for 2016 came from just about every country.  There were only a few that weren’t represented, but the largest numbers were from the expected:

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/01/Screen-Shot-2... 300w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

I also write from time to time on Linked in.  Linked in has become the home for my Women in Technology posts and its lead me to receive around 30 messages a week from women looking for guidance, sharing their stories or just reaching out.  I appreciate the support and the value its provided to those in the industry.

RMOUG Conference Director- No Escape!

The 2016 conference was a great success for RMOUG, but much of it was due to budget cuts and changes that were made as we went along and addressed trends.  I’ve been collecting the data from evaluations and it really does show why companies are so interested in the value their data can provide them.  I use what I gather each year to make intelligent decisions about where RMOUG should take the conference direction each year-  what works, what doesn’t and when someone throws an idea out there, you can either decide to look into it or have the data to prove that you shouldn’t allocate resources to an endeavor.

A New Job

I wasn’t into the Oracle cloud like a lot of other folks.  It just wasn’t that interesting to me and felt that Oracle, as much as they were putting into their cloud investment, deserved someone who was behind it.  I’d come to Oracle to learn everything I could about Oracle and Enterprise Manager and an on-premise solution as it was, it wasn’t in the company focus.  When Kyle and I spoke about an opportunity to step into a revamped version of his position at Delphix, a company that I knew a great deal about and admired, it was a no-brainer.  I started with this great, little company in June and there are some invigorating initiatives that I look forward to becoming part of for 2017!

2 Awards

In February, I was awarded RMOUG’s Lifetime achievement award.  I kind of thought this would mean I could ride off in the sunset as the conference director, but as my position ended at Oracle, which had been a significant fight to keep me managing the conference as an Oracle employee, (transitioning me to a non-voting member to keep within the by-laws) not many were surprised to see me take on a sixth year of managing the conference.

In April I was humbly awarded the Ken Jacobs award from IOUG.  This is an award I’m very proud of, as Oracle employee’s are the only ones eligible and I was awarded it in just the two years I was employed at the red O.

3 Makers Events

I haven’t had much time for my Raspberry Pi projects the last number of months, but it doesn’t mean I don’t still love them.  I gained some recognition as 2nd ranking in the world for RPI klout score back in July, which took me by surprise.  I love adding a lot of IOT stories into my content and it had caught the attention of social media engines.  Reading and content is one thing, but it was even more important to do- I had a blast being part of the impressive Colorado’s Maker Faire at the Denver Museum of Nature and Science earlier in 2016.  I also was part of two smaller makers faires in Colorado, allowing me to discuss inexpensive opportunities for STEM education for schools using Raspberry Pis, Python coding and 4M kits.

Speaking Engagements

Even though I took a number of months off to focus on Delphix initiatives, I still spoke at 12 events and organized two, (Training Days and RMOUG’s QEW.)

February:  RMOUG– Denver, CO, (Director and speaker)

March: HotSos– Dallas, CO, (Keynote)

April: IOUG Collaborate– Las Vegas, NV

May: GLOC– Cleveland, OH, NoCOUG– San Jose, CA

June: KSCOPE– Chicago, IL

July: RMOUG Quarterly Education Workshop– Denver, CO, (Organizer)

September: Oracle Open World/Oak Table World– San Francisco, CA

October: UNYOUG– Buffalo, NY, Rocky Mountain DataCon & Denver Testing Summit–  Denver, CO

November: MOUS– Detroit, MI, (Keynote) ECO– Raleigh, NC

New Meetup Initiatives and Growth

I took over the Denver/Boulder Girl Geek Dinners meetup last April.  The community had almost 650 members at the time and although it wasn’t as big as the Girls Develop It or Women Who Code, I was adamant about keeping it alive.  Come the new year and thanks to some fantastic co-organizers assisting me, (along with community events in the technical arena) we’re now on our way to 1100 members for the Denver/Boulder area.

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/01/Screen-Shot-2... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/01/Screen-Shot-2... 768w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

The Coming Year

I’m pretty much bursting with anticipation due to all that is on my plate for the coming year.  I know the right hand side bar is a clear indication that I’ll be speaking more, meaning more travel and a lot of new content.  With some awesome new opportunities from Delphix and the organizations I’m part of, I look forward to a great 2017!

 



Tags:  , , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [2016 DBAKevlar Year in Review], All Right Reserved. 2017.

The post 2016 DBAKevlar Year in Review appeared first on DBA Kevlar.

Graphics for SQL Optimization

Dan Tow, in his book SQL Tuning, lays out a simple method of tuning SQL queries. The method is

  • Draw a diagram of each table in the query with Children above Parents
  • Draw join lines between each join (many-to-many, one-to-many)
  • Mark each table with a predicate filter and calculate the amount of table filtered out

Then to find a great optimal optimization path candidate

  1. Start at the table with the strongest predicate filter (the filter that returns the fewest % of the table)
  2. join down to children (if multiple children join to child with strongest predicate filter)
  3. If you can’t join to children, join up to parent

The basics are pretty simple and powerful. Of course there are many cases that get more complex and Dan goes into these complex cases in his book.

What about indexes? Well the method will point out joins that should happen and if those joins are missing indexes then it indicates that indexes should be created.

What about join type? I generally leave this to the optimizer. The join type can be important but generally order of joins and indexes are more important. I look at join type as the final optimization.

Let’s take an example query:

SELECT COUNT (*)
FROM   a,
       b,
       c
WHERE
       b.val2 = 100 AND
       a.val1 = b.id AND
       b.val1 = c.id;

There are  indexes on b.id and c.id.  Diagramming the query in DB Optimizer gives

sql1

The red lines with crows feet mean that as far as the definitions go, the relations could be many to many.

Question is “what is the optimal execution path for this query?”

One of  the best execution plans is to

  1. start at the most selective filter table
  2. join to children  if possible
  3. else join to parent

There is one filter in the diagram, represented by the green F on table B. Table B has a filter criteria in the query “b.val2=100″.

Ok, table B is where we start the query. Now where do we go from B? Who is the parent and who is the child? It’s not defined in the constraints nor indexes on these tables so it’s hard for us to know. Guess what ? It’s also hard for Oracle to figure it out. Well, what does Oracle decide to do? This is where the cool part of DB Optimizer  comes in.

The super cool thing with DB Optimizer is we can overlay the diagram with the actual execution path (I think this is awesome)

sql2

For the digram we can see Oracle starts with B and joins to A. The result if this is joined to C. Is this the optimal path?

Well, let’s keep the same indexes and just add some constraints:

alter table c add constraint c_pk_con unique (id);
alter table b add constraint b_pk_con unique (id);

Now let’s diagram the query with DB Optimizer:

sql3

We can now see who the parent and child is, so we can determine the optimal query path which is to start at B, the only filter and  join to the child C then to the parent A.  Now what does Oracle do with the added constraint info:

sql4

Guess what? The execution plan has now changed with the addition of constraints and now Oracle’s execution path goes from a suboptimal plan to  the optimal path. Moral of the story is to make sure and define constraint information because it helps the optimizer, but what I wanted to show here was the explain plan overlay on the diagram which makes comparing execution plans much easier. Putting the queries VST diagrams side by side along with the overlay of execution path we can clearly and quickly see the differences:

sql5sql6

I plan to blog more about this awesome feature. It’s really cool.

Here is an example from an article by Jonathan Lewis

http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

The query Jonathan discusses is

SELECT order_line_data
FROM
         customers cus
         INNER JOIN
         orders ord
         ON ord.id_customer = cus.id
         INNER JOIN
         order_lines orl
         ON orl.id_order = ord.id
         INNER JOIN
         products prd1
         ON prd1.id = orl.id_product
         INNER JOIN
         suppliers sup1
         ON sup1.id = prd1.id_supplier
   WHERE
         cus.location = 'LONDON' AND
         ord.date_placed BETWEEN '04-JUN-10' AND '11-JUN-10' AND
         sup1.location = 'LEEDS' AND
    EXISTS (SELECT NULL
            FROM
                 alternatives alt
                 INNER JOIN
                 products prd2
                 ON prd2.id = alt.id_product_sub
                 INNER JOIN
                 suppliers sup2
                 ON sup2.id = prd2.id_supplier
           WHERE
                  alt.id_product = prd1.id AND
                  sup2.location != 'LEEDS')

which diagrammed looks like

sql7

There are multiple filters, so we need to know which one is the most selective to know where to start, so we ask DB Optimizer to display the statistics as well  (blue below a table is the filter %, green above is # of rows in table and numbers on join lines are rows returned by a join of just those two tables)

sql8

Now that we can determine a candidate for best optimization path, does Oracle take it?

sql9

Can you find the optimization error?

Dark green is where execution starts. There are two starts: one for the main query body and one for the subquery.

The red is where query execution ends.

PS a big part of this work is by the lead developer Matt Vegh. Many thanks to Matt for this awesome work.

 

PPS another example from Karl Arao

sql10

The dark green nodes are starts, so there are 4 separate starts. We can see how the result sets from each start are joined with each successive table join set. The red is the final step.

Hot cloning and refreshing PDBs in #Oracle 12cR2

Hot cloning PDBs is new in 12.2, so you don’t have to put the source PDB into READ ONLY mode before the cloning if you have it in local undo mode. I suppose shared undo mode will become very unpopular. Another 12.2 New Feature is the option to clone a PDB that can be refreshed from the source PDB. I will show both features with this article, but you may of course do hot cloning without a later refresh. In this case, just leave out the REFRESH MANUAL clause and you don’t have to OPEN READ ONLY the cloned PDB afterwards. On a high level, what I demonstrate is this:

Hot cloning & refreshing a PDB

Hot cloning & refreshing a PDB

Now let’s see that with details:

SQL> connect sys/oracle_4U@cdb1 as sysdba
Connected.
SQL> select name,open_mode from v$pdbs;

NAME	   OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
PDB1	   READ WRITE

I have prepared a tnsnames.ora with connect descriptors for pdb1 and the future pdb2:

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
PDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb2)
    )
  )

Now I create the clone user inside of the source PDB and the database link inside the destination CDB:

SQL> connect sys/oracle_4U@pdb1 as sysdba
Connected.
SQL> grant create session,create pluggable database to cloner identified by cloner;

Grant succeeded.

SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> select name,open_mode from v$pdbs;

NAME	   OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY

SQL> create database link clone_link connect to cloner identified by cloner using 'pdb1';

Database link created.
SQL> select * from session_privs@clone_link;

PRIVILEGE
----------------------------------------
CREATE PLUGGABLE DATABASE
CREATE SESSION

The steps so far are the same as with 12.1. My databases are single instances running on the same Linux server without ASM.

SQL> connect sys/oracle_4U@cdb2 as sysdba

SQL> host mkdir /u01/app/oracle/oradata/pdb2
SQL> create pluggable database pdb2 from pdb1@clone_link file_name_convert=('pdb1','pdb2')
     refresh mode manual;  

Pluggable database created.

SQL> alter pluggable database pdb2 open read only;

Pluggable database altered.

The source pdb1 remains OPEN READ WRITE all the time during the demo. Now I change something there and refresh pdb2 afterwards:

SQL> connect adam/adam@pdb2
Connected.
SQL> select count(*) from sales;

  COUNT(*)
----------
    748999

SQL> connect adam/adam@pdb1
Connected.
SQL> delete from sales where rownum<1000; 

999 rows deleted. 

SQL> commit;

Commit complete.

SQL> select count(*) from sales;

  COUNT(*)
----------
    748000

SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

Notice that you need to perform the refresh from inside the cloned PDB, else you get this error message:

SQL> alter pluggable database pdb2 refresh;
alter pluggable database pdb2 refresh
*
ERROR at line 1:
ORA-65118: operation affecting a pluggable database cannot be performed from
another pluggable database

So I connect into the cloned PDB to do the refresh there:

SQL> connect sys/oracle_4U@pdb2 as sysdba
Connected.
SQL> alter pluggable database refresh;

Pluggable database altered.

SQL> alter pluggable database open read only;

Pluggable database altered.

SQL> select count(*) from adam.sales;

  COUNT(*)
----------
    748000

Fortunately, you are not allowed to open this cloned PDB in normal READ WRITE mode because of the REFRESH clause added to the CREATE PLUGGABLE DATABASE command:

SQL> shutdown immediate
Pluggable Database closed.
SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> alter pluggable database pdb2 open;
alter pluggable database pdb2 open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode

Another possible implementation would have been to go to OPEN READ ONLY silently like with a Physical Standby. Don’t believe it, test it!</p />
</p></div>

    	  	<div class=

Oracle Database Cloud (DBaaS) Performance - Part 1 - CPU

After having looked at the performance consistency provided by the Oracle Database Cloud offering in the previous series, I'll focus here on the raw performance figures I've measured during my tests, starting with the CPU related performance findings.

One of the first surprises is related to the fact that Oracle uses a unit called "OCPU" to specify the CPU capacity provided, which is explained here:

So one "OCPU" is supposed to represent one core with two Hyperthreading CPU threads, and hence should correspond for example to two VCPUs used as unit by Amazon.

But when looking at the actual CPU configuration of such a DBaaS VM, I got the following results for a 4 OCPU configuration:

So, that 4 OCPU configuration provides 8 CPUs, which is expected, but it provides those 8 CPUs with one thread per core, so that means 8 cores.

This is what I get when I configure a corresponding Amazon EC2 VM with 8 VCPUs (m4.2xlarge), which should be same as the Amazon RDS "db.m4.2xlarge" configuration (but I can't access a RDS instance on O/S level, hence the EC2 fallback):

Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                8
On-line CPU(s) list:   0-7
Thread(s) per core:    2
Core(s) per socket:    4
 

Socket(s):             1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 79
Model name:            Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHz
Stepping:              1
CPU MHz:               2300.062
BogoMIPS:              4600.12
Hypervisor vendor:     Xen
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              46080K
NUMA node0 CPU(s):     0-7


So this more in line to what is expected - 4 cores providing 8 CPU threads.

Does it make a difference in terms of actual performance? It does - when running my CPU tests, both the PL/SQL as well as the SQL engine based tests (see the previous "performance consistency" series for more details about the tests) show the following average duration per iteration per thread when running with 4 vs. 8 threads:

Oracle DBaaS:

 Amazon RDS:

So, using 8 threads instead of 4 threads only increases the duration of a test iteration slightly in the Oracle DBaaS 4 OCPU environment, whereas the Amazon RDS 8 VCPU environment significantly slows down, even more than expected considering Hyperthreading effects - in particular the SQL Logical I/O test requires more than twice of the time at 8 threads, but the PL/SQL based test, too, significantly slows down. It's interesting to see that running the SQL Logical I/O test at 4 threads the Amazon RDS environment outperforms the Oracle DBaaS offering to an unexpected degree.

Whether the better Amazon RDS SQL Logical I/O performance at 4 threads comes from the larger cache sizes reported by "lscpu" I don't know. I also don't know why Oracle provides more cores than outlined in their own product description. Maybe this should avoid exactly the effects seen with the Amazon RDS environment - maybe Hyperthreading doesn't work that well in virtualized environments - but that is just my speculation. Whether Oracle will keep this different approach in future I don't know either.

All I can say is that I consistently got that CPU / core ratio when configuring several services using a different number of OCPUs and that the my performance tests showed the difference outlined above when comparing the Oracle DBaaS and Amazon RDS environments.

Trusted Information Sharing – Some Underlying Concepts

In a recent post, I explained a little bit about what my new role at archTIS is. archTIS is a company that focuses on the area of Trusted Information Sharing. Trusted Information Sharing is a concept that not too many people would understand the complexities of. In fact, when I first started in my new role I wasn’t aware of just how complex it was myself! To explain all that complexity in a single post would make for an incredibly long post, so what I’m going to do is explain it in a series of blog posts. Doing so will help you (the reader) understand this more readily, and assist me in checking my level of understanding as well. So let’s start off at the very basic level of some of the underlying concepts of Trusted Information Sharing (hereafter referred to as TIS) and why we need to have it. Note that this post is entirely generic – it’s not restricted to Oracle technology is any way, shape or form, so if you’re after that you can stop reading now and come back to later posts in this series. </p />
</p></div>

    	  	<div class=

Happy New YouTube Channel!

As announced, I start into 2017 with my own YouTube channel. You can access it at the right upper part of this site by clicking on the red icon:

youtube_channelhttps://uhesse.files.wordpress.com/2017/01/youtube_channel.png?w=132&h=150 132w, https://uhesse.files.wordpress.com/2017/01/youtube_channel.png?w=264&h=300 264w, https://uhesse.files.wordpress.com/2017/01/youtube_channel.png?w=768&h=873 768w, https://uhesse.files.wordpress.com/2017/01/youtube_channel.png?w=901&h=1024 901w, https://uhesse.files.wordpress.com/2017/01/youtube_channel.png 1123w" sizes="(max-width: 620px) 100vw, 620px" />

Do it, by the way</p />
</p></div>

    	  	<div class=

Removing Unnecessary Indexes: 1. The Need for Extended Statistics

This is the first post in a series about unnecessary indexes and some of the challenges that they present 

I have always noted that it is remarkably easy to add indexes to systems, especially OLTP systems. However, removing them is generally more difficult.
The reward for removing an unused, or at least an unnecessary, index is that you no longer need to maintain it during DML operations, thus reducing I/O and not loading index blocks into the buffer cache. However, the risk is that performance will degrade somewhere, because an execution plan changes.
I will start by looking at a class of indexes that are easily identified as candidates for removal. If the columns in a non-unique index are the same as the leading columns in another index then it is reasonable to remove it because any query that uses it could also use the other index. We cannot drop unique indexes because they are need to enforce a unique or primary key constraint.
However, there is a catch. A multi-column index might be used by the optimizer to provide the number of distinct keys without being referenced in the execution plan. Here is a simple example.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE TABLE t 
(a NUMBER
,b NUMBER
,c NUMBER
,d NUMBER
,CONSTRAINT t_pk PRIMARY KEY (a)
);

CREATE INDEX t_ab ON t (a,b);
CREATE INDEX t_bc ON t (b,c);
CREATE INDEX t_bcd ON t (b,c,d);
  • T_BC is a subset of T_BCD, so it is redundant and could be dropped.
  • (correction 7.1.2017) The primary key index T_PK is a subset of T_AB.  It is also redundant because the primary key constraint, on A alone, could be altered to use index T_AB.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE t MODIFY PRIMARY KEY USING INDEX t_ab;

However, I am going to leave the default primary key index in place for the rest of this demonstration.  I will now populate the table, and gather statistics but not histograms.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">INSERT /*+APPEND*/ INTO t
    WITH x AS (
    SELECT rownum-1 n FROM DUAL connect by level <= 1E5)
    SELECT n
    , MOD(n,100)
    , ROUND(MOD(n,100),-1)
    , dbms_random.value(1,100)
    FROM x;
    EXEC dbms_stats.gather_table_stats(null,'T',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1');

    The table has 100,000 rows, column B has 100 distinct, and column C has 11 distinct values. But there is a close correlation between them. The value of C is the value of B rounded off to the nearest 10.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">column column_name format a20
    SELECT column_name, num_distinct, num_buckets, num_nulls, histogram
    FROM user_tab_col_statistics
    WHERE table_name = 'T'
    ORDER BY 1
    /

    COLUMN_NAME NUM_DISTINCT NUM_BUCKETS NUM_NULLS HISTOGRAM
    -------------------- ------------ ----------- ---------- ---------------
    A 100000 1 0 NONE
    B 100 1 0 NONE
    C 11 1 0 NONE
    D 100000 1 0 NONE

    We can also see that index T_BC has 100 distinct keys.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT index_name, uniqueness, visibility, distinct_keys, clustering_factor
    FROM user_indexes WHERE table_name = 'T'
    /

    INDEX_NAME UNIQUENES VISIBILIT DISTINCT_KEYS CLUSTERING_FACTOR
    ---------- --------- --------- ------------- -----------------
    T_PK UNIQUE VISIBLE 100000 525
    T_AB NONUNIQUE VISIBLE 100000 525
    T_BC NONUNIQUE VISIBLE 100 52500
    T_BCD NONUNIQUE VISIBLE 100000 99894

    Let's look at the execution plans for some simple queries: This first query uses index T_BC because. It doesn't even need to look up the table because column A is not null.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT COUNT(A) FROM t WHERE b = 42 AND c = 40          

    Plan hash value: 2931606879
    --------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
    --------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 5 |
    | 1 | SORT AGGREGATE | | 1 | 1 | 6 | | | 1 |00:00:00.01 | 5 |
    |* 2 | INDEX RANGE SCAN| T_BC | 1 | 1000 | 6000 | 3 (0)| 00:00:01 | 1000 |00:00:00.01 | 5 |
    --------------------------------------------------------------------------------------------------------------------

    This query references column D, which is nullable, so it uses index T_BCD.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT COUNT(D) FROM t WHERE b = 42 AND c = 40

    Plan hash value: 2099617883
    ---------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
    ---------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 1 |00:00:00.01 | 8 |
    | 1 | SORT AGGREGATE | | 1 | 1 | 28 | | | 1 |00:00:00.01 | 8 |
    |* 2 | INDEX RANGE SCAN| T_BCD | 1 | 1000 | 28000 | 8 (0)| 00:00:01 | 1000 |00:00:00.01 | 8 |
    ---------------------------------------------------------------------------------------------------------------------

    Note that in both cases the optimizer estimated it would receive 1000 rows, and it did.

    Invisible Indexes 

    Rather than drop index T_BC, I am going to make it invisible, and repeat the test. I would always do this in a real production situation before dropping the index later. If removing the index does have undesirable consequences it can instantly be made visible again, whereas rebuilding it may take time and cause contention on a large and active table.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER INDEX t_bc INVISIBLE;

    The execution plan for the first query has changed to use T_BCD (which is perfectly reasonable), but the estimated number of rows is now just 91, not the 1000 that were actually found. Although it didn't make a difference to this execution plan, it is this misestimate which could cause plan regressions in more complex cases.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT COUNT(A) FROM t WHERE b = 42 AND c = 40          

    Plan hash value: 2099617883
    ---------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
    ---------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 8 |
    | 1 | SORT AGGREGATE | | 1 | 1 | 6 | | | 1 |00:00:00.01 | 8 |
    |* 2 | INDEX RANGE SCAN| T_BCD | 1 | 91 | 546 | 3 (0)| 00:00:01 | 1000 |00:00:00.01 | 8 |
    ---------------------------------------------------------------------------------------------------------------------

    The other query produces the same plan, with the same misestimate of the number of rows.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT COUNT(D) FROM t WHERE b = 42 AND c = 40          

    Plan hash value: 2099617883
    ---------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
    ---------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.02 | 8 |
    | 1 | SORT AGGREGATE | | 1 | 1 | 28 | | | 1 |00:00:00.02 | 8 |
    |* 2 | INDEX RANGE SCAN| T_BCD | 1 | 91 | 2548 | 3 (0)| 00:00:01 | 1000 |00:00:00.01 | 8 |
    ---------------------------------------------------------------------------------------------------------------------

    The optimizer misestimates of the number of rows because it can no longer use index T_BC to tell it that there are only 100 distinct values on the table for the combination of columns B and C.

    Extended Statistics

    Now I will create extended statistics on the table

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">select dbms_stats.create_extended_stats(null,'T','(B,C)') from dual;
    EXEC dbms_stats.gather_table_stats(null,'T',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1');

    Or I could just have done this. It comes to the same.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">EXEC dbms_stats.gather_table_stats(null,'T',cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 1 (B,C)');

    The extended statistics on the combination of columns B and C tells the optimizer that there are 100 distinct values for these columns. Note that the extended statistics only have a single bucket.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS  NUM_NULLS HISTOGRAM
    ------------------------------ ------------ ----------- ---------- -----------
    A 100000 1 0 NONE
    B 100 1 0 NONE
    C 11 1 0 NONE
    D 100000 1 0 NONE
    SYS_STU3$58HONF9VK$$69P2OW4P4X 100 1 0 NONE

    SELECT * FROM user_stat_extensions WHERE table_name = 'T'

    TABLE_NAME EXTENSION_NAME EXTENSION CREATO DRO
    ---------- ------------------------------ -------------------- ------ ---
    T SYS_STU3$58HONF9VK$$69P2OW4P4X ("B","C") USER YES

    Now the optimizer reverts to correctly estimating the number of rows as 1000.

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT COUNT(A) FROM t WHERE b = 42 AND c = 40                                                                    

    Plan hash value: 2099617883
    ---------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
    ---------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 1 |00:00:00.01 | 8 |
    | 1 | SORT AGGREGATE | | 1 | 1 | 6 | | | 1 |00:00:00.01 | 8 |
    |* 2 | INDEX RANGE SCAN| T_BCD | 1 | 1000 | 6000 | 8 (0)| 00:00:01 | 1000 |00:00:00.01 | 8 |
    ---------------------------------------------------------------------------------------------------------------------

    #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT COUNT(D) FROM t WHERE b = 42 AND c = 40                                                                    

    Plan hash value: 2099617883
    ---------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
    ---------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 1 |00:00:00.02 | 8 |
    | 1 | SORT AGGREGATE | | 1 | 1 | 28 | | | 1 |00:00:00.02 | 8 |
    |* 2 | INDEX RANGE SCAN| T_BCD | 1 | 1000 | 28000 | 8 (0)| 00:00:01 | 1000 |00:00:00.01 | 8 |
    ---------------------------------------------------------------------------------------------------------------------

    Conclusion

    • If you are going to drop a redundant multi-column index, at least replace it with extended statistics on that combination of columns.
    • If you are going to add columns to an existing index rather than create another, perhaps to satisfy a query without the need to visit the table, then again consider creating extended statistics on the original list of columns.

    Acknowledgement: my thanks to Jonathan Lewis for his blog: Index Usage, and for prompting the correction.

    Happy New Year 2017!

    fireworks-animation

    An interesting year has passed and 2017 awaits us. My best wishes to you and your families – have a great and happy New Year</p />
</p></div>

    	  	<div class=

    "ORA-16047 - DGID mismatch between destination setting and target database" while configuring dataguard.

    While setting up dataguard configuration you may encounter ORA-16047 - DGID mismatch between destination setting and target database.

    From alert.log on primary database:

    ******************************************************************
    LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
    ******************************************************************
    Sat Dec 31 08:29:30 2016
    FAL[server, ARC3]: Error 16047 creating remote archivelog file 'sauxmes'
    FAL[server, ARC3]: FAL archive failed, see trace file.
    ARCH: FAL archive failed. Archiver continuing
    ORACLE Instance auxmes - Archival Error. Archiver continuing.
    Error 16047 for archive log file 2 to 'sauxmes'
    Errors in file /u01/app/xxxxx/diag/rdbms/auxmes/auxmes/trace/auxmes_nsa2_9525.trc:
    ORA-16047: DGID mismatch between destination setting and target database
    Sat Dec 31 08:30:12 2016
    PING[ARC1]: Heartbeat failed to connect to standby 'sauxmes'. Error is 16047.
    Sat Dec 31 08:33:39 2016
    PING[ARC1]: Heartbeat failed to connect to standby 'sauxmes'. Error is 16047.
    Sat Dec 31 08:37:12 2016
    PING[ARC1]: Heartbeat failed to connect to standby 'sauxmes'. Error is 16047.

    Solution:
    Check the configuration on STANDBY side in parameter log_archive_config:

    SQL> show parameter log_archive_config  

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config                   string
     

    Most likely it will not be set. Therefore set it to appropriate value like:

    alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(auxmes,sauxmes)';

    This will resolve the problem and the redo transport will start working.

    Another possibility for getting this kind of error is if you didn't set parameter DB_UNIQUE_NAME on standby database (must be different from the name on primary site):

    alter system set DB_UNIQUE_NAME="sauxmes" scope=spfile;  

    and then you need to restart the standby database as this parameter can't be dynamically set.

    When local partitions….aren’t

    Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year Smile.

    Since 11g, I can sleep easy at night by using the INTERVAL partition scheme. Here’s my table

    
    
    SQL> create table t ( x date, y int )
      2  partition by range ( x )
      3  interval ( numtoyminterval(1,'MONTH'))
      4  (
      5    partition p201412 values less than ( date '2015-01-01' )
      6  );
    
    Table created.
    
    SQL> insert into t
      2  select date '2015-01-01'+rownum, rownum
      3  from dual
      4  connect by level <= 330;
    
    330 rows created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select partition_name from user_tab_partitions where table_name = 'T';
    
    PARTITION_NAME
    ----------------------------------------------------------------------------
    P201412
    SYS_P20234
    SYS_P20235
    SYS_P20236
    SYS_P20237
    SYS_P20238
    SYS_P20239
    SYS_P20240
    SYS_P20241
    SYS_P20242
    SYS_P20243
    SYS_P20244
    
    12 rows selected.
    
    

    The problem is … man, do I hate those automatic names Smile. I suppose I can fix them up later, but in the meantime, I’ll create my local index on the table, but I’ll get the names right whilst I do

    
    
    SQL>
    SQL> create index IX on T ( y  )
      2  local
      3  (
      4      partition P201412
      5      partition P201501,
      6      partition P201502,
      7      partition P201503,
      8      partition P201504,
      9      partition P201505,
     10      partition P201506,
     11      partition P201507,
     12      partition P201508,
     13      partition P201509,
     14      partition P201510,
     15      partition P201511
     16  )
     17  ;
    
    Index created.
    
    

    So far so good… I created 12 partitions in my table, and I explicitly listed 12 partitions in my index. But you need to be careful with such an approach, because if you’ve got that script in (say) your source control system, then even though you’ve specified a LOCAL index, you have also (perhaps unknowingly) set of limit of 12 partitions on the index should be re-run that script. So if I was to drop that index and recreate with the unchanged script (or for example, you’ve done a datapump extraction of DDL etc), then you might get yourself into a jam if the table data changes.

    
    
    SQL>
    SQL>
    SQL> drop index ix;
    
    Index dropped.
    
    SQL>
    SQL> insert into t
      2  values (sysdate,2000);
    
    1 row created.
    
    SQL>
    SQL>
    SQL> create index IX on T ( y  )
      2  local
      3  (
      4      partition P201412
      5      partition P201501,
      6      partition P201502,
      7      partition P201503,
      8      partition P201504,
      9      partition P201505,
     10      partition P201506,
     11      partition P201507,
     12      partition P201508,
     13      partition P201509,
     14      partition P201510,
     15      partition P201511
     16  )
     17  ;
    create index IX on T ( y  )
                       *
    ERROR at line 1:
    ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
    
    

    Now my same DDL does not work, because there are 13 table partitions and I only listed 12 index partitions. The easy workaround is not to list the partitions at all.

    
    SQL>
    SQL> create index IX on T ( y  )
      2  local ;
    
    Index created.
    
    SQL>
    SQL>
    

    and perhaps have an renaming routine that can be applied after the fact.