Bobby Durrett's DBA Blog

Here is a zip of two examples I built as I’m learning about the new adaptive plans features of Oracle 12c: zip

The first example has the optimizer underestimate the number of rows and the adaptive plans feature switches the plan on the fly from nested loops to hash join.

In the second example the optimizer overestimates the number of rows and the adaptive plans feature switches the plan from merge join to nested loops.

I ran the same scripts on 12c and 11.2.0.3 for comparison.

Example 1 11g:

Plan hash value: 2697562628

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |      18 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |      18 |
|   2 |   NESTED LOOPS                |      |      1 |        |      8 |00:00:00.01 |      18 |
|   3 |    NESTED LOOPS               |      |      1 |      1 |      8 |00:00:00.01 |      17 |
|*  4 |     TABLE ACCESS FULL         | T1   |      1 |      1 |      8 |00:00:00.01 |      14 |
|*  5 |     INDEX RANGE SCAN          | T2I  |      8 |      1 |      8 |00:00:00.01 |       3 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2   |      8 |      1 |      8 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

Example 1 12c:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |       6 |       |       |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |      1 |      8 |00:00:00.01 |       6 |  2168K|  2168K|     1/0/0|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      8 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |      1 |     16 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Example 2 11g

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |      16 |       |       |          |
|   2 |   MERGE JOIN                  |      |      1 |      4 |      1 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2   |      1 |     16 |      2 |00:00:00.01 |       2 |       |       |          |
|   4 |     INDEX FULL SCAN           | T2I  |      1 |     16 |      2 |00:00:00.01 |       1 |       |       |          |
|*  5 |    SORT JOIN                  |      |      2 |      4 |      1 |00:00:00.01 |      14 | 73728 | 73728 |          |
|*  6 |     TABLE ACCESS FULL         | T1   |      1 |      4 |      1 |00:00:00.01 |      14 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Example 2 12c

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |       5 |
|   2 |   NESTED LOOPS                |      |      1 |        |      1 |00:00:00.01 |       5 |
|   3 |    NESTED LOOPS               |      |      1 |      4 |      1 |00:00:00.01 |       4 |
|*  4 |     TABLE ACCESS FULL         | T1   |      1 |      4 |      1 |00:00:00.01 |       3 |
|*  5 |     INDEX RANGE SCAN          | T2I  |      1 |        |      1 |00:00:00.01 |       1 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2   |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

The output of the plans for the 12c examples end with this line:

Note
-----
   - this is an adaptive plan

So, that tells me it is the adaptive plan feature that is changing the plan despite the wrong estimate of the number of rows.

– Bobby

Spent a good amount of time yesterday and today reading about auditing in Oracle 12c.  Can’t say I read every word, but I think it was worth reading the three chapters in the Security manual related to auditing:

Chapter 21 Introduction to Auditing
Chapter 22 Configuring Audit Policies
Chapter 23 Administering the Audit Trail

I haven’t used these features but it seems like a major new piece of code with the Unified Audit Trail.

I also read this chapter of the VLDB guide because it seemed to have a lot of things that were either new to 12c or new to me:

Chapter 5 Managing and Maintaining Time-Based Information

This chapter describes features that cause data to age out and get moved on to less expensive storage automatically over time.

Anyway, just wanted to pass on some full chapters that I’ve read and am pondering as I try to comprehend the new 12c features.

– Bobby

A RMAN restore and recovery that I completed today answered a couple of questions that remained after the recovery that was the topic of my post from June.  Both today’s recovery and June’s involved a restore of a production database on another host and a recovery of that database to a particular point in time.

Question 1: How to rename redo logs?

When doing a restore and recovery to a point in time RMAN does not restore the redo logs.  So, the production redo log directory does not have to exist on your target.  All you have to do is rename the redo logs after the restore and recover rman commands and before the alter database open resetlogs command.

Oracle document 1338193.1 in step 8 titled “Relocate all the online redo logs” documents the needed command and when to run it.

For each production redo log you run a command like this on the mounted but not opened restored and recovered database:

alter database rename file 
'old redo log path and name' to 
'new redo log path and name';

Question 2: How to I prevent the restored archive logs from filling up the archive filesystem?

It turns out that there is an option of the recover command that limits the amount of space the restored archive logs will take up and there is another option that causes the recover command to delete the archive logs after applying them:

recover database delete archivelog maxsize 50 G;

Otherwise this was the same case as the earlier blog post.  But, at least in this case I didn’t worry about archivelogs filling up the filesystem and I was able to put the redo logs where I wanted them.

– Bobby

This is the second in a series of posts about different performance tuning authors and speakers who have had a big impact on my career.  My first post was on Craig Shallahamer.  I am ordering these posts chronologically.  Craig was the first to impact my career by improving my Oracle performance tuning knowledge.  The next person was Don Burleson.

I have heard Don Burleson speak at several user group conferences and got a lot out of these talks, but I got the most benefit from his book titled “Oracle 9i High-Performance Tuning with STATSPACK“.  Don’s 9i Statspack book helped me understand what was in the various v$ views that Statspack captures.  I mentioned in an earlier post that a coworker of mine asked how to learn about the different parts of an AWR report.  I learned about these initially from Don Burleson’s book.  Today, three major database versions later, on Oracle 12c and with the more full featured AWR reports I still use information I learned about v$ views from Don’s 9i Statspack book.  The idea of taking snapshots of the v$ views like v$system_event and getting the difference between the values captured in two snapshots remains a critical component to Oracle database tuning.

In addition to understanding the v$ views I learned how to add operating system metrics like CPU utilization, disk I/O latency, and network latency to the database metrics captured by Statspack.  A very cool connection occurred when I realized that Don Burleson’s approach to augmenting Statspack database v$ information with Unix operating system metrics mirrored the way Craig Shallahamer’s OSM Toolkit stores both v$ information and Unix OS metrics.  It has been a huge help to understand what the operating system tools like sar and vmstat are telling you and compare that to what Statspack is saying as you observe the changes in v$ view values over time.

Lastly, I remember learning about database internals and their impact on performance.  One example that stands out is that I learned from Don Burleson’s book that indexes are more effective if rows in the table are ordered by the indexed columns.  Later I learned more about “clustering factor” and how this value attempts to quantify how ordered the rows are relative to the indexed columns.  But, Don’s 9i book first opened my eyes to the relevance of the way the rows are sorted in the table relative to the columns in the index.  I’m sure I learned other key things but the relationship between the indexed columns and the order of rows really opened my eyes to how data organization on the disk can impact performance.

In conclusion, Don Burleson was the second major figure in my Oracle performance tuning journey.  His 9i Statspack book in particular improved my knowledge of v$ tables, operating system performance metrics, and Oracle database tuning topics and internals.  Each of these three types of knowledge have proven invaluable in developing my performance tuning skills.

– Bobby

p.s. I believe this is the expanded more current AWR based version of the 9i Statspack book: Oracle Tuning: The Definitive Reference, Third Edition

In an earlier blog post I showed how I used coe_xfr_sql_profile.sql from the sqlt toolkit from Oracle support to force a particular sql statement to run with a more efficient plan that it had used in the past.

Today, I’m trying a slight variation.  Take the problem query, run it with hints, and use coe_xfr_sql_profile.sql to apply the resulting plan to the original query.  I built a quick and dirty test to make sure it works.

-- create test table with index and stats

SQL> create table test as select * from dba_tables;

SQL> create index testi on test(owner);

SQL> execute dbms_stats.gather_table_stats(NULL,'TEST');

-- run query unhinted

SQL> select sum(blocks) from test where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

-- show plan - uses the index

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a0nw2xjnmjcxd, child number 0
-------------------------------------
select sum(blocks) from test where owner='SYS'

Plan hash value: 1551939256

--------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |
|   1 |  SORT AGGREGATE              |       |     1 |     9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |  2122 | 19098 |
|*  3 |    INDEX RANGE SCAN          | TESTI |  2122 |       |
--------------------------------------------------------------

-- use hint to force full scan

SQL> select /*+full(test) */ sum(blocks) from test 
where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

-- get plan with full scan

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5521zhmq67vun, child number 0
-------------------------------------
select /*+full(test) */ sum(blocks) from test 
where owner='SYS'

Plan hash value: 3467505462

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   295 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| TEST |  2122 | 19098 |   295   (1)|
----------------------------------------------------------------

So, let’s pretend that sql_id=a0nw2xjnmjcxd is the real production query you are trying to tune.  Without hints it is doing an index range scan.  With a hint you run a similar query forcing a full scan.  The new query, sql_id=5521zhmq67vun has a plan with this hash value: 3467505462.  So, I used coe_xfr_sql_profile.sql to force the unhinted original query to run with the hinted query’s plan:

cd /var/opt/oracle/admin/sqlt/utl

-- login to sqlplus as system

@coe_xfr_sql_profile.sql a0nw2xjnmjcxd 3467505462

-- login to sqlplus as system

@coe_xfr_sql_profile_a0nw2xjnmjcxd_3467505462.sql

Now when I run the original query it runs with the new plan and indicates that the profile is in use:

SQL> select sum(blocks) from test where owner='SYS';

SUM(BLOCKS)
-----------
    1042402

SQL> select * from 
table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a0nw2xjnmjcxd, child number 0
-------------------------------------
select sum(blocks) from test where owner='SYS'

Plan hash value: 3467505462

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   295 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| TEST |  2122 | 19098 |   295   (1)|
----------------------------------------------------------------

Note
-----
   - SQL profile coe_a0nw2xjnmjcxd_3467505462 used for 
     this statement

I edited the output to make it more readable.  See the zip with the full scripts and logs.

So, if you have a recurring production SQL query that runs faster with hints just run that query once with the hints and then use coe_xfr_sql_profile.sql to force the real query to run unchanged but with the same plan as the hinted query.

– Bobby

I attended two events this week that used the term “analytics” to describe a performance tuning feature that displays metrics in various useful ways.

On Tuesday, I attended a Delphix 4.0 Webinar where Delphix’s CTO Adam Leventhal described the new Delphix Analytics including the very interesting Latency Heat Map.  The latency heat map looked like a customer usable front end to the sort of performance information that Delphix support had collected when I worked with them on some network issues.  In this case the word analytics seems to mean the ability to look at the performance information in an easy to understand visual way that you can customize to see various perspectives. such as NFS latency, disk latency, etc.

Yesterday, Oracle gave us a four-hour on site OEM 12c seminar which included “ASH analytics”.  It was similar to the Delphix analytics in that you could see performance information visually and choose among various dimensions – machine, wait, session id, etc.

Perhaps Delphix and Oracle drew from business reporting – cubes, dimensions, drilling down – and applied the approach to performance information.  I have not used either example to solve a real problem so I can not vouch for the usefulness of either, but I thought it was an interesting coincidence that I ran into two such similar features in the same week.

– Bobby

I decided to read the entire Oracle 12c Concepts manual.  It’s kind of overkill to read the entire manual if you just want to learn the new 12c features but it has been a long time since I’ve read the entire Concepts manual on the current version of Oracle.  I’m not sure that I remember correctly, but I think the last version I read entirely was the 9i version.  I definitely read the Oracle 7 concepts and several other manuals when I was first getting started.

The 12c manual has things in it that seem virtually unchanged from what I remember from earlier versions.  But, it also has a lot more technical depth than I remember.  It was boring at times reading over sections filled with things I’ve worked on for almost 20 years now.  But, these monotonous sections still had reminders of things I probably don’t have in the front of my brain because I don’t use them every day.  Then there were whole sections about features that are either new in 12c or new to me such as the multitenant features.

I’ve heard many people say that a new DBA (database administrator) should start with the Concepts manual.  Reading it again just confirms this often stated advice.  Concepts has pointers to all these other manuals where you can find details on the things you found interesting or needed for your job.  For example, in the sections relating to partitioned tables there are references to the VLDB and Partitioning manual.  A new DBA who will be working on partitioned tables might not realize that this manual existed, but the Concepts manual will send you there.

I realize that a lot of people don’t like to read manuals, but if you had to force yourself to sit down and read one Oracle manual cover to cover so you could be a better DBA you should read Concepts.  It’s the starting point and after 19 years and counting as an Oracle DBA I still got a lot out of reading it.

– Bobby

Here is the sqlplus scripting I use to check whether row chaining is degrading a query’s performance:

VARIABLE monitored_sid number;

begin

SELECT sid into :monitored_sid 
from v$session 
where audsid=USERENV('SESSIONID');

end;
/

select b.value "table fetch continued rows"
from
V$SESSTAT b
where b.SID = :monitored_sid
and b.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME 
WHERE NAME='table fetch continued row');

I create a test script with the problem query and put this code after it to see how much chaining affected it.

Here is an example of a fast query:

Elapsed: 00:00:15.87

table fetch continued rows
--------------------------
                     19723

Here is an example of a slow query:

Elapsed: 00:03:17.46

table fetch continued rows
--------------------------
                  23775056

This was the same query for two different date ranges.  The second range had a lot of row chaining and the query was much slower.

– Bobby

Today is the second anniversary of my first post on this blog.

Some time ago I played with a free blog that came with a yahoo email account that my wife and I share but didn’t get very far with that.  Finally for this blog I decided to spend a small amount of money to get my own hosting and domain name.

I’m using iPage for the hosting.  I’ve been happy with their service.  There were a couple of billing issues, but I opened tickets with them and they were quickly resolved in a fair way.

I evaluated a few blog sites and chose WordPress because it allowed me to do what was most important.  WordPress’s software enabled me to upload sqlplus scripts and their logs along with PowerPoint, Microsoft Word, and PDF files.  WordPress also enabled me to include output from sqlplus and have the columns line up nicely like this:

SQL> select table_name, blocks
  2  from dba_tables
  3  where owner='SYS'
  4  order by table_name;

TABLE_NAME                         BLOCKS
------------------------------ ----------
ACCESS$                               759
ALERT_QT
APPLY$_CHANGE_HANDLERS                  0
APPLY$_CONF_HDLR_COLUMNS                0
APPLY$_CONSTRAINT_COLUMNS               0
APPLY$_DEST_OBJ                         0
APPLY$_DEST_OBJ_CMAP                    0
APPLY$_DEST_OBJ_OPS                     0
APPLY$_ERROR                            0

I’m pretty happy with the results.  I’ve had some nice interaction with the Oracle community and I’ve used links to my posts with coworkers on the job.  Plus, I actually query the site myself when I forget how to do something I wrote about.

I.e. If I forget where to plus sign on an outer join I search for it on my blog!

Overall it has been an enjoyable experience and I think helpful to me and my employer as I try to communicate with others the things I’m learning about Oracle database technology, especially in the performance tuning area.

– Bobby

I’m trying to get up to speed on 12c, especially the multitentant features.  So, I decided to read the sections of the manuals that related to using multitentant.  Here is the name of the manual I was looking at:

Oracle® Database Administrator’s Guide 12c Release 1 (12.1)

It isn’t great reading.  It is kind of slow-paced and really more of a reference but since it is a large new feature I wanted to go over the practical details of using it.  Some of the sections had a command line sqlplus version and a web-based cloud control version so I skipped the cloud version.  I’m more of a command line, DBA and v$ view kind of DBA.

Here are the chapter numbers and titles of the sections I read:

36 Overview of Managing a Multitenant Environment
37 Creating and Configuring a CDB
38 Creating and Removing PDBs with SQL*Plus
40 Administering a CDB with SQL*Plus
42 Administering PDBs with SQL*Plus
43 Viewing Information About CDBs and PDBs with SQL*Plus
44 Using Oracle Resource Manager for PDBs with SQL*Plus
46 Using Oracle Scheduler with a CDB

I haven’t really used what I’ve learned yet but I feel like I have a good overview and know where to go to get more information.

I’m also working on reading through the 12c concepts manual on my Kindle but I haven’t gotten to the multitenant part.  Once I’m done with that I hope to get serious about studying up for the 12c OCP upgrade exam.  It looks like you can sign up for the tests now, but I don’t have any practice exam software or a book and I usually get one of these before I take the test.  I see one book that is supposed to be available in June so I may have to wait until then but I have plenty to do between now and then anyway so I may not be ready to take the test before the book is available so that should work out.

In any case, certification or no, it was helpful to read the db admin guide multitenant sections to get a feel for how it works.  Probably chapter 44 was the most interesting because it talked about using resource manager to divide resource usage between the pluggable databases that are sharing the same instance.

– Bobby