Skip navigation.

Bobby Durrett's DBA Blog

Syndicate content
Oracle database performance
Updated: 12 hours 41 min ago

Quick documentation for new PeopleSoft DBAs

Tue, 2014-04-22 11:10

I did a quick survey of the latest PeopleSoft manuals to find a set of links to pass on to a couple of coworkers of mine that are interested in doing PeopleSoft DBA work so I thought I’d include the links in a post.  This might give a new PeopleSoft DBA some highlights without having to read the entire manual set.

This page has a nice picture of how the environments connect:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tgst/concept_UnderstandingthePeopleSoftTopology-827f9d.html

This is the top level URL for the PeopleTools 8.53 documentation:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/index.html

Another nice architecture diagram:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tgst/task_WebServer-827f33.html

Nice overview of application development using app designer:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tapd/task_PeopleSoftApplicationDesignerImplementation-0776f7.html

Yet another architecture diagram:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tprt/concept_PeopleSoftPureInternetArchitectureFundamentals-c071ce.html

More in depth view of app server and its processes:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tprt/concept_ApplicationServers-c071d0.html

Web server with discussion of servlets and jolt:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tprt/concept_WebServer-c071dc.html

Nice overview of datamover commands:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tadm/concept_UnderstandingDataMoverScripts-077b05.html

Datamover basics

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tadm/task_CreatingandRunningPeopleSoftDataMoverScripts-077af9.html

Nice explanation of Oracle connections from PeopleSoft:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tadm/task_MonitoringPeopleSoftDatabaseConnections-077989.html

Good to know but not very clear explanation:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsec/concept_PeopleSoftAuthorizationIDs-c07669.html

Important to know but not very clear:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsec/concept_PeopleSoftSignIn-c0766f.html

PS_HOME versus PS_CFG_HOME

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/concept_UnderstandingPS_HOMEandPS_CFG_HOME-eb7ece.html

Starting psadmin

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_StartingPSADMIN-c07e6b.html

Nice run down of config files:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_UsingPSADMINConfigurationFiles-c07e7a.html

App server menu:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_UsingtheApplicationServerAdministrationMenu-c07e84.html

process scheduler menu:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_UsingtheProcessSchedulerMenu-c07ea3.html

web server menu – I don’t think I’ve ever used this:

http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tsvt/task_UsingtheWebPIAServerMenu-1773ed.html

- Bobby

 

Categories: DBA Blogs

SQL*Loader Express bug

Mon, 2014-04-21 15:41

I’m still studying for my Oracle 12c OCP exam and I was trying to run a simple example of using SQL*Loader Express and the first thing I did blew up and I think it is a bug.  When I load a table with one or two columns it works fine, but when I load a table with 3 or 4 columns the last column is not loaded.  Tell me this isn’t a special feature! :)

First I create the table with four columns:

create table test
(a varchar2(20),
 b varchar2(20),
 c varchar2(20),
 d varchar2(20));

Then I create a comma separated values file named test.dat with four values per line:

[oracle@ora12c dpsl]$ cat test.dat
a,b,c,d
f,g,h,i
j,k,l,m

Then I run sql*loader in express mode:

[oracle@ora12c dpsl]$ sqlldr system/xxxxxx table=test

SQL*Loader: Release 12.1.0.1.0 - Production on Mon Apr 21 07:32:43 2014

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

Express Mode Load, Table: TEST
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table TEST:
  3 Rows successfully loaded.

Check the log files:
  test.log
  test_%p.log_xt
for more information about the load.

Then I query the newly loaded table:

ORCL:CDB$ROOT:SYSTEM>select * from test;

A                    B                    D
-------------------- -------------------- --------------------
a                    b                    d
f                    g                    i
j                    k                    m

Queue the mysterious music.  Actually, now that I look at it really it is the third column that is missing.  Maybe it doesn’t work with a column named C.

Sure enough, here it is with column C replaced with column X:

A                    B                    X                    D
-------------------- -------------------- -------------------- --------------------
a                    b                    c                    d
f                    g                    h                    i
j                    k                    l                    m

So, I guess SQL*Loader Express doesn’t work with columns named C?  Odd.

- Bobby

 

Categories: DBA Blogs

ORA-00600 [3631] recovering pluggable database after flashback database in Oracle 12c

Wed, 2014-04-16 15:44

I was trying to recreate the scenario where a 12c container database is flashed back to a SCN before the point that I recovered a pluggable database to using point in time recovery.

I got this ugly ORA-00600:

RMAN> recover pluggable database pdborcl;

Starting recover at 16-APR-14
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/16/2014 06:07:40
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 datafile 32 , 33 , 34 , 35
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3631], [32], [4096], [4210689], [], [], [], [], [], [], [], []

I think the above error message stems from this bug:

Bug 14536110  ORA-600 [ktfaput: wrong pdb] / crash using PDB and FDA

There may have been some clever way to recover from this but I ended up just deleting and recreating the CDB through DBCA which was good experience playing with DBCA in Oracle 12c.  I’m trying to learn 12c but I have a feeling that I have hit a bug that keeps me from testing this flashback database, point in time recovery of a pluggable database scenario.  I wonder if I should patch?  I think that Oracle has included a patch for this bug in a patch set.  It could be good 12c experience to apply a patch set.

- Bobby

Categories: DBA Blogs

Using test prep software to prepare for 12c OCP upgrade exam

Tue, 2014-04-15 17:54

I got the newly available Kaplan test prep software for the Oracle 12c OCP upgrade exam.

I took the test in certification mode when I was tired at the end of the day some day last week and got 44% right – fail!  I usually wait until I get all the questions right before taking the real test so I have a ways to go.

The practice test software has been useful  in terms of showing me things I didn’t study very well or at all.  I’m expecting to significantly improve my correct answer percentage on my next pass.

I’m a little nervous though because it seems that the real test involves some questions that are generic database questions and I don’t think that the test prep software includes that section.  If you look at the list of topics they have a  section called “Key DBA Skills”.  I’d hope that after 19 years as an Oracle DBA I’d have some skills, but there are plenty of things I don’t do every day, such as setting up ASM.  I guess I’ll just have to bone up on the key areas of pre-12c that I don’t use all the time and hope I’m not surprised.

Anyway, I’m at 44% but hoping to make some strides in the new few weeks.

- Bobby

 

Categories: DBA Blogs

Two Adaptive Plans Join Methods Examples

Wed, 2014-04-02 14:49

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

 

Categories: DBA Blogs

Oracle 12c Auditing Chapters

Wed, 2014-03-26 17:51

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

 

 

 

 

Categories: DBA Blogs

Learned a couple of things from RMAN restore

Mon, 2014-03-24 14:37

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

 

 

 

 

Categories: DBA Blogs

Don Burleson

Fri, 2014-03-21 14:39

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Categories: DBA Blogs

Using hints with coe_xfr_sql_profile.sql

Wed, 2014-03-19 15:16

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

 

 

 

 

 

 

 

 

 

 

 

 

 

Categories: DBA Blogs

Two performance analytics examples this week

Fri, 2014-03-14 17:00

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

 

Categories: DBA Blogs

Finished reading 12c Concepts manual

Tue, 2014-03-11 17:47

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

 

 

 

 

 

Categories: DBA Blogs

Sqlplus script to check for row chaining

Fri, 2014-03-07 15:10

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

 

 

 

 

Categories: DBA Blogs

Blog second anniversary

Wed, 2014-03-05 09:52

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

 

 

Categories: DBA Blogs

Finished reading multitenant parts of db admin manual

Tue, 2014-03-04 14:27

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

 

 

 

Categories: DBA Blogs

Good blog post on learning Oracle performance

Mon, 2014-03-03 17:44

Saw this blog post about how Cary Millsap learned about Oracle performance tuning and I thought it was interesting:

http://carymillsap.blogspot.com/2014/02/how-did-you-learn-so-much-stuff-about.html

It is interesting because I’ve learned so much from Cary Millsap and Jeff Holt’s book and it is cool to see how Cary got to the position he is in as a well-known Oracle performance expert.  I guess I’ve always found the performance tuning part of my Oracle DBA job the most interesting so it is intriguing to see someone else who feels the same way about it and how they pursued their interest and made a living doing it.

Cary’s blog post contains good advice to someone who wants to be an Oracle DBA/performance analyst.  Read the Concepts manual and other relevant manuals.  Read books by the performance experts.  Find opportunities to work on real performance problems.  Pretty much these are the steps I’ve taken, although I’ve always worked as an employee for a company instead of being a consultant.  Still, working for a large company has been a good situation for me because we have a variety of systems and performance is consistently a problem that needs work.

- Bobby

 

 

 

Categories: DBA Blogs

Updates of tables with query high compression slow

Fri, 2014-02-28 14:22

In an earlier post about how query high compression eliminated row chaining in tables with more than 255 columns I got question about whether updates to tables with more than 255 columns and query high compression ran slower with this type of compression than on an uncompressed table.

The answer is that, at least in the tests I ran, the updates of a table with more than 255 columns took much longer on a table with query high compression than on the uncompressed table.

Here is a zip of my test scripts and their logs: zip

I took a table with 256 columns and populated it with 1,000,000 rows filling all columns with data.  Then I updated the first column, the last column, and then all columns in three separate transactions.

Here are the results:

                   Column 1    Column 256  All Columns

Uncompressed run 1 00:00:11.81 00:00:24.20 00:00:57.89
Uncompressed run 2 00:00:12.25 00:00:23.94 00:00:59.28

Compressed run 1   00:02:27.20 00:02:27.65 00:02:50.08
Compressed run 2   00:02:26.83 00:02:26.33 00:02:47.88

I don’t have the time or motivation to dig any deeper into the causes of this but here is what I think it going on based on these results.

  1. Row chaining in the uncompressed table with more than 255 columns causes the update of the last column or all columns to be slower than updating just the first column.
  2. Updating any or all columns of the table when it is compressed for query high uncompresses the table (I think it remains compressed for OLTP but did not check).
  3. The time it takes to uncompress the table during the update is much more than the time taken to update the uncompressed table, even in situations where we have row chaining.

Maybe someone could construct a situation where the time taken by row chaining exceeds the time taken to uncompress the rows for the update, but in my test case it isn’t close.  The uncompress takes more than twice the time of the updates that experience row chaining.

- Bobby

 

 

 

 

 

Categories: DBA Blogs

Production RMAN recovery

Thu, 2014-02-27 10:31

I was on call again last week and again had to do a database recovery using RMAN.  I thought I might as well document some lessons learned even though it went quite smoothly.

First, here are the steps I took.  I’ve modified the commands and script so that our real usernames, passwords,  database names, and host names are obscured, but otherwise it is exactly what I ran.

Environment wise we are on 11.2.0.3 Oracle database running on HP-UX 11.31 on 64 bit Itanium.

First I had to build the Unix script that I would run to do the recovery:

rman target / catalog CATUSER/CATPASSWORD@CATDB <<EOF

run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_1' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_2' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_3' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
allocate channel 'dev_4' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OURDB,OB2BARLIST=VTL_ourhost_OURDB_ora4)';
set until time "to_date('Feb 20 2014 21:38:10','Mon DD YYYY HH24:MI:SS')";
restore database;
recover database;
}

exit
EOF

In my previous recovery scenario that I blogged about I didn’t use the catalog because I was recovering to a different database just to retrieve data from before the failure I was trying to recover from.  In this case I was recovering the production database in place to our Thursday night backup that we took just before embarking on a PeopleTools upgrade.  So, first thing I had to do was look up the user, password, and connect string for the catalog.  Then I just tested connecting without doing anything.

rman target / catalog CATUSER/CATPASSWORD@CATDB
exit

Next I had to figure out the allocate channel commands.  In our case we have a graphical front end to our data protector backup system and I was able to extract the log from Thursday night’s backup.  I copied the allocate channel commands unchanged from the log to my script.

The hardest part of setting up this script was knowing what time to use in the set until time command.  I decided to use the last time that appeared on the backup output.  I knew that the application was down and so not much updating was occurring so I wasn’t worried about going past the earliest possible point that I could use.  I knew that our upgrade didn’t start until after the backup finished so I reasoned that the last backup message would be safe.  But, the backup program spits out messages in a different time zone than our database is in.  (I’m in Arizona, but my database is on Chicago time…).  So, getting the timezone right was key otherwise I could be off by an hour.  The date and time in the last backup message looked like this in Arizona time:

Time: 2/20/2014 8:38:10 PM

I had to translate it to

Feb 20 2014 21:38:10

This got it into central time and 24 hour format.

Lastly, I considered whether we had enough space for all the archive logs that would be retrieved for the recovery.  Since we were recovering from just after a hot backup and with the application down I decided that it couldn’t be more than one or two archive logs.  As it turns out the archive logs were on the disk when the recovery ran so they didn’t even need to be restored from tape.

To actually run the script I first manually put the database in mount mode:

sqlplus / as sysdba
shutdown immediate
startup mount

Then I ran the recovery nohup in the background:

nohup ./recover.sh > recover.out &

Lastly, after carefully reviewing the output from the recovery to make sure it looked right I opened the database resetting logs:

sqlplus / as sysdba
alter database open resetlogs;

It went very smoothly.  It was a little nerve-racking recovering the production system in place, but we did make two different backups of it before doing the recovery so presumably we could have gone back to the way it was before the recovery.

- Bobby

 

 

 

 

 

 

 

 

Categories: DBA Blogs

Bug 14383007 workaround

Thu, 2014-02-13 11:44

We appear to be hitting this bug on our production Exadata RAC system:

Bug 14383007 – Sort runs out of temp space in RAC even when temp space is available

One particular job errors out with ORA-01652 errors on the TEMP tablespace even though there is tons of free space in the sort segments.  So, I got the idea of building a work around to have this job only login to the node that has the most free temp space.  Normally space just gets reallocated from the node that has it to the node that needs it.  But, I guess the bug is that in certain cases this doesn’t happen and you get the ORA-01652.

Here is my example unix script (actually this is run on an HP-UX box, don’t ask me why).

# This script demonstrates how to login to the instance
# of a RAC database that has the most free space in 
# the TEMP tablespace.  It takes three arguments
# which are the connection information for the RAC
# database:
# 1 - Oracle database username
# 2 - password
# 3 - connect string
#
# in sqlplus this would be like connecting as username/password@connectstring
#
# Step one - login to the RAC database and extract a connect string that
# connects to the node with the most free temp space.  
# This has several assumptions:
# 1 - temporary tablespace is named TEMP
# 2 - all of the temp space is allocated to sort segments
# 3 - the port number is 1521
NEW_CONNECT_STRING=`sqlplus -s /nolog<<EOF
connect $1/$2@$3
set heading off
set feedback off
set linesize 32000
set trimspool on
select 
'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = '||
host_name||
')(Port = 1521))) (CONNECT_DATA = (SID = '||
INSTANCE_NAME ||
')))'
from
gv\\$instance
where inst_id=
(select 
inst_id
from 
gv\\$sort_segment
where 
TABLESPACE_NAME='TEMP' and
FREE_BLOCKS =
(select max(FREE_BLOCKS)
from 
gv\\$sort_segment
where 
TABLESPACE_NAME='TEMP'));
exit
EOF
`
#trim the leading space from the connect string
NEW_CONNECT_STRING=`echo $NEW_CONNECT_STRING|sed 's/^ //'`
echo connect string = $1/$2@$NEW_CONNECT_STRING
# Now connect to the node using this connect string just to show
# that it works and query the sort segment view to show that this
# really is the instance with the most free temp space.
sqlplus /nolog<<EOF
set echo on
set termout on
set pagesize 1000
connect $1/$2@$NEW_CONNECT_STRING
-- show current instance
select instance_name from v\$instance;
-- list instances in descending order
-- of free blocks.  current instance
-- should be listed first.
select inst_id,free_blocks
from gv\$sort_segment
where 
TABLESPACE_NAME='TEMP'
order by free_blocks desc;
EOF

You wouldn’t want to use this except for a special case like this where you need a workaround for the bug.  Otherwise you would just end up running on one node and all the temp space would get allocated to it.  But, if you are hitting bug 14383007 this may be helpful.

- Bobby

 

Categories: DBA Blogs

Library cache lock scripts for RAC

Tue, 2014-02-11 18:16

I’ve been having issues for a long time now with an Exadata RAC database that has user reports experiencing library cache lock waits.  The challenge is to figure out what is holding the library cache locks that the queries are waiting on.

My starting point on library cache locks has always been this Oracle support document:

How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)

But it doesn’t tell you how to find the session across nodes of a RAC database.

I also found this helpful blog post that briefly addresses finding the session across RAC nodes: Library cache lock and library cache pin waits

I’ve spent many hours over more than a year now dealing with these waits without a lot of success so I finally tried to build a script that I could run regularly to try to capture information about the sessions holding the library cache locks.

First, I knew from Oracle’s document that the x$kgllk table could be used to find the blocking session on a single node so I included queries against this table in my script and set it up so I would ssh into every node of the cluster and run a queries like this against each node:

-- sessions on this instance that are waiting on
-- library cache lock waits
-- unioned with
-- sessions on this instance that are holding locks that other
-- sessions on this instance are waiting on.

insert into myuser1.library_cache_lock_waits
(
SOURCETABLE,
BLOCKER,
SAMPLE_TIME,
INST_ID,
SID,
USERNAME,
STATUS,
OSUSER,
MACHINE,
PROGRAM,
LOGON_TIME,
LAST_CALL_ET,
KGLLKHDL,
KGLLKREQ,
USER_NAME,
KGLNAOBJ,
sql_id,
SQL_FULLTEXT
)
(select
'X\$KGLLK',
'N',
sysdate,
(select INSTANCE_NUMBER from v\$instance),
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
x.KGLLKHDL,
x.KGLLKREQ,
x.USER_NAME,
x.KGLNAOBJ,
s.sql_id,
q.SQL_FULLTEXT
from 
v\$session s, x\$kgllk x, v\$sql q
where
x.kgllkses=s.saddr and
s.sql_id=q.sql_id(+) and
s.event='library cache lock' and
x.KGLLKREQ > 0 and
q.child_number(+)=0)
union all
(select
'X\$KGLLK',
'Y',
sysdate,
(select INSTANCE_NUMBER from v\$instance),
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
x.KGLLKHDL,
x.KGLLKREQ,
x.USER_NAME,
x.KGLNAOBJ,
s.sql_id,
q.SQL_FULLTEXT
from 
v\$session s, x\$kgllk x, v\$sql q,
x\$kgllk x2
where
x.kgllkses=s.saddr and
s.sql_id=q.sql_id(+) and
x.KGLLKREQ = 0 and
x2.KGLLKREQ > 0 and
x2.KGLLKHDL = x.KGLLKHDL and
q.child_number(+)=0);

commit;

The dollar signs are escaped with a backslash because these queries are part of a Unix shell script.  I picked a few columns that I thought would be helpful from v$session and joined to v$sql to get the text of the blocking and blocked SQL.  Note that I ran these queries as SYSDBA.  Here is an example of my test case where the blocker and blocked sessions are both on one node:

SOURCETABLE                    B Sample Time            INST_ID        SID USERNAME                       STATUS   OSUSER                         MACHINE                                                          PROGRAM                                          Logon Time          LAST_CALL_ET KGLLKHDL           KGLLKREQ USER_NAME                      KGLNAOBJ                                                     SQL_ID        RESOURCE_NAME1                 RESOURCE_NAME2                 SQL_FULLTEXT
------------------------------ - ------------------- ---------- ---------- ------------------------------ -------- ------------------------------ ---------------------------------------------------------------- ------------------------------------------------ ------------------- ------------ ---------------- ---------- ------------------------------ ------------------------------------------------------------ ------------- ------------------------------ ------------------------------ --------------------------------------
X$KGLLK                        N 2014-02-17 17:19:01          1       1183 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:18:57            5 00000005F9E7D148          2 MYUSER1                        TEST                                                         g4b4j3a8mms0z                                                               select sum(b) from test
X$KGLLK                        Y 2014-02-17 17:19:03          1        995 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:18:52           10 00000005F9E7D148          0 MYUSER1                        TEST                                                         gv7dyp7zvspqg                                                               alter table test modify (a char(100))

Next, I noticed that on gv$session when a session was waiting on library cache lock waits sometimes FINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION were populated and that might lead me to the session holding the lock.  Also, this query and the ones following can run in a less privileged account – you don’t need SYSDBA.

drop table lcl_blockers;

create table lcl_blockers as
select distinct
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id
from
gv\$session s, 
gv\$session s2
where
s2.FINAL_BLOCKING_INSTANCE=s.INST_ID and
s2.FINAL_BLOCKING_SESSION=s.SID and
s2.event='library cache lock';

insert into myuser1.library_cache_lock_waits
(
SOURCETABLE,
BLOCKER,
SAMPLE_TIME,
INST_ID,
SID,
USERNAME,
STATUS,
OSUSER,
MACHINE,
PROGRAM,
LOGON_TIME,
LAST_CALL_ET,
sql_id,
SQL_FULLTEXT
)
select
'GV\$SESSION',
'Y',
sysdate,
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id,
q.SQL_FULLTEXT
from 
lcl_blockers s, gv\$sql q
where
s.sql_id=q.sql_id(+) and
s.INST_ID=q.INST_ID(+) and
q.child_number(+)=0
order by s.INST_ID,s.sid;

commit;

When this works – sporadically in my tests – it shows the same sort of information the previous queries do for same node locking.  Here is an example of these gv$session queries catching the blocker:

SOURCETABLE                    B Sample Time            INST_ID        SID USERNAME                       STATUS   OSUSER                         MACHINE                                                          PROGRAM                                          Logon Time          LAST_CALL_ET KGLLKHDL           KGLLKREQ USER_NAME                      KGLNAOBJ                                                     SQL_ID        RESOURCE_NAME1                 RESOURCE_NAME2                 SQL_FULLTEXT
------------------------------ - ------------------- ---------- ---------- ------------------------------ -------- ------------------------------ ---------------------------------------------------------------- ------------------------------------------------ ------------------- ------------ ---------------- ---------- ------------------------------ ------------------------------------------------------------ ------------- ------------------------------ ------------------------------ --------------------------------------
GV$SESSION                     Y 2014-02-17 17:19:05          1        995 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:18:52           12                                                                                                                         gv7dyp7zvspqg                                                               alter table test modify (a char(100))

Lastly, I got a cross node query working that uses the view gv$ges_blocking_enqueue.  The key to making this query was that the pid column in gv$ges_blocking_enqueue is the same as the spid column in gv$process.

-- join gv$ges_blocking_enqueue, gv$session, gv$process to show 
-- cross node library cache lock blockers.  Blocked session will 
-- have event=library cache lock.

drop table ges_blocked_blocker;

create table ges_blocked_blocker as
(select distinct
'N' blocker,
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id,
s.process,
p.spid,
e.RESOURCE_NAME1,
e.RESOURCE_NAME2
from
gv\$session s, 
gv\$process p,
gv\$ges_blocking_enqueue e
where
s.event='library cache lock' and 
s.inst_id=p.inst_id and
s.paddr=p.addr and
p.inst_id=e.inst_id and
p.spid=e.pid and
e.blocked > 0)
union
(select distinct
'Y',
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id,
s.process,
p.spid,
e.RESOURCE_NAME1,
e.RESOURCE_NAME2
from
gv\$session s, 
gv\$process p,
ges_blocked b,
gv\$ges_blocking_enqueue e
where
s.inst_id=p.inst_id and
s.paddr=p.addr and
p.inst_id=e.inst_id and
p.spid=e.pid and
b.RESOURCE_NAME1=e.RESOURCE_NAME1 and
b.RESOURCE_NAME2=e.RESOURCE_NAME2 and
e.blocker > 0);

insert into myuser1.library_cache_lock_waits
(
SOURCETABLE,
BLOCKER,
SAMPLE_TIME,
INST_ID,
SID,
USERNAME,
STATUS,
OSUSER,
MACHINE,
PROGRAM,
LOGON_TIME,
LAST_CALL_ET,
sql_id,
SQL_FULLTEXT,
RESOURCE_NAME1,
RESOURCE_NAME2
)
select
'GV\$GES_BLOCKING_ENQUEUE',
s.blocker,
sysdate,
s.INST_ID,
s.SID,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.sql_id,
q.SQL_FULLTEXT,
s.RESOURCE_NAME1,
s.RESOURCE_NAME2
from 
ges_blocked_blocker s, gv\$sql q
where
s.sql_id=q.sql_id(+) and
s.INST_ID=q.INST_ID(+) and
q.child_number(+)=0
order by s.INST_ID,s.sid;

commit;

Here is some example output from my gv$ges_blocking_enqueue script.  I edited my username, machine name, etc. to obscure these.

SOURCETABLE                    B Sample Time            INST_ID        SID USERNAME                       STATUS   OSUSER                         MACHINE                                                          PROGRAM                                          Logon Time          LAST_CALL_ET KGLLKHDL           KGLLKREQ USER_NAME                      KGLNAOBJ                                                     SQL_ID        RESOURCE_NAME1                 RESOURCE_NAME2                 SQL_FULLTEXT
------------------------------ - ------------------- ---------- ---------- ------------------------------ -------- ------------------------------ ---------------------------------------------------------------- ------------------------------------------------ ------------------- ------------ ---------------- ---------- ------------------------------ ------------------------------------------------------------ ------------- ------------------------------ ------------------------------ --------------------------------------
GV$GES_BLOCKING_ENQUEUE        N 2014-02-17 17:19:55          2        301 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:19:46            7                                                                                                                         g4b4j3a8mms0z [0x426d0373][0x224f1299],[LB][ 1114440563,575607449,LB        select sum(b) from test
GV$GES_BLOCKING_ENQUEUE        Y 2014-02-17 17:19:55          1        497 MYUSER1                        ACTIVE   myuser2                        MYMACHINE                                                        sqlplus.exe                                      2014-02-17 17:19:41           13                                                                                                                         gv7dyp7zvspqg [0x426d0373][0x224f1299],[LB][ 1114440563,575607449,LB        alter table test modify (a char(100))

The alter table command on node 1 is holding the lock while the select statement on node 2 is waiting on the library cache lock.

So, I've got this going on a script that runs every 15 minutes in production.  It worked great in my test case but time will tell if it yields any useful information for our real problems.

- Bobby

p.s. I've uploaded a zip of my scripts: zip

Here is a description of the included files:

Testcase to create a library cache lock:

create.sql - creates a table with one character first column CHAR(1)
alter.sql - alters table expanding CHAR column
query.sql - queries table - waits on library cache lock wait if run while alter.sql is running

all.sh - top level script - you will need to edit to have the host names for your RAC cluster and to have your own userid and password

lcl.sh - x$ table script that is run on each node.  Only key thing is that our profile required a 1 to be entered to choose the first database from a list.  You may not need that line.

resultstable.sql - create table to save results

dumpresults.sql - dump out all results

dumpresultsnosql.sql - dump out all results except sql text so easier to read.

Here is the definition of the results table:

create table myuser1.library_cache_lock_waits
(
 SOURCETABLE    VARCHAR2(30),
 BLOCKER        VARCHAR2(1),
 SAMPLE_TIME    DATE,
 INST_ID        NUMBER,
 SID            NUMBER,
 USERNAME       VARCHAR2(30),
 STATUS         VARCHAR2(8),
 OSUSER         VARCHAR2(30),
 MACHINE        VARCHAR2(64),
 PROGRAM        VARCHAR2(48),
 LOGON_TIME     DATE,
 LAST_CALL_ET   NUMBER,
 KGLLKHDL       RAW(8),
 KGLLKREQ       NUMBER,
 USER_NAME      VARCHAR2(30),
 KGLNAOBJ       VARCHAR2(60),
 SQL_ID         VARCHAR2(13),
 RESOURCE_NAME1 VARCHAR2(30),
 RESOURCE_NAME2 VARCHAR2(30),
 SQL_FULLTEXT   CLOB
);

P.P.S. This was all tested only on Exadata running 11.2.0.2.

Oracle documentation on Library Cache:

The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles. In a shared server architecture, the library cache also contains private SQL areas.

Oracle 12c Concepts manual diagram with library cache

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Categories: DBA Blogs

Parse time by day of the week

Mon, 2014-02-03 15:58

I wanted to find out if queries against a particular table were experiencing parse time during the week.  We had previously seen issues where queries on the table would be stuck parsing for tens of minutes before they started actually running.  I think this has been resolved but I’m looking for some validation from ASH.  I know that on Sundays we recompress some subpartitions for the table and this leads to higher parse times if the users run queries then, but I’m not trying to resolve that at this time.  I just care about the other days of the week.  So, here is the query I used:

select 
to_char(sample_time,'DAY') sample_day,
IN_PARSE,
count(*)*10 total_seconds
from DBA_HIST_ACTIVE_SESS_HISTORY a,sqlids s
where 
a.sql_id= s.sql_id
group by to_char(sample_time,'DAY'),in_parse
order by to_char(sample_time,'DAY'),in_parse;

The table “sqlids” has a list of all the SQL statements that refer to the table.  I populated sqlids like this:

create table sqlids as select SQL_ID
        FROM DBA_HIST_SQLTEXT 
        where 1=2;

DECLARE 
    CURSOR SQL_CURSOR IS 
        SELECT DISTINCT 
            SQL_ID,
            DBID
        FROM DBA_HIST_SQLSTAT; 
    SQL_REC SQL_CURSOR%ROWTYPE;
    CURSOR TEXT_CURSOR(SQL_ID_ARGUMENT VARCHAR2,DBID_ARGUMENT NUMBER)
      IS 
        SELECT  
            SQL_ID
        FROM DBA_HIST_SQLTEXT
        WHERE 
            SQL_TEXT like '%MYTABLE%' and
            SQL_ID = SQL_ID_ARGUMENT and
            DBID = DBID_ARGUMENT;
    TEXT_REC TEXT_CURSOR%ROWTYPE;
BEGIN
    OPEN SQL_CURSOR;
    LOOP
        FETCH SQL_CURSOR INTO SQL_REC;
        EXIT WHEN SQL_CURSOR%NOTFOUND;

        OPEN TEXT_CURSOR(SQL_REC.SQL_ID,SQL_REC.DBID);
        LOOP
            FETCH TEXT_CURSOR INTO TEXT_REC;
            EXIT WHEN TEXT_CURSOR%NOTFOUND;
            insert into sqlids values (TEXT_REC.SQL_ID);
            commit;
         END LOOP;
        CLOSE TEXT_CURSOR;
     END LOOP;
    CLOSE SQL_CURSOR;
END;
/

Here is the output of the query I mentioned earlier with the parse time in seconds by day of the week:

SAMPLE_DA I TOTAL_SECONDS
--------- - -------------
FRIDAY    N       1092220
FRIDAY    Y        281980
MONDAY    N       2158620
MONDAY    Y         77860
SATURDAY  N       1483420
SATURDAY  Y        259680
SUNDAY    N      18939770
SUNDAY    Y      65665540
THURSDAY  N       1180780
THURSDAY  Y        300800
TUESDAY   N       1286980
TUESDAY   Y        108510
WEDNESDAY N       1399100
WEDNESDAY Y        306890

As I hoped Sunday is the only day of the week that parse time exceeds non-parse time for the sql statements against the given table.  I’m still validating this in other ways – most importantly by trying to contact the users to see if they perceive the problem to be gone.

But, I thought I’d pass along the query in case others find it useful.

- Bobby

 

Categories: DBA Blogs