Infrastructure at your Service

Category Archives: Oracle

Franck Pachot

Matching SQL Plan Directives and queries using it

By | Database management, Oracle | No Comments

By Franck Pachot . This is another blog post I’m writing while reviewing the presentation I’m doing next week forSOUG and next month for DOAG. You have SQL Plan Directives used by several queries that have same kind of predicates. And queries that can use several SPD. Can you match them? When a query uses a SPD (meaning that the SPD in usable state – NEW, MISSING_STATS or PERMANENT internal state) the execution plan show…

 
Read More
Franck Pachot

Matching SQL Plan Directives and extended stats

By | Database management, Oracle | One Comment

By Franck Pachot . This year is the year of migration to 12c. Each Oracle version had its CBO feature that make it challenging. The most famous was the bind variable peeking in 9iR2. Cardinality feedback in 11g also came with some surprises. 12c comes with SPD in any edition, which is accompanied by Adaptive Dynamic Sampling. If you want to know more about them, next date is in Switzerland:http://www.soug.ch/events/sig-150521-agenda.html SQL Plan Directives in USABLE/MISSING_STATS…

 
Read More
Franck Pachot

Variations on 1M rows insert (4): APPEND_VALUES

By | Database management, Oracle | No Comments

By Franck Pachot . In the first variation I did a bulk insert with the PL/SQL FORALL statement. A comment suggests to add the APPEND_VALUES hint that appeared in 11gR2. APPEND_VALUES hint does the same than APPEND but it’s for inserts with VALUES clause: It inserts in direct-path – directly into the datafile, bypassing the buffer cache. Of course, this makes sense only with bulk insert because you don’t want to do that with only…

 
Read More
Franck Pachot

Rebuilt index on increasing values after deletes?

By | Database management, Oracle | No Comments

By Franck Pachot . Yesterday while giving our Oracle tuning workshop I discussed with the customer about a job they have that rebuilds indexes every Sunday. Except in very rare cases Oracle indexes are maintained so that free space is reused by further inserts. But an index is an ordered structure. When we insert from a sequence, the value is always increasing, and go at the end of the index. And when we delete old…

 
Read More
Franck Pachot

Variations on 1M rows insert (3): TimesTen

By | Database management, Oracle | No Comments

By Franck Pachot . In the previous blog post I measured how inserting 1 million rows takes 5 minutes instead of 45 seconds when we commit at each row inserted. In Oracle we can improve that down to 2 minutes if we can accept (and manage) the loss of transactions following an instance crash. Let’s go further. For OLTP that need to face a high rate of transactions, Oracle has TimesTen which is a Database…

 
Read More
Franck Pachot

Variations on 1M rows insert(2): commit write

By | Database management, Oracle | One Comment

By Franck Pachot . In the previous blog post I measured the time it takes to insert 1 million rows. About 45 seconds when done row-by-row which goes down to 7 seconds when inserted in bulk. In both case, the commit was done only at the end. But in some cases you need to commit at each row, in order to make it visible to other sessions as soon as it is inserted (think of…

 
Read More
Franck Pachot

Variations on 1M rows insert (1): bulk insert

By | Database management, Oracle | No Comments

By Franck Pachot . This blog is the first one of a series about inserting 1 million rows. Do you have an idea about the time it takes? The idea came from another project, about NoSQL databases, but I’ll focus on Oracle in those posts. There are a lot of different ways to insert 1 million rows in Oracle: row-by-row insert or bulk, row-by-row commit or not, different ways to commit, In-Memory Database, Database In-Memory…

 
Read More
Franck Pachot

A migration pitfall with ALL COLUMN SIZE AUTO

By | Database management, Oracle | No Comments

By Franck Pachot . When you migrate, you should be prepared to face some execution plan changing. That’s not new. But here I’ll show you a case where you have several bad execution plans because lot of histograms are missing. The version is the same. The system is the same. You’ve migrated with DataPump importing all statistics. You have the same automatic job to gather statistics with all default options. You have repeated the migration…

 
Read More
Franck Pachot

List listeners and services from the instance

By | Database management, Oracle | No Comments

By Franck Pachot . Want to know all your listeners – including scan listeners, and the services it listens for? It is possible from the instance, with the – undocumented – view V$LISTENER_NETWORK which is there since 11.2 Let’s look at it from a RAC database: SQL> select * from GV$LISTENER_NETWORK And here is the result: INST_ID NETWORK TYPE VALUE CON_ID 1 LOCAL LISTENER (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.62)(PORT=1521)) 1 1 SERVICE NAME racpdb1.racattack 1 1 SERVICE NAME racpdb2.racattack…

 
Read More
Franck Pachot

ALTER TABLE INMEMORY

By | Database management, Oracle | No Comments

By Franck Pachot . In-Memory Column Store is amazing. It brings very good performance to full table scans. I’t easy: just ‘flip a switch’ and you accelerate all reporting queries on your table, without thinking about what to index and how. But in this post, I would like to warn you about the consequences when you just flip that switch. The new full table scan plan will replace the old ones… even before the table…

 
Read More