Infrastructure at your Service

Category Archives: Oracle

Oracle Team

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
Oracle Team

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
Oracle Team

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
Oracle Team

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
Oracle Team

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
Oracle Team

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
Oracle Team

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
Oracle Team

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
Oracle Team

C15LV: Nothing to BLOG About – Think Again

By | Database management, Oracle | No Comments

By Franck Pachot . Before going to the airport to come back in Europe, the las session I assisted at IOUG Collaborate is a panel: Nothing to BLOG About – Think Again Some famous bloggers were there to help, motivate and inspire people that are afraid to blog. The idea is good. The session was good. The panelists have shared ideas, tips, experience. It was very good. I’m already a blogger, but that inspired me anyway….

Read More
Oracle Team

SQL Monitor report little improvement in 12c

By | Database management, Oracle | 2 Comments

By Franck Pachot . This is a very short post about something I though I had already blogged about. I like SQL Monitoring active reports. The are graphical, and still very light. There is only one thing I don’t like about it in 11g and it has been improved in 12c Look at the following. The query has run for 1.1 minute. And from the ‘Database Time’ line we can see that it used mostly…

Read More