Search

Top 60 Oracle Blogs

Recent comments

Exadata

Direct path and buffered reads again.

Since the direct path feature for serial processes was discovered after it became available in Oracle 11.2.0.1 (as far as I know, I haven’t checked Oracle 11.1), there have been a lot of blog posts on when this happens. A lot of these do not specify the Oracle version, which is a failure in my opinion. There are different decisions made in different versions.

The purpose of this blogpost is to show the results of my tests on when the Oracle database engine switches from buffered to direct path reads and vice versa. There probably are decisions made by the database engine for this feature based on internal kept statistics, like general activity and object usage, which means my tests might be different in your database. For that purpose I included an anonymous PL/SQL block in this post so you can replay the same test in your own database, except for the table, which you have to create yourself.

OOW 2015: my presentation

I don’t have an official OOW presentation in the conference this year. However, I am presenting a session at the Oak Table World 2015 event behind held concurrently with OOW 2015. My topic is “Exadata Database Machine Security” and I plan to review some of the newest updates to security for the Exadata Database Machine engineered system.

As the website indicates, the event is completely free and there is no pre-registration or enrollment required–just show up and come on in to hear some great speakers present on great topics. Hope to see you there on Monday, October 26, 2015!

getSQLMON… mining SQLMON reports from Oracle

As an Oracle performance engineer, I often work with customers on benchmarks and diagnosis of various issues.  Now, everyone can gather an AWR report and send it my way, but that is just the tip of the iceberg.  AWR reports are great for summarizing activity at a high level and can even find a SQL that is taking longer than expected.  So, once we have identified a poor performing SQL, how do we fix it?

…that is where SQLMON comes into the picture.

gathering sqlmon reports

The power of the SQLMON report is very well documented.  It provides an insight into EXACTLY what was happening when that query was running on the system.  It documents the run time, SQL plan, CPU activity, IO activity, Offload, etc…  The report can be gathered by running the “dbms_sqltune.report_sql_monitor” utility to extract a report.  Each SQL in Oracle maps to a specific “SQL_ID” as this is really just a hash of the sql text….But, that is not all.

getSQLMON… mining SQLMON reports from Oracle

As an Oracle performance engineer, I often work with customers on benchmarks and diagnosis of various issues.  Now, everyone can gather an AWR report and send it my way, but that is just the tip of the iceberg.  AWR reports are great for summarizing activity at a high level and can even find a SQL that is taking longer than expected.  So, once we have identified a poor performing SQL, how do we fix it?

...that is where SQLMON comes into the picture.

gathering sqlmon reports

The power of the SQLMON report is very well documented.  It provides an insight into EXACTLY what was happening when that query was running on the system.  It documents the run time, SQL plan, CPU activity, IO activity, Offload, etc...  The report can be gathered by running the "dbms_sqltune.report_sql_monitor" utility to extract a report.  Each SQL in Oracle maps to a specific "SQL_ID" as this is really just a hash of the sql text....But, that is not all.

Oracle 12 and latches

Oracle DBAs who are so old that they remember the days before Oracle 11.2 probably remember the tuning efforts for latches. I can still recall the latch number for cache buffers chains from the top of my head: number 98. In the older days this was another number, 157.

But it seems latches have become less of a problem in the modern days of Oracle 11.2 and higher. Still, when I generate heavy concurrency I can see some latch waits. (I am talking about you and SLOB mister Closson).

I decided to look into latches on Oracle 12.1.0.2 instance on Oracle Linux 7. This might also be a good time to go through how you think they work for yourself, it might be different than you think or have been taught.

How long does a logical IO take?

This is a question that I played with for a long time. There have been statements on logical IO performance (“Logical IO is x times faster than Physical IO”), but nobody could answer the question what the actual logical IO time is. Of course you can see part of it in the system and session statistics (v$sysstat/v$sesstat), statistic name “session logical reads”. However, if you divide the number of logical reads by the total time a query took, the logical IO time is too high, because then it assumed all the time the query took was spend on doing logical IO, which obviously is not the case, because there is time spend on parsing, maybe physical IO, etc. Also, when doing that, you calculate an average. Averages are known to hide actual behaviour.

JSON support in Exadata 12.1.2.1.0 and later

Some time ago Oracle announced that RDBMS 12.1.0.2 has built-in support for JSON processing. A little later it was also mentioned that you have support for JSON in the Exadata storage servers for offloading. This is probably a lot more exciting to users of JSON than it is to me as I’m not a developer. However, whenever an announcement such as the one I’m referring to is made I would like to see for myself how much of it is implemented in software. Like I said, I’m not a developer so apologies for a silly example: what I’m showing you here can probably done differently and is not the best use of an Exadata. But all I really wanted to test is if JSON support actually exists. I am using cellsrv 12.1.2.1.0 and RDBMS 12.1.0.2.2 for this test.

JSON

Investigating the full table direct path / buffered decision.

A lot of blogposts and other internet publications have been written on the full segment scan behaviour of a serial process starting from Oracle version 11gR2. This behaviour is the Oracle engine making a decision between scanning the blocks of a segment into the Oracle buffercache or scanning these blocks into the process’ private process global area (PGA). This decision is even more important on the Exadata platform, because the Oracle engine must have made the decision to read the blocks into the process’ PGA in order to be able to do a smartscan. This means that if you are on Oracle 11gR2 already, and thinking about using the Exadata platform, the wait event ‘direct path read’ gives you an indication on how much potentially could be offloaded on Exadata, if you keep all the settings the same.

IO Resource Manager for Pluggable Databases in Exadata

Another interesting topic that goes over and above the CDB Resource Manager Plans I described earlier this week is the implementation of IORM Plans for Pluggable Databases. Pluggable Databases are an interesting object for studies, and I like to research things. When 12c came out there was no support for offloading initially, it is required that you are on cell software 12.1.x.x.x for full 12c support on Exadata. One aspect I was particularly interested in were PDBs and IORM. Limiting CPU on a PDB basis is already a cool thing, but in a hugely consolidated environment it does make sense to limit I/O based on a consumer group. Another one of the perks of working on Exadata :)

Testing 12c CDB Resource Plans and a little bit about OEM Express

Inspired by Sue Lee’s presentation at Enkitec’s E4 conference I decided to re-run my test suite to work out how you can use Database and I/O Resource Manager in Oracle 12.1.0.2.2 to help consolidating databases in the new Multi-Tenant architecture. I should point out briefly that the use of Multi-Tenant as demonstrated in this article requires you to have a license. Tuning tools shown will also require you to be appropriately licensed…

Setup