Oracle Database as our consultants live it every day: installation and patching, upgrades, RMAN backup and recovery, Data Guard, RAC, performance tuning, multitenant, licensing, and engineered systems such as ODA and Exadata.
By Franck Pachot . If you followed the 1M rows insert variations, you have seen how something simple brought me into an investigation with the latest tools available to see where the time is spent. When the time is CPU time, there is no wait event to get the detail of DB Time. I’ve used perf events in the previous post…
By Franck Pachot . In previous post I explained that I’ve observed a 2x CPU usage when running my insert test case in 11.2.0.4 vs. 11.2.0.2 I discussed that with @martinberx @OracleSK @TanelPoder @xtner @Ycolin @fritshoogland @lleturgez @LucaCanaliDB on twitter and it became clear that: ‘perf’ is the tool to use when investigating CPU usage. we must be use hardware…
By Franck Pachot . Oracle In-Memory option is for reporting. Oracle has chosen an hybrid approach: maintain the In-Memory Column Store in addition of the buffer cache. Dont’ forget our In-Memory event, but for the moment, let’s try the 1M row insert when the target table is IN MEMORY. 12c I’ve done the previous variations on 11g because I wanted…
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…
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…
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…
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…
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…
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…
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…
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…
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…
