Infrastructure at your Service

Category

Oracle

Oracle Team

Linux: how to monitor the nproc limit

By | Database Administration & Monitoring, Oracle | 16 Comments

By Franck Pachot . 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…

Read More
Oracle Team

How to list all Oracle system schemas

By | Database management, Oracle | 4 Comments

By Franck Pachot . Do you want to know which users come with the Oracle Database and which are the ones you have created? This is that not easy, especially prior to Oracle 12c. You know SYS and SYSTEM, but there are many others – especially when you have installed all options. And it is important to know them all, e. g. when you import or apply a password policy, etc. Let’s see the options…

Read More
Oracle Team

Oracle Exadata – poor optimization for FIRST_ROWS

By | Database Administration & Monitoring, Oracle | 3 Comments

By Franck Pachot . 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…

Read More
Oracle Team

Oracle 12c extended datatypes better than CLOB?

By | Database management, Oracle | 8 Comments

By Franck Pachot . 12c has introduced character strings that can can go above 4000 bytes. In the previous versions, in PL/SQL only we were allowed to have VARCHAR2 up to 32k. In SQL the VARCHAR2 datatype was limited to 4000 and CHAR was limited to 2000. That became a bit small especially when lot of applications needed to store unicode characters. From 12c we can have SQL datatypes having up to 32k bytes for…

Read More
Oracle Team

Oracle SQL Monitoring reports in flash, html, text

By | Database management, Oracle | One Comment

By Franck Pachot . I have recently posted on the way I like to extract execution plans. When we have Tuning pack, I suggest to get them with SQL Real-Time Monitoring as an active report. However, Martin Preiss said in a comment that he prefers the text format – easier to search, copy, and paste. And that’s a very good remark. We still need plain text. My point is that if you send me the…

Read More
Oracle Team

Exploring Oracle SE & EE performance statistics with Orachrome Lighty

By | Database management, Oracle | No Comments

By Franck Pachot . At dbi services, we really like Lighty for Oracle, a tool which helps exploring performance statistics whether they originate from AWR (available only in Enterprise Edition with Diagnostic Pack option) or from Statspack (available via Standard Edition). As a matter of fact, we like it so much that we have became Orachrome Partner. If you want to try it out for yourselves, there is a free trial here. If you have…

Read More
Oracle Team

ROWNUM vs ROW_NUMBER() and 12c fetch first

By | Database management, Oracle | 4 Comments

By Franck Pachot . Prior to Oracle 12c, there were two ways to do ‘top-n’ queries: use rownum after sorting rows with “order by” use row_number() over (order by) Top-n queries are usually required for result pagination. The application shows the first 10 rows, then the next 10 on the following page, etc. In these queries, user interactions are often stateless, so we cannot just open a cursor and fetch 10 rows on each user…

Read More
Oracle Team

Oracle 12c CDB – metadata & object links internals

By | Database management, Oracle | 23 Comments

By Franck Pachot . Warning: this is only geek stuff about internals on multitenant database dictionary, metadata, and object links. It has nothing to do with the operations that you can do on your database. Don’t try that in production or you can corrupt the whole dictionary. In 12 multitenant database (aka CDB) we know that each pluggable database is isolated in order to act as a standalone database. But they share some common resources…

Read More
Oracle Team

Oracle 12c Adaptive Plan & inflection point

By | Database management, Oracle | One Comment

By Franck Pachot . The Oracle 12c Adaptive Plan feature was already presented by Nicolas Jardot in OOW 2013: Solving customer issues with the 12c Optimizer. I recently had to answer several questions about its behavior at execution time. Maybe the term ‘adaptive’ is misleading. It’s not that a join will stop and restart to another join method. Even with adaptive plan there will only be one join method to be applied. The feature only…

Read More
Oracle Team

Best practice for the sending of an Oracle execution plan

By | Database Administration & Monitoring, Oracle | 5 Comments

By Franck Pachot . 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…

Read More