By Franck Pachot . You have read all Cary Millsap work about Oracle database performance tuning. You know that there are no silver bullets. Reducing the response time requires a methodical approach in order to analyze the response time with the goal of eliminating all unnecessary work. But I’ll show something completly opposite here. A performance tuning silver bullet. Do…
Last night, I had an interesting discussion with one of my MVP French friend that faces a weird situation where a query that uses a statement level RECOMPILE hint produces an execution plan that disappointed him. He told me we can simulate the same situation by using the AdventureWorks database and Sales.SalesOrderHeader table. First, we have to add a nonclustered…
Which DBA has not yet face a performance problem issued by several blocked processes? In reality, I’m sure a very little number of them. Troubleshooting a blocked issue scenario is not always easy and may require to use some useful tools to simplify this hard task. A couple of months ago, I had to deal this scenario at one of…
By Franck Pachot . In the previous post I explained the performance issue encountered when using a generic query to deal with optional search criteria on multiple columns. The statement was shared by all executions, was marked as bind sensitive, but never became bind aware. Let’s use the BIND_AWARE hint. All binds null I assign null for all of them…
By Franck Pachot . For Exadata, oracle has introduced an ‘EXADATA’ mode which sets a high transfer rate (with IOTFRSPEED as in NOWORKLOAD statistics) and set a MBRC (as in WORKLOAD statistics). Those values are set rather than gathered because all the SmartScan optimization done at storage cell level, which makes the multiblock reads less expensive, is difficult to measure…
By Franck Pachot . The IOUG – Independant Oracle User Group – has a great event each year: the COLLABORATE. This year it’s in April 12-16, 2015 at The Mandalay Bay Resort & Casino in Las Vegas. I’ll be a speaker and a RAC Attack Ninja as well. IOUG COLLABORATE provides all the real-world technical training you need – not…
By Franck Pachot . If you want a statement to be hard parsed on its next execution, you can flush the shared pool, but you don’t want all the cursors to be hard parsed. Here is how to flush only one statement, illustrated with the case where it can be useful. During the performance training, here is how I introduce…
By Franck Pachot . This month we started to give our Oracle Tuning Workshop. And with a new workshop comes new questions. We advise to give to the optimizer the most accurate statsistics that we can have. That suggests that WORKLOAD statistics are better than NOWORKLOAD ones because they gather the average number of blocks read in multiblock read, rather…
Recently I had the opportunity to audit a SQL Server database hosted on a Hyper-V 2012 cluster. I noticed that the guest operating system had the Power Plan configured to High performance. This is great thing but when I talked to the system administrator to verify if the same option is turned on on the Hyper-V operating system, this was unfortunately…
This blog aims to make a performance comparison between the different MySQL versions/editions and also comparing the differents MySQL forks such as Percona Server and MariaDB. Indeed number of improvements as been done to innodb storage engine in the last MySQL versions. You can find below some of the performance improvements applied to InnoDB these last years (non exhaustive list):…
I have recently come across an issue with one of my customer that told me that their SQL Server 2008 R2 instance had a memory leak. These are strong words! The server has 64 GB of RAM and the concerned SQL Server instance is limited to 54 GB according to the max server memory (GB) server option. However, he noticed…
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…
