Wednesday, March 4, 2015

When too many bind variables is a bad thing

It has hardly been a full week, and my streak has been snapped yet again by missing my post last night!  I will fix that with asking if you know how many bind variables make a SQL statement no longer bind sensitive?  Give up?  Obviously the title of this MOS article "SQL Containing More Than 8 Bind Variables is not Marked as Bind Sensitive" from note ID 1983132.1 gives the answer away, but the answer changes for when you try to take advantage of the 12c Adaptive Cursor Sharing option so check out the article!

Monday, March 2, 2015

R12: Revenue Recognition Execution Report signal 6

Did you know that in R12 your Revenue Recognition Execution Report can end up generating an error message of “was terminated by signal 6”?  We had that exact scenario happen to us when the business ran multiple copies of this report for the same org that started at the EXACT same second!  We did not find any hits on MOS for a resolution, so we asked the business to run them again but in sequence instead of parallel and they both completed successfully.  Searching on MOS does not yield great results, but the closest hit is note ID 1631873.1 which matches some of the error details we saw at that time, but the resolution for it is all wrong as we did not need to patch:

emsg:was terminated by signal 6
Enter Password:
#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
#  SIGSEGV (0xb) at pc=0xb740156c, pid=9281, tid=3031722560
#
# Java VM: Java HotSpot(TM) Server VM (1.4.2_14-b05 mixed mode)
# Problematic frame:
# C  [librw.so+0x75856c]  hshuid+0xe0
#

Sunday, March 1, 2015

Weekend Learning: Gathering data for InfiniBand issues

Have ever had to submit InfiniBand diagnostics to Oracle Support?  If you have not, did you ever wonder how to do it?  Check out MOS document "SRDC - EEST Infiniband Connection Issue on Linux" note ID 1683910.1 to get more details this weekend on what needs to be done to pick up some more things for your toolbox!

Weekend Learning: Introduction to technical details about SLA

While finishing up my presentation and whitepaper for COLLABORATE so I can present in April, I found the perfect article for Weekend Learning, saved a draft, and then promptly forgot to post it last night!  My consecutive posting streak is broken!  Oh well, I am going to post twice today and then know next year on February 29th that I have posted 366 days in a row!  Maybe it has something to do with my last post being the 666th post that was posted too!  Anyways, on with the show!

The MOS document "SLA: A TECHNICAL PERSPECTIVE OF THE AP TO GL RECONCILIATION" from note ID 605707.1 has a lot of good reading about how SubLedger Accounting (XLA) works with R12 so that the AP module no longer retains the accounting information.  Walk through the tables that retain all of the data, so that you can learn about the interconnections between modules that might need alerting!

Friday, February 27, 2015

Weekend Learning: Getting the most out of My Oracle Support

Since I review a lot of articles from My Oracle Support and recommend that you add them as favorites, this post from Steven Chan's blog reminds us all about the Hot Topics e-mail that goes out every day and how it can be modified so you are notified when your favorited MOS documents are changed!  Do not miss out on this easy way to stay up to date on MOS articles that are relevant to your current technology stack, or even about where your technology is going in the future!!

Thursday, February 26, 2015

Thursday Twitter - 2/26/15

The last few weeks I have been sharing 10 tweets with you, but after the RMOUG Training Days conference last week I want to share just one link with you to open up a whole world of tweets that I know you will find interesting!  Follow the link for #TD2015 learn a lot, and make a lot of connections by adding the presenters to your timeline!

Wednesday, February 25, 2015

OS: Missing backout data and unable to remove patch

Do you want to add more tools to your toolbox?  How much do you know about OS patching?  Would you like to know how to remove an OS patch?  Read up on MOS document "Unable to remove patch due to missing backout data" from article ID 1984126.1 to learn the commands for backing out a patch, and how you might be able to salvage a bad situation!

Tuesday, February 24, 2015

11.2.0.4: Parse time can be increased for same query on 11.2.0.3

If you have been in the Oracle space for any amount of time you will know that a new version or the latest files can actually introduce problems, but even knowing that it is surprising to read MOS article "Increased Parse Times for Queries Using Subqueries Containing Multiple OR And AND Statements on 11204 Compared to 11203" from note 1980761.1!  Not only do we learn what is going on here, but we get to learn a few ALTER SESSION commands that we may not have otherwise had to know about plus this is a good warning that your performance may be suffering for no other reason than you are on the latest patchset of 11gR2!

Monday, February 23, 2015

R12.1.3: Cannot unreserve PO to allow changing charge accounts

While you can modify the amounts in the Oracle Purchasing Forms, the document "Unable To Un-reserve PO’s From Buyers Workbench To Allow Changing Charge Account" from article ID 1957711.1 shows us that the amounts cannot be changed via the Buyer Work Center due to a validation check on the html page and what patch will resolve the issue.

Sunday, February 22, 2015

Weekend Learning: Improve hardware support from Oracle

Did you know there are ways you can get better hardware support from Oracle via MOS and the Proactive Hardware Services portal?  Visit the page "Improve Your Hardware Support Experience - Part 2" to find out more about this, visit Part 1, ad get the latest blog site for MOS too by visiting the most recent post on this blog site!

Saturday, February 21, 2015

Weekend Learning: Notification Mailer and Java error stack logging

Regardless of what EBS version you are on, you can follow the directions in MOS article "How to obtain Java Error Stack when configuring Workfow Notification Mailer using OAM" from note ID 1348284.1 and then even extend it further by designing some automation around the log files if you want to leave the logging enabled so you can proactively look for problems.

Friday, February 20, 2015

Weekend Learning: PeopleSoft HCM, upgrading to 9.2 and staying on PeopleTools 8.53

Did you know that the old upgrade source images for PeopleTools 8.53 are no longer available?  Find out more by visiting My Oracle Support note "Is it Possible to Upgrade to 9.2 (not Using Image 9) and Remain on PeopleTools 8.53?" from document ID 1970335.1 to see how you can avoid from upgrading to PeopleTools 8.54 if needed.

Thursday, February 19, 2015

RMOUG Training Days 2015 - Day 2 + Me...Twice!

8:30 - John Strempek - Keeping Hyperion Financial Management Healthy: How to Perform Maintenance on Your Applications

I felt really bad for John since I was the only person in the room for a bit, and luckily another person did join us, but he did come down to us to do a sort of roundtable which was an interesting switch up.

DRM / EPMA - Oracle applications for maintaining HFM data.
Good practice to have business change their things in DEV.
Approach any changes as a project, and make sure it is not done in a vacuum.
Checklist Manifesto - A suggested reading
Member lists is something I am not familiar with, so I will have to look it up when I get home to see if the business has access to this!

9:45 - Fuad Arshad - Exadata Oracle 12c New Features

A vision when Exadata was created, originally called SAGE (Storage and Grid Environment).
Platform is almost never a pure OLTP because there are always batch processes that have to run.
Maximum Availability Architecture to help prevent outages.
KVM, PDU, ILOM, CISCO switch - With older V2 Exadata platforms.
Plugging Exadata into OEM via PDU can help plan for data center power usage.
Hardware improvements from Intel chipsets are low when compared to Exadata software innovations so there is a natural benefit gap between those on Exadata and those that are not.
Finally added support for ACFS file systems which are ASM based for 10.2 databases so they can go to Exadata.
Version 12.1.1.0 of Exadata software adds support for 12.1 (12c) databases, and it added cell to cell data transfers and IORM support for multitenant DBs.
Exadata Storage Cell: MS, RS, CELLSRV, CELLOFLSRV 11.2.x, CELLOFLSRV 12.1.x
Version 12.1.2.1 of Exadata software introduces massive DB optimizations: 3x faster Direct to Wire InfiniBand Protocol, 5x faster Pure Columnar Flash Cache, 3x faster JSON and XML analytics with storage offload.
Exadata can now be virtualized with isolation of the CPU, memory and OS.
Upgrade to Oracle Linux 6.6 without a reimage!
Now instant server death detection because they LOOK instead of WAIT.  Awesome proactivity!!
New DBMCLI service dbserverd.
Patching note 888828.1 in MOS.
If you patch your own Exadata, do the pre-checks and resolve any warnings beforehand.
Get the EM 12c Exadata Plugin but you need EM 12.1.0.4 installed first!
MOS note 1070954.1 for checker.
Orachk Collection Manager not just for Exadata and in MOS document ID 1268927.2.

11:15 - Bobby Curtis - Extreme Replication: Performance Tuning Oracle GoldenGate for the Real World

Quick history of GoldenGate: Change Data Capture -> Oracle Advance Replication -> Oracle Streams -> Oracle GoldenGate.
Integrated Extract introduced in GG 11.2.0.2 for Oracle source DB only with 11.2.0.3 or later DB version and it works with the logminer.
Logminer server: Reader, Preparer, Builder, Capture, then it sends the capture off to the trail file.
Integrated Replicat introduced in GG 12.1.2 for Oracle DB only with 11.2.0.4 or 12.1 or later DB versions.
Inbound Server: Receiver, Preparer, Coordinator, Applier.
GG performance areas: GG (LAG, REPORTCOUNT), DB (AWR, ASH, UTL-SPADV, TRACE), and host (mpstat, vmstat, iostat, strace).
LAGINFO, LAGREPORT, LAGCRITICAL are Manager processes for monitoring latency and goes to ggserr.log for some reason.
Streams performance advisor, in $ORACLE_HOME/rdbms/admin/utlspadv.sql but must be installed as GG user.
There are a bunch of run time and configuration views for GG performance tools like V$GOLDENGATE_CAPTURE or DBA_APPLY.
MOS health check 1448324.1.
Go from GG to DB to host on the source for troubleshooting first before you go look at the target at all.
Case Study!!  Diving deep into a problem!
STREAMS_POOL_SIZE should be 1G and then add 25% per each additional replicant or extract.
On the target side indexes can cause slowness in applying processes.
TCPBUFSIZE and TCPFLUSHBYTES and use ping to find the average round trip time.
Awesome picture breaking down network tuning after getting your ping results!


12:15 - Various - Oracle ACE conversations

At lunch I sat down at the RAC and Database Internals table, hosted by Riyaj Shamsudeen (@RiyajShamsudeen) and we were joined by Marc Fielding (@mfild), David Fitzjarrell (@ddfdba) and Iordan from yesterday's presentation!  Some interesting conversations, and I was glad I picked that table because David and I had several very good conversations.

1:30 - Rusty Schmidt (@TheOracleEMT) - Analyzing Oracle Workflow Data for Increased System Performance

Hey, look at that!  It is me!  I'll brag a bit and say that I ended right about when I wanted to, had several people engaged asking questions, and had a couple Oracle ACEs come up to me to talk about an aspect of the presentation afterwards too saying that it was interesting and kept their interest!  Really awesome to hear, and hope that I get some good scores from the other people in the crowd too.

2:45 - Janis Griffin (@DoBoutAnything) - Advanced Oracle Performance Tuning Tips

Tuning is hard, who needs to be doing it and what expertise do they need?
How to identify?  User/batch job complaints, queries with most I/O, queries consuming CPU, response time analysis.
Lots of tables to find this info like V$SESSION, V$SQLAREA, etc.
V$ACTIVE_SESSION_HISTORY gets flushed to WRH$_ACTIVE_SESSION_HISTORY
Get baseline metrics and collect wait event information.
Get the execution plan too!  Use EXPLAIN PLAN, V$SQL_PLAN, DBMS_XPLAN, TKPROF, or historical plans from AWR.
There are new functions in DBMS_XPLAN with 12c!
Adaptive plans with 12c introduce new columns in V$SQL of IS_RESOLVED_ADAPTIVE_PLAN and IS_REOPTIMIZABLE which will get read the next time it gets executed.
OPTIMIZER_ADAPTIVE_REPORTING_ONLY is a new parameter to help drive this.
Passing in the +adaptive value into DBMS_XPLAN can have the explain plan include more data which you may want included such as ignored plan rows.
Reminder about using SQL baselines if your 3rd party code cannot be hinted.

4:00 - Viswa Vadlamani (@ViswaVadlamani) Rusty Schmidt (@TheOracleEMT) - SOA Implementations and Design Decisions: A Support Perspective

Unfortunately Viswa came down with a very bad cold preventing him from coming out to Training Days, so I chipped in and tried to cover it as best as I could! I think it went rather well because the crowd on the last day was pretty interactive and they seemed to get a lot out of it by their comments!

Now, I'm ready to board to leave Denver and return to sunny Phoenix!

Wednesday, February 18, 2015

RMOUG Training Days 2015 - Day 1

8:30 - Biju Thomas (@biju_thomas / Oneneck.com / Bijoos.com) - Oracle Database 12c New Features for 11gR2 DBA

First a review of 12c, as it came out in June of 2013 and 12.1.0.2 came out in July 2014 which introduced DB In-Memory and the READ privilege which is really READ ONLY as it cannot lock tables via SELECT.
Changes in temporary undo, new parameter TEMP_UNDO_ENABLED.
Another new parameter is APPROX_COUNT_DISTINCT which gives you an approximate number of rows.
File move in 12c has 1 step, without outage ALTER DATABASE MOVE DATAFILE X TO Y, but can only move 1 at a time, can move from ASM to non-ASM and vice versa.
New background process LREG for listener registration, and PMON had this responsibility in 11g, so do not kill off the LREG process because you do not know what it is.
There can be 100 DBWR processes, was 36 in 11g.
PGA_AGGREGATE_LIMIT, set by default.
ENABLE_DDL_LOGGING existed in 11g but changed in 12c and needs additional licensing now.
adrci has a new command SHOW LOG.
Stats are automatically collected during bulk loads!
New security features regarding USER$ table in 11gR2, SPARE6 shows last login time for the user.
CASCADE clause for TRUNCATE, works with some conditions but will remove children downstream.
More ONLINE operations: dropping index, dropping constraint, moving tables/partitions, marking indexes and columns.
The VARCHAR2 column is now bigger but it requires the parameter MAX_STRING_SIZE enabled with DB in UPGRADE mode, and you cannot go back plus it will invalidate views and MVs.
New clause in SELECT for row limiting clause FETCH OFFSET.
Invisible columns, catch is that column numbers/order changes when this becomes visible again!
DBMS_QOPATCH is a queryable patch inventory, learn more at MOS note ID 1530108.1.
Also check out MOS note ID 1585822.1 to learn about datapatch which is used for post patch scripts.
TRANSFORM= DISABLE_ARCHIVE_LOGGING, VIEWS_AS_TABLES for Data Pump.
With db12c a new option (TABLE) to not require a SQL*Loader control file.
Describe is now in RMAN and you can run most SQL statements in RMAN!
SYSBACKUP admin role so you can replace SYSDBA in your scripts.
RECOVER TABLE, biggest RMAN feature, option of NOTABLEIMPORT doesn't import but does create export dump file.
RMAN catalog needs EE but covered under the Infrastructure Repository License.
For more details you can look in the new features guide, an OTN interactive reference, or the 12c Oracle Learning Library.
REMEMBER: Extended Support ends for 11g in 2016!!

9:45 AM - Maria Colgan (@db_inmemory or @SQLMaria) - General and Keynote Session

I actually did not attend this as I have heard Maria's excellent talk before at Hotsos, and I had a very important errand to run, but you can also refresh yourself with the launch event post I put up last year too!

11:15 AM - Alex Gorbachev (@alexgorbachev) - Anomaly Detection for Database Monitoring

What is the motivation here?  Unusual metric values vs prior observations.
Typical datasets: time-series metrics, sampled session states, sql execution metrics, IO metrics per disk.
Traditional: human driven threshold settings, different thresholds based on timeframes, load (backup) but have to be set manually and regularly reviewed.
Instead you should use standard distribution metrics +\- 3 stdev
With this it is important to avoid false positives.
Bollinger Bands (stock trading) graph, something to investigate more!
USER_CALLS_PER_SEC_METRIC_VALUE - What is this?  I need to find out!
Averages hide individual measurements and introduces a skew.
Histogram buckets in V$EVENT_HISTOGRAM and DBA_HIST_EVENT_HISTOGRAM are default and not the greatest, so try to build a DIY solution from ASH that is tooled to your installation.
Machine learning: supervised algorithms, unsupervised algorithms - again, another great thing to research.
Kale stack by Etsy, Skyline, Oculus, Redis, Carbite (Graphite Carbon) to Agent - not for Oracle but not difficult to write…this had me thinking "whhhhhaaaaatt?!?" when he said it because I know I could not do it!!
Oracle Data Mining demo was really good, using features in Oracle SQL Developer to easily make some analysis which would be easily repeatable too.
DBA_SYSMETRIC_HISTORY - Another thing to research when I get home!

12:30 PM - Panel of @KerryOracleGuy, @csierra_usa, @AlexFatkulin, @riyajshamsudeen, and @mfild - Exadata SIG

Even though a lot of time here was spent trying to convince one person how awesome Exadata is and what benefits his company could get out of it, it was still a very good SIG session hearing what other types of Exadata installations people have, or their experiences with Oracle Platinum Support.

1:30 PM - Dean Halbeisen (@DeanHalbeisen / www.oracle.com/optimizedsolutions) - Be a Hero with Your DBA: Database Performance Tuning for Admins and Architects

This was a session I was REALLY looking forward to, but not long into it I had to look up the abstract because it did not seem like a normal presentation and that is when I saw that this hour was for exhibitors!  No wonder why it seemed like a sales presentation!!  That is not to say I did not get a thing or two from it, so yay!

As with most problems, you need to narrow down how much data you are looking at for instance in an AWR report.
Automatic Diagnostics Database Monitor ADDM - I have a feeling I know what this is, but I also want to research it just to make sure this is not something I have overlooked.
Dean had an overview of AWR and statspack as major tuning tools and introduced flash cache to convince the audience that they need it.  I cannot dispute that, because we have Exadata and flash cache which has worked wonders for us!
Init.ora parameters DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE need some investigation back home so I can be up to speed on our settings!
To learn more review the DB Concepts Guide (Chapter 14) and the Performance Tuning Guide (Chapter 10).

2:45 PM - Graham Wood (@OracleGraham) - Architecture Review by AWR Report

There are usually 3 reasons for performance problems: database not being used as it was designed to be used, application architecture/code design is sub-optimal, sub-optimal algorithm (BUG) in the DB.
Change is required and is scary!
Have to look at the big picture to get order of magnitude gains, which is a great reminder about what needs to be investigated and when!
Go to oracle.com/goto/oll/rwp for some Real World Performance videos!
Graham suggests a good measurement is 10 active sessions per core, which means you will need to be active with your connection management to help performance.  Great point!
GC cleans up after Java processes, but not the DB sessions/cursors left behind!!
He showed us an AWR report where there were: 10 logons per second, 900 rollbacks and 1350 transactions per second, lots of _ parameters (which makes your DB a unique snowflake hard to support), cursor_sharing = false (which Graham calls evil), and a DB_block_size value of 16K instead of the default 8K block!
Did you know that open_cursors is how many cursors a session can have open at one time?  This helps to hide cursor leaking bugs!
Optimizer_index_cost_adj is another interesting parameter that can cause problems too!
Latches and mutexes pop up when you have CPU problems, which are a symptom not the cause!

4 PM - Iordan Iotzov (iiotzov.wordpress.com) - Managing Statistics of Volatile Tables in Oracle

Distribution of data and all about reducing volatility, but that needs you to get proactive by thinking about DB design (does it have to be stored in the DB?).
Bigger tables but they are less volatile.  For instance, creates a view of just active records.
I thought this was an interesting concept, allowing a table to grow by large scales for soft deletes later but as I asked afterwards this is a custom application and not an EBS, CRM or other standard Oracle application so I do not know how much application it has for most people.
12c introduces Adaptive Execution Plans!  Are you up on this?
377152.1 - Automatic Statistics Collection
Long term volume is unknown so this is difficult to know how to handle.
Bug #12897196 is something Iordan has talked about with Oracle to try and get DBMS_STATS not to lock, and why he created his own package JUST_STATS which gathers the stats and does NOT lock.

Tuesday, February 17, 2015

RMOUG Training Days 2015 - Day Minus 1

The flight was a breeze (compared to some people in North Carolina that did not leave until late in the day!), had a great ride in compliments of the volunteer army from RMOUG, checked in to the conference just fine, and then had a hiccup visiting the Which Wich next door that was not staffed for a lunch rush from several conferences at the same time!  I ended up a few minutes late to Chaitanya's presentation because of that!!

1:00 PM - Chaitanya Geddam (@GeddamChaitanya) - Deep Dive: Extreme Performance, Resiliency, and Monitoring of Improved GoldenGate 12c

I really liked this presentation because it brought me up to speed on a technology that I do not use, but one that we do have in our environment at work so I need to understand it a lot better than I currently do!
Recommends data pump for resiliency
General GG architecture - He had very good graphics and explained it well!
RBA address - This is a new type of address to me, which is like an SCN in the DB.
2 new types of replicats with 11g, coordinated (understands barrier transactions) and integrated
GG doesn't use SCNs for initial extract
11.2CC vs 11.2IC
Parameter files, obey scripts, INCLUDE files, custom shell scripts
Service resiliency
Bundled Agents use v4, not v5
MOS notes: 273674.1, 1298562.1, 1448324.1
What I thought was interesting is there are lots of ways to monitor GG but no single unified direction for the product!
Plugins don't understand VIPs and if 1 node is down it doesn't understand other nodes are working!
How to measure for trend analysis?  Heartbeat Table Monitoring found in a MOS note so it looks like I am going to be digging around MOS when I get back home.
Is the table DBA_COMPARISON_SCAN just for GG?  Time to find out!

3:15 PM - Riyaj Shamsudeen (@RiyajShamsudeen)- Deep Dive: RAC 12c Optimization

It is clear from Riyaj's presentation that he knows, like REALLY REALLY knows, the Oracle internals (probably why he has www.orainternals.com huh?) and while at some points it was too technical for the audience, I enjoyed how in depth he was digging into the RAC transactions!!  I do not know if I will be digging that far down, but at least now I know it is possible and maybe I will think of a way to use this information in the future!
LMS is the workhorse of RAC, but remember it talks to LGWR too so that is just as important to tune!
I saw him call a DBMS_ROWID package that I am not familiar with so I will be looking that up when I get home too!
Database and instance transaction commits are different!  That is a key point!
DBMS_CACHEUTIL was another package that I need to research when I get home!

The importance of NOT looking at the top wait events in a RAC environment was the biggest take away for me, and it gives me more items to research when I go home!  Now I have another technology goal to add, and that is just after a couple sessions today!  Imagine how many I will have tomorrow after a really FULL day of the conference?!?