Monday, March 31, 2014

12c: Bypassing EM to turn off metric thresholds

File this one under the heading "not quite sure why this exists" as the article "How Disable Database Time Spent Waiting (%) At Database Level" on MOS with note 1629021.1 clearly explains the commands to turn the thresholds off for several different types of wait classes, but I do not understand WHY this is an option, required, or even suggested.  Is there a bug in the 12c EM which requires this to be completed "by hand"?  If there was a problem, I could understand turning off some threshold alerting but this seems completely backwards to anybody wanting to be proactive.

Sunday, March 30, 2014

SOA: Failure to start when missing data source

While MOS note 1630343.1 is straightforward for why a SOA server can't start up when the data source is missing or unavailable, this note has a link to Oracle documentation which explains WHY that is and offers ways to tune data source connections.  This has multiple applications as it applies to WebLogic and JDBC connections, so this may add some value for you either today or down the road!

Saturday, March 29, 2014

R12 Payables: Invoice Workbench error APP-SQLAP-10108 when committing

Some days, the articles I share have a very specific focus but today this feels like a much larger lesson on several fronts.  The MOS article "Invoice Workbench/Distributions - APP-SQLAP-10108: An Error has occurred while trying to commit" in 1570047.1 is very straightforward for how to replicate the issue and that the cause is non-printing characters but it goes to make me wonder why this was not included in a testing suite by Oracle.  Even more curious, is the reaction that "this just should not be done, so stop doing it" instead of Oracle development working to make sure the form will not accept these types of characters.  I wonder what other forms have this ticking time bomb lurking in them, and how many times it has to be reported until it gets resolved in an eventual release.

Friday, March 28, 2014

Weekend Learning: OBIEE 11g and DBFS

MOS note 1621469.1 captured my attention, as we have just implemented OBIEE again but on the 11g platform this time but I haven't had a lot of exposure to that or DBFS.  What is DBFS?  The note has a good explanation, I'll need to find more documentation about the subject later, but it boils down to a File System for Oracle Databases that can be used instead of NFS or ZFS based on SecureFile LOBs with access controlled with PL/SQL procedure calls.

The point of the article was if DBFS can be used for OBIEE 11g, and I'll steal a bit of thunder for once and tell you no it can't be used.  What I won't tell you, is WHY that is the case, and if you are curious you can visit the MOS note!

Thursday, March 27, 2014

R12.1.3: Can a refund from Receivables have a Payables invoice in a different OU?

I am not sure why this feature is requested, but I do not spend time designing business cases so I assume this could be valid in certain scenarios depending on different business processes that some companies may have.  Regardless, it appears that this could have existed prior to R12.1.3 but is not possible after that code release per MOS note 1626484.1, yet if you need it to work this way an Enhancement Request has been opened in bug 18274880.  Oracle suggests that you open an SR, and mention this bug, if you want to be associated with the development of the ER.

Wednesday, March 26, 2014

R12.2.3: Error when creating Receivables Customer

Keeping an eye on the future, the bug documented in MOS note 1635413.1 is an interesting one.  When upgrading to R12.2.3 you will need to be aware of an ORA-06508 error in the procedure sync_contact_point when trying to create a Customer in Receivables.  This is as a result of an INVALID HZ_STAGE_MAP_TRANSFORM package as this and other key packages are dynamically generated so they need to be re-generated after upgrades when there are changes made to them or their underlying packages.

Tuesday, March 25, 2014

Selecting from too many tables hits Oracle DB bug on 11.2.0.3

Doing some research, I found a few hundred tables which were being gathered as if they contained no data but I wanted to verify it myself by looking at the data (or absence of data).  No problem I thought, I would just take the list of tables, put a comma at the end of each one, and then use the list in something like this:

SELECT * FROM table1, table2, etc

They are empty, so it sounds like a great idea right?  Not so much.  I believed that there was such a thing as too many tables (or maybe columns) that can be joined together (in a Cartesian join in this example) as the following ORA-00600 error was spit out at me:

ORA-00600: internal error code, arguments: [mal0-size-too-large], [kksoff : opitca], [], [], [], [], [], [], [], [], [], []

At first, I thought it was simply because of the Cartesian join but when I reduced the query to only have 20 or 30 tables I no longer received my ORA-00600 message and I received the single null row with all of the columns from each table returned.  It turns out, that since we are on 11.2.0.3 this is a bug I'm encountering detailed in MOS note/bug 553051.1/675576 which is resolved in 11.2.0.4 and 12.1.0.1 versions of the database.

Monday, March 24, 2014

12c: Datapatch failure with ORA-01017 error

As we are going to be upgrading a database to 12c, I'm keeping my eyes open for issues that might crop up when we install it and MOS note 1635007.1 is one worth reading up on!  Titled "Datapatch fails with ORA-01017, as it only connects to the database with OS Authentication" it appears that datapatch only is using OS authentication and it reports back the rejection error of ORA-01017 if you are trying to install it under an account which fails the OS authentication.  Even though this note indicates that the issue may be resolved with bug 18151716, if you read that bug information it doesn't really match the description of this note!

Regardless, I learned a bit as I usually track against ad_bugs to see if patches were applied but there is another table DBA_REGISTRY_SQLPATCH which Oracle suggests to modify if the datapatch needs to be applied manually in one of the two workarounds suggested.

Sunday, March 23, 2014

Pictures, pictures: see all about it!

As some of you may have seen, I got to present at Hotsos and one of the requirements I completely spaced was getting them an updated headshot!  Luckily the team at IMAGINE Graphic Design were able to fit me into their schedule, took quite a few photos which were hard to choose from, and allowed me to get my picture published on the web and in the materials for the conference too!  I'm so happy with the results, that I'll be updating my social media profiles in several places with the official picture and maybe a few of the other versions as well.

If you're in Arizona, and are looking for a team to take pictures for most any event, contact the folks at IMAGINE Graphic Design.  Oh, and tell them I sent you too!  :}

Saturday, March 22, 2014

Weekend Learning: Where are Audit Trails for the PLM product?

Did you know that there are audit trails for the Product Lifecycle Management (PLM) module?  Well I didn't either!  MOS note 1612322.1 has a script to pull from the commonLifecycleEventLog table, linking it to several other tables, but I'm more interested to see what this log table has in our environment as we just installed a new module called PLM which I think is the same thing.  If this tracks the audit trails for every change, we'll need to get a handle on how it gets purged, and add it to our documentation!

Friday, March 21, 2014

Weekend Learning: Oracle Real World Performance team

Are you interested in getting the most performance that you can out of the software and hardware you purchase from Oracle?  Of course you are!  That reason is why you should start following Oracle's Real World Performance team, including visiting their site that has several YouTube videos and which I'm sure will contain a link to the webcast Andrew Holdsworth (@ajholdsworth) held today about this very topic.  There will be other webcasts from Andrew, so look for them in the very near future!

Thursday, March 20, 2014

Oracle Applications Patchset Comparison Utility

You may already be aware of this tool, but it has come up recently at work "how to know if we have the most recent patches applied?"  That's a great question!  Fortunately, My Oracle Support has the answer in note 139684.1 titled "R11i / R12.0 / R12.1 / R12.2: Oracle Applications Current Patchset Comparison Utility - patchsets.sh".  The best part?  You can download an updated file containing the newest patches EVERY day from Oracle and re-run this utility to find out if there are new patches EVERY day!  That's awesome!  I'm even thinking of putting this into a cron job to go out and get the file, then run the utility automatically so I'll have a fresh output each morning when I get in.  Talk about being proactive!

Wednesday, March 19, 2014

Oracle OpenWorld 2014 Call for Proposals!

The floodgates have opened for submitting abstracts to this year's Oracle OpenWorld!  I've been able to present for OAUG and Hotsos, but I'm looking to join others at OpenWorld as a presenter this year to continue the journey I've started.  Time to brush up on abstracts that might have been rejected previously, those selected for other conferences, and brand new abstracts that are percolating in my brain for OOW this year!

Tuesday, March 18, 2014

R12: Accounting Program/Create Accounting reports stall

At 7 AM our Submit Accounting and Unposted Items Report that should have run hours earlier were just sitting in the Concurrent Manager in a Pending status.  I found that this was due to an incompatibility between these reports, the Accounting Program and Create Accounting reports but they started at 8 PM so I had to solve the puzzle for why they were still running.  What challenges did I face while trying to verify this?
  • The Accounting Program and Create Accounting reports did not actually create any entries in the log files so I couldn't tell that they were alive at all.
  • Even further, I tried finding these alive in gv$session but I couldn't find anything by ACTION or MODULE.
  • Our monitoring that constantly runs was reporting the Create Accounting report had a particular SID/SERIAL# combination but looking at that SID in gv$session actually saw different sessions coming in and out which were not our programs.
When a co-worker came in, I was at my wit's end, and I asked him to take a look at this.  Imagine my surprise when he tells me the Accounting Program and Create Accounting reports had been completed!  What did I learn from this?  The reports don't actually log their activity while they are alive and running, until they complete so it is easy to think they are dead!  I'll be opening up an SR to see if bug 17662654 relates to this problem, and if so I'll have to get the bug reopened as it has been closed.  If this isn't related, then I'll need to research this more locally when I get a chance.

A few other items I found on MOS that may be related are as follows: 1378432.1, 1463805.1, patch 14013094, 1465689.1, 791049.1, patch 16060007.

Monday, March 17, 2014

What prevents you from dropping a tablespace in a 12c PDB?

As we're looking to go to 12c, I've found myself paying more attention to this content so MOS note 1634495.1 grabbed my attention when it talked about receiving the following when trying to drop a tablespace in a 12c PDB:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-65040: operation not allowed from within a pluggable database

This is especially interesting for me as the application on top of the database that we are migrating to 12c uses tablespaces as versioning, so we started out with VERSION as the initial tablespace name and the next release was in a tablespace named VERSION2, etc.  Well, what happens now when we migrate the database to 12c and our initial tablespace can no longer be removed?  This note tells you why it is an issue, how you resolve it, and will be a key reference document I'm sure by our team down the road.

Sunday, March 16, 2014

Weekend Learning: DBA_NETWORK_ACLS

Did you know that you can use the information in the views DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES to control access to your system?  Note 1634275.1 from My Oracle Support clued me in on it, as this gives us a script to reverse-engineer a solution in case the ACLs are ever lost and it clearly shows this access is controllable down to the user level.  I was talking about security for our system a few days ago, and this is yet one more way to help secure the system!

Saturday, March 15, 2014

Oracle Support at Collaborate14!

The next OAUG Collaborate conference is coming up quick, and MOS note 1525331.1 tracks the sessions that feature Oracle Support during the conference!  Have you ever wanted to know more about Oracle Support, or even meet some of the people you've worked with previously?  Well then, this is your chance!  Take advantage of it!

Friday, March 14, 2014

ALERT 11gR2: Windows Bundle Patch 28 Rescinded

Tonight is a fairly straightforward warning about Windows Bundle Patch 28 for 11.2.0.3 being rescinded by Oracle due to guaranteed ORA-00600 errors.  The suggested courses of action listed in MOS note 1635077.1 are to apply BP 29, or rollback BP 28 immediately.

We do not have Oracle running on Windows, but I know others out there do, so I wanted to share this critical information with you before you have a problem.

Thursday, March 13, 2014

Delegation of Expense Reports gives ORA-20001 error

Yesterday I ran into an interesting issue where trying to reassign an expense report from one manager to another resulted in the error:

ORA-20001: Exactly one of an approverRecord's person_id and user_id fields must be null. ORA-06512: at "APPS.WF_ENGINE", line 5931 ORA-06512: at line 1

This error message obviously doesn't make a whole lot of sense, so off to My Oracle Support I went!  I found note 1299908.1 that seemed to be fairly on point, but when I looked neither employee had vacation rules set up and bug 13616552 didn't get resolved yet so that wasn't any help.  Pinging one of my co-workers about this, he knew what was going on with this and asked if I was choosing to transfer the notification or delegate the response.  I wasn't prepared to find out that this happens at times when we choose to delegate the response for notifications to another manager, but on the same record if you choose to transfer the notification to another manager no error message pops up at all!  This seems like such a small thing, but I typically use delegate to switch manager assignments in the system and apparently at some time we introduced this bug into our system.

Wednesday, March 12, 2014

URGENT R12: Complete Concurrent Manager processing stop

Hot off the presses is an urgent update for anybody on R12 or planning on going to R12.  Today we encountered an issue where our users reported their concurrent requests were ending in Error, and in the log file was "emsg:was terminated by signal 25" but we didn't know of anything wrong or why this would be happening.  My co-worker searched on MOS and found note 842850.1 which is directly on point indicating that once the reports.log file in $APPLCSF/$APPLLOG or $INST_TOP (ours was in $FND_TOP) grows to be 2GB concurrent processing stops.  All reports, no matter how many nodes you have if it's all on a shared directory structure, come to a complete dead stop.

Luckily we didn't have any critical business processes die because of this, but you need to take steps immediately to make sure you don't encounter this same situation which could happen while reports are being run that could cause orphan records or incomplete data to be left in your system.  Our DBA was able to follow the action listed in the note without taking the Concurrent Manager down, so I don't know if that will cause a part 2 for this document or not, but I'll keep you updated if we encounter any issues because of that.

What is also interesting about this, is that our DBAs did encounter this is problem in QA during their building and maintenance of R12 and applied patch 10152652 which is notated as fixing this issue when we went to PROD but it did not work for us today.  That is probably why the note was updated within the last week.  :}

Tuesday, March 11, 2014

Oracle BDA: OEL 5.8 Kernel Hang

Following up on my post yesterday about Exalytics having problems of a quite serious nature, today I introduce you to the Oracle Big Data Appliance and how it experiences kernel problems on version 2.4 as well in MOS note 1624337.1 if you are running it on the Oracle Enterprise Linux 5.8 OS.  Symptoms include being unable to ping or ssh servers due to Ethernet interfaces going down or the OS kernel hanging, and in some cases the server actually runs out of memory.  There is a workaround listed, but you can avoid this problem by upgrading to OEL 6 or patching BDA to 2.5 when it is released.

Monday, March 10, 2014

Exalytics: Stability problems after 208 days uptime

Last year, I learned a very good lesson about letting your servers go too long before reboot but this surprising issue affects the latest and greatest Oracle product: Exalytics.  My Oracle Support note 1579194.1 details what version of Linux OS are susceptible to "Oracle Exalytics Stability and Performance Issues on Compute Nodes Running Linux After Uptime of More Than 208 Days" which affects all releases and PSUs of Exalytics.

Symptoms include kernel panics, task scheduler issues, and CPU soft lockup errors!

Sunday, March 9, 2014

11.2.0.4 certified with EBS 12.2

Just a few days ago, Elke Phelps announced that Oracle Database version 11.2.0.4 has been certified with R12.2 of Oracle EBS!  Sure this is big news, but even better is that in this article she has listed all of the associated reference materials that will help you with getting to 11.2.0.4 and on to R12.2!  This is great for me, because I'm looking forward to upgrading from 11.2.0.3 and even though we just got on R12.1.3 in October we will need to upgrade at some time right?  Sounds like I need to set some time aside to get all caught up on this information!

Saturday, March 8, 2014

Weekend Learning: Steps to stabilize RAC

If you are running a RAC cluster, you should look at MOS note 1344678.1 for "Top 11 Things to do NOW to Stabilize your RAC Cluster Environment".  Not only do you get the top 11, but there are a LOT of links in the article (and at the bottom) which will lead you to even more information to help you on your way to stabilization.  I thought the idea of turning off ADDM in favor of Linux pages was an interesting idea, especially after hearing about performance results on Linux during the past week!

Friday, March 7, 2014

Participating in my first Twitter live-chat

During the excellent Training Day from Tanel Poder, I had another opportunity to learn by participating in my first Twitter live-chat!  Hosted by Pete Finnigan, this live chat was focused on data security and you can read all about it (including my questions that were answered) by searching for the tag #datachat or click here for the mobile Twitter link.  This was really fun to do, and I did get a lot out of it too, so I think I'll try to get in on these types of events in the future when they fit my interests.  Heck, maybe someday I will actually host one!  :}

Thursday, March 6, 2014

Hotsos Symposium 2014 - Training Day


I broke my routine and skipped breakfast this morning, but I made it here in time for Tanel's presentation!

Topics
  Systematic Troubleshooting Approach
  Metrics, what to use when?
  Oracle 12c topics
  OS touch-point and lower level stuff

Doing a really deep dive into today's presentation isn't fair to Tanel, or the other attendees that spent their cold hard cash to be here, so I'm only going to highlight a few items that might help you out.  Before you leave!  Tanel did post his Training Day from 2010 which had a lot of what he based today's presentation off of, so if you need to learn something from him here is an excellent opportunity.

Review the ASH table.  There are some "hidden" gems in it!
What does IS_SQLID_CURRENT in ASH do or mean?  Tanel doesn't know, he hasn't really used it.  Sounds to me like a good thing to learn!  He suggests that we read te documentation. :}
Totally off subject, but it makes me feel good that Tanel thinks he would fail some Oracle exams!
What are the different types of profiling, and what do you use?

Some tables mentioned today:

V$OSSTAT
V$SYS_TIME_MODEL
V$SESSTAT
V$SYSSTAT
V$TOPLEVELCALL
V$ENQUEUE_STATISTICS
DBA_HIST_SYSMETRIC
V$EVENT_HISTOGRAM
V$LATCH
V$LATCH_CHILDREN

Tools mentioned today:

Snapper script (located here)
Ashtop.sql (located here)
Swingbench

How does a phone bill relate to Oracle performance tuning?  You'd be surprised!  Great example!
Great use of EM to dive into a performance problem!
Tanel was channeling Cary Millsap a bit and it was awesome!
He taught us something about ASH Analytics in 12c, which looks really cool and useful too!

If you didn't attend Hotsos Symposium this year, I would strongly recommend it, and even MORE strongly recommend signing up for the Training Day next year!

Wednesday, March 5, 2014

Hotsos Symposium 2014 - Day 3

Following a trend, I had breakfast and then onto the show!

8:30 AM - Leveraging in-memory storage to overcome Oracle Database PGA memory limits - Alex Fatkulin - @AlexFatkulin

Alex starts with the observation that data trends have changed over the years, and bloat in the database is not unexpected.
The idea of legacy Oracle code vs. 64 bit OS innovation.
"Kill it with iron" is a great mantra!
A short PGA memory overview which honestly had some new stuff for me, but don't worry I'll be tackling learning more about Oracle internals this year!
Review of SQL work areas - hash area, sort area, bitmap area.
ORA-02017 was an error code he received when setting memory values.  Look it up, the message will surprise you!
Alex showed us that he was on a 64 bit system, but Oracle will only accept a 32 bit value for a particular memory parameter.
Why can Oracle accept 1TB for another memory parameter?  There are a bunch of hidden parameters related to PGA_AGGREGATE_TARGET that get set.
I thought that Alex had a really good graph to tell his story as well.
Importantly, he pointed out that per process limits are multiplied by the Degree of Parallelism.  In that case, an Exadata X3-8 system would need a DOP set to 768 to use the PGA of 1.5TB for processes.
Review MOS note 453540.1 / patch 17951233 for how to override memory limits.
Can we accept memory spilling as a fact of life?
There are ways to radically improve TEMP I/O:
  In-memory file system
  SAN/NAS LUN with write-back cache
Linux tmpfs via loop device
InfiniBand SRP with ZFS storage device had incredible payoffs so I wonder if we can replicate that in our space.
I loved seeing the spilling to temp!

9:45 AM - Profiling the Logwriter and Databasewriter - Frits Hoogland - @fritshoogland

lgwr and dbwr are the process names at the database level, and this presentation is about what their wait events tell you at a pretty detailed technical level.
lgwr manages the redolog buffer and Frits was able to run oradebug on the lgwr process.
"rdbms ipc message" indicates a sleep/idle event.
lgwr looks every 60 seconds for PMON existence, if it doesn't find it the instance gets terminated.
I really enjoyed his expectations vs. actual graph because he was very honest about the difference!
semctl system call wakes up lgwr so it submits an io_submit call.
Frits was genuinely excited about the DB not working as intended, which was plain as day and I love that he feels that sense of thrill just as I and others do when we have problems or can't explain something!
When he put overhead on the process, he created an edge case which made me think of Schrödinger's cat.
The parameter "_use_adaptive_log_file_sync" is automatically set to TRUE in 11.2.0.3.
The wait event "log file parallel write" is a sign lgwr is waiting!
It was really wonderful seeing latches I've never seen before!
pwrite64 is the parallel write io subprocess.
I thought it was awesome that Frits might have a bug which may be fixed in 12.1.0.2!
Logswitch events!
"_disable_logging" is a forbidden switch but all Frits could see was a lack of writing as a result.
The processes oss_write (stands for Oracle Storage System) and oss_wait are what you will see on an Exadata system.
DBWn writes dirty buffers and looks for PMON to be alive too!
"DB file async I/O submit" was a new event he wasn't expecting.  This did not exist prior to 11.2.0.2 and while he thought it may have been instrumentation of io_submit it wasn't.
"DB file parallel write" is not physical I/O indicator or I/O latency timing, this is the timing of what's gone on before.

This was a really great presentation and gave me some new insight on how to dive deeper into the mechanics of what Oracle is doing behind the scenes!

11:00 AM - Journey to the Center of the Database - Ric Van Dyke - @RicVanDyke

Not knowing a whole lot about database internals, I wanted to attend this session to pick up what might be some common knowledge while Ric dives into a bigger issue with his presentation

First he starts off by asking what is a data block, and comes back with an answer that most are blocks representing table or data.
The size of your blocks depends on the DB_BLOCK_SIZE parameter, and different block sizes are designed for different purposes such as OLTP versus DW.
Block overhead is about 100 bytes which contains a header, table directory, and row directory.
What is a rowid really?  Ric points out that it isn't what we think it is.
He then takes us into the row!  I thought it was pretty awesome how he was doing a dump of the blocks and then putting it back together again to see what it said.
Trailing nulls are not stored in a block, so they should be put at the end of your rows to save space.
Test scripts!
Number format doesn't show up in block dump.
Character data is stored in reverse order…sort of
Did you know the old row of data still exists after update in the block if the whole row is updated, instead of a single column being re-written?  Pretty cool to see him prove it out!  Because of this, there are redo log considerations.
Ric had a good breakdown of the differences of the pros for block sizes!
He keeps underscoring how you need to test your environment to see how your indexes (and data) run with different block sizes.
There is a new 12c feature that introduces batched indexes to grab a set of rowids and associated data!  Very cool!
His example showed block size being increased from 4k to 16k, and as expected the consistent gets went down, but the time taken went up.
From the audience, Ric was taught something that he taught the audience member before!  Priceless!
PCTFREE vs PCTUSED - I think this is likely why my Workflow purging hit the HWM issues it did!

1:00 PM - Understanding How Adaptive Cursor Sharing (ACS) Produces Multiple Optimal Plans - Carlos Sierra - @csierra_usa

Carlos started out with showing us allies to plan flexibility, and several plan stability tools which are in sharp contrast to each other.
Reiterates that with cursor sharing, you use bind variables to reduce hard parsing but the problem is that different values in your binds changes your return results so plans eventually change from good to bad.
With ACS, introduced in 11g, you can have multiple optimal plans per SQL depending on the selectivity of the predicates.
ACS has overhead, so it shouldn't be used "globally" and bind variable statements are candidates which are eventually categorized as "Bind Aware".
SQL becomes Bind Aware when the rows processed change between executions substantially or oscillates significantly.
V$SQL - columns: is_sharable, is_bind_sensitive, is_bind_aware
V$SQL_CS_STATISTICS - rows processed
V$SQL_CS_HISTOGRAM - buckets S/M/L (S - less than 1k, M - 1k to 1m, L - 1m and up)
V$SQL_CS_SELECTIVITY - selectivity profile
Live demo!  This was really interesting, and I can't wait to play around with the scripts Carlos will share!
The hints /*+ BIND_AWARE */ and /*+ NO_BIND_AWARE */ help to force ACS to do what we want if we can't change the code.
With ACS, there is the idea of plan selection but it is not permanent and SQL Plan Management (SPM) gives you that permanency.

2:15 PM - Creative Problem Solving - Kerry Osborne - @KerryOracleGuy

There was SO much that Kerry brought up in his presentation, it feels like if I told you everything, it'd give away the ending and you wouldn't get as much out of it when you get a chance to hear him talk.  As a result, I'm going to narrow his talk down to just a few points that were really relevant to me.

Can we teach people to be better problem solvers?  I believe so, but I'm probably biased because I've trained a lot of people and I like to think I've helped to make some of them better problem solvers.
Kerry loves working with people, but hates working with people which I think is very insightful about him, but speaks a lot about our profession choices as well because we can choose how we act even though we know our preferences.
What makes a good problem solver?  Kerry boiled this down to 4 pillars, but one I disagree with is needing to be optimistic (if you know me, you'll know why I disagree).  I think depending on your job/role, you can absolutely be pessimistic and an effective problem solver as you can be optimistic about yourself or your skills and be pessimistic about situations or actions that others take.
I thought his statement that it takes 10,000 hours of practice before you start getting good at something very interesting.  That's 5 years if you do the same thing for 40 hours a week for 50 weeks out of the year.  Who does the exact same thing over and over like that?  Especially in my line of work of Support where I do a little of this, and a little of that, but not a lot of it the same at any one time to get repetitive practice at it.  Maybe time to find something for my toolbox that I can get practice at, and get better at huh?
Problems are complex and have multiple solutions.  Sounds a lot like what Cary Millsap says a lot right?
Can we overcome who we naturally are, to get better?  Yes, and there are mind tricks we can use to improve our odds.
The effects of stress are typically negative, but it is hard to totally avoid stress so there are several techniques that can be used to lessen that effect on your work product.

It may sound like I disagree with Kerry, but honestly he makes a lot of good points and it's made me look at some things in a different light.

Tuesday, March 4, 2014

Hotsos Symposium 2014 - Day 2 + Me

Breakfast in my room this morning while I reviewed my presentation, and then onto the show!

8:30 AM - General Session: Optimizer Ying and Yang - Tom Kyte - @oracleAskTom

Tom started with a great tip following up from his session yesterday, and that is don't program to fail!  Great tip!  I know it sounds odd, but trust me he made it work!
This leads into being able to abuse functions which leads to cardinality estimation and CPU usage issues.
This really reminded me of what I saw yesterday about working with functions.
ALTER SESSION SET PLSQL_WARNINGS = 'error:all' - allows the Optimizer to warns you about "suboptimal" code such as "when others null" and it has been there since 10g but doesn't have a lot of adoption from what Tom sees.
Another tip is to think about NLS settings on how your data/programming may turn out if it gets changed.
What happens when partition elimination is eliminated?
A reminder about what happens when you have bad stats, which is something I've seen before, temp tables being empty or full when gathered.  Choices are maybe dynamic sampling or filling with representative data.  A new feature in 12c is "session private statistics" that can lead to excessive hard parsing because everybody is doing the same thing.
He shared DBMS_STATS.COPY_TABLE_STATS() which makes me think about his example about gathering at 9AM vs 3PM with my next purging project.
When do you have to gather stats more often?
Is 10% right?
Underscored that you need to have knowledge of data pattern and data in your system.
Hints are often a bad idea, unless it gives the optimizer good information.
Valid hints can be ignored!
If you can hint it, you can baseline it.
Most important stats are NUM_ROWS and NDV!
AUTO_SAMPLE_SIZE is another option.
X is bad - is something always bad? or good?
Live tuning by a volunteer!
Idea of "key preserved" helps to eliminate table joins.
Count could change after retrieval!  Great reason to not code that in as a logic check!
10053 trace shows the details from his live tuning, so another tool in the toolbox.
He made the comment that if we think that he and Maria Colgan (@db_inmemory) are really unique, the actual Optimizer developers are really unique individuals!
Fake values = fear of nulls
Using the year 9999 makes the system think it has a lot of data so throws the Optimizer off.
Great example used of license plates people picked of NO TAGS and XXXXXXX which caused problems for them because the users/system had those "reserved" for unidentified cars!

9:45 AM - 10:15 AM - Diving into ASH and AWR - Kellyn Pot'vin - @DBAKevlar

First off, bravo!  She kept her cool and was able to rush through her presentation because the hotel had a problem partitioning the two rooms and she was left with only 30 minutes of time after a 30 minute delay.
The theme of her presentation was staying on the right road, and not letting ASH and AWR take you down a rabbit hole of tuning.  How do you prevent that?  Only tune for time, and nothing else.
Great reminder that one shouldn't assume, as data must be gathered to prove any point.
ASH and AWR came out due to needing "always on" performance metrics, but it does require a license from Oracle to run.
I really liked how she was running ASH from 12c EM, and giving us very relevant hints such as how the tool shows HTML output which is useful for explaining or proving things to executives.
Kellyn introduced a new feature called Compare ADDM which uses 2 AWR snapshots to tell you what the differences are for several key metrics.
12c also shows you regression SQL as well for what has decreased in performance.
Resource Comparisons is another feature that compares memory and I/O.
She believes (strongly) that DBAs should share tools with development, and that is a great idea that needs to be shared more!
SQL Monitor in 12c also shows Exadata cell offloading!
Search SQL is another new feature in 12c that lets you find out what has happened to that SQL previously in your system.
Another tool in the toolbox is:  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id))
A few keys from the ASH table is that user_id is same as in DBA_USERS and WAIT_TIME is on previous event.
The best part of the session for me were the scripts that Kellyn shared with us!  There's easily 5 or 6 that I'm bouncing around in my head for how we can build it into our proactive monitoring platform!  This includes a query that originated with Tyler Muth from Oracle and has been tweaked for several years.
Ending her presentation with the idea that there are RAC global ASH reports got me excited for where we can take this in the future.

11:00 AM - 12:30 PM - Power Up Your Apps with Recursive Subquery - Jared Still - @perlDBA

CONNECT BY refresher was good for me, because I really don't use it or see it used a whole lot.
Bug 17834663 / MOS note ID 17834663.8 for the associated bug
Two options:
With Fibonacci
Factorial
What's the difference between SYS_CONNECT_BY_PATH vs CONNECT_BY_ROOT?  Not much, but it's there.

I think it is concerning when a speaker says "this is hard to understand, and I've spent a lot of time on this but it's not second nature to me yet".  So…why am I here trying to learn if you say it's still confusing?  Probably why I didn't get a whole lot out of it, (either that or I was focused on what I wanted to say in my presentation next) but I'm going to try and use this stuff when I get home anyways to try something new…when I understand it better.

1:00 PM - 1:40 PM - Analyzing Oracle Workflow Data for Increased System Performance - Rusty Schmidt (Me!) - @TheOracleEMT

Sure, I wanted to go see Tanel Poder (@TanelPoder) present "Troubleshooting a Yet Another Complex Performance Issue" too, but the show had to go on and I appreciate everybody that attended my session over his.  Especially since he got to have the second room opened because of the delay Kellyn endured, and we were banished to the basement.  :}  I did rush through the presentation a bit, since I was thrown off some from the changes, and I think I hit every high point that I wanted but if you want a little more in-depth you can visit my Whitepaper that Hotsos has on their portal or the copy Oracle has hosted.

After my presentation I didn't get to attend the next sessions that I had planned on, so I'll cover these in a future blog post after I get access to the audio!  Sorry about that folks!

2:15 PM - 2:50 PM - SQL Tuning: A Look Behind the Curtain - Craig Martin - @c_martin2
3:30 PM - 4:00 PM - How Hot Is My Data?  Leveraging Automatic Database Optimization (ADO) Features in Oracle 12c Database For Dramatic Performance Improvements - Jim Czuprynski - @jczuprynski
4:45 PM - 5:00 PM - Where Did My CPU Go? - Karl Arao - @karlarao

This was a difficult choice for me, but after reading the abstract for Luca Canali's "A Latency Picture Is Worth a Thousand Storage Metrics" I realized I had blogged about it almost a year ago!

Monday, March 3, 2014

Hotsos Symposium 2014 - Day 1

The day started early so I could get ready and attend breakfast to do some networking, and then it was off to the main room for the conference to start!

8:30 AM - Keynote Address: An Introduction to the New Oracle Database In-Memory Option - Maria Colgan - @db_inmemory

First off, Maria told us that she can't be held accountable for anything she tells us as this is not PROD code but after that she said that 12.1.0.2 should be coming this calendar year!  She also made us re-think what ad-hoc report running really is, especially with objects such as Materialized Views existing sometimes to help speed reporting up.
What's the secret sauce of In-Memory?  Both row and columnar formats are in memory at the same time and the Optimizer is smart enough to go to the correct data store to make the queries better.
The new pool in the SGA for this is called "In-Memory Area" and cannot be changed from the size determined at creation.
How do you put objects into memory?  ALTER TABLE foo INMEMORY
The new memory-optimized compression format does a 90 degree pivot of your data.
The table V$IM_SEGMENTS shows what is in-memory.
At this point Maria was prepared to do a demo, but it didn't work which was unexpected and she showed her professionalism by having prepared to talk about the material anyways.
Based on the data available for the OpenWorld 2013, 12c was 100x faster with the column store!
SIMD vector processing vs. Tom Kyte's row by row, slow by slow.
This is a great example of how closer talks within Oracle, and between teams, is getting them better results.  What teams should talk or work together for you?
Another new table: V$MYSTAT
This uses bloom filters which wasn't new for Exadata, but existed in 10g.
The Optimizer is sophisticated enough to apply multiple bloom filters so chances of the query going to disk are greatly reduced because of the data reduction applied originally.
A new vector group by shows up to replace the old hash group by.
This solution gives us extreme capacity and cost effectiveness while not being limited by memory.
Neither is this limited by RAC, as each instance has their own caches for in-memory.
In-Memory uses direct-to-wire InfiniBand speeds for messaging in Exadata systems bypasses all of the OS speeds/interfaces.
If tables are kept in all RAC nodes, the column stores will be kept in sync across the cluster.
This is deeply embedded in the DB, and is transparent to the application as all applications are compatible because the on disk format has not changed.

9:45 AM - General Session: SQL Gone Bad - But Plan not Changed? - Kerry Osborne - @KerryOracleGuy

A reminder about the Exadata E4 conference, sign up at www.Enkitec.com/e4!
The presentation is summed up by "good news, bad news" and "that which doesn't kill us makes us stronger".
This is keyed on the fact that plan hash values (PHV) can change instead of staying static.
Kerry reminds us that plan stability is a hard thing to deal with, first the CBO was introduced, then hints, and then outlines.
There are several different "automagic troublemakers" so you need to keep your eyes open.
What is missing from the PHV calculation?
Live demo!
AWR doesn't capture predicates, neither does statspack or SQL monitor.
SQL Plan Management does work in 12c to capture the plan, but older versions don't work to capture it!
Nobody in the room is using 12c in production…or at least that they would admit to!  I'm concerned!
First time an online question was taken, and even though it didn't get answered, it was awesome to get that out there!

The presentation may have been short, but I thought it was really valuable because it helped to break some ideas people may have had and introduced the idea of not seeing the same thing everything the same way.

11:00 AM - Real-Time SQL Statement Monitoring - Tim Gorman - @timothyjgorman

Tim was a victim of the rooms being broken down, so he lost probably a good 5-10 minutes but he kept it together and ended up knocking it out of the park!  Seeing gaps with ASH and DBMS_XPLAN, which they could each fill for each other, he created a package called ASH_XPLAN!
A good reminder that indexes are disabled on function usage.
An introduction (for me at least) that INDEX SKIP SCAN not a good thing in general just as going REMOTE over database links are typically not good.
This was a lot like Kerry's presentation about bad instead of good, but these are the things we need to face and learn about instead of running from.
Tim also indicated that V$SQL_MONITOR is added to automatically when more than 5 seconds of time has elapsed for the current SQL.
He shared output from SQL tracing, but time isn't included and that's what you should be tuning for.
Another new table: V$SQL_PLAN_MONITOR has a lot of stuff in it including I/O info.
What is the workarea and why does it spill?
Hidden ASH parameters _ash_disk_filter_ratio and _ash_eflush_trigger control how ASH acts!
Tim suggests a 45 day retention of AWR due to monthly business cycle.
I thought it was also brilliant to wrap up his references to a phone book (dated) and Olympics (current) in the same breath.

1:00 PM - Three Approaches to Shared Pool Monitoring - John Hurley - @GrumpyOldDBA

A reminder for the GLOC conference, sign up at https://www.neooug.org/gloc/!
Some key questions that John asks us are what size should the shared pool be and how much is in the shared pool?
This stems from an ORA-04031 error crash resulting from ASM dying in his main database.
Chapters 2 and 4 from Tom Kyte's book and Jonathan Lewis's Oracle Core, chapter 7 are main references for him including most of what Tanel Poder puts up on his blog.
The shared pool was introduced with Version 7 as a metadata cache which was only 3.5MB!
Now the shared pool size has expanded from 9 -> 90 -> 900 -> 9000 MBs.
He gives us several different ways to look at SQL in your system with a good script!
An outline of the difference between ORA-04030 and ORA-04031 errors.
Great visuals to make his point!!
Sub sub pools?  My head is getting dizzy!
These might not be new tables to everybody, but you should know about: V$SGA, V$SGAINFO and V$SGASTAT (ASM info here).
If you see "KGH: NO ACCESS" this is a bad thing.
Another new table: V$DB_OBJECT_CACHE
How to chew the shared pool up?  There are many ways!
Who is using OSwatcher?
Another new new table: V$SQL_SHARED_CURSOR
There were many example SQL scripts which can be setup as alerts in my system!
MOS note 296377.1 for SQL statements with children.
ASM uses space in the shared pool, which is why it was able to throw an ORA-04031 error!

2:15 PM - Five Things You Might not Have Known about the Optimizer - Tom Kyte - @OracleAskTom

It's Tom Kyte.  Come on, I HAVE to go see this one!  I didn't get to because the room was full, and I didn't feel right on taking a seat from the paying attendees, so I went to see Julian Doncheff give his presentation "DBA Best Practices for Performance Tuning in a Pluggable World" to learn something new!

I really liked how he opened up the presentation with quizzing us by throwing oddities at us, that's a welcome change!
Bryn Llewellyn was in the audience talking about the difference between hint returns if it has the choice not to return an error, which was news to me.
Julian shared a query that had a value of 6 Exabytes, which is an Exabyte more than all the words spoken in the human language to date.  I think the query is off by a few factors!
A recent study indicated that 20% of DBAs admit to doing nothing to address performance issues.  The scary thought is that this is only the ones that admitted it!  The same study indicated that the current ratio of instances a DBA manages is around 45:1.  That's insane!
He indicates that it is not possible to upgrade 11g to a 12c CDB, as it has to be upgraded to 12c and then made a PDB.
I thought it was really interesting how CDB and PDB are changing the game, but in several instances Oracle is taking steps back from "support" aspects like 1 log file for up to 254 PDBs.
Did you know that statspack still exists even in this area?
In-Database Archiving - This is a new feature that essentially masks information from being returned.
Don't like the supportability of 12c?  Blame the Commonwealth Bank of Australia!  I guess they did the cost/benefit ratio on aggregating databases, and Oracle was sold!

3:30 PM - Compression and Performance in Oracle Database 12c - Gregg Christman with Oracle

Two compression presentations walk into the Thunderdome, but only one compression presentation can be attended!

First off, I thought it was interesting that Gregg came from a "Competitive Intelligence" group within Oracle that analyzed their competition.  That sounds like a fun job!  Anyways, his presentation went over many reasons for data growth and also that Oracle provides basic compression for free.  Free!  Well, only for bulk loads which is best suited for a Data Warehousing situation but at least it's better than nothing!

The RMAN/backup compression options really strike home for me right now because we've been fighting long running backups for a few weeks now.
Everything Gregg went over was existing prior to 12c, but Advanced Network Compression was created just for 12c and obviously is for network traffic.
He also talked about a Flashback Data Archive which seems odd since flashback data usually ages out within a day or two.
All of these options are 100% transparent to application just like In-Memory was Maria talked about earlier in the day.
There is another free product called Index Key Compression that is included with EE!
There is a tool called ANALYZE INDEX which mimics the different products to run against your system so you can see what benefits you will get.
Lastly, he went over Hybrid Columnar Compression (HCC) for Exadata.

4:45 PM - Evolution of Histograms in Oracle - Amit Poddar

This was difficult because George Srdanov was giving his presentation at the same time, but since his was "Extreme Performance without Extreme Hardware" and already have Exadata, I felt this would benefit me a lot more because histograms are something I'm very weak on.  Amit went on to cover the following in detail:

Selectivity of predicates
Uniform and linear distributions
Zipfian distribution
Frequency histogram in 11g limited to 256 values, 2048(right?) in 12c
Top-N histogram
Equi-width histogram
Equi-height histogram
Hybrid histogram

A lot of this presentation went right over my head and it really wasn't what I thought it would be based on the abstract, as it was a deep dive into the theory behind histograms instead of anything I could take back home to apply in order to address performance issues with a script.  Some of theory I've obviously seen or dealt with, but in particular I wanted to learn about how to determine what types of histograms work best in certain situations and I came away empty on that count.

Sunday, March 2, 2014

Checking into Hotsos!

Through some bumpy weather, I finally arrived in the Dallas-Fort Worth area to below 30 degree weather and snowflakes.  In a t-shirt.  Good thing I'm originally from Michigan and am used to cold weather!

A short ride later, and checked into the Omni Mandalay hotel and then relaxed a bit before going downstairs to pick up my registration badge.  Next it was on to the technical portion of our broadcast where I got to try out my hardware to see how it would present to the attendees (both local and virtual), which went rather well without a hitch!

The best part was actually getting to meet with some of the speakers for a few minutes, some names I've seen, some people I follow on Twitter or my blog roll, others I had not yet met:

Amit Podder
George Srdanov
Jim Czuprynski - @jczuprynski and http://jimczuprynski.wordpress.com
John Hurley - @GrumpyOldDBA and http://grumpyolddba.blogspot.com
Luca Canali - @LucaCanaliDB and http://externaltable.blogspot.com

George had presented last year and was kind enough to take Jim and I down to a local deli that had a bunch of stuff that we could stock up on and we all grabbed a few things after looking around a bit.  While we mentioned something about a return trip later tonight, the hail that came down just as we left the deli made us rethink those plans a bit even though it was just 2 blocks away.  Hail.  In Dallas.

If you haven't registered, it's still not too late as you could enjoy the virtual option and not even leave your house!

Saturday, March 1, 2014

Weekend Learning: Oracle Data Dictionary

This likely will be old hat to many out there in the community, but I found note 1500058.1 in My Oracle Support that deserves a good viewing if you're new to looking for information related to Oracle database and application technology.  Titled "Master Note: Overview of Oracle Data Dictionary" it goes over the difference between the views that are prefixed with ALL_, DBA_, and USER_ plus teaches us about the view DICTIONARY which lists all views in the data dictionary.

For more advanced individuals, this note also has several interesting tidbits about the O7_DICTIONARY_ACCESSIBILITY parameter as well as a change in 12c to the SELECT ANY DICTIONARY privilege which limits access to several items so they are no longer able to be viewed.