Bobby Durrett's DBA Blog

I haven’t even started on Sam Alapati’s OCP book but I realized today that Matthew Morris’s OCP 12c upgrade book has an outline of the Key DBA Skills section of the OCP exam and that is what I need most.  I was two questions away on that section when I took the test the first time and I need to make sure I’m studying the right things.  After I got back to the office after failing my first attempt (a $245 lesson) I made notes about what I remember being unsure of and that gave me some ideas what to study.  But, it is nice to get some advice from someone else who has passed the exam.  I saved a couple of bucks by getting the Kindle version for $9.99 so it seemed like a safe bet.  Hopefully I can hit it hard on both books and my practice test software, which also covers the key DBA skills, and be able to take the test again by the end of the August.

I think I’m done with the script for my Toastmaster’s talk: pdf

It’s Toastmaster’s talk 3 “Get to the Point” so I’m trying to do a 5-7 minute talk that focuses on how Oracle’s SQL optimizer sometimes chooses a slow plan.  It’s tough to say anything meaningful in 7 minutes and I’m not using any kind of slides so I have to describe everything verbally.

But, it’s a topic I’m passionate about so I will enjoy giving it.  I kind of got bogged down thinking about ways to relate this information to my non-technical audience’s life and I decided to give up on that.  I would probably need another 7 minutes or more to explain why they should care that there are limits to SQL optimization so I decided to focus on convincing them that the limits exist.

– Bobby

I just finished working with a coworker on speeding up a PS/Query.  It was running well in a development environment but not in production.  I tried to find a simple hint or two to make production run like development but didn’t have any luck.  Then I remembered that my script to get a query’s plan prints out a full set of outline hints like this:

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$E903463E")
      USE_NL(@"SEL$E903463E" "CLS"@"SEL$4")
      USE_NL(@"SEL$E903463E" "SEC"@"SEL$3")
      USE_NL(@"SEL$E903463E" "SOC"@"SEL$4")

... lines removed to make this post shorter ...

      OUTLINE_LEAF(@"SEL$F5BB74E1")
      PUSH_PRED(@"SEL$F5BB74E1" "A1"@"SEL$1" 1)
      OUTLINE_LEAF(@"SEL$E903463E")
      ALL_ROWS
      OPT_PARAM('_unnest_subquery' 'false')
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

A coworker edited the PS/Query and tried to add this hint but got errors because of the quotation marks.  So, I simplified the hint by removing all the double quotes and taking out the lines with single quotes because I knew they weren’t needed.  They only related to parameters that I knew were already set in production.

Here is the new quote-less hint:

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@SEL$E903463E)
      USE_NL(@SEL$E903463E CLS@SEL$4)
      USE_NL(@SEL$E903463E SEC@SEL$3)
      USE_NL(@SEL$E903463E SOC@SEL$4)

... lines removed to make this post shorter ...

      OUTLINE_LEAF(@SEL$F5BB74E1)
      PUSH_PRED(@SEL$F5BB74E1 A1@SEL$1 1)
      OUTLINE_LEAF(@SEL$E903463E)
      ALL_ROWS
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

We ran the modified PS/Query in production and I verified that the query was running the correct plan.  The only weird thing was that because the query has a group by PS/Query stuck the hint in the group by clause and in the select clause.

SELECT /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@SEL$E90346...

... middle of select statement ...

GROUP BY /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@SEL$E90346...

... end of select statement ...

At first this put us off, but really the hint in the group by clause is just a comment and syntactically is insignificant even though it is ugly.

By the way, here are the options I used to output the outline hint:

select * from 
table(dbms_xplan.display('PLAN_TABLE',NULL,'ADVANCED'));

I get all my plans this way now.  It generates a lot of output that I don’t usually use.  But, seeing it reminds you that it is there if you need it.

– Bobby

I think I’ve run across a bug in 12c where v$sql_cs_statistics is not populated in 12c as it was in 11.2.0.3.

I’ve messed with adaptive cursor sharing using an example on Tim Hall’s web site: web example

I’ve tried to break it and change it in various ways to understand adaptive cursor sharing.  I was using some 11g databases and on those systems the example results in output from v$sql_cs_statistics.  But in 12c the view returns nothing.  Maybe this is normal but it seems bug like to me.

Here is a zip of my version of Tim’s example and it’s output on an 11.2.0.3 and 12.1 database: zip

In the working 11.2.0.3 example you have this output from v$sql_cs_statistics:

ORCL::SYSTEM>SELECT * FROM v$sql_cs_statistics WHERE sql_id = '9bmm6cmwa8saf';

ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
0000000089035390 4171522382 9bmm6cmwa8saf            1          2064090006 Y          1          50001         501          0
0000000089035390 4171522382 9bmm6cmwa8saf            0          2342552567 Y          1              3           3          0

On 12c you get nothing:

ORCL:CDB$ROOT:SYSTEM>SELECT * FROM v$sql_cs_statistics WHERE sql_id = '9bmm6cmwa8saf';

no rows selected

The other queries in the example all return results so I guess it is a bug.

– Bobby

I guess I should have waited around a bit longer before taking my 12c OCP upgrade exam.  I purchased Kaplan’s Self Test software to prepare for the 12c OCP upgrade exam but it only included 50 questions and they only covered the first part of the OCP exam – the part I passed!

I noticed on the Self Test website that the test now includes 105 questions.  So, I got the bright idea of seeing if there was some way to refresh my software with an update and voilà! Now I have 105 questions and 128 flash cards.  Probably I should have waited for this practice software to mature before attempting the 12c OCP upgrade test.  One thing of particular interest to me is that it does include practice questions related to the second part of the exam which I failed.

So, hopefully I’ll be in good shape for the retest.  I am doing a lot of hacking around with different features but I need to figure out a more methodical way to study.  Still, having taken the test once I know some of the features that I need to study.  Combine that with the new questions on the Self Test software and that will get me closer.  Lastly, I pre-ordered Sam Alapati’s book on Amazon so that should put me over the top.  I was really only two questions away on my first attempt but I definitely should have waited and have done more thorough preparation.

– Bobby

I’m doing Toastmasters to try to improve my speaking and my third talk will be related to why a function on a web site can suddenly slow down due to a change in plan.  It has to be 5 to 7 minutes long and the audience is non-technical.

So, just for fun, here is my first draft.  If you feel like giving me any suggestions that would be great.

First draft of Toastmasters Sudden SQL Slowness talk

– Bobby

Over the weekend a coworker applied a SQL profile to a long running SQL statement but it looked like the profile was not applied.  For one thing, the SQL statement ran just as long after applying the profile.  Also, the hash value for the plan that the optimizer chose for the statement after applying the SQL Profile was not the same as the desired plan.  But, after looking at it today I’ve realized that the SQL Profile did apply the desired plan.  It was just that the desired plan wasn’t really any more efficient than the one picked by the optimizer.

Here are earlier posts related to our use of SQLT to force a given SQL statement to run with a good plan:

Using coe_xfr_sql_profile.sql to force a good plan

SQLT Emergency Plan Change

In this weekend’s case we were looking at sql_id fpa2sb9pt9d6s.  It looked like plan 1394068771 was faster than the most recently used plan because it had a lower average elapsed time:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
fpa2sb9pt9d6s      1615990741 11-MAY-14 11.00.06.474 AM                1         21550951.2        9720630    467066.276                  0                  27.24              82825.793           126238536            1913606                      0
fpa2sb9pt9d6s      1394068771 18-MAY-14 12.00.52.752 PM                1         1687493.09         657760    402607.781                  0                 39.814               36561.61             4701985             566205                      0
fpa2sb9pt9d6s      1611789738 25-MAY-14 10.00.59.900 AM                1         11390629.8        6835390    462073.968                  0                 32.818              39010.945            86448768             993957                      0

So we used SQLT to force fpa2sb9pt9d6s to use plan 1394068771 but instead it appeared to use plan 420390762 and it didn’t run any faster:

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
fpa2sb9pt9d6s      1615990741 11-MAY-14 11.00.06.474 AM                1         21550951.2        9720630    467066.276                  0                  27.24              82825.793           126238536            1913606                      0
fpa2sb9pt9d6s      1394068771 18-MAY-14 12.00.52.752 PM                1         1687493.09         657760    402607.781                  0                 39.814               36561.61             4701985             566205                      0
fpa2sb9pt9d6s      1611789738 25-MAY-14 10.00.59.900 AM                1         11390629.8        6835390    462073.968                  0                 32.818              39010.945            86448768             993957                      0
fpa2sb9pt9d6s       420390762 25-MAY-14 11.00.27.175 PM                1         19055402.5        8975130    492406.429                  0                  24.93              64279.331           120027479            1943256                      0

But if you look at the plan for fpa2sb9pt9d6s that has hash value 420390762 it says it is using the sql profile we created which should result in a plan with hash value 1394068771:

Note
-----
   - SQL profile "coe_fpa2sb9pt9d6s_1394068771" used for this statement

So, I dumped out both plans – 420390762 and 1394068771 and edited out the columns from the plan output that were irrelevant and did a diff and came up with this result:

45c45
< |  41 |                          TABLE ACCESS FULL      | SYS_TEMP_0FD9D67B9_9DB13F0D |       |       |  Q2,01 | PCWP |            |
---
> |  41 |                          TABLE ACCESS FULL      | SYS_TEMP_0FD9D6897_9DB13F0D |       |       |  Q2,01 | PCWP |            |

420390762 and 1394068771 were identical except that the system generated temporary segment name was slightly different.  The temp segment name affects the hash value for the plan so this is a gotcha on using plan hash value to compare plans.  Two distinct plan hash values could really represent the same plan if they have system generated temporary segments in the plan.

So, SQLT did successfully apply a given plan to a given sql_id using a SQL Profile but it didn’t improve performance and the resulting plan hash value was not what we expected.

– Bobby

Here are my rough screenshots from an install of 12c grid on a standalone Linux vm: pdf

I’m running version 6 of Oracle’s Linux in a VMWare Player 4 gigabyte virtual machine on my Windows 7 laptop.  I ran into a couple of key points that I wanted to document.

I added two new disks to my VM but after booting my machine they disappeared.  I ended up adding them back in and rebooting again and they stayed.  Not sure what to say except after you add them reboot and make sure they are still there.

The new disk devices showed up as /dev/sdb and /dev/sdc.  Then I had to run fdisk on each one to create a partition that took up the entire disk.  That led to devices being out there called /dev/sdb1 and /dev/sdc1.

Next I had to do some steps related to the oracleasm utility but I found out later that the next step I should have done was disable SE Linux:

I had to edit the file /etc/selinux/config so that SELINUX=disabled and reboot.  Then I ran these commands to configure oracleasm:

yum install oracleasm-support
/usr/sbin/oracleasm configure -i
/usr/sbin/oracleasm init
/usr/sbin/oracleasm createdisk DISK1 /dev/sdb1
/usr/sbin/oracleasm createdisk DISK2 /dev/sdc1

The next tricky thing was figuring out the path to these two disks during the creation of the disk group as part of the grid install.  Here is what the install screen looked like:

It didn’t have any disks to choose from.  I tried various discovery paths such as /dev/sd* and ORCL:* but finally found one that worked:

Now the disks showed up and I was able to continue:

So, that’s all I wanted to point out, just a few key things I had to understand.  You can check out my link for all the screenshots I took.

– Bobby

I’ve been learning about online table redefinition in Oracle 12c.  Here is a zip of some test scripts I built for my own understanding: zip

I spent some time trying out DBMS_REDEFINITION on an 11.2 Exadata test system and then started messing with it on 12c in a generic Linux VM.  The 12c version of the DBMS_REDEFINITION includes a new procedure called REDEF_TABLE which lets you do in one step certain things you did with multiple calls to the package in 11.2.  This is an online table compress on 12c:

BEGIN
  DBMS_REDEFINITION.REDEF_TABLE(
    uname                      => user,
    tname                      => 'TEST',
    table_compression_type     => 'COMPRESS');
END;
/

Things that can’t be done in one step like this require calls to procedures such as CAN_REDEF_TABLE, START_REDEF_TABLE, REGISTER_DEPENDENT_OBJECT, COPY_TABLE_DEPENDENTS, and FINISH_REDEF_TABLE.  Example online12c5.sql uses all of these.  Here is a summary of each included file’s test:

online12c1.sql – compress table in one step

online12c2.sql – compress table in multiple steps and show that it creates a hidden column without a primary key

online12c3.sql – same as previous test but with primary key

online12c4.sql – copy contraints and indexes

online12c5.sql – change the columns for the non-unique index

online12c6.sql – change order and type of columns

– Bobby

Quick post.  I’m messing with 12c again.  I finished recreating my cdb but this time found the option to create multiple pdbs on the initial creation.  I’m using DBCA.

PDF with screenshots of me creating a cdb with two pdbs

– Bobby