Infrastructure at your Service

Oracle Archives - Page 3 of 35 - Blog dbi services

Franck Pachot

(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=YES)(LOAD_BALANCE=NO)

By | Oracle | No Comments

Do you have complex connection strings with DESCRIPTION_LIST, DESCRIPTION, ADDRESS_LIST, ADDRESS and a nice combination of FAILOVER and LOAD_BALANCE? You probably checked the documentation, telling you that FAILOVER=YES is the default at all levels, but LOAD_BALANCE=YES is the default only for DESCRIPTION_LIST. But when disaster recovery and availability is concerned, the documentation is not sufficient. I want to test it. And here is how I do it.  

 
Read More
Franck Pachot

Full page logging in Postgres and Oracle

By | Oracle, Postgres | 4 Comments

In my opinion, the volume of logging (aka redo log, aka xlog, aka WAL) is the most important factor for OLTP performance, availability and scalability, for several reasons: This is the only structure where disk latency is a mandatory component of response time This is a big part of the total volume of backups This is sequential by nature, and very difficult to scale by parallelizing In this post, I look at the volume of…

 
Read More
Franck Pachot

Server process name in Postgres and Oracle

By | Oracle, Postgres | No Comments

Every database analysis should start with system load analysis. If the host is in CPU starvation, then looking at other statistics can be pointless. With ‘top’ on Linux, or equivalent such as process explorer on Windows, you see the process (and threads). If the name of the process is meaningful, you already have a clue about the active sessions. Postgres goes further by showing the operation (which SQL command), the state (running or waiting), and…

 
Read More
Franck Pachot

JAN18: Database 11gR2 PSU, 12cR1 ProactiveBP, 12cR2 RU

By | Oracle | No Comments

If you want to apply the latest patches (and you should), you can go to the My Oracle Support Recommended Patch Advisor. But sometimes it is not up-todate. For example, for 12.1.0.2 only the PSU is displayed and not the Proactive Bundle Patch, which is highly recommended. And across releases, the names have changed and can be misleading: PSU for 11.2.0.4 (no Proactive Bundle Patch except for Engineered Systems). 12.1.0.2 can have SPU, PSU, or…

 
Read More
Franck Pachot

Result Cache: when *not* to use it

By | Oracle | One Comment

I encountered recently a case where result cache was incorrectly used, leading to high contention when the application encountered a peak of load. It was not a surprise when I’ve seen that the function was called with an ‘ID’ as argument, which may have thousands of values in this system. I mentioned to the software vendor that the result cache must be used only for frequently calling the function with same arguments, not for random…

 
Read More
Franck Pachot

Testing Oracle SQL online

By | Oracle | One Comment

Want to test some DDL, a query, check an execution plan? You need only a browser. And you can copy-paste, or simply link, your test-case in a forum, a tweet, an e-mail, a tweet. Here is a small list (expecting to grow from your comments) of free online services which can run with an Oracle Database: SQL Fiddle, Rextester, db<>fiddle and Oracle Live SQL  

 
Read More
Franck Pachot

Explain Plan format

By | Oracle | No Comments

The DBMS_XPLAN format accepts a lot of options, which are not all documented. Here is a small recap of available information. The minimum that is displayed is the Plan Line Id, the Operation, and the Object Name. You can add columns and/or sections with options, such as ‘rows’, optionally starting with a ‘+’ like ‘+rows’. Some options group several additional information, such ‘typical’, which is also the default, or ‘basic’, ‘all’, ‘advanced’. You can choose…

 
Read More
Franck Pachot

Spectre/Meltdown on Oracle Public Cloud UEK – PIO

By | Oracle | 2 Comments

The Spectre and Meltdown is now in the latest Oracle UEK kernel, after updating it with ‘yum update': [opc@PTI ~]$ rpm -q –changelog kernel-uek | awk ‘/CVE-2017-5715|CVE-2017-5753|CVE-2017-5754/{print $NF}’ | sort | uniq -c 43 {CVE-2017-5715} 16 {CVE-2017-5753} 71 {CVE-2017-5754} As I did on the previous post on AWS, I’ve run quick tests on the Oracle Public Cloud.  

 
Read More