12c HCC Row-Level Locking

In Oracle Database 12c we can find many new and shiny things… So many that we can miss the little good things really easy. I think that this one, is one of them. Previously I made a post “All About HCC“, describing how HCC is working and some of the issues that we can hit […]

FIXED_DATE and LAST_ANALYZED

I tested this on Oracle 11.2.0.1. You need to be careful when looking at the LAST_ANALYZED column in USER_TABLES. I created a table and removed the FIXED_DATE parameter from the database:
 
SQL> create table t1 (c1 number)
  2  /
 
Table created.
 
SQL> alter system set fixed_date = none
  2  /
 
System altered.
 
SQL>
 
…then I used DBMS_STATS.GATHER_TABLE_STATS and the old ANALYZE command to (more...)

Oracle 12c RAC and Flex ASM Overview


Dear Readers,

In preparation for upcoming Oracle Open World 2014 and BGOUG 2014 (Bulgarian User Oracle Group) conferences, I will write a series of tips and tricks for new Oracle 12c RAC and ASM features. Today I would like to review the new Oracle 12c RAC Flexible ASM feature. For our discussion as a reference point you can source details in the useful My Oracle Support (http://support.oracle.com) note FAQ: Oracle Flex ASM 12c (more...)

Piggyback commits

Not every commit results in a redo write. This is because there are multiple optimizations (some controlled by the user e.g. with COMMIT_LOGGING parameter, some automatic) that aim at reducing the number of redo writes caused by commits by grouping redo records together. Such group or “piggyback” commits are important for understanding log file sync waits and various statistics around it. In particular, “piggyback” commits play a key role when many sessions commit concurrently (more...)

Getting Started with Windows VDI by Andrew Fryer

Virtual desktop infrastructure (VDI) is the practice of hosting a desktop operating system within a virtual machine (VM) running on a centralized server. VDI is a variation on the client/server computing model, sometimes referred to as server-based computing.
VDI is the new technology that gives lots of benefits.
• Efficient use of CPU and memory resources
• Reduced desktop downtime and increased availability
• Patches and upgrades performed in data center
• New users can (more...)

Intra-Database IORM in action

I have been teaching the Enkitec Exadata Administration Class this week and made an interesting observation I thought was worth sharing with regards to IO Resource Management on Exadata.

I have created a Database Resource Manager (DBRM) Plan that specifically puts a resource consumer group to a disadvantage. Actually, quite severely so but the following shouldn’t be a realistic example in the first place: I wanted to prove a point. Hang-on I hear you say: you (more...)

Quiz Night

I have a table with several indexes on it, and I have two versions of a query that I might run against that table. Examine them carefully, then come up with some plausible reason why it’s possible (with no intervening DDL, DML, stats collection, parameter fiddling etc., etc., etc.) for the second form of the query to be inherently more efficient than the first.


select
        bit_1, id, small_vc
from
        bit_tab
where
        bit_1  (more...)

How not to troubleshoot log file sync waits

Introduction

There is a very common mistake in troubleshooting log file sync (LFS) waits: comparing its average time to average log file parallel write (LFPW) and trying to deduce from that whether the root cause of the wait is slow I/O or something else. The fact that this approach is recommended by Oracle itself (e.g. MOS 1376916.1) and many independent experts unfortunately doesn’t make it any less wrong.

Theory

It is well known that averages and ratios can distort the reality (Milsap and (more...)

Zone Maps On Commit Refresh Oddities

One of the ways Zone Maps can be refreshed when the underlying table data is changed is fast on commit. This is similar to how materialized views can be refreshed with the exception that a Zone Map does not need a materialized view log to do so.

It can also lead to some peculiar side effects.

Test setup

Let's begin by creating a test table with the on commit refresh materialized zone map:
SQL> create  (more...)

ORA-01460 on PL/SQL Developer

An Oracle 9 database had the following character set:
 
SQL> l
  1  select sys_context('userenv','LANGUAGE')
  2* from dual
SQL> /
 
SYS_CONTEXT('USERENV','LANGUAGE')
----------------------------------------
ENGLISH_UNITED KINGDOM.WE8ISO8859P15
 
SQL>
 
I used PL/SQL Developer to look at the code in the database (as usual, click on the image to enlarge it and bring it into focus):

 
Somebody typed the following command in the database by mistake:
 
SQL> l
  1* create database (more...)

Blooms

In Oracle releases 10.2.0.x and later join processing can be made more efficient by the use of Bloom filters, primarily to reduce traffic between parallel query slaves. What is a Bloom filter? Named after Burton Howard Bloom, who came up with the concept in the 1970s, it’s an efficient data structure used to quickly determine if an element has a high probability of being a member of a given set. It’s based (more...)

Querying trace files

SQL trace file provide the highest level of detail possible about SQL execution. The problem with that information is converting it to a convenient format for further analysis. One very good solution is parsetrc tool by Kyle Hailey written in Perl. It gives high-resolution histograms, I/O transfer rates as a function of time, and other very useful info. Unfortunately, I myself am not a Perl expert, so it’s a bit difficult for me to customize (more...)

Adding additional agents to OEM12c

One question I get asked a lot is “how can I add additional agent software to OEM 12c”?  The answer is pretty easy; just download and apply to the software library.  Now what does that mean?  In this post, I’ll explain how to download additional agents for later deployments to other platforms.

After logging into OEM 12c, go to the Setup -> Extensibility -> Self Update (Image 1).

Image 1:

SelfUpdate_Menu.png

 

 

 

 

 

(more...)

ASSM Truncate.

Here’s one that started off with a tweet from Kevin Closson, heading towards a finish that shows some interesting effects when you truncate large objects that are using ASSM. To demonstrate the problem I’ve set up a tablespace using system allocation of extents and automatic segment space management (ASSM).  It’s the ASSM that causes the problem, but it requires a mixture of circumstances to create a little surprise.


create
	tablespace test_8k_auto_assm
	datafile	-- OMF
	SIZE 1030M
	autoextend  (more...)

API Integration with Zapier (Gmail to Salesforce)

Recently I attended a training session with +General Assembly  in London titled the What and Why of APIs. It was a training session focusing on usage of APIs and it was not technical at all. I find these type of training sessions very useful as they describe concepts and controlling ideas behind technologies rather than the hands-on, involved implementation details.

What grabbed my attention from the many different and very useful public and private (more...)

Watch Oracle DB Elapsed Time and Wall Time With Parallel Query

Watch Oracle Elapsed Time and Wall Time With Parallel Query


In my recent postings I wrote that when using the Oracle Database parallel query a SQL statement's wall time should be equal to its elapsed time divided by the number of parallel query slaves plus some overhead.

That may seem correct, but is it really true? To check I ran an experiment and posted the results here. The results are both obvious and illuminating.

If (more...)

Latest Bundle Patch for EM12c and White paper for reducing downtime when patching the OMS

Friends,
Enterprise Manager 12.1.0.4 was released in June 2014, and the 12.1.0.4 OMS Bundle patches  have also been released in July 2014. If you have recently upgraded your Enterprise Manager OMS  to 12.1.0.4, it is recommended to apply the latest available bundle patch to your 12.1.0.4 OMS.
For the list of the available Bundle patches or System patches, please refer to the My (more...)

Openworld bound..

I’ll be in Openworld again this year, and have a couple of speaking slots…

I’ll talk about new features for developers in 12c on Sunday, Sep 28, 2:30 Moscone South 303

https://oracleus.activeevents.com/2014/connect/sessionDetail.ww?SESSION_ID=2244 

and of course, at the awesome Oaktable world..

http://www.oraclerealworld.com/oaktable-world/agenda/ 

Drop in, learn some cool things, or just pop up and say Hello!


Oracle IO wait events: db file sequential read

(the details are investigated and specific to Oracle’s database implementation on Linux x86_64)

Exadata IO: This event is not used with Exadata storage, ‘cell single block physical read’ is used instead.
Parameters:
p1: file#
p2: block#
p3: blocks

Despite p3 listing the number of blocks, I haven’t seen a db file sequential read event that read more than one block ever. Of course this could change in a newer release.

Implementation:
One of the important things (more...)

First AZORA usergroup meeting October 23

Just got the invitation to the first AZORA (Arizona Oracle user group) meeting on October 23.  Here is the link: url

It’s 2 pm at Oracle’s office, 2355 E Camelback Rd Ste 950, Phoenix, AZ.

I’m looking forward to it!

– Bobby