DBA Blogs

The Hitchhiker’s Guide to the EXPLAIN PLAN: The story so far (Part 1–10)

Iggy Fernandez - Sat, 2014-06-28 16:13
On the Toad World site, I’m writing a series of blog posts and articles on the subject of EXPLAIN PLAN. I’m using EXPLAIN PLAN as a motif to teach not just SQL tuning but also relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for a […]
Categories: DBA Blogs

Editor’s Choice award at ODTUG Kscope14: NoSQL and Big Data for the Oracle Professional

Iggy Fernandez - Sat, 2014-06-28 08:42
My paper on NoSQL and Big Data won the Editor’s Choice award at ODTUG Kscope14. Here are some key points from the paper: The relational camp made serious mistakes that limited the performance and usefulness of the relational model. NoSQL is based on the incorrect premise that tables in the relational model must be mapped to […]
Categories: DBA Blogs

The Art of War for Small Business

Surachart Opun - Mon, 2014-06-23 11:51
The Art of War is an ancient Chinese military treatise attributed to Sun Tzu, a high-ranking military general, strategist and tactician. A lot of books have written by using Sun Tzu's ancient The Art of War and adaptation for military, political, and business.

The Art of War for Small Business Defeat the Competition and Dominate the Market with the Masterful Strategies of Sun Tzu, this is a book was applied the Art of War for small business. So, it's a perfect book for small business owners and entrepreneurs entrenched in fierce competition for customers, market share, talent and etc. In a book, it was written with 4 parts with 224 pages - SEIZE THE ADVANTAGE WITH SUN TZU, UNDERSTANDING: ESSENTIAL SUN TZU, PRINCIPLES FOR THE BATTLEFIELD, ADVANCED SUN TZU: STRATEGY FOR YOUR SMALL.
It's not much pages for read and it begins with why the art of war should be used with the small business and gives lot of examples and idea how to apply the art of war with the small business and use it everyday (It helps how to Choose the right ground for your battles, Prepare without falling prey to paralysis, Leverage strengths while overcoming limitations, Strike competitors' weakest points and seize every opportunity, Focus priorities and resources on conquering key challenges, Go where the enemy is not, Build and leverage strategic alliances).

After reading, readers should see the picture of  the common advantages and disadvantages in the small business and why the small business needs Sun Tzu. In additional, Readers will learn the basic of the art of war and idea to apply with the small business. It shows the example by giving the real world of small business.

Categories: DBA Blogs

Move That Datafile!

alt.oracle - Thu, 2014-06-19 15:56
Moving datafiles has always been a pain.  There are several steps, it’s fairly easy to make a mistake and it requires the datafile to be offline.  There are also different steps depending on whether the database is in ARCHIVELOG mode or not.  In ARCHIVELOG mode, the steps are…

1)      Take the tablespace containing the datafile offline
2)      Copy/rename the datafile at the OS layer
3)      Use ALTER TABLESPACE…RENAME DATAFILE to rename the datafile so that the controlfile will be aware of it
4)      Backup the database for recovery purposes (recommended)

If the database is in NOARCHIVELOG mode, you have to shutdown the DB, put it in the MOUNT state, etc, etc.  That’s certainly not that hard to do, but you get the feeling that there should be a better way.  Now in Oracle 12c, there is – using the ALTER DATABASE MOVE DATAFILE command.  With this command, you can move a datafile, while it’s online, in one simple step.  Let’s set this up.

SQL> create tablespace test datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf' size 10m;

Tablespace created.

SQL> create table altdotoracle.tab1 (col1 number) tablespace test;

Table created.

SQL> insert into altdotoracle.tab1 values (1);

1 row created.

SQL> commit;

Commit complete.

Let’s go the extra mile and lock the table in that datafile in another session.

SQL> lock table altdotoracle.tab1 in exclusive mode;

Table(s) Locked.

Now let’s use the command.

SQL> alter database move datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf'
  2   to '/oracle/base/oradata/TEST1/datafile/newtest01.dbf';

Database altered.

That’s all there is to it.  Datafile moved/renamed in one step while a table it contained was locked.

SQL> select file_name from dba_data_files where file_name like '%newtest%';

Categories: DBA Blogs

Intermediate Python Practical Techniques for Deeper Skill Development

Surachart Opun - Thu, 2014-06-19 10:40
It's time to learn more about Python. I found "Intermediate Python Practical Techniques for Deeper Skill Development" video course by Python expert Steve Holden.
It's very useful for Python video learning, but users should have basic about Python. They must install ipython.
Note start ipython by " ipython  notebook" command and users can check how to install ipython?and users should download example codes at https://github.com/DevTeam-TheOpenBastion/int-py-notes

This video course gaves deeply Python learning topics by using iPython, including:

  • Functions: return values, arguments, decorators, and the function API
  • Comprehensions, generator functions, and generator expressions
  • Understanding the import system and namespace relationships
  • Using the Python DB API to query and maintain relational data, and JSON to extract data from the Web
  • The NumPy, SciPy, and Matplotlib libraries for numerical and analytical computing
  • An introduction to unit testing with unit test
  • Deeper understanding of Unicode, with explanations of encoding and decoding techniques and the relationship between byte strings and text
  • An introduction to textual analysis using regular expressions
  • Information sources for documentation, further research, and coding style considerations

First of all, Users should install "ipython" and download examples codes. Users will be able to learn Python each topic easier, because it's easy to follow each example demo in video. It's very good to use this video course and iPython for Python improvement.

Categories: DBA Blogs

Oracle GoldenGate Data Transformation

VitalSoftTech - Tue, 2014-06-17 22:05
Oracle GoldenGate supports data mapping and manipulation. It is done by using options of Table (Extract) and Map (Replicat) parameters. By default OGG assumes that SOURCE and TARGET table definitions are same that part of replication.
Categories: DBA Blogs

The Hitchhiker’s Guide to the EXPLAIN PLAN (Act II)

Iggy Fernandez - Mon, 2014-06-16 14:04
Over at ToadWorld: Part 5: SQL Sucks! Part 6: Trees Rule Part 7: Don’t pre-order your EXPLAIN PLAN Part 8: Tree Menagerie Bonus article: Equivalence of Relational Algebra and Relational Calculus The story so far: A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables)  and […]
Categories: DBA Blogs

Oracle 12c Last Login time for Non-Sys Users

VitalSoftTech - Tue, 2014-06-10 09:45
Oracle 12c introduced a new, very useful security feature to store and display the last login information for non-sys users, logging in via SQL*PLUS. The last login time is displayed in the banner by default when we log into the database using SQL*PLUS in 12c. Related Articles • Oracle 12c: Data Redaction Unified Auditing Extended SHA-2 […]
Categories: DBA Blogs

The LGWR Three Second Rule. Really?

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.Does the Oracle Database 12c Log Writer Really Sleep For Three Seconds?I have learned the part of Oracle Database performance tuning is checking if what you have been taught is actually true. What I'm writing about today has been done before in Oracle Database 10g and 11g, but I wanted to document this using 12c.

When I was first learning about the Oracle Database process architecture, the instructor said there are a number of reasons the log writer (LGWR) background process will wake up and do "something." In addition to this, if the LGWR was not signaled to wake up, every three seconds it would wake up by itself and check if there was something it needed to do.

Is this "three second rule" really true? That's what this posting is all about.

If you don't want to read the details below, I created a short video for you to watch.

Here is what you just saw in static text. First let's get the LGWR process PID. Here's one way to do this.
$ ps -eaf | grep lgwr
oracle 41977 1 0 May16 ? 00:00:50 ora_lgwr_prod35
oracle 46294 46110 0 07:39 pts/0 00:00:00 grep lgwr
oracle 60264 1 0 Jan13 ? 00:14:14 ora_lgwr_prod30
My Oracle Database 12c instance is prod35, so the process I'm interested in 41977.

Note: Since this is a 12c instance, there are also two additional log writers, lg00 and lg01. But that's a topic for another posting!

At this point, for simplicity sake it's important there be little instance activity. It will make it much simpler to find what we are looking for... the three second sleep

To watch the LGWR process, I'm going to use the Linux strace command. To easily see the system call timing, I'm going to use the -r option. I'm also going to redirect the output, using the -o option to the out.txt file. I'll let the below strace run for about 15 seconds. OK... here we go:
$ strace -rp 41977 -o out.txt
Process 41977 attached - interrupt to quit
^CProcess 41977 detached
To break out of the strace, I simply did a control-c and the result is what you see above. Let's look at the first three lines of the out.txt file so I can tell you what we will be looking for.
$ head -3 out.txt
0.000000 clock_gettime(CLOCK_MONOTONIC, {19940373, 720653971}) = 0
0.000186 gettimeofday({1401979552, 853214}, NULL) = 0
0.000207 gettimeofday({1401979552, 853444}, NULL) = 0
With the -r option, the first column (far left) shows how long the system call took the for call directly above. For example, in the snipped above, the first clock_gettime call took 0.000186 seconds. The first gettimeofday call took 0.000207 seconds. Got it? Read on!

You may wonder why the system call time is the next line down? It actually makes since, because strace displays information as it becomes available. When the call starts obviously it does not know the call duration, so it just displays the call itself. When the call completes and therefore has the time, it shows return details (e.g., "= 0"), line feeds, prints the call time, the next call is displayed, and repeat. OK, back to our objective.

What we are looking for is a three second sleep. In other words, we are looking for a call that takes three seconds. And since a call's duration shown in the first column of the next line down, we want to look for values in the first column that start with 3.0.

Below is an example of what I found in the out.txt file.
0.000033 clock_gettime(CLOCK_MONOTONIC, {19940376, 746821505}) = 0
0.000033 semtimedop(7503875, {{16, -1, 0}}, 1, {3, 0}) = -1 EAGAIN
                   (Resource temporarily unavailable)
3.000436 clock_gettime(CLOCK_MONOTONIC, {19940379, 747351774}) = 0
0.000188 gettimeofday({1401979558, 879922}, NULL) = 0
0.000171 clock_gettime(CLOCK_MONOTONIC, {19940379, 747708976}) = 0
Looking at the above output snippet, notice the time of 3.000436. Then look at the call directly above it. It's the semtimedop call. This call can be used to put a process to sleep, but the process can be woken up. In other words, the LGWR can set an alarm for three seconds, go to sleep, and if it is not disturbed, it will wake up in three seconds. Shown above is an example of the LGWR setting the alarm for three seconds, not being woken up until the alarm fired. This is an example of the three second rule!

If you do what I did and look at your output file, you'll see lots of these "three seconds." If you do this on a pretty-much idle Oracle Database 12c instance's LG01 process (one of the two default LGWR children), it may contain mostly semtimedop calls, each with a three second sleep.

As you might have guessed, the sleep time is part of the system call. Look closely at the semtimedop call below:
semtimedop(7503875, {{16, -1, 0}}, 1, {3, 0})
Notice the "{3, 0}" near the far right? The defines the maximum delay, that is, the sleep. The 3 is the seconds and the 0 is the number of nanoseconds. If you're interested in other sleep options and the actual code to make this work, check out this posting.

Oracle could have used the select system call (not "select * from...") but that would have been a bad idea because then the process could not be woken before the three seconds limit. This topic touches very closely on Oracle Time Based Analysis and more specifically about Oracle's wait interface. I really get into this, including sample C code in my online video seminar entitled, Introduction To Oracle Time Based Analysis.

So there you go! The Oracle kernel code developers set the default sleep time to three seconds along with the ability for the process to be awoken, should the need arise.

Give It A Try
Check this out for yourself on a test or QA system. Once you see it for yourself, you'll never forget about it!

But it gets even better! If you really want to dig into this, do the same but on your database writer (DBWR). Does the DBWR have a three second rule? Does Oracle use the same system call? I'm not going to tell you the answer. You'll have to figure that out for yourself!

Always A Three Second Delay?
You may have noticed that sometimes the process does not sleep the entire three seconds and at other times Oracle sets the sleep time less than three seconds. What up with that? That will be my next posting!

Thanks for reading,

Categories: DBA Blogs

Oracle 12c - New SYS-level Administration Privileges

alt.oracle - Wed, 2014-06-04 08:47
For a while now, Oracle has been moving its security model toward a more differentiated set of privileges than just SYSDBA and SYSOPER.  We saw this in 11g with the SYSASM privilege.  This is in response to the growing number of DBA shops that delineate permissions at a more granular level, even among DBAs.  Rather than saying, “Here’s my DBA team, everyone has SYSDBA,” more and more IT shops are defining DBA job roles at a lower level.  If an application DBA never has the job responsibility to shutdown or backup a database, then maybe they don’t need SYSDBA.  However, the lines aren’t always that clear, so Oracle has been adding new levels of admin privileges.  In 12c, they’ve added several.

The SYSBACKUP privilege allows a user to connect to the target database and run RMAN commands, without requiring SYSDBA.  Here’s what it looks like.


Recovery Manager: Release - Production on Tue Aug 13 14:57:41 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target "altdotoracle@test1 as sysbackup"

target database Password:
connected to target database: TEST1 (DBID=1787845942)

RMAN> select user from dual;

using target database control file instead of recovery catalog

RMAN> backup tablespace users;

Starting backup at 13-AUG-13
allocated channel: ORA_DISK_1
Finished backup at 13-AUG-13


In truth, SYSBACKUP has a lot more rights than just those needed to do something like a hot backup, including startup and shutdown, creating tables and tablespaces and executing a number of supplied packages.  So from that perspective, I’m not exactly sure they hit the mark on this one.  Nevertheless, it does differentiate SYSBACKUP from the god-like SYSDBA admin privilege to some degree.  There are also the new admin privileges SYSDG, for administering Data Guard, and SYSKM to do Transparent Data Encryption (TDE) administration.  Consequently, there are new columns in v$pwfile_users to reflect these new privs.

SQL> desc v$pwfile_users
 Name                        Null?        Type
 --------------------------- --------     ---------------
 USERNAME                    VARCHAR2(30)
 SYSDBA                      VARCHAR2(5)
 SYSOPER                     VARCHAR2(5)
 SYSASM                      VARCHAR2(5)
 SYSBACKUP                   VARCHAR2(5)
 SYSDG                       VARCHAR2(5)
 SYSKM                       VARCHAR2(5)
 CON_ID                      NUMBER

If we examine the view itself, we see this in action.

SQL> select * from v$pwfile_users;

----------------- ----- ----- ----- ----- ----- ----- ----------
SYS               TRUE  TRUE  FALSE FALSE FALSE FALSE          0

Categories: DBA Blogs

Beware April 2014 PSU and Golden Gate Integrated Capture Users

Don Seiler - Tue, 2014-06-03 14:55
When the latest quarterly Patch Set Update (PSU) came out from Oracle, we planned to get it in place as soon as reasonable due to our need to stay current with security patches, and my need to apply what I had assumed were well-tested bug fixes for our installations. However we were in for an unpleasant surprise.

We first applied the April 2014 PSU on our staging & development database hosts and things ran fine. After two weeks, we put it into production. The next morning we noticed that our Golden Gate extracts were abending with a message like this:

2014-05-13 01:41:44 ERROR OGG-02077 Extract encountered a read error in the asynchronous reader thread and is abending: Error code 600, error message:
ORA-00600: internal error code, arguments: [knlogcPackColList:101], [1], [], [], [],[], [], [], [], [], [], []. 

Obviously the PSU was the only change from the day before when these GoldenGate extracts ran fine. The error itself seemed to match Bug 16306373 (OGG Lightweight capture fails with ORA-600 [knlogcPackColList:101]), which affects integrated capture extracts, which was what we were trying to use.

So we had two questions to answer:

  1. Was it really the PSU, and if so do we need to rollback?
  2. Why didn't we see this in development/staging?

The answer to #2 came pretty quickly: the extracts had been stopped prior to applying the PSU in development and were never restarted. Our use of GoldenGate is for a new project that is still not quite solid in form, and so the extracts might be shut down for weeks at a time. However the DBA team was not aware of this (something that will be added to the checklist in the future) and so that part was never tested. We decided to turn the extracts on and, sure enough, we saw the problem within 30 minutes.

As far as #1, we opened an SR with Oracle Support. At first they assured us it wasn't the PSU, but within a few hours had started backing away from that confidence and by the end of the day were suggesting we roll back. However we decided to hold off on that due to the luxury of not needing those extracts to run quite yet (we had just been capturing data in production to make sure we could handle the workload). That gave us and Oracle Support some breathing room to get it fixed.

Eventually Oracle Support did confirm that it was Bug 16306373. The patch for this bug, however, conflicted with another patch we had installed. So they created a new merge patch MLR 18689166 for us. This was applied that evening to just our development database home, and immediately we saw more problems.

First, regularly-scheduled materialized view refreshes were failing with:

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SNAPSHOT" 

Then we saw DataPump exports failing with:

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL" 

On top of that, GoldenGate gave us these errors trying to unregister the extract:

GGSCI (stagingdb) 14> unregister extract ext1ol database
ERROR: Cannot register or unregister EXTRACT EXTFOO because of the following SQL error: OCI Error ORA-01775: looping chain of synonyms (status = 1775). 

It definitely looked like something in that patch had hosed the catalog. Oracle quickly found the problem:

The problem with the invalid dictionary objects after the installation of patch 18689166 is due to the execution of script e1102000.sql that is being called from postinstall.sql e1102000.sql is a downgrade scripts and removes some objects from the data dictionary resulting in other dependent objects to become invalid. 
It is here that I should point out that Oracle had this merge patch in their QA for over 4 days. I could have seriously hurt myself from simultaneously rolling my eyes and face-palming as violently as I did.

The fix was to restart the instance into UPGRADE mode and run the catupgrd.sql script from $ORACLE_HOME/rdbms/admin, which rebuilds the catalog. We confirmed that this did fix the problem in DEV, and proceeded to patch the other databases, this time commenting out the call to e1102000.sql n the postinstall.sql script, per Oracle's workaround.

So, not only did the April 2014 PSU introduce a bug, but then the patch for that bug made things incredibly worse. We were fortunate in that we did not yet need the GoldenGate extracts in production. However many others will not have that luxury and will want to double-check that bug and the PSU if they're using GoldenGate and integrated capture.
Categories: DBA Blogs

Hot off the press : Latest Release of Oracle Enterprise Manager 12c (R4)

Pankaj Chandiramani - Tue, 2014-06-03 07:53

Read more here about the PRESS RELEASE:  Oracle Delivers Latest Release of Oracle Enterprise Manager 12c

Service Catalog for Database and Middleware as a Service; Enhanced
Database and Middleware Management Help Drive Enterprise-Scale Private
Cloud Adoption

In coming weeks  , i will be covering latest topics like :

  1. DbaaS Service Catalog incorporating High Availability and Disaster Recovery

  2. New Rapid Start kit

  3. Other new Features 

Stay Tuned !

Categories: DBA Blogs

The Hitchhiker’s Guide to the EXPLAIN PLAN (Act I)

Iggy Fernandez - Mon, 2014-06-02 20:17
On the Toad World site, I’m publishing a whole series of blog posts and articles on the subject of EXPLAIN PLAN. I’m using EXPLAIN PLAN as a central motif to teach not just SQL tuning but relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for […]
Categories: DBA Blogs

It's All About CPU But There Is NO CPU Bottleneck! What?

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.It's All About CPU But There Is NO CPU Bottleneck...
Diagnosing Oracle Database performance is like solving a puzzle. But what I really enjoy is coming up with performance solutions that are anti-intuitive for most people. You know, the ones when you can see people stop talking and actually think, "Why would Craig say that!?" In this posting, I delve into one of these situations.

The Situation
Just over a month ago I received an email from a former Australian Oracle Performance Firefighting student about their "CPU bottleneck." Based on an one-hour AWR report's Time Model Statistics section (which is based on v$sys_time_model view), the  DB CPU was 95% of DB Time, leaving 5% for Oracle wait time. The top CPU consuming SQL statements were easily identified and work had begun with tuning them. By "tuning" I mean a small group of DBAs were working to reduce their CPU consumption. But something didn't feel right to him, so he emailed me about it.

My first question was, "Is there an OS CPU bottleneck?" That is, is the host (or virtual machine if you like) out of CPU resources? I was wondering if the average CPU utilization was 80% or higher. Why was I asking this? Read on...

DB Time Math
Over an interval of time, DB Time is all the Oracle server process CPU time (i.e., DB CPU) plus all the non-idle wait time.

Note: Oracle does not include background process CPU in DB Time. Why they don't include background process CPU time is an entirely different subject. And of course, we are trusting Oracle that what they consider "non-idle" wait time should actually be "non-idle" wait time. Again, that's an entirely different subject.

My former student knew the DB Time math and therefore understood that since DB CPU was pretty close to DB Time, there was little non-idle wait time. It was "all about CPU." While I still wanted to know the wait event situation because it provides performance hindering clues and forces the DBA to review their work and think a little more.

OS CPU Utilization Math
In my Utilization On Steroids online video seminar, I demonstrate utilization math by shrinking myself down to the size of a water glass and also the size of a printer cartridge. (Like the motto says, "What Online Training Should Be!") In a nutshell, utilization can be viewed/understood as requirements divided by capacity. If a 500ml glass contains 250ml of water, the glass is 50% utilized that is, 50% full.

As I present in my online seminar, there are two simple methods to figure OS CPU utilization using only the v$osstat view. One of these methods as based on the BUSY_TIME and the IDLE_TIME statistics from the Operating System Statistics section of any AWR and Statspack report. The BUSY_TIME is the instance CPU consumption, that is, the CPU requirements. As I carefully explain in my seminar and in my classes, the capacity is actually equal to the BUSY_TIME plus the IDLE_TIME. That is:

Utilization = Requirements / Capacity

Utilization =  BUSY_TIME / ( BUSY_TIME + IDLE_TIME )

This is true unless the OS is updating OS structures incorrectly (e.g., /proc) or Oracle is not gathering the data from the OS correctly or frequently enough. And yes, I talk about how to check this in my seminar.

My former student did the utilization math. The average utilization was around 65%. Since this is the only Oracle instance doing any real work on the box, is was not a surprise to him that vmstat also indicated the OS CPU utilization was 65%.

Why I Asked About A CPU Bottleneck?
Suppose there is plenty of available CPU resources AND the SQL users are complaining about are doing nothing but buffer gets (i.e., logical reads). In other words, from a resource consumption perspective, it's all about CPU consumption. Is this possible? The answer is, YES!

If there was a single server process running this SQL statement, parallel query operations were not occurring and there are four CPU cores, what would the CPU utilization be? Probably around 25% (plus overhead between 5% to 15%). That is because on average only one CPU core was processing the SQL statement!

If this SQL statement took "too long to run" adding more CPU power (specifically more cores) would not help.

As strange at it may seem, one performance improving solution is to use the available CPU! Read on.

Use More CPU! Now!
Suppose the average CPU utilization of a 10 CORE box (or VM if you prefer) was running at an average of 50% utilization. This means that on average 5 of the 10 cores are busy. Now suppose a SQL statement takes 4 minutes to process without parallel query or very-cool (but perhaps costly) application design. How long would the SQL statement take to run if it ran in four parallel streams?

It depends. We need to ensure there is available CPU power. Assuming each parallel stream consumes one CPU core, the box has 10 cores with 5 being available (remember the average utilization is 50%), that leaves with us 5 "extra" cores. For our SQL statement to run in 4 parallel streams, we need three more CPU cores... and we have them!

If there is no parallelism overhead (ha! don't count on that!), the SQL statement would run in around 1 minutes, 8 CPU cores would be active (5+3), and the average CPU utilization would be around 80% (8 cores required / 10 cores of capacity).

So by creatively using available CPU resources, we were able to consume 4 minutes of CPU in a single minute! ...a beautiful example of parallelism. And of course, the users will be delighted and I may get a free pizza.

Be Anti-Intuitive
When a SQL statement, batch process or an Oracle process is constrained by CPU, yet there is plenty of CPU power available, the problem is probably a serialization issue. (Another possibility is some type of locking or blocking problem.) The solution can be found by parallelizing, thereby using the available CPU resources which will reduce the wall/clock/run time of the process.

If the above paragraph makes sense, then you understand what I'm talking about. If it does not make sense, stop, breath, and re-read the previous paragraph or paragraphs. It will be worth your time.

This next paragraph is really important to understand:

It is important to understand we have NOT reduced the total SQL statement's CPU requirements. We have NOT increased the total SQL statement's CPU requirements. We have NOT tuned the SQL statement in the traditional sense by reducing the buffer gets (logical reads, logical IOs). What we have done is simply altered the situation by consuming the necessary CPU requirements within a shorter duration. (In our example, the four minutes of CPU time was consumed within a single minute.) We did this taking a serial process and parallelizing it to use more of the available CPU resources.

Expand Our View Into The OS
Without understanding the OS situation we can easily misdiagnose the problem. And we can miss creative and powerful solutions.

Most DBAs immediately jump to tuning the SQL and tuning Oracle. While those are both valid technical solutions, there are others... like parallelizing to reduce run time while consuming more resources (in a shorter period of time).

Some DBAs but more likely OS Administrator but for sure hardware vendors may insist on getting more CPU power. While faster CPU will help somewhat, adding more of the same CPUs cores will likely do absolutely nothing...unless we can increase parallelism to use them.

Being able to determine the OS bottleneck is so important, I devote an entire chapter in my Oracle Performance Firefighting book on the subject. When I was learning to optimize Oracle systems, learning how to find the OS bottleneck significantly increased my value in the Oracle DBA market. Later when I ruminated on serialization and parallelization, I turned situations upside down by placing a larger (yet shorter-term) load on the system as a valid performance improving solution.

If performance is not what it needs to be AND there are available OS resources, consider using them to improve performance. It's a creative and rarely used solution.

Thanks for reading!

Categories: DBA Blogs

Oracle VM VirtualBox Tutorial: Connecting Applications from your Desktop to a database on VirtualBox

VitalSoftTech - Mon, 2014-05-26 09:45
If you have your Oracle VM VirtualBox setup and running there is a possibility that you may need to setup the network for the Virtual Machine to make it accessible from the host machine (Desktop or Laptop). This may be needed when you want to connect your local applications to the database running on the VM VirtualBox.
Categories: DBA Blogs

New OTN Tour

Hans Forbrich - Mon, 2014-05-19 12:51
For those of you who watch the OTN Tours, here is a list of the big ones this year

Feb 2014 - OTN Yathra by AIOUG (India) ... http://otnyathra.com/
May 2014 - OTN MENA by ARAB OUG ... http://www.otnmenatour.org/
August 2014 - OTN LAD North
August 2014 - OTN LAD South
November 2014 - OTN APAC

I was part of OTN Yathra (fantastic time again, thanks Murali), and have my name in the hat for LAD and APAC.  Unfortunately MENA conflicts with other scheduled events.
Categories: DBA Blogs

Limiting PGA with PGA_AGGREGATE_LIMIT in Oracle 12c

VitalSoftTech - Mon, 2014-05-19 09:50
In Oracle 12c, a new interesting much needed feature, has been introduced which controls the maximum amount of the PGA memory consumed by the user processes. The PGA_AGGREGATE_LIMIT parameter limits the amount of memory used by processes in the PGA.
Categories: DBA Blogs

Interesting info-graphics on Data-center / DB-Manageability

Pankaj Chandiramani - Mon, 2014-05-19 05:21

 Interesting info-graphics on Data-center / DB-Manageability

Categories: DBA Blogs

Tackling the challange of Provisoning Databases in an agile datacenter

Pankaj Chandiramani - Wed, 2014-05-14 02:03

One of the key task that a DBA performs repeatedly is Provisioning of Databases which also happens to one of the top 10 Database Challenges as per IOUG Survey .

Most of the challenge comes in form of either Lack of Standardization or it being a Long and Error Prone Process . This is where Enterprise Manager 12c can help by making this a standardized process using profiles and lock-downs ; plus have a role and access separation where lead dba can lock certain properties of database (like character-set or Oracle Home location  or SGA etc) and junior DBA's can't change those during provisioning .Below image describes the solution :

In Short :

  • Its Fast

  • Its Easy 

  • And you have complete control over the lifecycle of your dev and production resources.

I actually wanted to show step by step details on how to provision a 11204 RAC using Provisioning feature of DBLM  , but today i saw a great post by MaaZ Anjum that does the same , so i am going to refer you to his blog here :

Patch and Provision in EM12c: #5 Provision a Real Application Cluster Database

Other Resources : 

Official Doc : http://docs.oracle.com/cd/E24628_01/em.121/e27046/prov_db_overview.htm#CJAJCIDA

Screen Watch : https://apex.oracle.com/pls/apex/f?p=44785:24:112210352584821::NO:24:P24_CONTENT_ID%2CP24_PREV_PAGE:5776%2C1

Others : http://www.oracle.com/technetwork/oem/lifecycle-mgmt-495331.html?ssSourceSiteId=ocomen

Categories: DBA Blogs

archive_lag_target Works in SE

Don Seiler - Mon, 2014-05-12 15:34
TL;DR: The archive_lag_target parameter will force log archiving in Standard Edition.

Just a quick note here that I wanted to share since I didn't see anything directly confirming this when I was searching around.

I have an Oracle 11gR2 Standard Edition (SE) database that I'm also maintaining a manual standby for, since Oracle Data Guard is not available in SE. I created a metric extension in EM12c to alert me if the standby is more than 1 hour behind the primary. However since this is a very low-activity database, archive logs were not switching even once an hour. Obviously, I could include a command to force a log switch/archive in the script that I use to push archivelogs to the standby. However we all know that with Data Guard on Enterprise Edition (EE), one would use the archive_lag_target initialization parameter to set the desired maximum standby lag. Oracle enforces this by performing a log switch at most every X seconds, where X is the number specified by the archive_lag_target value. By default this is set to 0, which disables the feature.

I had assumed that archive_lag_target would only work in EE but decided to give it a try and was pleasantly surprised to see that it does work as intended in SE. So I can set archive_lag_target=900 to specify a 15 minute maximum log archiving (it would be more frequent if the database activity warranted an earlier switch).
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs