Christopher Lawson's Blog, page 14

Oracle Performance: How to Find Slow Sql

Find the Culprits!

The first step in performance tuning is to find the culprits! Well, Oracle provides a great view we can use:  V$Sql, or Gv$Sql in the case of RAC. We can simply query that view, and look for suspicious sql.

I like to find sql that has a lot of logical reads per execution, and also runs over a certain time.

Here's a simple query that I use a lot:

Look for Resource-Consumers

You can see above that there are a few jobs that take a long time to run. Of course, you can play around with the script, looking for longer or shorter runs, etc.

It's that easy!  Of course, finding the bad sql is just the start, but it's a good start!

Snappy Interviews: 100 Questions to ask Oracle DBAs

By Christopher Lawson






Oracle Performance Tuning: Find the Slow Sql

How to Prevent Ebike Fires

How to Prevent Ebike Fires























Tips to Avoid Causing a Fire1. If you modify the electrical connections, have an expert electrician examine your connections--just to be sure!2. Buy batteries from a trusted source.3 Don't overcharge your battery.4 Don't over heat batteries. Store between 40 - 80 F5. Charge when your awake .6. Do not install amateur wiring (twisted, wire nuts, electrical tape). Use quality connectors, solder and heat shrink!7. Buy a smart charger and set it to charge to a safe less-than-100% level. Use a mechanical timer cutoff for the AC power so if the charger does not cut off, the power is cut before the battery hits 100% (or pick an even safer number like 75% charge and 80%-ish power cut). The charger is a bit pricey but a mechanical timer is peanuts.

Thanks to members of the Sondors Owner's Group, and Sondor's Modder's Group!

Avoid Fires on your Ebike

How to Avoid Setting Your Ebike on Fire

How to Avoid Setting Your Ebike on Fire























Five Tips to Avoid Burning Up Your Ebike

1

2

3

4

5

The Native Advertising Advantage: Build Authentic Content that Revolutionizes Digital Marketing and Drives Revenue Growth by Mike Smith

This Better be Good

In THE NATIVE ADVERTISING ADVANTAGE Mike Smith gives a good overview of the history of native advertising—plus his perspective on how it might evolve in the future. Much of the book documents interviews with experts in the field, and their opinion on native advertising.

Keep in mind that this isn’t some type of “Dummy Guide” on hot to implement ads:

“This book isn’t intended to be a “how-to” guide to native advertising. Instead, the goal is to explain what native advertising is; when native advertising started, and how native advertising has grown and developed.”

The early part of the book covers interviews with experts, and what they think “Native” really means. The author concludes by giving his own definition: “Native advertising is a form of paid media that follows the form and function of the site or user experience that it lives within.”

Native advertising is a big field now—especially as users have grown weary of traditional forms of digital advertising:

“Native advertising has become a necessity, since display advertising is proving less effective and ad-blocking technology is rapidly being adopted.”

The Native Advertising Advantage by Mike Smith

The Native Advertising Advantage by Mike Smith

The key to this type of advertising is to make it blend in really well, and not disrupt the user experience, or look really cheap: “The challenge to native advertising is to make it subtle enough that it truly becomes “native”—that it blends in with the surrounding editorial material so that readers will read it and not care that it was written by an advertiser rather than by a journalist or by a journalist on behalf of an advertiser.”

In short, “Most people prefer to read ads that don’t look like ads.”

Readers especially don’t want to be seen as “duped” by articles which are really just cheap pitches for a product. The ads have to be “aboveboard,” clearly identifying the sponsor—that the piece doesn’t confuse or irritate readers who may feel that they’ve been duped into reading something that isn’t truly informative but just turns out to be a pitch for some product or company. “

Although the book is not really a “how to” type of book, there is still lots of good advice. Here is the best piece of advice I read in this book:

“Good native advertising should be written so that readers or viewers or consumers get something out of the piece, other than a pitch for a product they could buy.”

In other words, “Create Content That Matters to Customers.”

So all in all, I found THE NATIVE ADVERTISING ADVANTAGE to be a useful book, and an interesting survey of the digital advertising field. It seemed to me that the author has a good grasp on this topic. I would have preferred perhaps less interviews, and more analysis, but other readers might enjoy the interviews.

After reading this book, I found myself clicking on native ads MORE. I want to see how well they blend in. One funny thing—I discovered that articles that are native ads sometimes take a lot more time to load on my ‘Droid. Well, I guess that means those are the ones that DON’T blend in too well.

Shop Amazon Devices - Introducing All-New Kindle Travel Bundles, starting at $199[image error]The Native Advertising Advantage by Mike Smith

Oracle Golden Gate Performance: Parallel Process Groups

For the "Classic" Replicat, there is just one process applying changes. But if the source system is doing lots of DML, this single process can easily be overwhelmed. This is especially true if the source database is a RAC system, and thereby can make changes at a huge rate.

Here's the Problem

For example, on a large RAC system where I work, one of the replicated tables has about 25 million changes applied per hour.  The single Replicat process can manage only 6 million changes per hour.  As one would expect, we experienced several hours of lag time. It's not a question of the transactions running too slow--they are taking less than 1 ms.  The problem is that there is only 1 process.

To solve this problem, we modify GoldenGate configuration and create additional Replicat process groups. In our case, the changes were happening about 4x faster than we could process them, so we modified our setup to have a total of 4 Replicats. 

Update the Parameter Files

With multiple Replicat processes, the work will be evenly split. This is achieved via the @Range function.  In each parameter file, we add this Range parameter to the Map line:

@Range (Group#, #of Groups)

So, our original Replicat will contain @Range(1,4), and the other (new) Replicat parameter files will contain

@Range(2,4), @Range(3,4), and @Range(4,4)

Checking the Final Configuration

Here is what our setup looked like with 4 Replicat process groups:

The Result

After we added the extra Replicat process groups, our ability to process transactions went up as expected. This is a good way to solve a performance bottleneck, but you must exercise great care in changing the parameter files.

Oracle GoldenGate 12c Implementer's Guide



$59.97



By John P Jeffries















Snappy Interviews: 100 Questions to ask Oracle DBAs







By Christopher Lawson

Oracle Performance: Why is Flashback Query Slow?

I'm Flashing-Back as Fast as I Can!

Flashback - a Misleading Name

Despite the appealing name, "flashback," a flashback query can run very slowly. On a large production system, a flashback query going back a few hours can easily take 10 hours. What--how can that be?

This happens because Oracle must reconstruct an object as it existed at a certain time. This is the same idea of read-consistency. This reconstruction happens one block at a time, going backwards in time, undoing each transaction

Starting the Undo

There are other issues with a flashback query that make the process run even slower. Of course, Oracle does indeed save the undo information--we can certainly find it, and a flashback query really does work. Here's the problem: The structure of undo segments is heavily biased towards quickly saving transaction information--not quickly reversing transactions.

Before Oracle can reconstruct an object, it has to identify what needs to be undone. One would think this is a trivial step, but not so. This can be very time-consuming--especially when the database has undergone lots of recent transactions.

Transaction Table

In each undo segment header there lies a critical structure known as the transaction table. It's not a "table" as we normally think of it. Maybe a "list" would have been a better name. The transaction table identifies the undo information held in that undo segment. For example, any given entry points to where to find the actual undo block. That sounds excellent, but the entire transaction table only has information for 34 transactions. (Yes, that sounds small to me, too.)

Each entry is called a transaction slot. As more transactions are housed in a given undo segment, transaction slots, being so few, are very often overwritten. The information is not lost, of course, but to find it, several extra steps will be required. On a very busy system, it could take thousands of extra reads just to find where to start. (That's why I observed that Oracle seems very biased towards going forward with the undo, not actually applying it.)

Remember--all this effort is before Oracle even starts the "real work" of rebuilding the object of interest to the time desired. Of course, that final step will add even more time. The point is, the delay of determining where to start can be vastly more than the work required to actually do the reconstructing of the object.

Troubleshooting

Troubleshooting a flashback query delay is not so easy. On a busy system, I have seen flashback queries require millions of extra reads to flashback a small table with only 20,000 transactions that needed to be undone. If you query the active session history for the session of interest, it will show that it is performing sequential reads from an undo tablespace. One could easily be fooled into thinking (as I did) that there must have been a huge number of transactions on the table of interest. We know better now--the reads were actually Oracle synthesizing the undo information in the transaction table, not actually applying it to the object of interest.

When a transaction table slot is reused, what happens to the valuable information that used to be kept in that slot? Well, there's one logical place for it to go--somewhere in undo-land. In fact, Oracle stores the old slot information right at the beginning of the new undo block that used that slot. In this way, the information is linked together. Therefore, when we perform a flashback query, we can discover what the transaction table looked like at some prior state.

Measuring Undo of the Undo?

Oracle Performance: Why is Flashback Query Slow?

Hey, what a minute--all this almost sounds like "undoing the undo!" You're right, and Oracle calls it, "Transaction Table Rollback." You can also get a summary in the AWR report, in the Instance Activity section.

You can also quantify this event in real time, to get a feel for how often this is happening. On a busy system, it is likely happening all the time. Let's see how we do this on a busy RAC system. Here is one way to see this occurring in the current connected sessions. This would be helpful to know if someone is doing a flashback query that seems to be running far longer than expected.

In this script, I look for large values of transaction table undo, and list the sessions. I also ignore the background processes (that's why I exclude programs like 'oracle')

What can I do?

The essence of the problem is having to repeatedly reconstruct the contents of the "slots" in the transaction table. If there were fewer re-uses of the slots, then there would be less work required. Oracle support has suggested keeping more undo segments online--and therefore more slots available.

This is accomplished by setting the underscore parameter, _rollback_segment_count. The idea is, to override the automatic undo process, and force more undo segments to stay online. It seems like the number of reused "slots" should go down commensurate with the extra undo segments that are kept online. So, if we keep 4x as many undo segments online, I would expect to see approximately a 4x reduction in transaction table rollbacks. That's the theory, anyway, but I haven't confirmed that yet.

As of this date, we have not yet tried the secret rollback segment parameter. We are wondering about adverse effects, and intend to test the parameter with all of our batch jobs.

We can't help wondering why the database thinks it's a good idea to take undo segments offline in the first place (and what will happen when we block that.) Perhaps the caching effect is better when there are fewer undo segments involved?

We haven't been able to get a clear answer to that question. I am anxious to see what happens.

Snappy Interviews: 100 Questions to ask Oracle DBAs

By Christopher Lawson

Oracle Performance: Why is Flashback Query Slow?

Oracle Performance: Why is Flashback Query Slow?

Old School: Life in the Sane Lane by Bill O'Reilly and Bruce Feirstein

Old School: Life in the Sane Lane

Old School: Life in the Sane Lane by Bill O'Reilly and Bruce Feirstein

OLD SCHOOL: LIFE IN THE SANE LANE is a light-hearted comparison of the “snowflake” school versus “Old School.”  The authors explain that we find ourselves in “an age when everything has to be politically correct, a time when everybody expects a trophy and everyone gets to be outraged about everything, all the time.”

Much of the book contains reminiscences of each author.  They take turns relating stories—mostly from their youth, explaining how times have changed.

Describing his tough time in football practice, O’Reilly explains, “It is simply impossible to describe how much fun we all had on a dirt field with little water for almost four hours every hot, humid day. We had Iwo Jima–type fun, root canal joviality. But, again, I got through it. That’s because I had acquired discipline. I could handle a good amount of pain, both physical and mental. No question, Snowflakes would have melted in three minutes on that dusty field.”

One funny story relates to an odd event, called the “Moon Platoon,” which was “despite the name, was not an astronomy club.”  After the boys were all lined up and ready to go, “all at once, a whistle sounded and about two dozen American college students turned their backs on the Dayliner and dropped their pants. Horror is too slight a word to describe the reaction on the boat.”

Chapter 12 Are You a Snowflake? presents a series of questions.  For example, “You might be a Snowflake if you’re twenty-seven and still living in your parents’ basement.”

Conversely, “You’re Old School if you stand up during the national anthem,” or “still bend over to pick up a penny.”

So all in all, I found OLD SCHOOL: LIFE IN THE SANE LANE to be a humorous, easy read.  It’s full of funny stories.  This is a lighthearted read—don’t take it too seriously.

 

Old School: Life in the Sane Lane



$14.16



By Bill O'Reilly, Bruce Feirstein






Old School: Life in the Sane Lane by Bill O'Reilly and Bruce Feirstein

3 Steps To Find Your Next Job Using Your Current Job

Learn "On the Job"

Dedicated professionals continually struggle to keep up with the latest technology, while also meeting their many obligations at work.  This apparent conflict leads to the question, “Is it possible to learn new skills and technology at the same time as fulfilling the daily job requirements?” 

The answer to this is a resounding “Yes.”  The high-tech employee can learn new skills without compromising the service to the firm.  In fact, these skills can often be learned while improving the service provided to the firm.

Here's How it Works

This technique of “Learning on the Job” is based on implementing three types of behavior:

   Become a good mentor to your colleagues   Research new ideas and technology   Write up “lessons learned” from company projects

Note that these recommendations are really part of a “win-win” strategy; the employee is not at all shirking his or her duties.  Instead, the employee gains understanding of new technology, while the employer benefits from new ideas that profit the firm.  In fact, one could argue that the firm is receiving the better “deal,” because the employer receives this extra benefit without having to pay any training costs!

Mentoring

The first step in this win-win strategy is to become a good mentor.  “Transfer of knowledge” is a frequently heard mantra in many corporations today.  Companies naturally want a good return on their investment; thus, they want the more knowledgeable workers to help others “come up to speed.” 

Surprisingly, mentoring is often viewed as an annoying chore, perhaps of some benefit to the younger worker, but certainly of little value to the mentor.  This attitude, however, is a mistake, and causes the senior worker to miss valuable opportunities for professional growth.

What happens during mentoring?  When properly conducted, the mentor will solidify their understanding of the subject matter.  Mentoring tests the skills of the mentor; these “tests” lead the mentor to “stretch” as they ponder difficult technical details. Thus, a good Java programmer will naturally become a better Java programmer, and a good DBA will naturally become a better DBA.

These results naturally follow, because of a long-understood principle:

The teacher always learns more than the student.

Thus, instead of shying away from mentoring assignments, the wise employee will volunteer to assist peers with new technology.  Conversely, one should be open to asking questions and learning from more experienced “mentors.”

Research New Ideas

The second step in this career-growth strategy is to become a good researcher.  All companies want to use the best technology to meet their goals as efficiently as possible.  This corporate goal meshes perfectly with our individual goal of learning new ideas.  After all, someone needs to take the initiative to investigate the new technology—whether a new software language, new application, or new hardware.

Many in the firm will avoid branching out and researching new ideas.  These individuals will be stunted in their career growth; after all, little is learned from solving the easy, routine problems.  On the other hand, employees willing to tackle new technology will gain a reputation as the “go-to” person in the company.  These wise individuals are not just “lucky;” rather, they have made the effort to go outside their “comfort zone” and take the initiative.

How to Become an Expert?

As a practical matter, what are some ways to “become the expert?”   Here are a few suggestions:

    Try to reserve time for creative brainstorming sessions.
    Read user guides.  It is amazing how quickly someone becomes “the expert” just because they have read the manual.
    Research “white papers” or other articles relevant to the technology at work

Write up “Lessons Learned”

The final step in the “Learning on the Job” strategy is to write-up useful ideas—what we usually call “lessons learned.”  While a project is progressing, make a few rough notes of “things that worked” or technology that was of some benefit.  Then, at the end of a project, volunteer to document and present these findings. 

The effort of producing the document will naturally impart to the writer a greater understanding of the technology.  The firm will certainly appreciate your efforts, and once again, everyone benefits.  The firm has a permanent record of good project ideas, and the employee becomes more skillful at some new technology.

Besides a simple paper produced just for the project team, this idea can be carried further.  The lessons-learned paper can easily be transformed into a more formal “white paper.”  This document can then be presented to a wider audience at the firm, published in a journal or even presented at a professional conference.  Often, these presentations lead to an exchange of knowledge with like-minded professionals in your field of expertise.

A True “Win-Win” Strategy

By following these three steps: mentoring, researching, and writing, the employee or consultant will enrich their own skills, while simultaneously increasing their value to the firm.  None of these steps is difficult, but each requires focus and determination.  As a result, the employee achieves greater competence and exposure to new technology, while the employer adds a new “go-to” expert to their staff.  And that is a perfect “win-win” situation.

Nixon's White House Wars: The Battles That Made and Broke a President and Divided America Forever by Patrick J. Buchanan

NIXON’S WHITE HOUSE WARS documents, in great detail, the battles among top White House staff—especially the struggles for conservative causes. The author, Patrick J. Buchanan, kept detailed records and his correspondence with the president and other top officials. Richard Nixon “asked for and welcomed my missives. It became our primary means of conversation. Over the Nixon White House years, I would send him a thousand.”

This book is designed specifically for political junkies who really like all the nitty-gritty details about the Nixon presidency.  If you are fascinated by reading detailed memoranda arguing for or against certain political causes, you will likely enjoy this book.  I generally skipped over the memos.

It’s easy to see how the author came to such a high position at such a young age (barely 30!).  Buchanan writes well, and argues fervently for his conservative beliefs.  Coming into the White House, the author had high hopes that Nixon would advance true conservative causes. He soon discovered, however, that Nixon was not nearly as dedicated as Buchanan. The author laments Nixon seemingly embracing “Great Society” extensions in the tradition of LBJ. Right after Nixon took office,

“My fears that this was not going to be the conservative administration I had envisioned during my three years with Nixon were confirmed.“

If you read nothing else, don’t miss the chapter on Nixon’s historic visit to China. I thought this chapter was the most interesting part of the book; it also shows the author’s dismay with the administration’s lukewarm embrace of conservative principles.

Nixon's White House Wars: The Battles That Made and Broke a President and Divided America Forever by Patrick J. Buchanan

After the visit to China, for example, the author is disgusted at what he saw as a complete sell-out of our Taiwan friends.  On the flight back, Buchanan stands up to Henry Kissinger, who negotiated the “Shanghai Communique.”  Kissinger asked Pat what was wrong with the document, and tried to defend it. Buchanan would have none of it:

“Though sitting in a window seat , I stood up, leaned over, put my face about eighteen inches from his, and shouted, “Bullshit!”

The latter part of the book covers the whole Watergate mess—all the way from the first reports of a break-in, to Nixon’s resignation.  I did not know that Buchanan had actually testified about his peripheral role in Watergate.  Similarly, I had no idea that the author’s brother had been falsely accused of money laundering during that same time period. (Cronkite’s network had to issue an apology.)

The author includes the transcript of a light-hearted testimony before Senator Sam Ervin. The author also includes voluminous copies of memoranda sent to the president. Perhaps the most interesting was the one recommending that Nixon burn the tapes.

This book is quite serious, as is the author.  There are a few lighthearted moments, however.  In China, Buchanan describes the drinking bouts:

“One problem we all had that night was the drinking. The mao-tai the Chinese served for toasts— I still have four bottles—tasted as one imagines gasoline might taste. It was awful. The only thing that made it tolerable was that the more we consumed the more we began to ignore the taste.”

Another funny moment describes the author and Henry Kissinger poolside:

“Henry, wearing his bathing suit and working on a tan, repaired to his chaise, beside which lay papers and files. As we talked, he bemoaned the fact that though he was national security adviser to the most powerful man on earth and had secret papers lying all about him, no beautiful women had tried to seduce him.”

So all in all, I found NIXON’S WHITE HOUSE WARS to be an interesting book, documenting one of the most turbulent political periods in recent times. I liked seeing the author’s perspective on the Nixon years—especially the visit to China.  Prior to reading this book, I did not realize how controversial this trip was, and how it angered the conservatives in the White House. The whole time I was reading this book, I kept thinking, “Buchanan was barely 30?”

Advance Review Copy courtesy of the publisher.

Nixon's White House Wars: The Battles That Made and Broke a President and Divided America Forever
































Nixon's White House Wars by Patrick J. Buchanan

Oracle Performance: Use Stored Outline to Change Execution Plan

Stored Outline: A Tested Method

It's very common to need to change an execution plan. Oftentimes, we insert a sql hint, which is s agreat way.  But what if you can't change the code?

One easy method is to create a stored outline--but in a tricky way.  We use an outline not to stabilize a plan, but to change it. 

Here's the idea:  We create 2 stored outlines; one is the exact sql we need to change; the other outline will be for sql having the sql hints to get the correct execution plan.  After we have created the 2 outlines, we switch the plans interally, so that the sql without the hints actually gets the exection plan of the sql with the hints.

That is, when the optimizer sees the sql in question, it looks for an outline, and applies the hints that we have provided.

Tricky, huh?

Follow my Instructions!

Use This Template

Here's the template I always use.  I have used this nearly a hundred times to fix production problems. In the following scripts, I have used "TBD" as a placeholder for the sql_id.  My own habit is to use the last 3 characters of the sql_id.

(1) Create Outline 1

 Alter Session Set Current_Schema = [Schema Of Interest];

 CREATE OUTLINE SQLTBD FOR CATEGORY XYZ ON {SQL WITH HINT}

(2) Create Outline 2

CREATE OUTLINE SQLTBD_FIX FOR CATEGORY XYZ ON {REGULAR SQL}

(3) Switch the Hints

update outln.ol$hints set ol_name =

decode(ol_name,   ‘SQLTBD_FIX’, ‘SQLTBD’,  ‘SQLTBD’, ‘SQLTBD_FIX’)

where ol_name in (‘SQLTBD’, ‘SQLTBD_FIX’);

(4) Get Rid of the Unneeded Outline

When we are all done with the "switch," we don't need the extra outline any more.  So we just drop it.

drop outline SQLTBD;

(5) Confirm the New Execution Plan is Correct

Naturally, you will want to check to make sure the outline is being executed properly.

I hope you find this method as easy as I have.  I literally have used this hundreds of times on production systems.

Snappy Interviews: 100 Questions to ask Oracle DBAs

How did I do?

Oracle Performance: Use Stored Outline to Change Execution Plan