German Oracle User Group (DOAG) Conference 2015

Kirill Loifman is DOAG 2015 conference speaker dadbm.comThe 28th edition of DOAG 2015 annual Conference + Exhibition was held November 17 – 20, 2015 in Nuremberg Conference Center. Participants had the opportunity to attend the exibition and daily lecture programs with more than 500 talks and international top speakers, plus a wide choice of workshops,  and community activities.
Interseting key notes including one with Andrew Mendelsohn, Executive Senior Vice President at Oracle, who shared the information about new developments in Oracle database 12c.
This was a great opportunity for everyone to expand the knowledge and benefit from the know-how of the Oracle community.

Thanks for all the organizers and participants.

I was speaking there also with a database related topic called:

Reduce planned database down time with Oracle technology smart

Official Abstract of my DOAG presentation:
How to design an Oracle database system to minimize planned interruptions? That depends on the requirements, goals, SLAs etc. The presentation will follow top-down approach. First we will describe major types of planned maintenance, prioritize those and then based on the system availability requirements find the best cost-effective technics to address those. A bit of planning, strategy and of course modern OS and database technics including latest Oracle 12c features will follow.


Timelines and location: Thursday, 2015-11-19, room Shanghai

The presentation consists of 3 major parts including Linux and SQL code examples:

– System downtimes and high availability basics
– Reducing planned downtime approach and methodology
– Technical part: system configurations, technics, new Oracle 12c features

See the slightly adjusted presentation material below:
– Presentation: Reduce planned database down time with Oracle technology
– Scripts: will follow

Please share your ideas, expririence or ask questions in the Comments to this post below.

You can also review my previouse DOAG 2014 presentation including live demo: Live adventure – From my PC to Oracle remote database

Some a few photos below… Read more »

Oracle slow SQL query against dba_segments solved

I remember when long time ago one database consultant confused my manager saying that our Oracle 9i database had poor performance just taking into account a slow response from dba_segments data dictionary view.  That was a nasty trick to blame a DBA and the Oracle database for poor performance at that time. In fact there were a few Oracle bugs related to those performance issues after switching from dictionary to locally managed tablesspaces at that time. Recently I’ve noticed similar performance degradation on Oracle 11gR2 (11.2.0.2 and 11.2.0.3) by querying DBA_SEGMENTS or USER_SEGMENTS data dictionary views involving the columns BYTES, BLOCKS, or EXTENTS. Queries on DBA_TS_QUOTAS or USER_TS_QUOTES on columns BYTES or BLOCKS were also slow.

Even if you personally do not care about these dictionary views they are still very important since they are used by some Oracle internal components and the other database tools including Oracle Enterprise Manager (OEM) Cloud Control and its Database Home Page. Thus, I’ll describe below the problematic of those data dictionary views and the way how to fix their performance issues.

First of all do not wonder why queries against those views often seem to slow. DBA_SEGEMENTS for example is a very complex view that is built on another SYS_DBA_SEGS view. In summary DBA_SEGMENTS view on Oracle 11gR2 consists of the following components:

– 25 columns
– around 110 lines of SQL code
– 3 UNION ALL clauses
– A lot of joins between following tables: sys.user$, sys.ts$, sys.undo$, sys.seg$, sys.file$

Read more »

Database hang and Row Cache Lock concurrency troubleshooting

Issue background

This post will help to analyze Oracle database instance slowdown that can happen due to considerable row cache lock (enqueue) wait events. It’s is based on a real case of a database hang that I worked on recently. I must admit this type of situation does not appear often but it’s very dangerous since it can considerably slow down a database instance or even freeze it for a short period of time. In most cases SQL against ASH view and Systemstate dumps can help to nail down the problem unless this is an Oracle bug.

Usually it occurs suddenly and disappears quickly. See an example ASH graph below with brown peak that represents this type of concurrency: row cache lock wait events.

ASH graph - Row Cache Lock concurrency

ASH graph – Row Cache Lock concurrency

What is a Row Cache Enqueue Lock?

The Row Cache or Data Dictionary Cache is a memory area in the shared pool that holds data dictionary information. Row cache holds data as rows instead of buffers. A Row cache enqueue lock is a lock on the data dictionary rows. It is used primarily to serialize changes to the data dictionary and to wait for a lock on a data dictionary cache. The enqueue will be on a specific data dictionary object. This is called the enqueue type and can be found in the v$rowcache view. Waits on this event usually indicate some form of DDL occurring, or possibly recursive operations such as storage management, sequence numbers incrementing frequently, etc. Diagnosing the cause of the contention

Diagnosing the cause of the contention

Read more »

How CASE expression manipulates with NULL in Oracle SQL

My next post is an answer to the below question about Oracle SQL CASE expression and the way how it manipulates with NULL values compared to DECODE function. This topic in fact is very common in Oracle database developers’ area.

===============
Hi Kirill,

Could you please help me to clarify below mention doubt.

DECODE:-

select decode (null,null,’true’,’false’) as value from dual

OUTPUT:- TRUE

CASE:-

select case null when null then ‘true’ else  ‘false’ end as Case_Test from dual

OUTPUT :- FALSE

Why above mention queries are giving different output  and what is the difference between case and decode?

==============

CASE expression treats NULLs in Oracle database SQL a bit differently compared to DECODE. You have to understand that the Boolean expressions usually result in TRUE or FALSE , but NULLs introduce a third possible result which is : UNKNOWN . At the same time NULL is not the same as UNKNOWN. See an example below:

sum + NULL results in NULL (this is a Scalar expression.)
NULL = NULL results in UNKNOWN (this is a Boolean expression.)
sum < NULL results in UNKNOWN (this is a Boolean expression.)

Ok. Now let’s explore the Oracle SQL CASE expression itself.

Oracle database SQL CASE expression

Oracle database SQL – CASE expression

CASE introduces two ways of conditional expressions: Simple CASE and Searched CASE. Read more »

How to create Sample Schemas in Oracle 11g database – ORA-19613

The Oracle Database Sample Schemas provide a common platform for examples in each release of Oracle Database. They are easy to use for testing and training purposes. All Oracle Database documentation and training materials refer to the Sample Schemas environment.

Following are Oracle Sample Schemas

– OE (Order Entry schema) – useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including non-scalar data types.

– HR (Human Resources schema) – useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.

– PM (Product Media schema) is dedicated to multimedia data types.

– IX (Information Exchange main schema) includes a set of schemas for demonstrate Oracle Advanced Queuing capabilities

– SH (Sales History schema) is designed to allow for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

– SCOTT – old-fashioned famous schema with its two prominent tables EMP and DEPT that is used by Oracle for many years.

– BI – includes only synonyms on SH schema

With this simplicity I personally found difficulties in creating Sample Schemas in Oracle 11g database. In fact I could not do it in a standard way with Oracle installation binaries before 11.2.0.3. If somebody experienced similar issues, I give below the way how to install Sample Schemas manually. Read more »

Next Page »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email