Who's online

There are currently 0 users and 35 guests online.

Recent comments

Oakies Blog Aggregator

Table Duplication

I’ve probably seen a transformation like the following before and I may even have written about it (though if I have I can’t the article), but since it surprised me when I was experimenting with a little problem a few days ago I thought I’d pass it on as an example of how sophisticated the optimizer can be with query transformation.  I’ll be talking about the actual problem that I was working on in a later post so I won’t give you the table and data definitions in this post, I’ll just show some SQL and its plan:

        tr.item, tr.trans_date, tr.quantity
    , pr.gross_price
    , pr.net_price
    , pr.special_price
        xo_stock_trans tr,
        xo_prices pr
        tr.trans_date between '01-AUG-2014' and '3-AUG-2014'
and     pr.item = tr.item
and     pr.price_date = (
                        xo_prices pr2
                where   pr2.item = tr.item
                and     pr2.price_date <= tr.trans_date

The code is a fairly standard expression of “find me the most recent price available for each stock item as at the stock date of that item”. As you can see I’ve referenced the stock table once and the pricing table twice – the second appearance being in a “max()” correlated subquery. Oracle has decided to unnest the subquery – but spot the interesting detail in the plan:

| Id  | Operation                  | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT           |                |    14M|   748M|       | 77955  (84)| 00:06:30 |
|*  1 |  HASH JOIN                 |                |    14M|   748M|    37M| 77955  (84)| 00:06:30 |
|*  2 |   HASH JOIN                |                |   829K|    28M|       | 70867  (92)| 00:05:55 |
|   3 |    JOIN FILTER CREATE      | :BF0000        | 25274 |   370K|       |  2530  (13)| 00:00:13 |
|*  4 |     TABLE ACCESS FULL      | XO_STOCK_TRANS | 25274 |   370K|       |  2530  (13)| 00:00:13 |
|   5 |    VIEW                    | VW_SQ_1        |   210M|  4206M|       | 64135  (94)| 00:05:21 |
|   6 |     HASH GROUP BY          |                |   210M|  5408M|       | 64135  (94)| 00:05:21 |
|   7 |      JOIN FILTER USE       | :BF0000        |   210M|  5408M|       | 11807  (67)| 00:01:00 |
|*  8 |       HASH JOIN            |                |   210M|  5408M|       | 11807  (67)| 00:01:00 |
|*  9 |        TABLE ACCESS FULL   | XO_STOCK_TRANS | 25274 |   370K|       |  2530  (13)| 00:00:13 |
|* 10 |        INDEX FAST FULL SCAN| XO_PRICES_IX1  |  3918K|    44M|       |  1936  (10)| 00:00:10 |
|  11 |   TABLE ACCESS FULL        | XO_PRICES      |  4571K|    87M|       |  2275  (11)| 00:00:12 |

Predicate Information (identified by operation id):
   1 - access("PR"."ITEM"="TR"."ITEM" AND "PR"."PRICE_DATE"="MAX(PR2.PRICE_DATE)")
   2 - access("ITEM_1"=ROWID)
   4 - filter("TR"."TRANS_DATE">=TO_DATE(' 2014-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "TR"."TRANS_DATE"<=TO_DATE(' 2014-08-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   8 - access("PR2"."ITEM"="TR"."ITEM")
   9 - filter("TR"."TRANS_DATE">=TO_DATE(' 2014-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "TR"."TRANS_DATE"<=TO_DATE(' 2014-08-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  10 - filter("PR2"."PRICE_DATE"<=TO_DATE(' 2014-08-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

I was running at the time, which is why we can get a serial Bloom filter on the hash join – and it’s interesting to see that the filter has been pushed inside the view operator; but the really interesting part of the plan is the second appearance of the XO_STOCK_TRANS table.

My correlated subquery returns a value that is used in a comparison with a column in the XO_PRICES table, but the correlation predicates referred back to the XO_STOCK_TRANS table, so the optimizer has added the XO_STOCK_TRANS to the subquery as it unnested it.

I’ve written several examples of how we can optimise SQL manually by rewriting it to introduce extra copies of some of the tables (typically in a fashion analogous to the optimizer’s mechanism for star transformations), so it’s nice to see another variation on the theme of the optimizer using table duplication to optimise a statement.


The execution plan in is slightly different, but it still unnests the subquery, introducing a second occurrence of XO_STOCK_TRANS as it does so.

Slow external table access

We had an interesting issue on, where users were reporting very slow performance on queries to external tables. When I tried to replicate the problem, everything seemed just fine, so I initially reported back the familiar "Well, it works on my PC" :-) [Just kidding]

Anyway, connecting by proxy to one of their accounts, did reveal the error, which suggested something to do with privileges.  A sql trace revealed that the performance was due to a query which appears to get the list of directories and their privileges:


the definition of which was:

create or replace force view sys.loader_dir_objs
(name, path, read, write, execute)
bequeath definer
from   sys.obj$ o
      ,sys.x$dir d
where  o.obj# = d.obj#
and    (o.owner# = uid
or      exists
          (select null
           from   v$enabledprivs
           where  priv_number in (-177
union all
      ,decode(sum(decode(oa.privilege#, 17, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 18, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 12, 1, 0)), 0, 'FALSE', 'TRUE')
from   sys.obj$ o
      ,sys.x$dir d
      ,sys.objauth$ oa
where  o.obj# = d.obj#
and    oa.obj# = o.obj#
and    oa.privilege# in (12
and    oa.grantee# in (select kzsrorol
                       from   x$kzsro)
and    not (o.owner# = uid
or          exists
              (select null
               from   v$enabledprivs
               where  priv_number in (-177
group by

Re-gathering dictionary and fixed object stats yielded no benefit, so I tinkered with the view definition to come up with a faster version, which was this:

create or replace force view sys.loader_dir_objs
(name, path, read, write, execute)
bequeath definer
from   sys.obj$ o
      ,sys.x$dir d
where  o.obj# = d.obj#
and    (o.owner# = uid
or      exists
          (select null
           from   v$enabledprivs
           where  priv_number in (-177
union all
select /*+ leading(d o oa) */
      ,decode(sum(decode(oa.privilege#, 17, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 18, 1, 0)), 0, 'FALSE', 'TRUE')
      ,decode(sum(decode(oa.privilege#, 12, 1, 0)), 0, 'FALSE', 'TRUE')
from   sys.obj$ o
      ,sys.x$dir d
      ,sys.objauth$ oa
where  o.obj# = d.obj#
and    oa.obj# = o.obj#
and    oa.privilege# in (12
and    oa.grantee# in (select kzsrorol
                       from   x$kzsro)
and    not (o.owner# = uid
or          exists
              (select null
               from   v$enabledprivs
               where  priv_number in (-177
group by

[Note: If you’re having the same issue, before you race out as use the same hints, then be aware that the hints above work for us because the number of directories in our database is very small.  If you’ve got lots of directories defined, this might not be the best approach for you]

The challenge now was getting this performance benefit without actually hacking the dictionary view (which means calls to MOS..and well…that’s no way to be starting the new year :-))

So initially, I used the standard technique of applying a baseline, but encountered some dramas (more on that in a later post).  Then, following a suggestion from an OakTable colleague ( I ventured down the path of "sql patch":

I put my ‘corrected’ view in place, issued the problem query, and got the full plan using the DBMS_XPLAN with the +OUTLINE parameter.  I put the original version of the view back in place, and then attempted to patch in the altered plan as thus:

  l_sql  clob;
  l_hint clob :=
'[the full text of the plan outline]';
  -- '23ka1fq59wg0b' is the sqlid for my problem query.  
  select sql_text into l_sql from v$sql where sql_id = '23ka1fq59wg0b';


This failed with a PL/SQL error, because the hint text for sql patch is limited to 500 characters.  So then it was a case of stripping out hints that were "redundant" [Note: This is generally a bad idea, because there’s a strong argument to be made that no hint is redundant].  But once within 500 chars, the patch was applied, and external table performance is now fine for all users.

2014 Year in Review

As many bloggers and sites do this time of the year, here is my review of 2014. It was a great year and it was a lot of fun, as well as educational reviewing all the data.

DBAKevlar Blog

Busiest Day on my Blog:


Posts this year:


Most popular post of 2014:  Easy EM12c Agent Deployment on Windows


Windows installations are still a huge mystery and Cygwin still frustrates a lot of people, sometimes, me included.  This post describing the easiest deployment method has continued to be my most popular post.  I admit that I’m not too thrilled that it is attained via a search engine after typing in “DBAKevlar + Easy” though… :)


If you aren’t familiar with the term, it is when someone links to one site to another.  Right now, I believe Pete Sharman owes me a beer, (or a few) for the 100’s of redirects to his site my blog generates… :)


Most individual blogger pingback: Brian Pardy. In the last 90 days, he’s referred others to my site 146 times, second is Jeff Smith with 51 times and Bobby Curtis with 46. Nice going Brian-  Could you give Pete some lessons? :D


Obviously I’ve missed my calling to be a reviewer of tech products, which can be seen by search engine overload:


I’ve known for some time that there isn’t enough data to market smartwatches to women, which is proven by the searches that bring people to my blog.  Figure it out, tech wearables… :)


This year I spoke at 11 conferences.

I did 4 joint-keynotes with my husband, Tim Gorman and an Empowerment keynote for NWOUG, for a total of FIVE keynotes in 2014.

I lead 5 Women in Technology Panels and picked up 12 new individuals to mentor from those events.  I am very impressed with all their contributions to technology and their growth in the industry!

Two Social Media sessions-  teaching folks how to use Social Media instead of the same old discussion of “you should be using”.  Looking forward to the RMOUG 2015 session with Jeff Smith coming up in February!

An IOUG Master Class at Coors Field!  This was a great event and I thoroughly enjoyed this!  Great group, great co-speakers.  It was well planned and well attended, not to mention enjoying the game after the event!

Webinar with ODTUG on the AWR Warehouse.


I’ve uploaded 19 slide decks to Slideshare.  Enterprise Manager, ASH/AWR and Women in Technology is the topic in focus and although a slide deck is a poor excuse vs. seeing a presentation, there is still a good amount of valuable data in each of the uploaded presentations.



The Enterprise Manager 12c Command Line Interface book from Apress was released!


Oracle Magazine: Making a Change

NoCOUG: Women in Tech

Oracle Scene: Database as a Service in a DBA’s World

IOUG Member Spotlight

IOUG Ask an Oracle ACE

RMOUG:  Social Media for the Techie

IOUG Select Magazine: New Features in EM12c Release 4, (with Pete Sharman)

Denver Business Journal: Not Playing it Safe

UKOUG Women in Tech Initiative

Denver Post:  TechKnowsByte

O’Reilly Press, Thanks to Steven Feuerstein:  Celebrating Ada Lovelace Day


There were a few awards through out the year-

2014 Volunteer of the Year award for RMOUG.

November 2014 Oracle Pro from Dell/Toad

The big one of course was being recognized by the Colorado Technical Association as their Women in Technology APEX Award winner for 2014.  I was in no way prepared for this, as I was sure another finalist was the definitely the one they would call on stage.  I’m told I gave a great acceptance speech, so if anyone does have it on tape, I’d love to know what I said up there.  All I remember was trying to keep my legs from shaking up at the podium… :)


Its been a pretty big year personally, too.  My oldest son, Sam moved out of the house and is on his own.  He just turned 20 and it’s difficult to believe that my oldest child was born two decades ago.  His sister Cait has recently dyed her hair a lovely shade of purple and is finishing up her senior year in high school.  My baby, Josh is now a freshman in high school and just found out that those baby blues of his will need glasses.

The biggest news of the year is that my best friend and partner in this world, Tim Gorman and I got married October 5th.  After happily traveling around the world together, working on conferences, user groups and in similar technical arenas, we are now recognized by the government as partnered, too… :)

2015 Goals

Oracle Enterprise Manager Webinars-  This has been on hold way too long and I need to get them started.  Monthly webinars on EM12c topics and interviews!

RMOUG Training Days 2015-  Yeah, I’m the Conference Director again this year and it’s shaping up to be an AMAZING conference.  I took some time to talk about some of the new items on the 2015 agenda in this post, but keep an eye out, there’s more to come!

Speaking-  I’m currently set to speak at HotSos Sym 2015, OUGN, RMOUG, IOUG Collaborate, GLOC and?  Is your conference on my list?  Let me know if I need to submit an abstract and I would love to visit a few new locations this year to spread the word about Enterprise Manager, Women in Technology and other great Oracle topics!

Another Book?  I think I owe Leighton Nelson an apology, as I still haven’t started to collect myself on a book topic he wants us to start working on….sigh…

STEM and Women Empowerment Initiatives-  I will be working with CTA and powerful women in the industry to empower and inspire those around us to build their future in technology.

Articles-  I’m in the midst of writing an article on the AWR Warehouse now and will be writing a lot of articles for 2015.  I look forward to showing folks how much the Enterprise Manager 12c product can do and how much they have to look forward to in upcoming releases!





Well, it’s been a blast of a year and to close, I wish everyone a Happy New Year and a great 2015!


Tags:  ,





Copyright © DBA Kevlar [2014 Year in Review], All Right Reserved. 2015. in 2014 – a Review

Better than ever

2014 brought a new high water mark with 282,000 hits!

Meanwhile, gets over 1,000 hits per day on average.

Who are the referrers?

The vast majority of visitors came to via google searches: 163,000

On a distant second place is Twitter: 2,500 visitors came from there

Facebook and LinkedIn each lead 800 visitors to

400 visitors came from – thank you, Jonathan!

Where did refer to?

As a good employee, I sent 1,500 visitors to and 1,300 to :-)

About 600 each went to and, while got 350 visitors

Nationality of the visitors

They came from almost everywhere, the full list contains 200 countries in total – isn’t the internet a great thing?

Vistors of by nationality

Thank you all for visiting – I hope you come back in 2015 again :-)

STEM to STEAM, Andy Savage, and a manipulative huffpost ad that could have been great

Andy Savage gave a powerful and on-target keynote at the Collaborate Conference (Quest, OAUG, and IOUG #C14LV). There is a nice summary at , which I recommend. My favorite bit was point #7 : Art belongs in the same discussions as Science, Technology, Engineering and Mathematics (S.T.E.A.M., not S.T.E.M.).

Andy phrased his entire take, even the bits of irony, on calling for positive action.

This is in contrast to a recent huffpost manipulation:

that insinuates compliments on being pretty imply stupid repression. It cobbles together a fictional account that would make anyone who took even the basic youth sports coach seminar cringe. See it yourself. Now the voiceovers of objection to specific acts of the girl in question were about preventing certain imminent disasters, but were phrased in the worst possible way. It is probably worth watching. Then come back here.

Now as I recall the very first introductory NYSCA seminar video, there was a bit about encouraging positive action, especially in kids. Scene one, dad coaching new bike rider on a sidewalk fringed by hedges: “Don’t ride into the hedges.” Of course the kid looks at the hedge on one side and rides right into it. Scene two: “Ride between the hedges down the center of the sidewalk.” The kid looks at the hedges, then looks at the center of the sidewalk, and rides down the center of the sidewalk to success. Hooray. Oversimplified? Perhaps a bit, but the underlying point is to coach the thing you want to have happen as a positive and as the last words in an instruction.

So what does this have to do with the Huffpost ad? Sigh. Well plenty. So consider the girl in the muddy rivulet wearing a party dress. “Don’t get your dress dirty.” could have been: “Let’s get into our adventure clothes before we climb up that muddy stream.” Another bit has the girl struggling with a power drill awkwardly on what looks like expensive equipment that might be ruined or at least damaged by unskillful drilling. “Be careful! Give that to your brother!” (Who in addition to being male, seems older and skilled at using power drills just by the way he takes the handle.) So how about “Let’s practice some drilling on some scrap so we do a better job on the telescope.” The message still prevents the imminent danger and probable damage, but it re-casts the message from “you can’t do that” to “you surely will be able to do that better with a little practice.”

That sums up my objection to the manipulation of this ad. 1) Its very title implies that a compliment about being pretty must come coupled with offensive other remarks, and 2) That there is no way to prevent bad results without causing psychological restriction on the person restrained from getting dirty in party clothes or getting hurt and damaging expensive equipment.

So rise above this manipulation! Voice restraints and prevent accidents WITH your best thoughts about how to accomplish the desired activity safely and to a good result. If you cannot offer a solution in real time, there is always the generic fallback, something like: “Hang on a second. Let’s figure out how you can do that {safely | effectively | without trashing your pretty dress}. “

And let’s make sure we don’t trash ART while praising Science, Technology, Engineering and Math. Praise being well-rounded and note that excellence in Art is synergistic with excellence in STEM: STEAM! (Thanks Adam). And of course also, let’s get geared up for #C15LV. Happy New Year.

The Right Tools for the Job

I chose to work for Oracle because I wanted to immerse myself in the Oracle Enterprise Manager product.  It was the right place for me.  It didn’t matter that I had a background as a multi-platform DBA or that I my skill set was deeply involved in optimization of complex, large databases.  I knew it was right for me and I trust my instincts.  Due to research and the opportunities preesented to me, I have a great career, great peers and wonderful bosses.  It was the right choice for me and I ignored a number of people who would have me take their advice on assumptions they had about the situation vs. doing research or asking questions or even a more amazing idea, maybe trust my judgement… :)

This also goes for hardware and software.  I am continually surprised how often people, instead of asking questions as to what folks requirements are or what challenges they’ve had with previous equipment or software, just make blind recommendations on what they think is cool or what everyone else tells them is cool.

“You have to have a Mac Pro/Air, anything else is just crap!”

“If you install on anything other than HP-UX, well, you’re just an idiot!”

“I won’t even talk to an MSSQL DBA.  They are just on Windoze!”

No questions as to what someone is trying to accomplish or respect shown to others to find out what they need, what they know or to really get folks the right answer.  This goes for personal technology and hardware.  No respect to folks from other technology areas that are professionals in their own right.  Some are just teasing, but its surprising how many seriously just feel unless they get what everyone else says are cool, they aren’t cool.

I run into this with my own hardware choices.

This is what’s in my personal tech arsenal right now-

  • Asus Windows 15.6 laptop, (for work)
  • Surface Pro 3 (for travel)
  • Mac iPad Air tablet
  • Samsung Note 4 Smartphone
  • Moto 360 Smartwatch

I have had the most ridiculous comments come out of perfectly reasonable and intelligent people’s mouths over all of these products.

“Why wouldn’t you buy a Mac??”

“A Surface Pro 3?  If you didn’t buy a Mac Air, you weren’t thinking…”

“I only use Samsung Tablets.  If you have a Samsung phone, why wouldn’t you have a Samsung Tablet?”  (You can insert the Moto 360 for the Samsung Smartwatch, too… :))

“Why would anyone have a smartwatch?  Stupid technology!”

I use my technology every day, constantly and it has to work for me.  I research products and test them out for an extensive period of time and if something doesn’t work, I learn from my mistakes and take this into consideration on my next purchase.  Even when I sold computers, before entering IT, people would ask me how I made so much money and did so well.  Some, [limited individuals] would tease me and say it was because I was a girl on the sales floor, but even back then, I believed you found out the technology people needed and that’s what you sold them.  If they were satisfied, they came back and/or sent their friends/family to you.

Here’s why I have what I have:

1.  I burn through keyboards.  A laptop keyboard lasts me about 6-9 months-  SERIOUSLY.  I type about 120+ words per minute and I type with a lot of force.

2.  If I build a local demo of OEM, DBaaS and an AWR Warehouse, I can do it on 16G, but I really need 32G to have it run adequately.   I don’t do this very often, but I do need something that can handle it from time to time.

Due to these two requirements, I have the following:

The ASUS with 32G, (Mac did not have a 32G model at the time) was a great deal at $900.  I use it only for work and am using it less these days because the letters are starting to disappear on the keys.  Yeah, beating even this very tough keyboard up.  I gave another great PC, another i7 to my son after 10 months of use.  My son keeps joking about having to use an indellible marker to add the letters back to the keys.

The Surface Pro 3-  It has 16G or memory, 256G SSD with an i5 and honestly, it’s a great little unit that when I burn through a keyboard?  I just BUY A NEW KEYBOARD!  (Nope, can’t do that with a MacAir and if you would like to see what I did to the Asus Zenbook, who’s keyboard is the SAME as the MacAir, I’ll send you a picture of the poor thing… :))  It’s incredibly light, has great bluetooth accessories and travels well, including not having to be removed from my bag at most Airport security queues.

My tablet choice is a Mac iPad Air. It’s a wonderful tablet and I can do most of what I do for work, but there are some aspects that are limited and I can’t fully integrate it with my Oracle tools for work.  I wouldn’t give it up for the world, just as I love my Surface Pro 3, I love my Mac iPad Air tablet.  They are used for different aspects of my day.  If you want to know why I didn’t go Samsung, check out my post on it.  I was highly disappointed with the Samsung Pro.  The tablet’s OS just couldn’t handle 50% of the work tools that Apple’s OS could.

Now for my Samsung Note 4.  I tested out the iPhone 6Plus.  Missed my widgets and really rely on them.  I don’t like Pebble and find the interface for most smartwatches limited in their use vs. what works well for women.  As I’ve said over and over again-  where are women’s smartphones 80% of the time?  They’re in our purse, so for us, a smartwatch needs to be an extension of the phone with clear notifications. The Moto 360 worked with Android and hands down, it was the best watch for me.  I never miss phone calls, always am aware of work emails and important information.  I can also exclude any notifications that aren’t critical to my daily life.

So, where this post ends….

I wonder how far ahead we would be in technology if people stopped recommending what worked for them, (except to give reasons it worked for them, which are much more valuable than, “You need to get !”) and started asking questions and made recommendations off of what fulfilled those recommendations?

How often do those of us who work in optimization and enhancement enter businesses and find the wrong solution implemented because no one asked questions or listened to requirements?

I would love to see some folks stop blindly following what they hear is cool and just do some research and find out the right technology for them.  Don’t just do it, do the research, folks and allow others to get what is right for them.  If Mac Air is right for you, get it.  If a Samsung tablet is perfect for what you are going to do, then get it.  If you are drooling over a new Windows smartphone, get it!  Talk to a lot of people, read a lot of reviews, test out products, but get what you need.  There’s nothing worse than buying the wrong product and then finding out that you can’t do what you needed to accomplish because someone sold you on what was right for them.







Copyright © DBA Kevlar [The Right Tools for the Job], All Right Reserved. 2015.

xfinity customer service chat

MARK_: My Issue: An hour on the phone and three attempts to authorize my replacement equipment PKKVZQSMC ended with a hang-up while supposedly transferring me to a supervisor.

user MARK has entered room

analyst Cherry has entered room

Cherry: Hello MARK_, Thank you for contacting Comcast Live Chat Support. My name is Cherry. Please give me one moment to review your information.

Cherry: Hi! How are you doing today, Mark?

MARK_: read what I wrote

Cherry: I know it has been a roller coaster ride for your Mark. I understand that you have spent hours over the phone to activate the new box.

Cherry: I can only imagine what you have gone thru.

Cherry: No worries, I am here to assist you with your concern.

Cherry: I am now checking if the box is already in the account so I can start sending a signal.

Cherry: While waiting, may I know what do you see on the Led lights in front of the new box and if you are getting any error on your TV screen?

MARK_: I wonder if the problem is the two serial numbers. One is PKKVZQSMC and the other is SAZBFVHPS. The power light is on. The message on my screen is that my EXPLORER unit is not authorized for use.

Cherry: Thank you for providing that information.

Cherry: Nothing to worry about the Serial numbers, one of them is for the card, the other one is for the box.

MARK_: We’re now 2 hours and 17 minutes into simply trying to get this box to work.

Cherry: I understand. Rest assured that we will work on all possible solutions to get the box work.

Cherry: I see here on the account that the box is already added.

Cherry: However, the box is not getting signal from the network.

Cherry: Before I go ahead and send a signal to the box, let us make sure your box is connected tightly

Cherry: Is the box connected to the wall via CABLE IN?

MARK_: That is not correct. Three times the activation and reset has been received. When it gets to about 2:10 to go of the interactive program load, it shuts off and goes back to “not authorized for use”

Cherry: If yes, please make sure the cables are snug and fitted to the ports from wall and CABLE IN port of the box

Cherry: Thank you for letting me know. I will take note of that

Cherry: Are you using any third party device like a splitter, amplifier, power surge/strip that is connected to the cable box?

MARK_: The cables are snug. They are brand new RG6.

Cherry: Thank you.

MARK_: I’m using the splitter the xfinity tech installed, exactly as with the old box that recently froze.

Cherry: I see. Would you mind bypassing the said splitter to isolate where the issue is coming from? Let us see if the splitter affects the signal distribution. We also have to make sure that the connections are perfect before I send a special signal from my end.

MARK_: I am not willing to disrupt my phone and the working cable box to do that.

MARK_: Or my internet service

Cherry: I understand. No need to worry as we will only try this temporarily. If there is no improvement, then connect it the same way it was connected earlier. Yes we will be disconnected, but I will wait for you within 8 minutes to reconnect. We just have to make sure.

MARK_: A) I’m not disconnecting my phone at all

MARK_: B) I’m not disconnecting my internet

MARK_: c) I’m not disconnecting my working cable box.

MARK_: d) Do you think this is a reasonable plan, or is this a practical joke?

MARK_: And I’m NOT worried. I’m out of service and you are suggesting I get more out of service. Get a supervisor.

Cherry: Alright, Not a problem with me if you do not want to do the steps. Let me do it on my end then. Please let me know if there are changes on your new box

MARK_: There is a problem with ME that you suggest turning off my phone, internet, and working cable tv service as a step. That is not acceptable.

Cherry: I am only asking if it is fine. and the step is only for temporary. I am not asking you to turn them off all the way. Sorry for making you feel like that.

Cherry: Hows the box? are there any changes?

MARK_: no changes

Cherry: Thank you.

MARK_: I go look again if you just did something

Cherry: Let me Initialze the box now

Cherry: Thank you.

MARK_: You exactly asked me to bypass the splitter, which would disconnect my other services. If you do not understand that I am very worried.

Cherry: Alright

Cherry: I have sent the initializing signal to activate the box

Cherry: Please check if there are any changes. It should take effect within 2-3 minutes

MARK_: The power led blinked. The remote led came on. The data led came on. Then the power led went steady and resumed blinking. I’ll got back out, but I assume it will be starting the interactive program download again, then that will get to about 2:10 left to go, and it will screw up again. We’ll see.

Cherry: Thanks for the update,

MARK_: so once again the on demand information loaded very quickly and when the interactive program guide got to the 2:10 to go point the unit shut off and once again reports that it is not authorized for use.

MARK_: Based on the load moving at the wall clock speed and the on demand loading very quickly, I’m pretty doggone sure I’ve got a good signal. Something is going wrong.

Cherry: Thank you for updating me.

MARK_: Now get someone who can diagnose this problem.

Cherry: Since the box has reacted but has not stabilized as normally expected, I would like to ask your help to do the refresh manually.

Cherry: Oh, you do not want to continue on troubleshooting?

MARK_: Is this where I disconnect the power for 20 seconds and try again?

Cherry: No. Please turn off your cable box and unplug it from the power outlet. Wait for 60 seconds and plug it back in and turn on the cable box and TV after 3 minutes. Then once the TV is on, please wait another 5 minutes before accessing and pressing anything

Cherry: Guide, On demand and channels should be allowed to load for 5 minutes.

MARK_: OH. LONGER WAITS. SURE. I’ll try that. In the mean time, since you’ll have a lot of it, get some advice from someone else there. Ok. So unplugged for 60 seconds. Then plug in. Then wait 3 minutes, turn cable and tv on. Then wait 5 more minutes. Got it. I’ll be back to your in about 10 minutes. (including the walk)

Cherry: I understand that this has been an inconvenience for you, Mark. We are only trying the best solution to get your box work. But if you have already tried everything on your end and everything fails, then I’ll be more than glad to send out a technician to check on the new box.

Cherry: I am checking on the soonest schedule now

Cherry: May I have the best number to call you for appointment confirmation?

MARK_: I already have a tech coming tomorrow to put in new boxes. This is for family here tonight.

Cherry: I see

MARK_: I’ve already done the 60 second unplugged and we’re 2 minutes into the wait.

Cherry: Thank you.

MARK_: I’m walking back now and I’ll turn both the cable box and tv on at the 3 minute point on my timer

Cherry: Thank you.

Cherry: By the way, upon checking here in the account, the pending order for HD Complete was cancelled. Is this what you ordered for?

MARK_: yes. Who could possibly have cancelled that?

MARK_: by the way. the cable box power was not responsive. when I turned on the tv it reported it was in the process of downloading the interactive guide. Once again, when it got to the 2:10 point it stopped displaying anything. I have not touched anything since.

Cherry: Possibly the sales rep who applied the order since they are the only ones who can access the ordering system.

Cherry: Thank you for updating me, Mark.

MARK_: I’m glad I’m got this transcript.

MARK_: Now we’re at your 5 minute point. Should I try turning it back on?

Cherry: Yes please.

user MARK_ has left room

user MARK has entered room

MARK_: now it is back to your explorer unit is not authorized for use

Cherry: I understand.

Cherry: Thank you for cooperating and trying it with me.

MARK_: now can we get someone who can diagnose the problem?

Cherry: Since we have both tried to do the activation and refresh remotely and manually and the box has not responded effectively, I would like to suggest to send our field technician and personally check on your box

Cherry: Yes. I can send him as soon as 3-5 pm on 28th

Cherry: Shall I proceed>?

MARK_: I fetched the box personally to repair it for tonight

MARK_: We need to get someone on the line now who knows more.

Cherry: We all have the same knowledge and tools to have this issue resolved. The best resolution is for us to send out a technician since this is more than a line issue that we can remotely detect

Cherry: Our technician can personally check on your box to see if there is something wrong with it, He can also double check the connections and inside wiring .

MARK_: I’m verifying the replacement of this box tomorrow with complete service. They are supposed to come between 3 and 5.

Cherry: That is I am not sure of. I am seeing the order here as cancelled. We can only confirm this with the Sales Department.

Cherry: Shall I verify this with the Sales Department over here on chat?

MARK_: I’m calling them. Someone is playing games with me and interfering with my family’s enjoyment of the Christmas holiday.

Cherry: I understand.

MARK_: I do NOT want it cancelled. I did NOT cancel it.

The analyst has left and your issue has been closed.

Waiting for response from Cherry

Cherry: I understand.

Cherry: That is why I am asking you if you want to verify it with them here on chat

user MARK_ has left room

Not really. I’m still there waiting.

Install Nagios on a Synology DiskStation DS415+ (Plex Support Also Added)

December 24, 2014 (Modified December 29, 2014 – Added Plex Support Section, January 11, 2015 – Added Nagios Web Status and Fixed Status Logging) (Back to the Previous Post in the Series) This article describes how to compile and run Nagios 4.0.8 (with 2.0.3 plugins) on a Synology DiskStation DS415+ (64 bit), which utilizes an […]

Just in case

For those who don’t read Oracle-l and haven’t found Nikolay Savvinov’s blog, here’s a little note pulling together a recent question on Oracle-L and a relevant (and probably unexpected) observation from the blog. The question (paraphrased) was:

The developers/data modelers are creating all the tables with varchar2(4000) as standard by default “Just in case we need it”. What do you think of this idea?

The general answer was that it’s a bad idea (and unnecessary, anyway) and one specific threat that got mentioned was the problem of creating indexes and Oracle error ORA-01450; but coincidentally Nikolay Savvinov had written about a performance-related “bug” in August this year, which turned out, only last week, to be expected behaviour. You can read his articles for the details, but since he used a 4KB block size to demonstrate it I thought I’d repeat the exercise using an 8KB block size.


drop table t1 purge;
create table t1 (id number(6), v1 varchar(40), v2 varchar2(40), v3 varchar2(40));
create unique index t1_i1 on t1(id);

execute snap_redo.start_snap

insert into t1 
select	object_id, object_name, object_name, object_name 
from	all_objects
where	rownum <= 10000

execute snap_redo.end_snap

drop table t1 purge;
create table t1 (id number(6), v1 varchar(4000), v2 varchar2(4000), v3 varchar2(4000));
create unique index t1_i1 on t1(id);

execute snap_redo.start_snap

insert into t1 
select	object_id, object_name, object_name, object_name 
from	all_objects
where	rownum <= 10000

execute snap_redo.end_snap

I’ve dropped and created the same table twice, once with varchar2(40) columns and once with varchar2(4000) columns.

I’ve created an index on a (non-character) column – the specific results vary depending on whether the index is unique or non-unique, and whether or not you have the index, and whether or not the table already holds data, and the effective clustering on the index columns etc. etc. but the key difference between the two sets of results doesn’t go away.

I’ve inserted object_name values (maximum usage 32 bytes) into the varchar2() columns, inserting 10,000 rows.

The snap_redo package is one of my simple pre/post packages that calculates changes in values in some dynamic performance view – in this case it’s looking at v$sysstat (system statistics) for statistics relating to redo generation, which means you need to run this test on an otherwise idle instance. Here are the two sets of results from an instance of

Name                                                                     Value
----                                                                     -----
messages sent                                                               11
messages received                                                           11
calls to kcmgcs                                                            313
calls to kcmgas                                                             37
calls to get snapshot scn: kcmgss                                           74
redo entries                                                               769
redo size                                                            1,317,008
redo wastage                                                             3,888
redo writes                                                                 11
redo blocks written                                                      2,664
redo write time                                                             10
redo blocks checksummed by FG (exclusive)                                2,242
redo ordering marks                                                          1
redo subscn max counts                                                       1
redo synch time                                                              7
redo synch time (usec)                                                  88,875
redo synch time overhead (usec)                                          1,810
redo synch time overhead count (<2 msec)                                    11
redo synch writes                                                           11
redo write info find                                                        11
undo change vector size                                                261,136
rollback changes - undo records applied                                      2
IMU undo allocation size                                                17,184

Name                                                                     Value
----                                                                     -----
messages sent                                                                8
messages received                                                            8
calls to kcmgcs                                                            222
calls to kcmgas                                                             56
calls to get snapshot scn: kcmgss                                           52
redo entries                                                            20,409
redo size                                                            5,606,872
redo buffer allocation retries                                               1
redo wastage                                                             1,248
redo writes                                                                  6
redo blocks written                                                     11,324
redo write time                                                             26
redo blocks checksummed by FG (exclusive)                                  571
redo ordering marks                                                         32
redo subscn max counts                                                       1
redo synch time                                                              6
redo synch time (usec)                                                  60,230
redo synch time overhead (usec)                                            159
redo synch time overhead count (<2 msec)                                     1
redo synch writes                                                            1
redo write info find                                                         1
undo change vector size                                              1,590,520
IMU undo allocation size                                                   144

Notice, particularly, the great change in the number of redo entries and the total redo size when the character columns are defined at varchar2(4000). Note particularly that the number of redo entries is roughly “2 * number of rows inserted” – for each row that’s one for the row and one for the index entry. You can check the redo log content by dump the log file, of course (and Nikolay did), or you can take my word for it that Oracle is doing the equivalent of single row processing in the varchar2(4000) case and array processing in the varchar2(40) case.

When Oracle calculates that the row length is larger than the block size it falls back to single row processing; this can increase your redo generation significantly, and since the rate at which you can pump out redo is the ultimate rate at which you can load data this could have a significant impact on your data loading times. Declaring character columns as varchar2(4000) “just in case” is a bad idea.


Data Guard and Oracle Restart, aka relink always !!!

During my early years with PC I love to play "Leisure Suit Larry" (especially part 1 to 3 which has running on CGA or Hercules graphics card). Author of this games, Al Lowe had a good sense of humor and every time you failed your mission or stuck in a place - there was an pop up on the screen "Al says: save early, save often". After today fight with Oracle I should remember a next saying - "Relink after, relink always".

Let me explain what what happen today.

There used to be old Oracle bug in 11.2g which I described here. Basically Oracle Restart was not aware of role changes and could open a database in wrong mode after role switch. I created a workaround for it but I was really waiting for a patch. During a UKOUG 2014 I was speaking with Oracle Data Guard Product Manager - Larry Carpenter - and I learned that patch is ready. I asked @UweHesse for details and I got my numbers back - patch 15986647 and it is included in latest PSU

After I got this information I was happy - I just applied this PSU last week, so let's test it.

The first run looked OK - after switchover Oracle Restart displayed proper role and start-up option on new primary
2014-12-22 09:02:36.511: [ AGFW][2057]{0:0:44372} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:02:36.513: [ AGFW][9207]{0:0:44372} Command: res_attr_modified for resource: ora.apppre11.db 1 1 completed with status: SUCCESS
2014-12-22 09:02:36.516: [ AGFW][2057]{0:0:44372} Attribute: ROLE for resource ora.apppre11.db modified to: PRIMARY
2014-12-22 09:02:36.516: [ AGFW][2057]{0:0:44372} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre11.db modified to: open
2014-12-22 09:02:36.516: [ AGFW][2057]{0:0:44372} config version updated to : 69 for ora.apppre11.db 1 1
2014-12-22 09:02:36.516: [ AGFW][2057]{0:0:44372} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.663: [ AGFW][2057]{0:0:44374} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.apppre_adf.svc 1 1] ID 4355:617914

but only proper role on the new standby.


2014-12-22 09:03:10.420: [ AGFW][2057]{0:0:11369} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787
2014-12-22 09:03:10.423: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:03:10.424: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:03:10.424: [ AGFW][2412]{0:0:11369} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 09:03:10.424: [ AGFW][2057]{0:0:11369} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 09:03:10.424: [ AGFW][2057]{0:0:11369} config version updated to : 142 for ora.apppre21.db 1 1
2014-12-22 09:03:10.424: [ AGFW][2057]{0:0:11369} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787

Start up option was still open instead of mount. Hmmm, I was sure that both nodes are same but again I reconfigure Data Guard Broker and Oracle Restart - no change - issue was resolved on the one server only.  Then I decided to double check if I have this PSU installed, and I found out that everything was in order in terms of PSU.

-bash-3.2$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version
Copyright (c) 2013, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version :
OUI version :
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-12-22_15-08-59PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-12-22_15-08-59PM.txt

Installed Top-level Products (1):

Oracle Database 11g
There are 1 product(s) installed in this Oracle Home.

Interim patches (3) :

Patch 18235390 : applied on Mon Dec 22 14:54:02 GMT 2014
Unique Patch ID: 18349043
Created on 4 Dec 2014, 18:50:09 hrs PST8PDT
Bugs fixed:

Patch 19121549 : applied on Mon Nov 17 14:06:54 GMT 2014
Unique Patch ID: 18060349
Patch description: "OCW Patch Set Update : (19121549)"
Created on 7 Oct 2014, 03:38:04 hrs PST8PDT
Bugs fixed:
18328800, 18691572, 14525998, 18187697, 18348155, 17516024, 17387214
17750548, 17617807, 17551223, 14671408, 14207615, 18272135, 18180541
17292250, 17378618, 17500165, 18875012, 18464784, 17065496, 18848125
13991403, 17955615, 14693336, 17273020, 17238586, 17089344, 17405605
17531342, 17155238, 17159489, 18053580, 16543190, 17039197, 16317771
17947785, 10052729, 16281493, 18346135, 17481314, 18199185, 18399991
18024089, 18428146, 18352845, 18352846, 17391726, 18414137, 17001914
17927970, 14378120, 16346413, 17305100, 15832129, 15986647, 16901346
18068871, 17985714, 18536826, 16206997, 18752378, 16876500, 16429265
18343490, 18336452, 16613232, 17273003, 19276791, 12928658, 18226143
17172091, 18229842, 18053631, 16867761, 18231837, 15869775, 17483479
18729166, 17405302, 15920201, 18709496

Patch 19121551 : applied on Mon Nov 17 14:05:34 GMT 2014
Unique Patch ID: 17971200
Patch description: "Database Patch Set Update : (19121551)"
Created on 9 Oct 2014, 13:06:55 hrs PST8PDT
Sub-patch 18522509; "Database Patch Set Update : (18522509)"
Sub-patch 18031668; "Database Patch Set Update : (18031668)"
Sub-patch 17478514; "Database Patch Set Update : (17478514)"
Bugs fixed:
16929165, 16220077, 17235750, 17468141, 18191164, 17006183, 16315398
17501491, 13955826, 17288409, 12905058, 17446237, 17375354, 16992075
16855292, 17050888, 17546973, 18554871, 17726838, 17614134, 9756271
18673325, 17227277, 17232014, 13853126, 17545847, 17390160, 18096714
12747740, 17016369, 17786518, 19271443, 10136473, 16785708, 17311728
18018515, 16268425, 17610798, 18280813, 17082359, 17783588, 14245531
18094246, 13866822, 17477958, 16943711, 18673304, 18031668, 19463897
19463893, 19211724, 13498382, 16450169, 17397545, 17786278, 17767676
14458214, 19289642, 17622427, 17824637, 17716305, 16399083, 18744139
14852021, 19727057, 17242746, 17174582, 18277454, 13645875, 14084247
17551709, 17393683, 17614227, 17705023, 17883081, 16042673, 18996843
16285691, 17393915, 16228604, 17655634, 17596908, 17600719, 16180763
17754782, 17323222, 18264060, 17325413, 17343514, 17865671, 16613964
17811447, 16069901, 17390431, 16494615, 16043574, 17006570, 17027426
14602788, 17080436, 18673342, 17186905, 17011832, 17394950, 13944971
17752121, 17284817, 17811456, 17238511, 17239687, 17042658, 14764829
17602269, 17672719, 17891946, 17205719, 18262334, 16721594, 17071721
14565184, 17265217, 17389192, 17761775, 16360112, 17982555, 17842825
19121551, 16837842, 18139690, 17313525, 18203837, 18203838, 18203835
18436307, 17546761, 17721717, 17344412, 17787259, 16472716, 17346671
17588480, 13364795, 14657740, 11733603, 17811438, 19466309, 17040527
17037130, 17088068, 17612828, 18180390, 17449815, 19458377, 19554106
18973907, 18230522, 19544839, 17811429, 16863422, 17237521, 17951233
17752995, 16392068, 17437634, 14338435, 13609098, 17332800, 18199537
17465741, 17441661, 18522509, 18061914, 14133975, 17570240, 16692232
18247991, 18328509, 16956380, 17587063, 19049453, 16618694, 17586955
18009564, 14285317, 16542886, 17341326, 17571306, 17036973, 18641419
16524926, 17297939, 17478145, 17040764, 17299889, 16912439, 15861775
14054676, 13951456, 17267114, 16850630, 17648596, 14010183, 17296856
17478514, 16875449, 18681862, 14829250, 17385178, 17443671, 16731148
16314254, 17165204, 15979965, 19584068, 17381384, 17892268, 16198143


OPatch succeeded.

But on one server I was testing patch for OEM (18235390) to allow it to monitor tablespaces without ORA-1000 errors.Is it possible that my OEM patch add anything special ?

I was thinking about that for a while but I was not sure. So I did simple test - installed this patch on standby server and ... yes - Oracle Restart issue has been fixed. 
Strange, very strange - what this patch did that Data Guard was fixed ? Well it did one important thing - it relinked oracle binaries again.
Lucky enough I got one more pair with latest PSU ready for test.I run test with PSU installed and bug was still there. OK - so let's relink - and it was it
2014-12-22 14:33:40.942: [    AGFW][2057]{0:0:12827} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223
2014-12-22 14:33:40.944: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify {
2014-12-22 14:33:40.945: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify }
2014-12-22 14:33:40.945: [ AGFW][9494]{0:0:12827} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 14:33:40.946: [ AGFW][2057]{0:0:12827} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 14:33:40.946: [ AGFW][2057]{0:0:12827} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre21.db modified to: mount
2014-12-22 14:33:40.946: [ AGFW][2057]{0:0:12827} config version updated to : 153 for ora.apppre21.db 1 1
2014-12-22 14:33:40.946: [ AGFW][2057]{0:0:12827} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223


This is quite strange and a little bit scary. I have installed all PSU using opatch auto from Grid Infrastructure Home and there was no errors but relinking Oracle Home actually fix a problem which was included in the patch. 
So remember "Relink after (patch), relink always"