Infrastructure at your Service

Category Archives: Database Administration & Monitoring

Nathan Courtine

Get the main information from Windows Server with PowerShell

By | Database Administration & Monitoring | No Comments

This blog will present you a way to retrieve Windows Server main information via PowerShell. The goal is to be able to automate the extraction of information for different purposes: audit, report generation, dashboards…   Disclaimer: I am not a developer but a SQL Server dba. If you find errors or some ways of improvement, I will be glad to read your comments!     Computer System information First, I initialize the WMI object containing…

 
Read More
Franck Pachot

Flashback logging overhead: ‘db file sequential read’ on UNDO tablespace

By | Database Administration & Monitoring, Oracle | No Comments

in my previous post I’ve reproduced an issue where some bulk insert into a GTT was generating too much undo. But the real case I encountered was worse: the insert was reading lot of UNDO. And lot of this undo was read from disk. Jonathan Lewis has pointed me to a possible explanation he has given (here) I’ve reproduced the testcase from the previous post after setting the database to do flashback logging. Here are…

 
Read More
Franck Pachot

Oracle: an unexpected lock behavior with rollback

By | Database Administration & Monitoring, Oracle | No Comments

Here is an odd Oracle behavior I observed when a transaction that acquired a lock is rolled back. Note that this is related to a specific combination of locks that should not be encountered in production. So it’s not a bug. Just something unexpected.   First session In my first session I lock the DEPT table in share mode (RS) 20:56:56 SQL1> lock table dept in row share mode; Table(s) Locked. My first session (SID=53)…

 
Read More
Franck Pachot

Oracle 12.1.0.2: Wait event histograms in μs

By | Database Administration & Monitoring, Oracle | 2 Comments

When an Oracle Database spends a high percentage of its DB time in User I/O, I usually check the wait event histograms in order to see if the storage system is working well. But today, with storage going to SSD, most I/O are less than 1 milliseconds and we have no details about those wait times. Here is what is exposed by V$WAIT_EVENT_HISTOGRAM:   select event,wait_time_milli,wait_count from v$event_histogram where event like ‘db file sequential read’…

 
Read More
Franck Pachot

Linux: how to monitor the nproc limit

By | Database Administration & Monitoring, Oracle | 14 Comments

You probably know about ‘nproc’ limits in Linux which are set in /etc/limits.conf and checked with ‘ulimit -u’. But do you know how to handle the monitoring and be alerted when you’re close the fixed limit? Nproc and ps Nproc is defined at OS level to limit the number of processes per user. Oracle 11.2.0.4 documentation recommends the following: oracle soft nproc 2047 oracle hard nproc 16384 But that is often too low, especially when…

 
Read More
Franck Pachot

Oracle Exadata – poor optimization for FIRST_ROWS

By | Database Administration & Monitoring, Oracle | 3 Comments

In a previous blog, I discussed the difference between rownum and row_number(), in particular their behaviour in implicitely adding a first_rows(n) to the optimizer. That reminded me that I forgot to blog about an issue I encountered and which concerns both approaches. It was on an Exadata: a nice full table scan with smartscan was taking a long time. And forcing to an index access – with a very bad index – was better. The…

 
Read More
Franck Pachot

Best practice for the sending of an Oracle execution plan

By | Database Administration & Monitoring, Oracle | 5 Comments

You have a query that takes too long and you want help to analyze the execution plan? Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer SQL Monitoring when we…

 
Read More
Franck Pachot

Archivelog deletion policy for Standby Database in Oracle Data Guard

By | Database Administration & Monitoring, Oracle | 41 Comments

Do you use ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY for your Oracle databases in Data Guard? Maybe you also use the Fast Recovery Area as archive log destination. That’s good practice! But did you ever check that it works as expected? What I mean is this: The archived logs that you don’t need are reclaimable by the FRA when space is needed And the archived logs that are required for availability (standby or…

 
Read More
Stéphane Haby

SQL Server 2012: First Hotfix for Service Pack 1 available

By | Database Administration & Monitoring | No Comments

This month, Microsoft has released the first hotfix for SQL Server Service Pack 1. Analysis This Hotfix concerns the installation of SQL Server components and Analysis Services. If you have installed the RTM version with Analysis Service and successfully installed Service Pack 1, you may experience problems in the next installation process for other component: The installation freezes and does not finish. Problem Windows Installer tries to repair the installation and then blocks the next…

 
Read More
Stéphane Haby

SQL Server 2012: Feature Pack

By | Database Administration & Monitoring | 2 Comments

Since the third CTP version of SQL Server 2012 codename Denali, you have some features available to provide an additional value for Microsoft® SQL Server 2012 like the other SQL Server versions. This Feature Pack or Manageability Tool Kit are available but are not the final version. I have compiled all information in this blog to find it easily.

All DBA should know that Feature Pack exists, how to find it and of course use it.Wink

 
Read More