Oracle Database as our consultants live it every day: installation and patching, upgrades, RMAN backup and recovery, Data Guard, RAC, performance tuning, multitenant, licensing, and engineered systems such as ODA and Exadata.
By Franck Pachot . In a previous post I explained how to measure the number of processes that are generated when a fork() or clone() call checks the nproc limit. There is another limit in /etc/limits.conf – or in /etc/limits.d – that is displayed by ‘ulimit -n’. It’s the number of open files – ‘nofile’ – and here again we…
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…
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,…
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…
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…
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…
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…
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…
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…
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…
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…
By Franck Pachot . While answering to a question on Oracle forum about NOLOGGING consequences, I provided a test case that deserves a bit more explanation. Nologging operations are good to generate minimal redo on bulk operations (direct-path inserts, index creation/rebuild). But in case we have to restore a backup that was made before the nologging operation, we loose data.…
