Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Oracle Flash Cache on Linux with Exadata

cool stuff:
Guy Harrison explains how to set up flash caching on Linux for Oracle without Exatata
Late he give some analysis on performance of no flash, flash cache and flash tablespae

http://guyharrison.squarespace.com/blog/2010/1/24/flash-tablespace-vs-db-flash-cache.html

SQL Plan visualization by Tanel Poder

Check this out - SQL Plan visualization by Tanel Poder:


Thinking Clearly About Performance

I’ve posted a new paper at method-r.com called “Thinking Clearly About Performance.” It’s a topic I’ll be presenting this year at:

The paper is only 13 pages long, and I think you’ll be pleased with its information density. Here is the table of contents:

  1. An Axiomatic Approach
  2. What is Performance?
  3. Response Time vs Throughput
  4. Percentile Specifications
  5. Problem Diagnosis
  6. The Sequence Diagram
  7. The Profile
  8. Amdahl’s Law
  9. Minimizing Risk
  10. Load
  11. Queueing Delay
  12. The Knee
  13. Relevance of the Knee
  14. Capacity Planning
  15. Random Arrivals
  16. Coherency Delay
  17. Performance Testing
  18. Measuring
  19. Performance is a Feature

As usual, I learned a lot writing it. I hope you’ll find it to be a useful distillation of how performance works.

Deliberate Practice

Recently I did some soul searching about my expertise as a DBA. I am not talking about my knowledge, my talents and my work style. I’m talking about which things I’m really comfortable doing. The commands I know by heart, the issues I ran into so often that I can diagnose with the tiniest clues.

There are definitely things I’m very good at. Diagnosing why RAC crashed or wouldn’t start. Solving a range of different problems with Streams. User managed recoveries. Netapp. BASH. Top. sar. vmstat. Redo log mining. Datapump. ASH and its relatives AWR and ADDM. Using Snapper to work with wait event interface. SQL coding, network diagnosis, Patching.

These are mostly things I do every day or close enough to it that the commands, the techniques, the traps and the limitations are always clear in my mind. But there are things that I do rarely or even never. This are important DBA skills, some are even very basic, which I do not have because they are not very useful in my specific position.

These include RMAN, ASM, Dataguard, AWK, perl, python, PL/SQL, tracing, SQL tuning, upgrade testing, benchmarks, many Linux administration tools, hadoop and those new NoSQL things, MySQL, Amazon’s cloud databases, RAT, partitions, scheduler.

These are all things that I know something about, that I’ve read about – but I can’t say I’m confident with any of these because I simply haven’t played with them all that much. After all, you learn by doing and running into issues – not by reading people say how everything works perfectly when they use it.

In order to widen my skill set a bit, I’m planning to take time this year to deliberately practice some of the technologies I didn’t use much last year. I’m thinking of taking anything from few weeks to few month per technology. I’ll invent and look up “lab exercises” for the specific topic and then proceed to spend the month practicing. Think of it as a “poor DBA’s Oracle University”.

This is the opposite of what I’ve been doing until now which can be described as “read Oracle’s Concepts book”. Reading the concepts book is great, but I’m at a point where I feel that I know tons of theory and need to spend some quality time grappling with its various applications. There seems to be a lot of research that shoes that best way to become an expert is with deliberate practice, so this year – I’ll practice.

Unless I’ll run into something really exciting, I don’t expect to blog about this adventure. After all, if I start posting scores of trivial AWK scripts, I doubt if anyone will keep reading my blog. But I thought that maybe some of my readers will enjoy joining me in my practice. So I opened this mailing list.

If you are also interested in practicing with me, feel free to join the mailing list. I’ll announce a “topic of the month”. This month its AWK, next month probably RMAN, we’ll see how this goes. I’ll send to the list the questions I’m planning to solve. If you join, you can send in your own questions. We’ll send our answers to our own and others questions with a week delay (so everyone will have time to practice on his own first). We’ll discuss and compare our answers. We’ll cheer each other as we improve our skills. I will not try to sell you anything.

This is an obvious ploy. I need people on the list so I’ll be accountable to this practice. Otherwise I’ll probably forget it by next week. Feel free to join just to watch me stumble and remind me that this is what learning looks like and it will be worth it.

Here’s to a year of practice and hopefully better skills that will follow.

xplan: dbms_metadata.get_ddl for tables referenced by the plan

As a minor but useful new feature, xplan is now able to integrate into its report the DDL of tables (and indexes) referenced by the plan, calling dbms_metadata.get_ddl transparently.

This is mostly useful to get more details about referenced tables' constraints and partitions definition - to complement their CBO-related statistics that xplan reports about.

This feature can be activated by specifing dbms_metadata=y or dbms_metadata=all (check xplan.sql header of xplan.sql for more informations).

We spoke about xplan in general here.

Oracle Wait Event reference

Kyle Hailey has started putting together a much needed Oracle wait event reference.

You can access it here.

By the way, Oracle documentation also has a wait event reference section, it has more events, but it’s less detailed…

I have plans to go deep into some wait events and cover some less common ones in tech.E2SN too… in the future ;-)

Share/Bookmark

Scalar Subqueries

Better formatted at http://tinyurl.com/yfrjbwx
Query 2
The VST diagram looks like
There are two interesting things about this diagram.
Every thing is OUTER JOINED to F_OUTER
There are correlated subqueries in the select

There are two things to check - what is the effect of the OUTER JOINS. The OUTER JOINS can easily mean that all joins into F_OUTER don't change the result set size. Let's confirm by looking at the TTJ sizes:

The only thing that bounds the number of rows returned by F_OUTER is it's self join (the line going in and out of F_OUTER) on the bottom left of F_OUTER.
What this means is that it doesn't matter what order we join tables into F_OUTER.
Now we can turn to the other interesting thing about the query. There are 4 correlated subselects in the select clause. These queries in the select clause are called "scalar subqueries." These scalar subqueries can be a bad idea or a good idea depending on how mainly on how many distinct values are used as input into the them. AFAIK, Oracle doesn't merge subqueries in the select, and certainly not the ones in this query because they are embedded in cases statements - looks like I might have spoken too soon! more reseach to do but looks like Oracle can merge these scalar subqueries even with the case statement. I will try to run some more tests . To be continued)
In the worst case scenario the scalar subqueries are going to be run 845,012 times - that is one heck of a lot of work which would be a BAD IDEA.

Looking at the above diagram, and the 4 scalar subqueries in the select clause, the top red number is how many times the scalar subquery will be run (based on the case statement in the select clause) and the orange highlight is how many distinct values will be used in the scalar subquery where clause. P3 will benefit for scalar subquery caching but F won't because there are too many distinct values. On the other hand for P1 and P2 could if there are no collisions (see CBOF p216-217) and the scalar subquery caching is actually supports 1024 values. ( see http://www.oratechinfo.co.uk/scalar_subqueries.html#scalar3 for a great write up on analysis of scalar subquery caching - the analysis on this page seems to show that caching maxes out way before 1024 but that might be because of collisions)

The subqueries in the select clause look like

select CASE WHEN F.f1 IS NULL
THEN NULL
ELSE (SELECT X.f2
FROM X
WHERE code_vl = F.f1)
END AS f0
from F;

and could be merged into the query like:


select CASE WHEN F.f1 IS NULL
THEN NULL
ELSE ( X.f2)
END AS f0
from F , X
where code_vl(+) = F.f1;


( NOTE: the first query will break if the correlated sub query returns more than one value where as the second query will return the mulitple rows.)

The VST diagram can't tell you that this is the solution, but they can point out that the place to spend your time is the subqueries in the select.

Index Block Dumps and Index Tree Dumps Part I: (Knock On Wood)

I thought before I jump into a topic that requires looking at a number of index block dumps, it might be worth briefly recapping how one goes about dumping index blocks in Oracle.   A block dump is simply a formatted representation of the contents of a particular Oracle database block.  Although I’ll be focusing [...]

Oracle: SQL*Net Waits

article better formatted at


Introduction

Unfortunately, what Oracle calls "Network Waits" have little to do with Network but and almost exclusively to do with the time it takes to pack messeges for the network before they are sent.
Client = you, the tool, sqlplus, application
the shadow process is communicating to the client

Of the three waits, only "more data" is possibly related to network issues and that's not even clear, the other two are simply the time it takes to pack a message before sending it.

SQL*Net message to client - time to pack a message (no network time included) possibly tune SDU
SQL*Net more data from client - possible network issues, possibly tune SDU
SQL*Net more data to client - time to pack a message (no network time included) possibly tune SDU
The same events exist, but where the client is the shadow process and another database plays the roll of shadow process:
SQL*Net message to dblink
SQL*Net more data from dblink - possible network issues, possibly tune SDU
SQL*Net more data to dblink

SQL*Net Wait Events

SQL*Net message from client

Idle Event
Waiting for work from Client
Includes network transmission times for messages coming from shadow
Typically indicative of Client “think time” or “processing time”

SQL*Net message to client

Time it takes to pack a message to be sent to the client

Doesn’t include network timing
see Tanel Poder's analysis of SQL*Net message to client

SQL*Net more data to client

Same as SQL*Net message to client except this is for data that spans SDU packets.

Wait represents the time it takes to pack data.
Doesn’t include network timing

SQL*Net more data from client

The only SQL*Net wait that can indicate a possible NETWORK problem
Client is sending data to shadow that spans packets (think large data inserts, possibly large code blocks, large SQL statements)
Shadow waits for next packet.
Can indicate network latency.
Can indicate a problem with the client tool
Here is an example with ASHMON where the application server died mid-stream on inserts. The shadow processes were left waiting for completion of the message. You can see the regular load on the database on the left, then just past the middle the load crashes, and all that's left is waits on "SQL*Net more data from client"

Possibly set SDU=32768 as well as setting RECV_BUF_SIZE and SEND_BUF_SIZE to 65536.

SQL*Net break/reset to client

Error in sql statement

Control C
Usually highlights and error in application
Example:
CREATE TABLE T1 (C1 NUMBER);
ALTER TABLE T1 ADD
(CONSTRAINT T1_CHECK1 CHECK (C1 IN ('J','N')));
ALTER SESSION SET EVENTS
'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
INSERT INTO T1 VALUES (1);
Trace File
PARSING IN CURSOR #2 len=25 dep=0 uid=0 oct=2 lid=0 tim=5009300581224 hv=9816834
09 ad='8e6a7c10'
INSERT INTO T1 VALUES (1)
END OF STMT
PARSE #2:c=0,e=2770,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=5009300581220
BINDS #2:
EXEC #2:c=0,e=128,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5009300581418
ERROR #2:err=1722 tim=512952379
WAIT #2: nam='SQL*Net break/reset to client' ela= 31 driver id=1650815232 break?
=1 p3=0 obj#=-1 tim=5009300581549
WAIT #2: nam='SQL*Net break/reset to client' ela= 92 driver id=1650815232 break?
=0 p3=0 obj#=-1 tim=5009300581662

DBLINK SQL*Net Waits

These waits are the same as
SQL*Net message to dblink
SQL*Net more data from dblink
SQL*Net more data to dblink
SQL*Net break/reset to dblink

Analysis and Tuning

There isn't much to do on the Oracle side for tuning. You can try optimizing the SDU and SEND_BUF_SIZE and RECV_BUF_SIZE.
For actually getting information on network speeds you will have to use something like
  • ping
  • tnsping
  • network sniffe

SDU

The default SDU can be set in the sqlnet. ora
If it's not set, the default is 2048
The max is 32768
The default,or the value in sqlnet.ora, can be overridden in the tnsnames. ora and the listener.ora. The client and server negotiate the size aggreeing on the smaller of the two settings.
(TDU – Transmission Data Unit – see note 44694.1 The TDU parameter has been deprecated in the Oracle Net v8.0 and beyond and is ignored. It is only mentioned here for backward compatibility.)
tnsnames.ora
V10G = (DESCRIPTION =
(SDU=32768)
(ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = v10g)
) )
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=32768)
(SID_NAME = v10g)
(ORACLE_HOME = /export/home/oracle10)
))

Tracing

sqlnet.ora

trace_level_client=16

trace_directory_client=/tmp

trace_file_client=client.trc

trace_unique_client = true

trace_level_server=16

trace_directory_server=/tmp

trace_file_server=server.trc

client.trc

client_3582.trc:[12-JAN-2008 11:37:39:237] nsconneg: vsn=313, gbl=0xa01, sdu=32768, tdu=32767

RECV_BUF_SIZE and SEND_BUF_SIZE

The recommended size for these buffers (from Oracle's docs) is at least
Network bandwidth * roundtrip = buffer min size
For example if the network bandwidth is 100mbs and the round trip time (from ping) is 5ms then
100,000,000 bits 1 byte 5 seconds
---------------- x ------ x --------- = 62,500 bytes
1 second 8 bits 1000
tnsnames.ora
V10G = (DESCRIPTION =
(SEND_BUF_SIZE=65536)
(RECV_BUF_SIZE=65536)
(ADDRESS = (PROTOCOL = TCP)(HOST = fuji)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = v10g)
) )

listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SEND_BUF_SIZE=65536)
(RECV_BUF_SIZE=65536)
(SID_NAME = v10g)
(ORACLE_HOME = /export/home/oracle10)
))

sqlnet.ora
RECV_BUF_SIZE=65536
SEND_BUF_SIZE=65536

Automated Root Cause Analysis

I’ve ran into multiple products that claim to offer automated root cause analysis, so don’t think that I’m ranting against a specific product or vendor. I have a problem with the concept.

The problem these products are trying to solve: IT staff spend much of their time trying to troubleshoot issues. Essentially finding the cause of effects they don’t like. What is causing high response times on this report? What is causing the lower disk throughputs?

If we can somehow automate the task of finding a cause for a problem, we’ll have a much more efficient IT department.

The idea that troubleshooting can be automated is rather seductive. I’d love to have a “What is causing this issue” button. My problem is with the way those vendors go about solving this issue.

Most of them use variations of a very similar technique:
All these vendors already have monitoring software, so they usually know when there is a problem. They also know of many other things that happen at the same time. So if their software detects that response time go up, it can look at disk throughput, DB cpu, swap, load average, number of connections, etc etc.
When they see that CPU goes up together with response times – Tada! Root cause found!

First problem with this approach: You can’t look at correlation and declare that you found a cause. Enough said.

Second problem: If you collect so much data (and often these systems have millions of measurements) you will find many correlation by pure chance, in addition to some correlations that do indicate a common issue.
What these vendors do is ignore all the false findings and present the real problems found at a conference as proof that their method works. Also, you can’t reduce the rate of false-findings without losing the rate of finding real issues as well.

Note that I’m not talking about tools like Tanel Poder visualization tool. Tools which makes it easier for the DBA to look at large amounts of data and using our brain’s built in pattern matcher to find correlations. I support any tool that assists me in applying my knowledge to large sets of data at once.

I have a problem with tools that use statistical correlation as a replacement to applying knowledge. It can’t be done.

Here’s the kind of tool I’d like to see:
Suppose your monitoring tool will give you the ability to visually browse, filter and explore all that data you collect in ways that help you troubleshoot. The tool will remember the things you looked at and the steps you took. After you solve the problem, you can upload the problem description and your debug process to a website. You can even mark away the dead-ends of the investigation.

Now you can go to that website and see that for problem X, 90% of the DBAs started by looking at v$sesstat and 10% ran trace. Maybe you can even have a friend network, so you can see that in this case Fuad looked at disk utilization first while Iggy checked how much redo is written each hour.

If you are not into sharing, you can still browse your own past problems and solutions for ideas that might have slipped your mind.

I think that a troubleshooting tool combined with “collective wisdom” site can assist experienced DBAs and improve the learning curve for junior DBAs without pretending to automate away knowledge and experience.