Steve Karam

Obligatory Melodrama

No matter how much time goes by I still remember it. The day my database was crippled beyond reckoning. That moment when I saw my hopes for a bright and shining future with my database spill through my fingers like so many cracker crumbs falling on a clean and well pressed pair of slacks.

It was the day I was told that we had to ditch AWR/ASH for a downgrade to Standard Edition.

There Will Won’t Be Blood

You know, it’s funny. I got started on Oracle 7.3 (I know, I’m still a whippersnapper to many of you), back when men were real men, women were real women, and sqlplus / as sysdba was connect internal. We didn’t have no Statspack, we didn’t NEED no Statspack. We ran UTLBSTAT and we liked it. And when we didn’t like it, we ran UTLESTAT.

No, really, it sucked. But at the time, it wasn’t that bad. We seemed to find what we needed (most of the time) in the tools we had, and if we couldn’t find what we needed to do we’d drop back ten and punt (meaning we’d rewrite queries until our fingers bled).

Then Oracle 8 came and things still sucked. But then something magical happened. Oracle added an i. In case you don’t know, that ‘i’ stands for cloud internet. In version 8.1.6 we got Statspack. And suddenly understanding our databases got a little cooler. We could get the big picture, a real top level view of what was going on in the instance as a whole. It gave us hourly snaps that we could use to see how our database progressed throughout the day/week/month/year/ORA-01653. The wait interface was lacking, the details sketchy or missing, but it still worked. It made a lot of things easier to understand.

With Oracle 9i it got a little better. But then Oracle worked their magic again and added a g. In case you don’t know, ‘g’ stands for cloud grid. With Oracle 10g we got AWR and ASH. Whether we paid for it or not. And we got hooked. It wasn’t just a snapshot tool, it’s built into the architecture. It grows, it evolves, it slices, it dices! On sale today, and mind that you don’t disable it because that feature’s protected by license (I kid, I kid, they fixed that). And ASH, don’t even get me started. Rolling session-based performance snapshots? Near realtime performance tracking? Be still my beating heart.

So naturally when you’re losing it, it feels like you’ll never make it in this cruel, cruel world. It’s hard to have something so convenient taken away. But there’s still plenty of options out there for you.

Take in the Views

First of all, Oracle’s own DBA_, V$, and X$ views are becoming more and more detailed and easier to query by the version. Show of hands, how many of you still query v$session_event instead of v$session just to get an event name? There’s a ton of good information to be gleaned from Oracle’s stock views and packages. From DBMS_XPLAN (which gets better every version) to time model statistics and beyond, the metrics are getting better.

Just make sure they’re the metrics you’re looking for.

Oracle’s views are just a small piece of the performance tuning puzzle, however. Don’t forget that there is a world of events, tracing, and OS level analysis you can do. Just because we have these neat high level tools doesn’t mean you should forget the low level treasure troves. Sometimes the holistic tools don’t fully expose the details.

Snap It Up

Seriously guys, Snapper 4 is the coolest thing since Snapper 3. Using Snapper, you can get ASH-like session runtime details with manually controlled snapshots of V$ views by running a single script. Tanel Poder can work a SQL prompt like no other, and Snapper is his magnum opus.

You can download Snapper by visiting his page (as he says, download it, don’t paste it) and run it immediately. With or without ASH, it’s well worth running for in depth details on your instance runtime as a whole or targeted to a single SID/user/whatever.

Sashay Into the Room

Another option for more long-term ASHishness is S-ASH by Kyle Hailey and the ASH Masters crew. This pack of scripts actually lets you create a repository database for your statistics and gather runtime data just like the real thing. You’ll be able to get all your session details along with time model statistics. It works without a Diagnostics Pack license and can even work against Oracle 9i.

And if you’re really missing those stacked area charts where you can visualize that poor sweet CPU horsepower being crushed mercilessly by the evil bad User I/O and Concurrency, you can even use ASHMON (another ASH Masters product) to get your graphs back.

Ignite Your Performance – Free!

Confio Ignite is a highly popular monitoring software with response time analysis to help locate tricksy bottlenecks. But if your company pursestrings are a little tight (after all, cheapskates won’t even buy you Diagnostics Pack) Confio is gracious enough to offer Ignite Free.

While it doesn’t have the full drill-down capabilities of the full version, it IS free and therefore a bargain. It also monitors SQL Server, DB2, Sybase, and has a version for databases on VMWare.

The Web

There is a plethora of advice out there for nearly any version of Oracle you may be using and a decent portion of the problems you may encounter. And if the problems you face can’t be found anywhere on that lovable series of tubes we call the Internet, there are forums and discussion groups and social networks full of people who love to help. And that’s just aces. (ba dum bum bum)

But…

A learned man came to me once. He said, “I know the way, — come.” And I was overjoyed at this. Together we hastened. Soon, too soon, were we where my eyes were useless, and I knew not the ways of my feet. I clung to the hand of my friend; but at last he cried, “I am lost.” - Stephen Crane

Remember that actions have consequences (both good and bad). Be sure you test before you tune, and never ever blindly make changes to a production environment. Why, who knows where the advice has been. It might be dirty.

Good luck, and happy tuning!

The post High Performance Tuning Tools appeared first on Steve Karam :: The Oracle Alchemist.

This entry is part 19 of 19 in the series Adventures of Ace, DBA 

It’s the only explanation!

Related

Ace has dealt with problems before…

Default blame accepter?

The post Changes (Ace Comic) appeared first on Steve Karam :: The Oracle Alchemist.

This entry is part 5 of 5 in the series Grow Your Career 

All work and no play makes us dull DBAs.

Being a DBA can be a really thankless job. From Jr. Shutdown Immediaters to Sr. Rockstar Oracle DBA ACE OCM PMP LOL A+ BBQs, we’ve all trudged into work at some point in our careers only to find that we have three impossible tasks, four humiliating tasks, and no less than seven “why is the database causing this query to be slow we haven’t changed it in two years” tasks. Yet we keep on doing it, day after day, for love of the data. Or something like that.

I wrote an article a while back about galvanizing the DBA team into action and making them stand out a bit more in the corporate world. In the end, a lot of that boils down to making sure the DBA team looks less like a cranky support group that constantly needs disk space and expensive Oracle licenses and more like a team that wants to work as a key part of bringing new products to market (and thus earning money). But even then, it can be a tedious, mind numbing task at times.

So how can we spice up the DBA experience and the IT life in general?

Gamification, of course

Everybody loves games. More to the point, people like being competitive and reveling in a sense of accomplishment and achievement. The idea behind gamification is that many things in the day-to-day non-gamified world can have principles of game theory attached to them. For the most part, the current trend of gamification is a corporate strategy that can be used to further engage a customer base (and is predicted to be a $1.6bn market by 2016 as a result). But even from a departmental or team point of view, it can have great benefits to increase morale, productivity, and accomplishment. And with a little luck and project management, it can be used to solve critical issues for your business.

What gamification is:

• Making tasks more like missions. Everything from tuning a query to backing up a database can be a mission. Instead of becoming yet another ‘requirement’, tasks can become goals.
• Providing a system of Achievements, Badges, Rewards, etc. Modern games are nothing if they don’t have some sort of achievement system that allows players to share their accomplishments across their social network or game community. Within a business, that community can be the IT group (like the DBA team), the department as a whole, or even business-wide.
• Competition! Pitting people against each other in a fun, non-threatening way is a great way to improve morale and encourage growth of skills outside of the standard “do this and do it well or you won’t get a raise” paradigm.
• Leaderboards, which are good for tracking the competition and achievements but are also useful for status and review purposes. What’s the point of earning achievements if they aren’t tracked, or competing without scores?

What gamification is not:

• Strapping your DBA team into slingshots and giving them an achievement for knocking over the most developers.
• An IT version of Thunderdome where two DBAs enter, and one DBA leaves.

Let the Games Begin

So it sounds great in theory, but what can we possibly do to bring it into our DBA practices? As with all answers regarding database management, it depends. If you’re a consulting organization, gamifying is simple because you generally are performing work and solving problems for a wide array of clients. You track team members’ status and customer satisfaction anyways because it is part of the business model. For a DBA team inside an IT department at a major corporation, it can become a little more difficult. So let’s look into some of the ways you can make games part of your actual work.

Backup and Restore Challenges

Setting up and monitoring a good backup and disaster recovery solution can be a serious pain in the neck. But everyone would agree it is absolutely vital to your company and your job security. But even worse than setting it up and monitoring it is having to actually put those backups to use in the case of a critical failure. Whether the problem is a single corrupt data block or an entire database ground to dust, having to actually perform a restore when the heat is on and the managers are circling and all your plans for the weekend are being piled up in the “it’s the thought that counts” bin like so much garbage… well, it sucks. There, I said it.

Maybe that (with a hint of staffing problems and financial woes) is why companies are failing to adequately test their DR plans. That’s a highly dangerous practice, right there with giving developers DBA access to production and typing drop database commands and saying just kidding while you fumble for the backspace key. What is the point of organizing and configuring a complex backup solution if you only plan on testing it when disaster strikes? I can’t count the number of times I’ve worked with clients who said “oh, we needed that file?” or “well, the logs all looked okay” as their hopes for database recovery dropped like their company’s stock price after a three day outage.

So test your backup and recovery plan. But you can make it more fun by running simultaneous tests or presenting interesting and sometimes horrifying cases to the team when they are asked to perform restores. In this article I wrote about the Backup/Restore Workshop day when I worked for Oracle University, and how one student took his preparations to a whole new level, ultimately giving me the old what for, so to speak. Make a competition out of it. Challenge your DBAs to recover from sticky situations and to do it quickly and efficiently. Engage other teams too (such as application server administrators), who ultimately should be part of backup and recovery testing. Doing so invites the teams to improve their speed while also giving the business a good idea of where there may be bottlenecks.

Questionable Query Quality

Not only is this topic worth 192 points in a triple word score in Words with Friends, it’s also a serious issue that almost every DBA/Development group has to face. Writing queries can be a very tedious task to the point where many groups skip that whole tuning thing and go straight for production, guns blazing. This is, of course, a bad idea.

At one company I worked for we came up with a really interesting, fun, and productive way of tackling this issue: we pitted the query developers against each other in battle. Developers would requested to put a comment in their SQL code with their name so we could 1) track who wrote it, and 2) hold contests periodically. So when someone did “SELECT /* Steve Karam */ count(*) from RIDUCLOUSLYHUGETABLE;” we knew who it was and where to go to get it fixed.

That can make for some seriously awesome competition. Each month, quarter, release, etc. you can host ‘award ceremonies’ honoring the best and worst of the time window. Grab explain plans for all the queries and come up with valuable metrics to track like:

• Most readable
• Best use of indexes
• Least blocks touched
• Most convoluted query
• Least likely to make it to production
• Most likely to result in concurrency waits

It’s all in good fun. And it makes query reviews almost bearable. In fact, I’ve noticed in cases where I used this (whether in real environments or in training sessions) the participants were not embarrassed or angry about writing a bad query. They wanted to figure out how to make it better so they could be in a different acknowledgement tier the next time around.

Professional Growth Challenge

While there are a ton of competition ideas within the DBA team (best use of indexes, most SQL profiles used, fastest mouseslinger in the Grid) there is also that of professional development. How about a competition on who can get their OCP certification first? Or one of the many Oracle Expert certifications out there? Heck, even a contest to see who can rack up the most obscure or highest count of certifications from anywhere in the industry would be fun and potentially enlightening. Not only will the team benefit from the certifications and the process to get them, but it makes the team look better as a whole when everyone is clamoring to get professionally acknowledged the fastest.

If you have a really top notch team, then challenge them to get their OCM. While this will require some company backing to keep it fair since the OCM is an expensive and time consuming test to take, it definitely has its rewards. It is a great way to encourage professional growth within your team using pre-built metrics.

D&D Tabletop Drills

My current company had a pretty cool idea as the result of a stability and process review initiative. We started doing tabletop drills once per quarter. For these drills we assembled a team of moderators (usually one manager from each IT group) that came up with a couple scenarios of problems that can occur in our production systems. A ‘player’ is chosen from each team to act as representative: one DBA, one applications administrator, one system administrator, etc. And we would get everyone in a room, surrounded by observers, and act it out.

The idea is to act out the response to the crisis exactly as we would if it were really happening. Sometimes input is needed; for instance, if they discover that this scenario includes application servers being down, they can ask how many are down. Moderators answer questions and keep track of the progress to gauge how the activity goes as a whole, whether SLAs were acknowledged and met, and what can be improved. Following the activity, observers, moderators, and participants have a discussion about the scenarios as a whole and what action items should occur as a result of the findings.

I like it because it’s like the IT version of Dungeons and Dragons. We’ve got game masters and a group going through a perilous journey through a broken and charred land that was once a shining beacon of production goodness. Heck, we could even include dice next time. “How many DBs are down?” “Roll 2d6″. Critical hit, go to DR!

What’s In It For Me?

Even though there are built-in rewards to many challenges — better queries, faster backups, certifications, what have you — people still want to have something a little more, particularly in the gamified experience. So what can you offer up your team or colleagues for a job well done?

Achievements: One of the cornerstones of game theory, achievements are fun and fulfilling. Use stickers, logos, Portal shoutouts, even badges inside the company intranet to show accomplishments. I personally think it would be neat to see “Achievements” next to Job Description, Contact Information, and Org Chart in my company portal filled in with things like “Onward and Upward – Upgraded three databases in a two week period” or “Did I Do That? – Fixed a fat-finger mistake with flashback or restores”. In fact, I would go out of my way to do amazing things just to earn more achievements.

Seminars and Conferences: Maybe the DBA with the most accomplishments gets to go to Oracle Open World this year. Or perhaps the developers with the best (or worst, to be more logical) queries get tickets to the next big Java conference. Whatever the case may be, I’ve noticed a growing number of companies unwilling to send their teams to conferences. In some cases they believe them to be merely a chance for the DBA (or other professional) to find another job. In others, they see it as a waste of money compared to other training. While ultimately these are horrible reasons to deny technical conferences to your team, perhaps using achievements and fulfillment of goals as a way to send people will improve performance as well.

A break: You could always give the winner a day to work from home (or off, you taskmaster you). Or for the thrill seeking types, the ‘losers’ have to give the winner a week off on call by taking it for them.

Will Turner: What are they wagering?

‘Bootstrap’ Bill Turner: Oh, the only thing we have. Years of service.

Company Certifications: Why not create a set of certifications within your own organization? As someone who has been in education and publishing for quite a while and as one of the lead writers of the Oracle 10g Certified Master exam, you can trust me when I say that certifications are an outstanding way to bring job satisfaction and a feeling of recognition to an IT team. Come up with certifications within your company that can be awarded for exemplary work or completion of certain achievements. Use those certifications during review time as a concrete measurement of success within the company. Make it possible for anyone to get certified in-house (e.g. developers can get in-house DBA certifications) to keep the career paths open at your organization. There are many ways you can acknowledge people for hard work; a certification they can put on their company profile, their emails, or even their resume can go a long way toward that goal.

Let them destroy something: This one sounds funny, and it is… but next time you migrate from one server to another or decommission an environment, let the winners of various competitions be the ones to type and press enter on that DROP USER command or DROP DATABASE. A company I worked for a long time ago did this. We finally moved off of an old and painful architecture. Once the database was moved over and the old one set to be deleted, we allows developers to drop the tables they hated the most and gave the honor of dropping the main schema to our lead (and most overworked) customer service representative. It was a great way to celebrate the occasion.

Conclusion

Some of these ideas may seem far fetched or downright silly. But you have to track employee progress, tasks, and status anyways. Why not do it in a way that gives everyone a sense of competition and accomplishment?

Gamification doesn’t have to be strictly for companies to rope in consumers. By using it within teams it can also improve morale, grow employees at a professional level, and give everyone something to strive for.

The post Gamifying the DBA Experience appeared first on Steve Karam :: The Oracle Alchemist.

This entry is part 18 of 19 in the series Adventures of Ace, DBA 

More Friday fun from the World’s Leading DBA Comic!

Related:

Metrics That Matter: These Aren’t the Metrics You’re Looking For

Improve Your Experience: DBA, Grow Thyself – Moving and Shaking in the Era of Data Dominance

The post Industry Leader (Ace Comic) appeared first on Steve Karam :: The Oracle Alchemist.

April is almost over, and you know what that means…

Oracle really, REALLY wants to talk to you about some of their products.

It’s no secret that the end of Oracle’s fiscal year (May 31st) marks a flurry of activity for Oracle sales, and doubtless many of you have met or will be meeting with them soon. These meetings can be highly beneficial for your company particularly if you are on the hunt for new hardware, database licenses, or other software. And if you’re not on the hunt for anything new, well… they can be pretty persuasive. You might be the proud owner of a T5-8 on June 1st.

Before that time comes, there’s a few things you can do to prepare. Ready yourself!

Understanding Licenses

Oracle keeps pretty good records. Before meeting you they will make sure to know exactly what you own, how much you pay for support, and what your company’s public plans might be. They absolutely understand their own licensing model and know a good deal about how it plays into your environment.

So why would you go into a discussion with them about purchases without knowing what it is you’re actually buying?

First, make sure you understand the different license types and how they apply to your servers. Know whether you need Standard Edition or Enterprise Edition. Perhaps you are concerned about missing out on Diagnostics Pack and AWR/ASH. Yet perhaps STATSPACK and Simulated Ash (S-ASH) might work for you instead. Do you need DataGuard? Try DBvisit. Make sure before you take the Enterprise Edition plunge you really need all the features and add-ons it allows.

Don’t get me wrong, those features are many and wondrous. Parallelism, online operations, DataGuard, Tablespace Point In Time Recovery (TSPITR) and more are features worth having… if you want to pay for them.

Get a Grip on Your Licenses

It is equally important to understand your own licenses. The many companies I’ve worked with were a mixed bag. Some paid extraordinarily meticulous attention to their licenses, either entrusting them to an employee in charge of corporate license purchases or to the DBA team. Others had absolutely no idea and no control over their licenses. Developers, DBAs, or even small groups working under non-IT departments would install Oracle on an as-needed basis with no care for the complicated requirements of Oracle. Every few years they’d scramble out of fear of the audit, that oft whispered pain that may rain down on an unruly business.

I’ve never been through an audit so I can’t speak to their supposed rigorousness. But I do know the best thing you can possibly do — both to stay in the good graces of Oracle and to know what you need come bargaining time — is to know where Oracle is being used inside your organization and what features you have installed.

This might include a company-wide internal audit of your installations, asking each group what they have installed and where. A DBA team that is not aware of ‘secret’ Oracle installations is in for quite a bit of pain anyways, as when problems strike you will suddenly become aware of it in the form of a team of people requiring help. It is also recommended that you run scripts on your installations to gather information about installed features from DBA_REGISTRY. Make a spreadsheet, fill it in, and keep it updated. Track the licenses you’ve bought and where they are allocated. And if you are out of compliance, make note of it. The end of Oracle’s fiscal year may be a great time to true up.

Pay Attention to Metrics that Matter

I wrote an article recently about the misunderstanding behind some Oracle metrics called These Aren’t the Metrics You’re Looking For. In it I described five frequently misused metrics as they pertain to Oracle tuning.

But there’s another very very misunderstood metric as most people don’t even know it exists or why it’s important. And that is the only metric that matters when it comes to hardware purchases for database servers: per-core performance.

Why is this important? Because as Kevin Closson points out in his excellent article about the SPARC T5 Processor, performance per core can be correlated to performance per license. If you’re about to shell out your hard earned cash on a fistful of licenses then you need to know exactly what each license buys you. If Oracle’s licenses were based on the amount of disk you used, you would absolutely be sure to make the best use of that disk.

I’m not saying that the other things Oracle will bring up — caches, disks, secret sauce, oh my — are not important. Depending on your bottleneck the juice may be worth the squeeze. But before you buy a server with oodles of CPUs that you must license perhaps you should make sure your application is tuned to make the best use of CPU. Then perhaps you should make sure that server meets your CPU needs.

Do You Even Need Anything New?

Why are you sitting at the table in the first place? Is it because you really need to make some purchases or because you got a call? Or perhaps because someone up above made the request.

There’s been a long standing debate about throwing hardware at a problem. More and more companies these days are throwing hardware at issues… either to avoid expensive and time-consuming tuning and re-architecting or because standard computing can’t do the things they want to do. I’m not going to embark on that particular journey in this post, but I will say that you owe it to yourself and your finances to at least ensure your problems can’t be resolved with some parameter changes, query rewrites, or indexes.

Absolutely most importantly, make sure the hardware you are buying fixes the problem you are having. I’ve seen many companies buy new hardware only to find it didn’t make a single difference in their performance. And there is nothing that will anger a business and frustrate an IT department more. Pay attention to waits and usage, and make sure the bottlenecks you’re encountering are the ones you’re fixing. If your system is disk I/O bound, a new microprocessor probably won’t give you much, if any, relief.

Watch Out for the Eager Want

A principle of sales from my favorite business book, How to Win Friends and Influence People, is to “arouse in the other person an eager want.” Remember that Oracle’s “eager want” is already there. They want to sell you products all year round, and definitely right before the end of their fiscal year. When Oracle Sales comes knocking at your door, their job is to arouse that want in you.

You will hear about all sorts of amazing products, from SPARC chips to OVM to Advanced Compression to Real Application Clusters. You don’t have to wear a hat and shades to hide your emotions like a poker competition, but you may at least want to make sure you know what these features do and whether or not you need them. It is good to have a healthy relationship with Oracle. They are a maker of outstanding products, and they absolutely have knowledge of those products that will be beneficial to you. But just like with any sales meeting, make sure you keep your wants in check and mind the pocketbook.

Conclusion

Twas the month before end of fiscal year and all through the net, sales are stirring and getting us in debt. This is absolutely the time to get some outstanding deals from Oracle Corporation (and other companies). Get your ducks in a row and reap the benefits responsibly!

The post Bringing Oracle Sales to the Table appeared first on Steve Karam :: The Oracle Alchemist.

A couple weeks ago I posted a whimsical (and very slightly sardonic) article on the Internet of Things with a view on what an average day could be like if everything (even your shower soap tray) was network connected and capable of programmed rules or even decision making analytics.

It spurted a generous number of small Twitter conversations and a spot on a couple Internet of Things aggregators. But just a couple days ago I saw an interesting tweet:

Talking about “Does the internet of things create a future without surprise” on @branch. Who has something to add? branch.com/b/does-the-int…

— Matthew Knight (@webponce) April 18, 2013

Introducing Branch

I had heard of Branch before, and I had understood it as a place where you can have conversations about a topic with more than 140 characters. One of the things I noticed was that I was unable to initially reply to the branch conversation; instead, I had to ask the original conversation author to invite me. It was described to me by @branch thus:

@oraclealchemist The former. Gives you the ability to host little “dinner table” conversations, if you want to think about it that way.

— Branch (@branch) April 18, 2013

It turns out the reasoning for this is pretty cool. Have you ever been involved in a conversation on Twitter and wanted to take it offline? That’s where Branch comes in. It’s still in the public eye, but now you get 750 characters per response. The people who were on the Twitter conversation with you are still part of the Branch conversation, and other people can ask to be involved. A dinner chat indeed! You can also share any point in the conversation back to Twitter, even a single sentence of someone’s contribution. And the conversation as a whole can be embedded into any site. Here, for example, is the one regarding my Internet of Things article:

Does the internet of things create a future without surprise

It is a fairly cool platform, and I could see it being incredibly useful in the IT world thanks to our long product names, syntax, and tendency to talk too much (okay, maybe I’m projecting).

The post Branch Off a Twitter Conversation appeared first on Steve Karam :: The Oracle Alchemist.

This entry is part 1 of 1 in the series Optimizing WordPress 

In my adventures in Twitterland I’ve noticed a lot of people who are using WordPress blogs (this one included). Some use wordpress.com which gives them a free blog with minimal theme and plugin choices; however, they get a lot of cool optimizations built in.

If you’re like me and running your own self-hosted blog then you have all the options in the world available to you but far less right out of the box.

This post will go over the performance changes I’ve made in an attempt to get my blog up to speed. It is part one of a series called Optimizing WordPress which will cover performance, syndication, editing, and more.

For now, let’s drop a gear and disappear speed up our blogs!

WordPress – Location, Location, Location

The first major point of consideration is this: Are you master of your own fate, beholden to no one, or a slave to the will of others? And by that of course I mean: do you have root?

I wholeheartedly recommend having root privileges if you are going to run your own blog (particularly if you know what you are doing). Shared hosting of the bygone age generally did not allow this, but today you can get yourself setup via a number of dedicated or cloud virtual private server (VPS) environments and have your own fully configurable server in no time flat. I personally host mine with 15 Minute Servers as I want a robust environment I can use for many of my own sites, shared hosting for friends, etc.

Some providers are actually dedicated strictly to WordPress such as the ever popular WP Engine. These options will cost a bit more than running it yourself, but they also come complete with the enhancements you will probably want.

Whatever you choose to do, get root privileges. Not only will it make you feel lordly, but it will be necessary for the next step of this article.

Let’s Get Cached

There’s a piece of software out there that does such a good job at providing caching services to PHP that PHP will include it in the core sometime soon. Until then, I’d recommend you download Alternative PHP Cache (APC).

With APC installed PHP code can now take direct advantage of the RAM resources on your system and use it for caching chunks of data. MySQL results, objects, and even entire HTML pages can be cached inside of it with the right code.

Alone this cache won’t do much for you, but with the right plugins for WordPress you’ll be able to take full advantage of it. Before that point though we must get the APC software installed. You can find a good guide for that here.

And as if the software benefits weren’t enough, you also get a great monitoring page with all sorts of metrics to keep track of which I know my Oracle buddies will absolutely love.

Cold Pressed Performance

Once you have APC installed, you can go ahead and install a plugin in WordPress called W3 Total Cache. This is an extremely well known plugin that is able to cache various parts of the WordPress environment, including:

• Pages – When a user reads a page it will be cached so the next hit will be near instant.
• Minify – There are two parts to this component. It will take your JavaScript/CSS files and eliminate whitespace and change variable names to be smaller. It will also cache these ‘minified’ files for better performance.
• Database – Caches MySQL query results. Highly important for WordPress, where depending on your theme you may be running over 100 queries for a single page.
• Objects – Caches WordPress cache objects into your chosen cache (in our case, APC).
• Browser – Allows browser-based caching on the client side.

Turning these on is as simple as installing W3 Total Cache, navigating to the Performance > General Settings page, and selecting these caches with the Cache Methods set to “Opcode: Alternative PHP Cache (APC)”. There are other settings that may interest you which you can find under each cache type’s individual page under the Performance section of your admin page. One incredibly interesting option is the CDN, which stands for Content Delivery Network.

It’s not Delivery, It’s CDN

Content Delivery Networks are the coolest thing since ever. Simply by setting up a CDN with a “pull zone” and fiddling with some DNS settings you can put the power of many servers at your disposal.

CDNs are designed to take your static content such as images, CSS files, and JS files and distribute them over a wide network. This distribution is designed to be automatic both for you and your end user. Viewers on your website see nothing but sudden blazing fast speeds as the images and code for your pages are delivered to their browser from a variety of sources.

There are many CDNs to choose from. CloudFlare is an extremely popular one due to its performance and security features while also being somewhat easy to set up. I went with MaxCDN due to reviews I had read and the customizability it seemed to offer and I am very happy with it. Setup can be a little harrowing; basically you need to create an account, fork over some cache, and add CNAME records on your DNS pointing a subdomain to your new MaxCDN account. On the MaxCDN side, you will configure a Pull Zone which is configured to pull content from your blog on request and serve it up. There are options to clear cache files and all sorts of cool reports and other features.

There are a ton of settings to use and they are well documented. Instead of going through the entire thing I will point you to How To Install and Setup W3 Total Cache For Beginners. Just remember that we are using APC for caching, not Disk. The most important part of the article at this point is the MaxCDN and W3 setup for it.

Once you have Page, MySQL, Object, and Browser caching enabled along with a CDN to serve up your images, JavaScript, and CSS from all over the world you will be, for the most part, as fast as a software company jumping on the Cloud bandwagon.

One item of note: There’s a setting under Performance > Page Cache to enable/disable the cache for either logged in users or certain user roles. It is highly recommended that you turn off page caching for Administrators. That way you can see the results of your hard won changes. The only drawback is that your users will have faster performance than you, and may notice problems that you don’t. It’s always a good idea to have another browser ready that’s not logged in to your admin account to keep track of pages and how they look.

What’s Slowing You Down?

Even with all these optimizations you still may have some pain points. A lot of times these are caused by plugins that are simply too overbearing on user performance. Many plugins have components that ‘phone home’ to the plugin owner’s site for whatever reason which can drastically reduce your performance.

The plugin P3 (Plugin Performance Profiler) is able to review your site and give you great details on which plugins are causing the most pain. You can find the plugin here.

Once you’ve installed the plugin, direct it to the pages you’d like to test and let it go to work. It takes a while but gives you table and graph data to let you know what you need to disable, replace, or tweak.

Honestly the best policy overall is to review your plugins periodically. Many times you’ll find that there are a ton of plugins you just don’t need, or maybe stopped needing recently.

Testing, Testing 1ms 2ms 3ms

While you’re doing all these performance improvements it’s a good idea to keep an eye on your page speeds to track your progress. Pingdom Tools has a great speed test that can test your site from multiple locations and let you know how long it took. You also get a breakdown of where the performance hogs were so you know what to tune next (for instance, a large javascript file or big image). Just because you cache and distribute content doesn’t mean you will be fast for all users; remember, they still have to download the page which takes time. The more jQuery and reactive design stuff you have on your page, the bigger it is going to be.

Another good one is WebPageTest which I actually like quite a bit better than Pingdom. It gives you greater options for how you want the test conducted and can save the results so you can compare later. However, both are capable and can give you great clues as to which files are causing you grief.

What Else?

That’s the end of this performance boosting article, but there is more to come. Next in my Optimizing WordPress series is syndication, where I will go over different syndication and sharing plugins and technologies available to you to help get your blog to more places.

The post Performance Tuning Your WordPress Blog appeared first on Steve Karam :: The Oracle Alchemist.

This entry is part 17 of 19 in the series Adventures of Ace, DBA 

Related:

Loosely based on my (tongue-in-cheek) Replication Platform Comparison.

Learn more about Oracle Golden Gate in the Enterprise!

Don’t settle for the same technology every time. Deploy Outside the Box.

The post Replication (Ace Comic) appeared first on Steve Karam :: The Oracle Alchemist.

This entry is part 3 of 3 in the series Database Diversity 

We’ll call it the NoSQL Pessimism Variable: The number of search results you see before you find a page disparaging NoSQL as a viable datastore option. These pages are usually either good natured posts on why it’s a neat idea but ultimately not viable, or posts straight out slamming the platform. So too will you find the opposite; posts saying that RDBMS is the ancient past and NoSQL is the way of the future. Going by these articles you would think that RDBMS were a scourge led by Lord Sauron himself against the free peoples of Data Earth, and could only be stopped by the Fellowship of NoSQL and its distributed MongoDB Hobbit cluster.

And then of course there is the supposed animosity that exists between developers and DBAs, with DBAs thinking all developers are out to get them and developers thinking DBAs can be circumvented (but not always).

There is a saying that every nice piece of work needs the right person in the right place at the right time. -Benoit Mandelbrot

As you can probably tell from this Database Diversity series, I’m a huge fan of growth through learning about new technologies and lobbying for a place at the table. And there are of course many other forward thinkers such as Cary Milsap with his excellent article NoSQL and Oracle, Sex and Marriage (some concepts of which are included in this post). So where does the animosity come from? What is the problem some people have with NoSQL and does it have a place in our database inventory?

The ACID Test

Many articles that reference NoSQL as a poor substitute for RDBMS will bring up ACID compliance, and judging by Google there’s a lot of questions around it. So let’s take a look at ACID for a moment:

• Atomicity – A transaction as a whole either succeeds or fails, never anything in between.
• Consistency – Data must always be valid by following application logic (keys, constraints, etc)
• Isolation – No matter how much concurrency a system may have, transactions should committed and viewed in a synchronous manner
• Durability – Committed transactions must be safe in the event of a critical event

Well sorry folks, going by those definitions I’m willing to bet 99% of the actual production databases out there, RDBMS or otherwise, aren’t ACID compliant. Unless your applications perform every transaction perfectly, every column has proper constraints and error checking at all times, parameters like commit_logging are not being misused, and you can guarantee absolutely 0 data loss (not a single committed row) in the event of a nuclear strike on your data center then you’re not ACID compliant though your database system might be capable of being so.

And as Cary Milsap points out, how often is Isolation a concern anyways? How Durable is your database environment? Are you absolutely sure every required NOT NULL or UNIQUE property is actually set properly? Of course some of these things truly are a concern to some systems and thus you need ACID. Or do you?

Just because a database is NoSQL does not mean it’s not ACID compliant, and just because a system is not ACID compliant does not mean it’s not A, I, D, CID, etc. compliant. It may be that a custom combination of those four letters is all you truly need. For instance, FoundationDB promises full ACID compliance with a NoSQL engine, and systems like MongoDB, Riak, Cassandra, MarkLogic, and others can be far more durable than all but the most expensive of Oracle database environments.

No isolation? No problem! There are many times I want transactions to batch into the system and don’t really care if they’re committed or not. Some good examples are session metadata, audit information, usage history, historical data, and staging data. Load it quick and don’t worry about isolating if it gives me better performance. For that matter, forget atomicity too since I can just reload it if need be. While we’re at it, forget consistency. Some data just needs to be dumped, rules be damned.

In fact, I’d like to propose a new acronym for litmus testing NoSQL Engines: LSD:

• Limber – Flexible storage options for all types of data in multiple formats including columnar, document, key:value, or others
• Schema-less – Data need not be standardized or conformant to a strict design or datatype rules
• Distributed – Capable of spreading work over a large quantity of systems for elastic scaling when it really matters

I was going to use ALKALINE but it was just too long and K is annoying to start words with.

NoSQL at the Big Kids’ Table

NoSQL has been around for a long time, and it deserves a trusted spot in your enterprise architecture if the tool fits the need. No, you shouldn’t just migrate all of your production relational databases over to MongoDB because you read somewhere that leveraging it produced a significant ROI. But if the shoe fits, it’s your shoe. Use it when it makes sense.

Many DBAs I speak to fail to even see the reasoning behind it or why it is so popular with the development community. The pessimistic view is that developers enjoy it because they can deploy it in secret, utilizing the way of the Ninja to stealth drop it onto the server, hook their code into it, and do all sorts of conspiratorial things like efficiently query large batches of data without a single join and those pesky DBAs asking to do backups or other do-gooder stuff.

I however prefer to take a more optimistic view and say that the reason it’s so appealing is because of what it can do.

Big, Wide Rows

Have you ever had a table where a lot of descriptive details were needed in unspecified amounts, and not always guaranteed to be there? The conversation generally goes something like this:

Developer: “Alright, we need a table that can store any and all properties of items we’re selling on our website. There could be thousands or maybe none. And they’re different depending on what product it is.”

DBA: “Happy to help. Unfortunately we can’t just create a big wide table with thousands of columns. I’d suggest a table with PROPERTY_NAME and VALUE.”

Developer:

DBA: “It could get to be millions of rows.”

Developer:

DBA: “You should probably use PROPERTY_ID and VALUE_ID and join them to the PROPERTY and VALUE tables too.”

Developer:

Or something like that. In cases like this, it’s fairly clear where something like Cassandra seems appealing with its ability to store insanely wide rows and use data in the column name and query a slice range of those columns. Storing, querying, and joining tens or hundreds of properties for a single logical object as rows simply adds a ton of work. For instance, imagine a record describing a car sitting in a CAR table. You might have things like MAKE, MODEL, etc. to give an overall description. Now imagine you have a CAR_PROPERTIES table with every possible property a seller might want to provide: color, horsepower, intake, exhaust, seats, seat material, etc. Now I want you to write me a simple high performance query that finds all Hondas that are blue (if color is defined), with leather seats (if defined), racing stripes (if defined), and a moonroof, sunroof, or convertible (if defined).

Then there is the whole question of relational parent-child storage to begin with instead of storing logical groupings as a single document. For instance, consider a web based contact management system which must include businesses, people, contact methods, locations, and extra properties all on a single page. You’re not worried about atomicity or isolation at all, it’s just a dumping ground for contact information.

With a traditional relational database, many tables will have to be joined together to get all the data back for that page. If you want to make the page more reactive and allow asynchronous loading of various elements, you will have to program additional queries in to read/change parts of the page. If you want to build a search page that contains multiple contacts and all their associated metadata you’re in even more trouble. But with document storage offered by many NoSQL options, you can go from this:

To this:

A document = A page. As simple as that. Instead of crawling through nested loops and bringing data together from a multitude of locations, logically storing a chunk of application data in its native format is appealing. Is it always the right way to do things? Of course not. But it’s extremely intuitive, easy to develop against, and can be configured for concurrency, portability, redundancy, and nearly anything else you might want depending on the NoSQL engine you’ve selected.

Conclusion

NoSQL is not THE answer, but it is absolutely AN answer. In fact, I am a strong advocate of using it together with applications that use or should use a relational model. For transactional use requiring ACID compliance (most notably atomic, sequential operations) data can be stored in the relational database. Metadata, session data, logging, metrics, and other non-transactional information that is not required for the actual end user experience can be easily stored in one of many forms offered by the various NoSQL engines.

The post Putting NoSQL In Its Place appeared first on Steve Karam :: The Oracle Alchemist.

April 9th, 2013 is Internet of Things Day!

Happy Internet of Things Day! #IoT

— Kevin Ashton (@Kevin_Ashton) April 9, 2013

The futurist in me wishes that meant it is the day we all sift through our Drawer of Things to find our favorite sensors and collect all the extra data we can to share with the world, but it is not yet to be.

For now, check out my articles on the Internet of Things and how it can and will make a difference in our lives!

• Sensors Everywhere in the Internet of Things
• Wearable Computing and Personal Data Collection

The post Happy Internet of Things Day! appeared first on Steve Karam :: The Oracle Alchemist.

Since today is Monday and many of my devoted readers are at IOUG Collaborate 2013 (C13DEN) I decided to post one of my more lighthearted stories. It’s a harrowing tale of how I was a victim of ninja trickery — no, let’s not mince words, it was a strike — at my local Wegmans. If you’re only here for the technical stuff (coming soon to a blog near you) I apologize, but some stories must be told.

Ninja strike at Wegmans

Local shopper baffled – September 30, 2012

Today I was the victim of a ninja attack. If you’re one of those naysayers who don’t believe the ninja walk among us but conceal themselves to shady bars in the darker parts of Tokyo, you may go but know you have been warned. If you understand the severity of the ninja threat, read on.

So I was shopping at Wegmans, which I now regard as a hotbed for ninja activity. It is a chaotic large grocery store with many aisles and stands which are perfect for cover, and metal beams crisscrossing the roof which everyone knows will be used for transport of ninja soldiers. I had come with a list and was down to my last two items. The cart was relatively full, stocked with a variety of unique goodies that could not be easily mistaken for any other cart. I went down the ice cream aisle, intent on finding Rice Dream Ice Cream for my daughter.

Here’s the setup: I leave my cart at the end of the aisle, and walk down toward where the organic ice creams are. There is a man across from me, nondescript, blue jeans, a faded t-shirt, no one that you would remember. As I turned to the frosted doors to begin my search, I heard the dull slap of a dropped box of ice cream, and turned to look. The man stood there, looking down at a bland box of generic ice cream pops. So generic, in fact, that the makers of them didn’t even have the heart to call them ice cream pops; instead they were “Frozen Treat Pop Product” or something like that. The man didn’t look toward me, he didn’t bend to pick them up in shame, he just looked at the frozen product on the ground. Losing interest, I continued looking for my daughter’s ice cream, couldn’t find it, and walked toward my cart.

The cart was not there. I was the victim of cart theft. Petty cart theft, but cart theft nonetheless. It’s something you hear about but never really believe…who would steal a cart?

But steal it they did. I looked around the store for a good ten minutes, going up and down aisles, giving every cart I passed the hairy eyeball, glaring down old women and couples and little kids. But my cart was nowhere to be found. Finally I decided my only action could be to start my shopping excursion all over and get a new cart. So I did.

I took my empty cart to the first item on the list once again, which were apples. I looked down at the apples and picked a few which were okay but obviously not as good as the glorious red delicious fruit I had picked out previously. Finding a few to bag up I turned… and my cart was sitting right next to me. The old cart. With everything in it…plus the generic box of frozen ice cream pops sitting in the top basket. I considered the basket with new suspicion, and realized that something was very wrong, something far more sinister than a simple petty cart theft. But what happened then was even more disturbing. I put the frozen ice pops in the empty cart, triumphantly turned my old cart around to get my final items, turned…and the cart with the ice pops was gone. Vanished. Nowhere to be seen.

Ninja. The word reverberated in my head. I felt a little dizzy. I was not ready for this.

Getting my final items I went to the check out counter. The lady asked if I found everything okay and I told her my tale of weirdness. She laughed and said that people accidentally take carts. That poor naive woman. She had no idea. She finished checking me out, bagged my items, and I walked to the back of the cart to finish this strangeness once and for all. Turning the cart to the right to leave the check out lane, I found it blocked.

Blocked by a cart with nothing in it but a box of Frozen Ice Pops.

True story.

The post Victim of a Ninja Strike appeared first on Steve Karam :: The Oracle Alchemist.

This entry is part 16 of 18 in the series Adventures of Ace, DBA 

If you missed the first Big Data Ace Comic, check it out now!

Related:

See the first Big Data Ace Comic: Big Data

Want to make visualizations that look better than a pie chart? Web Friendly Interactive Data Visualization with D3.js

Size doesn’t matter: Just how big is your data?

The post Big Data #2 – Big Data Speaks (Ace Comic) appeared first on Steve Karam :: The Oracle Alchemist.

Back in 1999 I worked on a project with the US Army that incorporated RFID scanners for logistics tracking. At the time it was an extraordinarily neat concept, using a combination of Oracle, Forms (in Developer/2000) and RFID tags/sensors to track inventory. As items were loaded onto trucks, a barcode scanner (3 of 9, how I love thee, let me count the ways) would feed the record into a database and write it to a gray box called an RFID tag. Once all the inventory was on the truck the tag would be hung on a peg on the trailer wall and the truck would go on its merry (or not so merry depending on the location) way.

At the target warehouse there were UFO shaped objects that acted as RFID sensors. When the truck arrived at the depot, the tag hanging inside the trailer would be read and the expected inventory list loaded into a database. Teams unloaded and scanned each item confirming that all the required inventory made it in time. It was a cool use of sensor technology and a project that greatly piqued my interest in communications enabled ‘things’.

Little did I know that in that same year, a guy named Kevin Ashton used the phrase “Internet of Things”, something he is now semi-famous for. The work I was doing was something of a proto-version of what we now think of as the Internet of Things. Objects with sensors, RFID or other wireless communications, and the Internet as their chatroom.

Now I could write about how the Internet of Things is going to revolutionize business, save the planet, and change medicine as we know it. But today I’m in a selfish and somewhat whimsical mood, so we’ll talk about how it will make my day more awesome.

My ideal day with all the Things

As morning time rolls around, my bed begins to vibrate slightly. It does this in a way similar to the Jawbone Up, sensing when I am in the lightest stage of sleep and using vibration to gently nudge me out of it before the time I am supposed to wake up. This is a great and gentle way to wake up that I find very refreshing. As the bed wakes me for the morning ritual, it stores information about my sleep and waking patterns to a data store and sends messages that yes, Steve has arrived from the land of slumber.

When my eyes crack open and the bed knows that I am fully awake based on my movements, my shower will turn on and begin heating up. Sensors on the tub floor will keep track of how long it takes for the water to get hot and how long it takes me to actually step into it to ensure proper use of water and gas heating. If there is a time discrepancy, the next morning will be adjusted to maximize resource savings. I step into the shower and notice my bar of soap is nearly out; luckily, the soap tray knew that before I did based on the weight of the bar and has already ordered me a replacement online that will get here tomorrow. As I go on about my shower, sensors pick up the relevant details about my movements, the time I take, and the amount of water consumed for storage and later analysis.

I leave the shower and the coffee maker downstairs starts brewing the morning cup. One I get to the sink and pick up my shaving cream and razor (call me old fashioned) the hot water comes on so I can lather up. When my hand comes up to my face with the razor, the water shuts off. When I put the razor under the faucet to rinse, the water comes on. Consider my water bill optimized.

The light shuts off as I leave the bathroom and I get dressed. A quiet ‘ding’ chimes, notifying me that I forgot my wallet on the nightstand. Chagrined and duly chastised I make my way downstairs and enjoy the perfectly heated coffee. Based on analysis of my morning schedule over the last few weeks it knows that I won’t shave tomorrow so my schedule will be different. Either way the coffee brews at just the right time, far better than it did during the Switch Age (on/off) or the Chronolithic Era (timers). The outside temperature and estimated high are pushed to my phone as I walk toward the closet so I can pick the best jacket. As my movement patterns around the house indicate that I will be leaving it in about five minutes, my car starts outside so it can be warmed up by the time I get out there.

My car knows I was moving rather sluggishly this morning so it puts on some fast paced epic music to wake me up. A default route to work is ready on the GPS based on my driving habits and traffic patterns. As I’m driving, the gas light on the car blinks on and my GPS adds a waypoint to the nearest gas station on my route so I can tank up. When I arrive and pull up to the pump, the ‘Regular Unleaded’ option is already selected based on my car’s requirements and my previous purchase habits. Since I live in New Jersey someone else will always pump my gas for me, and it stops pumping at the optimal fill point based on my car’s requirements. My GPS recalculates and points me on the best way to continue the drive to the office.

At work, the elevator is called when I get within twenty feet of it (unless it was already called by another person). Once I get to my floor I make my way to my office and the light automatically flips on to a brightness based on the ambient light in the room. The monitor flips on as well so I can get logged in quickly. The phone rings once but then goes to voicemail because I have a meeting coming up in fifteen minutes and my computer system knows it requires some preparation due to the amount of documentation tagged as relevant to the meeting topic. I’m free to go on about my workday in the usual way while my screensaver and monitor automatically engage when I leave my seat for what it knows to be a longer than average time. Historical analysis and the sluggish way I’m working on the computer lead to a notification that it would be a good time for lunch and a walk. The computer pops up several local places and suggested menu items based on analysis of my preferences, my recent dietary choices, and the weight trend from stepping on the scale this morning.

After a hard day of having Things tell me what to do, it’s time to head home. Before I head out, I open an app on the phone and indicate that I want to bring pizza home for the family for dinner. As I head home geofencing notes when I’m within a 15 minute distance to the pizza place and puts in my carry out order so it will be ready and piping hot as soon as I arrive. Upon picking it up a text will go out to my wife and/or kids that I’m on the way home with pizza so be ready to feast.

Oh no! On the way home my car gets in a wreck (not a typical day for me). Nothing too serious, though the pizza does slide out of the box onto the floor of the car. No, that doesn’t send an alert that I need more pizza, that would be ridiculous. The car however notifies local police and my insurance company that there was a wreck, calculates where I was hit and begins to form assumptions on whose fault it was. All of the data it collects will be helpful should I need to go to court for the wreck. Unfortunately we don’t have medical tricorders yet but if we did they would be scanning me to make sure I am okay and don’t require medical attention. Perhaps a decibel indicator to see how loud I’m screaming? I digress…this is getting a little gruesome.

Thankfully it was only a small fender bender and I can head home (minus pizza). Something brooding and dark by Leonard Cohen plays on the radio since I’m obviously in a bad mood. When I get home, the garage door automatically opens as my car nears it so I can head into the house and finish the rest of my day in peace. Thankfully there are several good shows for me to watch that my TiVo automatically recorded for me based on my analyzed preference and habits.

Will the Internet of Things Really Make It So?

I have no doubt that this is our future. Notice that except for the tricorder, I didn’t name a single thing we can’t already do with current technology. As consumerism, marketing, and convenience shopping grow you will find more an more seemingly innocent objects around the home joining the Internet of Things. The addition of sensors and communications technology to everyday items will absolutely revolutionize the way we go about our day to day lives. Perhaps it will make us inattentive and lazy, unable to think or do for ourselves. And of course there are very real privacy concerns surrounding the huge use of sensors it will bring into every day life. But as with all things, convenience and progress will win out in the end. The Internet of Things is here to stay.

And it’s not just for big companies either. With tiny computers like the Raspberry Pi available for only $25 or rapid prototyping technology like Arduino anyone can jump in on the action. The interest is obviously there… just check out this Kickstarter for RFDuino, an RF enabled microcontroller for Arduino shrunk down to the size of a fingertip that has smashed through its $5,000 goal and currently sits at ~$300,000. Tech that cheap and that small will allow every electronics hobbyist to create communicating sensor based things on the internet.

And of course, there is the world of Wearable Computing which is already taking off in a big way. By wearing the sensors everywhere we go, a huge amount of data collection is capable to track and analyze everything we do at every time of the day.

Personally I’m excited about the insight that this kind of technology can bring into our lives. Privacy and the decay of human awareness notwithstanding, it’s an inevitable and exciting way to bring sensors, objects, humans, and big data to a stunning and revolutionizing culmination.

The post Sensors Everywhere in the Internet of Things appeared first on Steve Karam :: The Oracle Alchemist.

Bulk binds and bulk loading are nothing new; in fact, the feature came out in Oracle 8i. This was a time when Oracle Parallel Servers roamed the land, pfiles were all the rage, and connecting internal was a fading fad. But like the rest of Oracle, the features have grown over the years and are a great way to pull data into memory via PL/SQL, manipulate it if desired, and perform DML in a single operation with the FORALL command. In this article we will use the SH.CUSTOMERS table to perform a FORALL insert and FORALL merge.

Setting Up the Environment

The environment for this test is an Oracle 11gR2 Enterprise Edition installation with the default schemas (HR, SH, OE, etc) loaded. I have logged in as SH and created a copy of the CUSTOMERS table by using the query: select dbms_metadata.get_ddl(‘TABLE’, ‘CUSTOMERS’) from dual;

SQL> desc customers
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUST_ID				   NOT NULL NUMBER
 CUST_FIRST_NAME			   NOT NULL VARCHAR2(20)
 CUST_LAST_NAME 			   NOT NULL VARCHAR2(40)
 CUST_GENDER				   NOT NULL CHAR(1)
 CUST_YEAR_OF_BIRTH			   NOT NULL NUMBER(4)
 CUST_MARITAL_STATUS				    VARCHAR2(20)
 CUST_STREET_ADDRESS			   NOT NULL VARCHAR2(40)
 CUST_POSTAL_CODE			   NOT NULL VARCHAR2(10)
 CUST_CITY				   NOT NULL VARCHAR2(30)
 CUST_CITY_ID				   NOT NULL NUMBER
 CUST_STATE_PROVINCE			   NOT NULL VARCHAR2(40)
 CUST_STATE_PROVINCE_ID 		   NOT NULL NUMBER
 COUNTRY_ID				   NOT NULL NUMBER
 CUST_MAIN_PHONE_NUMBER 		   NOT NULL VARCHAR2(25)
 CUST_INCOME_LEVEL				    VARCHAR2(30)
 CUST_CREDIT_LIMIT				    NUMBER
 CUST_EMAIL					    VARCHAR2(30)
 CUST_TOTAL				   NOT NULL VARCHAR2(14)
 CUST_TOTAL_ID				   NOT NULL NUMBER
 CUST_SRC_ID					    NUMBER
 CUST_EFF_FROM					    DATE
 CUST_EFF_TO					    DATE
 CUST_VALID					    VARCHAR2(1)

SQL> select count(*) from customers;

  COUNT(*)
----------
     55500

SQL> desc customers2
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUST_ID				   NOT NULL NUMBER
 CUST_FIRST_NAME			   NOT NULL VARCHAR2(20)
 CUST_LAST_NAME 			   NOT NULL VARCHAR2(40)
 CUST_GENDER				   NOT NULL CHAR(1)
 CUST_YEAR_OF_BIRTH			   NOT NULL NUMBER(4)
 CUST_MARITAL_STATUS				    VARCHAR2(20)
 CUST_STREET_ADDRESS			   NOT NULL VARCHAR2(40)
 CUST_POSTAL_CODE			   NOT NULL VARCHAR2(10)
 CUST_CITY				   NOT NULL VARCHAR2(30)
 CUST_CITY_ID				   NOT NULL NUMBER
 CUST_STATE_PROVINCE			   NOT NULL VARCHAR2(40)
 CUST_STATE_PROVINCE_ID 		   NOT NULL NUMBER
 COUNTRY_ID				   NOT NULL NUMBER
 CUST_MAIN_PHONE_NUMBER 		   NOT NULL VARCHAR2(25)
 CUST_INCOME_LEVEL				    VARCHAR2(30)
 CUST_CREDIT_LIMIT				    NUMBER
 CUST_EMAIL					    VARCHAR2(30)
 CUST_TOTAL				   NOT NULL VARCHAR2(14)
 CUST_TOTAL_ID				   NOT NULL NUMBER
 CUST_SRC_ID					    NUMBER
 CUST_EFF_FROM					    DATE
 CUST_EFF_TO					    DATE
 CUST_VALID					    VARCHAR2(1)

SQL> select count(*) from customers2;

  COUNT(*)
----------
	 0

As you can see, the CUSTOMERS table is action packed with 55,500 scintillating (not really) rows of customer data while the CUSTOMERS2 table has nothing at this time. In order to remedy that problem we’re going to write an anonymous PL/SQL program to bulk collect data from CUSTOMERS into a PL/SQL collection then use FORALL to load the data into CUSTOMERS2.

Keep in mind that this is a very pedestrian example of what you can do with bulk binds. While bulk collecting into memory and using FORALL to perform the insert does present noticeable performance benefits, it’s very unimaginative and dull. In my opinion the real power of bulk binds comes in its ability to manipulate the collections in memory using standard PL/SQL code and then perform a straight DML to the DB with the results of the object. Regardless, here is the code used to perform the insert:

SQL> declare
  2    type t_customers is table of customers%rowtype;
  3    lv_customers_tab t_customers;
  4  begin
  5    select * bulk collect into lv_customers_tab from customers;
  6    forall rec in lv_customers_tab.first .. lv_customers_tab.last
  7      insert into customers2 values lv_customers_tab(rec);
  8    commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select count(*) from customers2;

  COUNT(*)
----------
     55500

As you can see, the code performed the following operations:

1. Create an object type (t_customers) which is a table of CUSTOMER%ROWTYPEs. Imagine a single row in the CUSTOMER table. CUSTOMER%ROWTYPE would be the definition of columns (name, datatype) which make up a single row. By creating an object that is a TABLE OF those definitions, we create a PL/SQL representation of the CUSTOMER table.
2. Create a variable (lv_customers_tab) of datatype t_customers. This lv_customers_tab variable is now an object that can hold a representation of the CUSTOMER table.
3. Query the CUSTOMERS table, bulk collecting all data into the lv_customers_tab variable. This loads the data from the physical table in Oracle into an in-memory collection which can be manipulated, written, or discarded.
4. Perform a FORALL operation using all data from the FIRST to LAST object in the collection, assigning the index for the pseudo-loop to the variable ‘rec’. There are many methods that can be used with PL/SQL collections (I will go through these later), but FIRST and LAST define the first and last index of the collection and are great for looping or pulling those specific records.
5. Perform an INSERT operation, taking all data from the collection and somewhat literally slamming it into the target table. Note that we do not have to list each column of the table; instead, we insert an entire object of columns at once based on the index from the previous step (which is contained in the variable ‘rec’).

To understand the way collections store data, consider the following table example:

EXAMPLE
-------------------------
EXAMPLE_ID       NUMBER
VALUE            VARCHAR2
MYDATE           DATE

In this table, a piece of data contained in the EXAMPLE_ID column of datatype NUMBER is just a numeric chunk of data. But a combination of the column name (EXAMPLE_ID), datatype (NUMBER) can be considered an object of type EXAMPLE.EXAMPLE_ID%TYPE; this object will contain the attributes associated with that column in that table. The data contained therein (for example, 12345) could be considered an instance of the EXAMPLE.EXAMPLE_ID%TYPE object.

Likewise, an object of type EXAMPLE%ROWTYPE would be a combination of EXAMPLE_ID:NUMBER, VALUE:VARCHAR2, and MYDATE:DATE. Three NAME:TYPE combinations together form a single row in the table and a single set of values in the EXAMPLE%ROWTYPE object.

Finally, if we make a TABLE OF EXAMPLE%ROWTYPE then we have a collection of EXAMPLE_ID:NUMBER, VALUE:VARCHAR2, MYDATE:DATE combinations. This format is no different from an actual table except that it is completely variable based inside a chunk of PL/SQL code. Any data put into this structure will exist in memory and can be manipulated via code.

Performing a FORALL MERGE

Despite Tom Kyte’s argument that a FORALL MERGE is not required as MERGE is always a bulk capable event that already worked on sets, it appears that Oracle has provided the ability regardless.

as I’ve said before — forall doesn’t *make sense* with merge! merge already *works* on sets. merge is only about sets. You don’t *need* forall. -Tom Kyte

I’m willing to bet it was added for one of two reasons: either to spare customers the agony (sarcasm) of using table(cast()) in their MERGE statements, or to satisfy customers who kept demanding it regardless of actual need. Either way it’s there now.

Before doing the example we will insert more rows into the CUSTOMERS table (using a new sequence to provide CUST_IDs) and update every 7th row in CUSTOMERS:

SQL> select max(cust_id) from customers;

MAX(CUST_ID)
------------
      104500

SQL> create sequence customer_seq start with 104501 increment by 1;

Sequence created.

SQL> insert into customers
  2  select customer_seq.nextval, cust_first_name, cust_last_name, cust_gender,
  3  cust_year_of_birth, cust_marital_status, cust_street_address,
  4  cust_postal_code, cust_city, cust_city_id, cust_state_province,
  5  cust_state_province_id, country_id, cust_main_phone_number, cust_income_level,
  6  cust_credit_limit, cust_email, cust_total, cust_total_id, cust_src_id,
  7  cust_eff_from, cust_eff_to, cust_valid
  8  from customers where rownum < 20000;

19999 rows created.

SQL> update customers
  2  set cust_year_of_birth = 1999,
  3  cust_postal_code = '07030'
  4  where mod(cust_id, 7) = 0;

10785 rows updated.

Now let’s do a comparison of the two tables to make sure there is a noticeable difference. We will use COUNT() to provide a difference in row count, and MINUS to check for any differences at all:

SQL> select 
  2  (select count(1) from customers) - 
  3  (select count(1) from customers2) as diff
  4  from dual;

      DIFF
----------
     19999

SQL>  select count(*) from
  2  (select * from customers2
  3   minus
  4   select * from customers);

  COUNT(*)
----------
      7928

From these queries you can see that CUSTOMERS contains 19,999 rows that are not in CUSTOMERS2. Additionally, the CUSTOMERS2 table has 7,928 rows that are different from CUSTOMERS in some way. Using “select * from tab2 minus select * from tab2″ compares every column of every row against the other table for comparison (something that came in very handy for testing replication back in 8i).

Now that we know our source tables are ready for the MERGE, let’s get to it:

SQL>  declare
  2    type t_customers is table of customers%rowtype;
  3    lv_customers_tab t_customers;
  4  begin
  5    select * bulk collect into lv_customers_tab from customers;
  6    forall rec in lv_customers_tab.first .. lv_customers_tab.last
  7	 merge into customers2 c2
  8	 using (select cust_id from customers c1
  9		where c1.cust_id = lv_customers_tab(rec).cust_id) match
 10	 on (match.cust_id = c2.cust_id)
 11	 when matched then
 12	   update set cust_first_name = lv_customers_tab(rec).cust_first_name,
 13		      cust_last_name = lv_customers_tab(rec).cust_last_name,
 14		      cust_gender = lv_customers_tab(rec).cust_gender,
 15		      cust_year_of_birth = lv_customers_tab(rec).cust_year_of_birth,
 16		      cust_marital_status = lv_customers_tab(rec).cust_marital_status,
 17		      cust_street_address = lv_customers_tab(rec).cust_street_address,
 18		      cust_postal_code = lv_customers_tab(rec).cust_postal_code,
 19		      cust_city = lv_customers_tab(rec).cust_city,
 20		      cust_city_id = lv_customers_tab(rec).cust_city_id,
 21		      cust_state_province = lv_customers_tab(rec).cust_state_province,
 22		      cust_state_province_id = lv_customers_tab(rec).cust_state_province_id,
 23		      country_id = lv_customers_tab(rec).country_id,
 24		      cust_main_phone_number = lv_customers_tab(rec).cust_main_phone_number,
 25		      cust_income_level = lv_customers_tab(rec).cust_income_level,
 26		      cust_credit_limit = lv_customers_tab(rec).cust_credit_limit,
 27		      cust_email = lv_customers_tab(rec).cust_email,
 28		      cust_total = lv_customers_tab(rec).cust_total,
 29		      cust_total_id = lv_customers_tab(rec).cust_total_id,
 30		      cust_src_id = lv_customers_tab(rec).cust_src_id,
 31		      cust_eff_from = lv_customers_tab(rec).cust_eff_from,
 32		      cust_eff_to = lv_customers_tab(rec).cust_eff_to,
 33		      cust_valid = lv_customers_tab(rec).cust_valid
 34	 when not matched then
 35	   insert values lv_customers_tab(rec);
 36    commit;
 37  end;
 38  /

As you can see, the one unfortunate part of the code is having to write out every column of the UPDATE part of the MERGE statement as UPDATE SET ROW does not work here. However, it does provide an opportunity to see better what is contained in the lv_customers_tab object. In this PL/SQL we performed the same steps as the INSERT, except we fed the lv_customers_tab object into a MERGE statement. The collection is compared to the target table (match) and rows are either inserted or updated into the target table depending on a match of CUST_ID. Let’s check the differences again to make sure it worked:

SQL> select
  2  (select count(1) from customers) -
  3  (select count(1) from customers2) as diff
  4  from dual;

      DIFF
----------
	 0

SQL> select count(*) from
  2  (select * from customers2
  3   minus
  4   select * from customers);

  COUNT(*)
----------
	 0

Success! No differences were found between CUSTOMERS and CUSTOMERS2 following our operation.

As I mentioned before, this is a very pedestrian example. The real power of bulk binds is in how you can manipulate the objects. For example, once you bulk load your data into a PL/SQL collection you can add new rows, delete rows, alter the data, etc. This allows you to pull data in, perform in-memory changes, then push it back to the source table or to another table effortlessly. If you incorporate this into a PACKAGE with the PL/SQL collection declared globally, you can keep entire sets of data in memory and manipulate them for the duration of your session which can be pretty cool.

The methods for doing this manipulation can be found in the Oracle documentation. Methods such as FIRST, LAST, PRIOR, and NEXT are good for traversing the collection while methods such as EXPAND and DELETE are good for manipulation. As you iterate through a collection you can use and modify data with the COLLECTIONNAME(index).COLUMN syntax as you would any other variable. And once you’re done working with the collection you can write it as a contiguous chunk of data or as a sparse collection to your target table.

Here are some more resources for Bulk Binds if you want to experiment further:

• Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle
• DEVELOPER: PL/SQL Practices On BULK COLLECT
• Collection Types in PL/SQL
• Collection extensions in 10g

The post FORALL operations IN oracle appeared first on Steve Karam :: The Oracle Alchemist.

This entry is part 15 of 17 in the series Adventures of Ace, DBA 

Related:

Don’t let yourself become part of the NoDBA team! Grow Thyself.

If your conference tables look like this one (DBA on one side, developers on the other), maybe it’s time to rethink your relationship. DBA Team, Front and Center!

The post New Standards (Ace Comic) appeared first on Steve Karam :: The Oracle Alchemist.

Make no mistake of it: vendor lock-in is a business goal. Sure, there are some solid benefits in going with one company for your hardware, software, cloud, and all other needs. But doing so can easily mean missing out on best-of-breed options, cost savings, or simple innovation.

It is important not to confuse ‘vendor’ with ‘framework’. For example, This article specifically discusses “ecosystem lock-in” and argues that adoption of an open-source Infrastructure as a Service (IaaS) platform such as OpenStack that is sponsored by a collaboration of industry giants may be just as confining as going with an enterprise solution. Still others hail open source platforms like OpenStack as the Linux of the Cloud, opening the platform to free innovation and adoption.

You are more likely to find a vendor skilled at cornering the lock-in market than the perfect infrastructure market.

Though there is a lot of hype from all sides of the debate, one thing is clear: choosing a single vendor to provide for your needs in the cloud and on the ground can be incredibly confining. No one company at this time dominates all markets in all areas of scalability, performance, agility, and stability. If anything, you are more likely to find a vendor skilled at cornering the lock-in market than the perfect infrastructure market.

Now, let me pull my head out of the clouds a bit and address this issue on the ground level as well. Even in your data center it is easy to find yourself locked into a single vendor. Companies like Oracle and IBM are old pros at it (as evidenced by their sparring over who makes faster, cheaper servers), with combination services including hardware, OS, database, middleware, ERP, BI tools, etc. Set a meeting at the end of the fiscal year with a good amount of money in your budget and see how quickly you can turn your entire architecture into a single vendor’s playground. And I’m not knocking their solutions, mind you. Oracle’s new SPARC T5 processor and servers featuring the latest Intel Xeon E7 processors are nothing to sneeze at. Likewise, their various MPP (or pseudo-MPP) systems like IBMs PureData System (Netezza) and Oracle’s Exadata are absolute powerhouses (Engineered Powerhouses) that would be a welcome addition in any server room. These vendors and others are smart, capable, and have the components that could fill your entire server room and software stack if you so desire.

Beyond the server component are many others; storage, middleware, apps, ERP, CRM, CMS, MDM, the acronyms go on and on. Some companies may be interested in having a “single throat to choke”; that is, one vendor that provides and is responsible for every environment in the stack. However, keep in mind that just because all your components are owned under one roof, they may not be supported under one roof. A call to support will generally not create a meeting of the minds with a representative from each group; instead, phrases like “that’s not our area” or “we don’t support that” will abound, leading to confusion and post-purchase angst.

So what can the IT professional do to help their company either avoid vendor lock-in or to make the best of it should it become inevitable?

Be agnostic

All too often a company will choose to go with a certain technology simply because it’s something they’ve always used. In many cases they will continue doing so even when the technology is outdated and underperforming. The comfort level of old or familiar architecture is difficult to let go of.

Strive to be agnostic. Instead of preferring one platform or software over another, keep an equal playing field. Doing so will allow better flexibility to choose the right component for the right job.

Embrace open source

Enterprise PaaS or other full service solutions have one major flaw: they have to invest a lot of time and money into being the right solution. As I mentioned above, vendor lock-in is a business goal, and money and thought must be put toward locking you in. The Open Source doesn’t have to contend for your business or with the goal of locking people in. It is a platform or software that you can choose to use or not use as you please. Generally this comes with better integration support with a wide variety of hardware/software and can easily be more flexible as a whole than vendor products.

This may or may not apply to vendor versions of open sourced software. Companies such as Cloudera take open source software like Hadoop and build it into an Enterprise package. However, at the core of this package is still an open source software, and many if not all of the benefits will carry forward to the customer along with new benefits like ease of use and incredible performance enhancements (such as Cloudera Impala). This specific subject is still up for debate with many in the community, however.

Don’t let cost be your only decision point on hardware

If you absolutely positively only want the cheapest solution around, build it yourself. If not, you will want to shop around and do a comprehensive comparison including proof of concept (POC) with many vendors. And while you may not be able to afford the solution the folks with the nicest suits bring to the table, you also shouldn’t opt for the cheapest possible route.

Remember that cheap solutions come with cheap support, cheap enhancements, and cheap stability. I’m not talking about commodity hardware; there is nothing wrong with using cheap components in a massively redundant whole. But a cheap overall solution can be very costly in the end.

Deploy outside the box

This topic got a whole article from me a while back. There are software and hardware components out there you may have never heard of that could revolutionize the way you do business. Do your research and get a feel for what is out there. Don’t stop at the traditional vendors. Don’t think of what you have to deploy, but what you want to deploy.

Most importantly, don’t let your current vendors dictate the way things work in computing. Want instant QA deployments of your Oracle databases? Look into Delphix. Want your own cloud platform? Check out OpenStack. Wish you had the ability to quickly provision storage volumes and make use of snapshots and cloning? You may not need an entirely new storage appliance, see if ZFS can do it for you with your current architecture. Many of the companies I’ve worked with have the capability to do so much more with their current architectures, but out of fear, uncertainty, and doubt (FUD) they stick with what they have until the time comes that a huge sum of money must be spent to upgrade the whole kit and caboodle.

Conclusion – Your application needs should pick the vendor, not the other way around

Product development and deployment drives business. If you’re looking for a new MDM solution or building a new application the last thing you need is to be limited based on your vendor. In the end, the key thing to remember is that your business and product development should drive the decision on which platform to pursue instead of having your vendor dictate what you must pursue. There is a tool for nearly every need; some good, some bad. By avoiding a complete lock-in to a single platform, you will be free to choose the components that will help you build the perfect environment.

The post Has Vendor Lock-In Locked Innovation Out? appeared first on Steve Karam :: The Oracle Alchemist.

During my one year stint as a Sr. Instructor at Oracle University I got to meet with a lot of current and aspiring DBAs. While I’m most at home in front of a computer with thirty clients breathing down my neck, a fat mug of coffee and a pile of metrics spilling off the desk, teaching a class is a heck of a lot of fun. You get to meet a lot of interesting people and hear war stories from all over the Oraclosphere.

But this story isn’t about teaching in general. I want to tell you about

The Most Clever Student I Ever Had

While teaching at Oracle, there were many courses I enjoyed teaching: 10g New Features, 10g RAC (9i not so much), 9i and 10g Security, etc. But my favorites were the 9i and 10g Workshop II. The reason is that in those classes, we had a full day of backup and recovery exercises. The students got a bit of time to prepare for their trials before I was free to unleash unholy terror on their poor, unsuspecting databases.

Now I would help them out a bit by putting up a list of recommended practices. Set your backup parameters properly, assemble the necessary pieces, take a backup, that sort of thing. But I also let them know they were welcome to add whatever personal touches they wanted to feel comfortable in the face of my onslaught. Once they finished we would begin. Twenty eager students versus me, Master of Destruction and Patron Saint of ORA-600s.

In one particular class I had a student who I could tell from day one was a cut above. He asked the truly difficult questions. He finished the labs in record time. He told me about his experiments, ideas, and plans. So I knew he would be a ton of fun when the final lab day came along. I had something special ready for him.

So everyone made their preparations, and I could see a cocky grin on the guy as I told them it was time. I started logging into student machines and tricking up their databases in various ways… delete a controlfile here, drop a redo log there, write some data to a datafile elsewhere. I won’t bore you with all the different predicaments. You and I both know only one story matters. The showdown was on. An epic battle, you see, between myself and the upstart who thought he could beat a teacher. A veritable David and Goliath in the works. And I would be victorious. Oh yes.

I started off light, testing his weaknesses. Dropped the USERS tablespace and TEMP. I figured he’d notice USERS but I wondered about TEMP… no dice. Within a minute or two he slapped his finger down on the Enter key, looked up, and grinned.

Round two. Two members of a log group down, one member of a different log group toast, overwrote one of the controlfiles so they were out of sync, and renamed a datafile. A little overboard so early on, but I needed to see how quick he was. He ended up going for a recovery until logseq, got it back with an incomplete recovery, and cleaned up after himself to make it look squeaky clean. Nothing too impressive, mind you. But he did it fast, ending the round with the loud clack of the Enter key and a smug smile. This time he put his hands behind his head for a final flourish. No more mister nice guy.

I opted for deletion of all his archive logs this time. Just to show him who’s boss, mind you. Two datafiles got bad blocks written to them. You might call it petulant but for good measure I deleted the RMAN binary from his $ORACLE_HOME. Seriously folks, it was for his own good. I stood up from my seat, relinquishing the protection afforded me by the screen that blocks every student’s view. Our eyes met. If it hadn’t been so sunny outside, thunder would have crashed.

Leonidas image property of Warner Brothers.

Five minutes later he was done. Not just finished, but done. Everything was recovered and back in place. He smiled like nothing every happened. He knew, the guy knew I deleted his rman binary. He didn’t say a word. He just smiled. The file was back like nothing had happened.

Dropping back into my seat like an Acme anvil over a coyote on rocket powered rollerskates I furiously started typing. Controlfiles gone. Datafiles gone. Redo logs gone. Archive logs gone. His secret stash of RMAN scripts (sneaky) gone. ORACLE_HOME gone. No, seriously. I deleted his entire Oracle installation. Don’t judge me. You would have done the same thing.

Seven minutes go by and he clacks and smiles. No. No way. I logged into his machine. Everything was back to normal. The ORACLE_HOME was back, the database was back, even the RMAN scripts directory. It was all there. He had beat the trials. I smiled and nodded like the proud dad at the end of a movie about little league baseball.

So what did he do?

This student was obviously prepared. In fact, he was hilariously over-prepared. If I had bothered to take a better look at the RMAN scripts he had created I would have seen the extremes he went to. Here is what he did at the beginning of every exercise:

• Took a tar backup of all scripts, the ORACLE_HOME, and other special files like oratab.
• Used RMAN to back up his database, archive logs, controlfiles (before and after), and spfile.
• Backed up controlfile to trace and spfile to pfile.
• Exported the database.

But that’s not all. He also put a copy of every single backup on at least three other students’ PCs. That time when I just deleted the rman binary, he scp’d the rman binary from another student’s installation to his own machine.

The biggest thing is that he was prepared. All theatrics and pseudo-gladiatorial commentary aside, he really did make sure he was prepared for any eventuality. Even though most of the things I inflicted his database with were outlandish and unlikely, he was ready anyways. That readiness is usually the mark of someone who has either been bitten (which we all have at some point) and someone who has vowed to treat their database as they would their own kin.

The fact of the matter is that your backups are production. No matter where you store them, how you manage them, or what tool you use to take them, backups should be treated like solid gold. In the event you lose a database, all that is between you and a really truly terribly bad day is a good backup plan. In fact, many companies opt to back up any database, even development and QA (which are also production to some folks). If there is one task that you should pay the utmost attention to, it is ensuring a solid and safe backup strategy for your company’s most critical asset: its data.

The post Backups and a Clever Student appeared first on Steve Karam :: The Oracle Alchemist.

[This entry is part 14 of 16 in the series Adventures of Ace, DBA] 

For your weekend pleasure (and back after many years), a new Adventures of Ace, DBA comic!

The post Big Data (Ace Comic) appeared first on Steve Karam :: The Oracle Alchemist.

Oracle is well known as a complex environment, and with good reason. Though they will object to that complexity at the sales level, they embrace it at the technical level. Let’s forget for a moment the pains this may have caused us in our careers and focus on an undeniable plus: Oracle gives great metrics.

Of all the environments and architectures I have worked with over the years, Oracle undoubtably has the best reporting. Statspack, AWR, ASH, X$ and V$ views, log files, the list goes on and on. Thanks to these repositories of metadata there really aren’t many problems you can’t diagnose by following the bottleneck trail to the root cause. But with this wealth of insight comes a lot of uncertainty and bad advice which ultimately can lead to a serious case of Too Much Information (TMI).

And because of that, we have…

Five Frequently Misused Metrics

There are of course many metrics that are mishandled, abused, or overlooked in the Oracle world. These are the five that are most apparent in the DBA and developer community.

5. db file scattered read

By talking to many DBAs, it has become apparent to me that there was in fact a third tablet given to Moses at Mt. Sinai which simply said “Thou shalt not full table scan”. For whatever reason it never made it into official biblical canon but DBAs and to some extent developers still honor the lesson to this day. Or something like that.

Full table scans are the Devil!

First of all, the ‘db file scattered read’ metric is not always indicative of a full table scan. It is simply an indication of multiblock I/O which could come from a full table scan; it can also come from index full scans and fast full scans. And ‘db file scattered read’ waits aren’t the only multiblock I/Os out there and certainly not the only indicators of full table scans. In 11g a ‘direct path read’ can also indicate a full table scan, and events like ‘db file parallel read’ also do multiblock I/O.

The misuse of this metric comes from the fear instilled in any new DBA regarding full table scans. At some point DBAs and developers alike were told to treat them like the plague, and an inexperienced DBA will often hunt for them at the first sign of slowness regardless of what’s actually holding up a system.

As you hopefully now know, full table scans aren’t always the devil. There are many times when a full scan is the right way to access table or index data and times when an index is the way to go. The mere sight of the ‘db file scattered read’ metric is not reason enough to panic.

4. Parse to Execute Ratio

Somewhere in the DBA initiation ritual “parse to execute ratio” became synonymous with “hard parsing too much”. Oracle training, documentation, and many tuning blogs and sources don’t do much to expand or dispute this.

Reducing hard parses is something you have to do, but simply reducing hard parses isn’t enough. If you do a hard parse and then execute you have a parse to execute ratio of 1:1. If you do a soft parse then execute you’re still at 1:1. A combination of hard parse reduction and cursor caching is required for a good parse to execute ratio.

Pay close attention to the client software being used against your database. Different application servers and programs will have different cursor caching mechanisms. In this example, JBoss required the ‘prepared-statement-cache-size’ parameter to be set to reuse cursors.

3. Buffer Hit Ratio

100% of Something Guaranteed!

Just because you’re pulling a bunch of data from cache doesn’t mean you’re using your cache properly. Caching and retrieving blocks is about managing your working set of data, not managing a number between 0 and 100.

When you run an AWR report and look at the Buffer Hit Ratio metric, remember that you’re looking at a one hour average. Over the course of the hour your hit ratio may be 99%, but that doesn’t mean the blocks you needed were in cache when you needed them.

If you are concerned only with having a good buffer hit ratio, make your buffer cache really small and cache a few thousand random blocks then query them again a few times. Instant success!

Buffer hit ratio is a feel good metric and good for dashboards and a quick glance for an outlier time window. Using it purely as a tuning or alert metric is not recommended.

2. CPU %

This is another ‘top’ DBA favorite, but is also very popular among developers, QA/testers, and management. When CPU on a Linux or UNIX system reaches 95%+ CPU usage, alarm bells instantly go up and bad performance is assumed.

I blame Windows. Single CPU machines running bulky operating systems taught us that if CPU is at 99% you’re having a bad day. However, on an Oracle database environment 99% CPU usage can actually be considered a very good thing under the right circumstances.

Usage alone is not a viable metric. One other important metric is the run queue length, or load. This is the stat you see with the uptime or top command (in 1, 5, and 15 minute averages) or realtime using vmstat. It (usually) represents the number of processes waiting/running on CPU at any given moment. As such it is a good measure of concurrency and overall usage of CPU resources in most cases; however, there are exceptions depending on your OS and I/O usage (e.g. uninterruptible I/O wait). A very general rule of thumb is that load should not go above the number of cores on your system, though there are exceptions to this rule as well. To be precise, a combination of CPU usage, load, and I/O wait should be used to evaluate capacity. While gauging your usage, remember that load != percent in use, and multi-core != multi-threaded (no matter what AWR says).

Average Active Sessions

Consider this: you buy Oracle licenses by the CPU. If your CPU usage percent metric is 50% during your busiest time, then you are only using half your processing power and dollar investment. CPU is fast and it is where work takes place. If your Oracle instance is not waiting, then it should be working. And if it is working, it should be using all the power (and licensing dollars) at its disposal.

If you really want to tie in wait + work in an Oracle environment, I advocate paying attention to Average Active Sessions. It is an easy to compute and easy to read metric within Oracle that can easily be broken down into individual waiting components and give a good indication of overall load on the Oracle environment.

More on DB Time and Average Active Sessions

• Time Matters – DB Time
• Average Active Sessions – The Magic Metric?
• Average Active Sessions in SQL*Plus

1. Cost

Taking the #1 spot for the title of Most Misused Metric Made Meaningful Maladroitly (M6) is cost.

Just because Oracle’s optimizer is cost based doesn’t mean your tuning practices should be. Cost is a metric Oracle calculates in order to rank execution paths for queries. This means that looking at cost values in explain plans can help you understand why Oracle chose the plan it did.

However, a quick look online or working with some developers and DBAs on query tuning will frequently raise questions like “How do I reduce the cost of this query?” or “Why is my query slow when cost is low?”. The simple truth is that cost is not a tuning metric. When you try to tune a query you should do so with the metrics that matter the most to your situation. In most cases this comes down to reducing block touches (both physical and logical reads) and processing requirements by improving the selectivity, join order, and index usage of your query. If you want to reduce the cost of a specific execution plan so it will be chosen by the optimizer that is a worthwhile goal but trying to reduce the cost of a query in general makes no sense.

Know the bottlenecks that hold up your Oracle instance and your query. Find the components of your query that take a long time or the combination of components that hold up efficient processing. Learn to properly display and read explain plans. And most importantly, don’t stop at just the explain plan. Learn to use tools like tkprof and Tanel Poder’s Snapper utility so you can get the big picture as many query issues arise from issues larger than a ‘bad explain plan’.

Cost is very important to Oracle but plays a bit part in your tuning saga. If you tune strictly to reduce the cost of a query, you are missing out on the big picture and only making tuning harder on yourself.

The post These Aren’t the Metrics You’re Looking For appeared first on Steve Karam :: The Oracle Alchemist.

I have been tinkering quite a bit with new database and development frameworks. Recently I decided to beef up my knowledge of data visualization as it’s a very hot topic and the ultimate way to make your data understood.

R is an incredibly popular option which can be as simple or as complex as you want it to be, showing incredibly detailed charts, tables, and more. However, I wanted to find something which was 100% web based and interactive, as this is the easiest way to really show your data visualization creations to the masses and put it to best use in dashboards or other sites.

So, enter D3.js. This incredible package is built in JavaScript with interactive components similar to jQuery but adding the drawing, math, and tweening capabilities necessary to build highly complex data visualizations. Anything that can be made with paths, shapes, and colors is possible as you can see on their example page. Personal favorites are the Streamgraph, Cubism, and concept network browser.

But in honor of Pi Day and due to its hierarchical capabilities, I decided to go with an interactive Sunburst. A Sunburst chart is a data visualization method similar to a pie or donut chart but with additional layers for child data. In the case of my tinkering, I decided to create one which includes:

• Oracle Schemas
• Object Types
• Objects

Each of these components is represented along with its respective size in MB. Even cooler, you can click on a component to drill down into it and click the center of the circle to zoom out. All of this was accomplished with no Flash, and it is tested on Chrome, Firefox, Safari, Internet Explorer, and iOS. Chrome and Safari definitely handle it best, but the other browsers appear to work fine. However, if you are reading this from a news aggregator like Oracle Base or OraNA you may need to click over to my actual site or use the full page demo link below.

The original D3.js example on Sunburst charts can be found here. I also took concepts from this page for zooming. The JSON supplying the data was created using the Alexandria PL/SQL Library which includes SQL to JSON capabilities. Alternatively you can use CSV, TSV, and XML to populate D3.js charts. It is worth noting that a decent amount of JavaScript knowledge is needed to really grasp what the code is doing. D3.js makes heavy use of attribute chaining like jQuery which is powerful but sometimes difficult to read and understand.

If you don’t see the example below then there may be a compatibility issue I’m not aware of; please post it in the comments! You can also try my full page demo which may work better for you. You can also view source on the full page demo to see everything I did to make it come together, or just click here.

Be sure to hover over different areas on the chart (labels show underneath) and click around! You can always click the center circle to zoom out.

Sunburst Data Visualization   var width = 340, height = 340, radius = Math.min(width, height) / 2; var x = d3.scale.linear() .range([0, 2 * Math.PI]); var y = d3.scale.sqrt() .range([0, radius]); var color = d3.scale.category20c(); var content = d3.select("#valtext"); var svg = d3.select("#chart").append("svg") .attr("width", width) .attr("height", height) .append("g") .attr("transform", "translate(170,170)"); var partition = d3.layout.partition() .value(function(d) { return d.size; }); var arc = d3.svg.arc() .startAngle(function(d) { return Math.max(0, Math.min(2 * Math.PI, x(d.x))); }) .endAngle(function(d) { return Math.max(0, Math.min(2 * Math.PI, x(d.x + d.dx))); }) .innerRadius(function(d) { return Math.max(0, y(d.y)); }) .outerRadius(function(d) { return Math.max(0, y(d.y + d.dy)); }); d3.json("/sunburst.json", function(error, root) { var path = svg.selectAll("path") .data(partition.nodes(root)) .enter().append("path") .attr("d", arc) .style("fill", function(d) { return color((d.children ? d : d.parent).name); }) .on("click", click) .on("mouseover", mouseover) .on("mouseout", mouseout); function click(d) { path.transition() .duration(750) .attrTween("d", arcTween(d)); } function mouseover(d) { content.append("h2") .attr("id", "current") .text(d.name + (d.size ? " - " + (d.size / 1024 / 1024) + "MB" : '')); } function mouseout(d) { content.html(''); } }); d3.select(self.frameElement).style("height", height + "px"); // Interpolate the scales! function arcTween(d) { var xd = d3.interpolate(x.domain(), [d.x, d.x + d.dx]), yd = d3.interpolate(y.domain(), [d.y, 1]), yr = d3.interpolate(y.range(), [d.y ? 20 : 0, radius]); return function(d, i) { return i ? function(t) { return arc(d); } : function(t) { x.domain(xd(t)); y.domain(yd(t)).range(yr(t)); return arc(d); }; }; }

The post Web Friendly Interactive Data Visualization with D3.js appeared first on Steve Karam :: The Oracle Alchemist.