Infrastructure at your Service

Category

Oracle

Franck Pachot

Does the block size matter?

By | Database management, Oracle | 23 Comments

By Franck Pachot . The default block size is 8k in Oracle. This is the most common. Sometimes, people create the database with 16k block size for datawarehouses. You can also find some 32k block size, but less common which means more bugs. In our tuning workshop we recommend the default, 8k, for most of the cases. I’ll show here 3 tests to show what different block size change for full table scan and index…

Read More
Franck Pachot

DOAG Database 2015

By | Database management, Oracle | No Comments

By Franck Pachot . It was my first time at DOAG Datenbank in Dusseldorf.  My understanding of German is very limited. I’m French, I’ve learned German at school but then didn’t practice. Now that I’m living in Switzerland it would help but when I tried to remember something from school time it failed with Ora-1555: Snapshot too old… So I was there because I presented SQL Plan Directives – in English – and I really…

Read More
Franck Pachot

Parallel DML in 12c

By | Database management, Oracle | 3 Comments

By Franck Pachot . Following a question from Randolf Geist (who can imagine that there is something about parallel query that Randolf didn’t know?), I get back to some notes I’ve taken when 12c was out and I’ve tested them again on 12.1.0.2 – it’s about the ability to enable parallel DML at query level. Test case In 12.1.0.2 I create two tables. DEMO1 has 100000 rows and is about 80MB. DEMO2 is empty. DEMO1…

Read More
Franck Pachot

Can you have pending system statistics?

By | Database management, Oracle | One Comment

By Franck Pachot . Your system statistics seems to be wrong and you want to gather or set more relevant ones. But you don’t want to see all your application execution plans changing between nested loops and hash joins. For object statistics, we can gather statistics in a pending mode, test them in a few sessions, and publish them when we are ok with them. But for system statistics, can you do the same? It…

Read More
Franck Pachot

Flame Graph for quick identification of Oracle bug

By | Database management, Oracle | No Comments

By Franck Pachot . Most of my performance stores start with a screenshot of Orachrome Lighty my preferred tool to have a graphical view of the database performance, in Standard and Enterprise Edition without any options: I’m investigating a long running query. That query obeys to the most important rule in Oracle performance: parce once (CPU) – execute many (IO+CPU) … except that the parse time is a bit too long here (2 hours) How to…

Read More
Franck Pachot

An alternative to DBA_EXTENTS optimized for LMT

By | Database management, Oracle | No Comments

By Franck Pachot . This is a script I have for several years, when tablespaces became locally managed. When we want to know to which segment a block (identified by file id, block id) belongs to, the DBA_EXTENTS view can be very long when you have lot of datafiles and lot of segments. This view using the underlying X$ tables and constrained by hints is faster when queried for one FILE_ID/BLOCK_ID. I did that in…

Read More
Franck Pachot

Oracle memory advisors: how relevant ?

By | Database management, Oracle | 2 Comments

By Franck Pachot . Do you look at memory advisors? I usually don’t but I still show them during the performance tuning workshop because there is no easy way to get an idea about memory sizing. This morning, while giving the training, I’ve shown on our workshop environment what the problem is: they are based on statistics cumulated from instance startup, which can cover months of heterogeneous activity, and then the result is probably meaningless….

Read More
Franck Pachot

How to disable a SQL Plan Directive permanently

By | Database management, Oracle | 4 Comments

By Franck Pachot . In 12c you will see a lot of SQL Plan Directives. Some are useful to get better execution plans, but some will trigger too much Adaptive Dynamic Sampling and that can become a big overhead, especially in Standard Edition. Then you don’t want to drop them – or they will reappear. You can disable them, but what will happen after the retention weeks? Let’s test it. Disabled directive A directive has…

Read More
Franck Pachot

Convert a snapshot standby to a new database

By | Database management, Oracle | One Comment

By Franck Pachot . A snapshot standby database is a nice solution when you want a temporary copy of the primary where you can do whatever you want (test a bug fix for example) and then convert it back to physical standby. But you don’t want to stay in that state definitely because you will accumulate archived logs from the changes done to the primary, and flashback logs for the changes made to the snapshot…

Read More