Infrastructure at your Service

Category Archives: Oracle

Oracle Team

From 8.0 extended rowid to 12c pluggable db: Why Oracle Database is still a great software

By | Database management, Oracle | 4 Comments

By Franck Pachot . Do you know why Oracle Database is a leader in the database market since 30 years? Do you know any other software that is still the leading solution after decades? I think that it is because Oracle has been a good software from the get-go. Many early decisions in the software architecture have revealed themselves later to be the right decision. Several decisions, such as the C language that made it…

Read More
Oracle Team

What about ALTER INDEX … SHRINK SPACE ?

By | Database management, Oracle | No Comments

By Franck Pachot . I have recently published a script to check index fragmentation. But then, do you COALESCE or REBUILD? Well, there also is another option: ALTER INDEX SHRINK SPACE. Let’s compare all those index defragmentation operations. Fragmented index I have an index created when the table had 1 million rows. Then, I deleted 90% of the rows. Here is the index state from: dbms_space.space_usage index_stats after an analyze index validate structure my index…

Read More
Oracle Team

Oracle IOT: when to use Index Organized Tables

By | Database management, Oracle | No Comments

By Franck Pachot . When can we use IOT? That’s a question I had when giving recently the Oracle DBA essentials workshop. the DBA Essential is very dense and there is only half a day about performance. We have the Oracle Performance Tuning workshop to go into those details. But IOTs are under used in my opinion, so I’ll post a use case for them where they are a good idea. Basically: Index access is…

Read More
Oracle Team

Oracle system statistics: Display AUX_STATS$ with calculated values and formulas

By | Oracle | 9 Comments

By Franck Pachot . System statistics can be gathered in NOWORKLOAD or WORKLOAD mode. Different values will be set depending on that and the others will be calculated – derived from them. We can see defined values from SYS.AUX_STATS$ but here is a script that shows the calculated ones as well. With no system statistics or NOWORKLOAD the values of IOSEEKTIM (latency in ms) and IOTFRSPEED (transfer in bytes/ms) are set and the SREADTIM (time…

Read More
Oracle Team

How to measure Oracle index fragmentation

By | Database management, Oracle | 8 Comments

By Franck Pachot . At Oracle Open World 2014, or rather the Oaktable World, Chris Antognini has presented ‘Indexes: Structure, Splits and Free Space Management Internals’. It’s not something new, but it’s still something that is not always well understood: how index space is managed, block splits, fragmentation, coalesce and rebuilds. Kyle Hailey has made a video of it available here. For me, it is the occasion to share the script I use to see…

Read More
Oracle Team

OOW14 Day 5 – not only Oracle OpenWorld

By | Database management, Oracle | No Comments

By Franck Pachot . Oracle’s OpenWorld has ended. It was the fist time I attended this great event and it really is a “great” event: 60000 attendees from 145 countries 500 partners or customers in the exhibit hall 400 demos in the DEMOgrounds 2500 sessions This is Howard Street, a four lane avenue transformed as the Oracle Plazza for the event – in the middle of the 100000m2 of exibition halls and conference rooms: Oracle…

Read More
Oracle Team

OOW14 Day 4 – Internals of the 12c Multitenant Architecture by Vit Spinka

By | Database management, Oracle | No Comments

By Franck Pachot . This is the session I preferred at Oracle Open World. Well, I’m writing that before going to @ludodba one and I’m sure I’ll have then two preferred sessions… So Vit Spinka has presented the internals of the new multitenant architecture. It’s always good to play with some internals things. Not only for the geeky fun of it but also because it helps understand how it work and address issues later. I had…

Read More
Oracle Team

OOW14 Day 3 – 12c Adaptive Bitmap Pruning

By | Database management, Oracle | No Comments

By Franck Pachot . I’m currently following the session ‘Real-World Performance of Star and Snowflake Schemas’ with Michael Hallas, Andrew Holdsworth, John Clarke. It’s really a good presentation. the Real Performance team tries to spread messages about how to design for performance. But today I’ll not blog about what I’ve seen but about what I’ve not seen. Everybody talks about those nice 12c features that are useful in BI workloads, such as adaptive joins, adaptive…

Read More
Oracle Team

Solving customer issues at OOW14: Dbvisit Replicate can replicate tables without primary key

By | Database management, Oracle | No Comments

By Franck Pachot . Usually, the logical replication of changes uses the primary key. Each row updated or deleted generate a statement to be applied on the target, which affects only one row because it accesses with the primary key. If there is no primary key, we need to have something unique and at worst it is the whole row. But sometimes old applications were designed before being implemented into relational database and have no…

Read More
Oracle Team

OOW14 Day 2 – Delphix #cloneattack

By | Database management, Oracle | One Comment

By Franck Pachot . Do you know Delphix? The first time I heard of it was from Jonathan Lewis. And from Kyle Hailey of course. So it’s not only about agile and virtualization. It’s a real DBA stuff. So as I did yesterday with Dbvisit #repattack let’s install the demo. Here is the setup: one source virtual machine with an XE database one target virtual machine with XE installed but no database one virtual machine with…

Read More