Infrastructure at your Service
By Franck Pachot . Your datafiles have grown in the past but now you want to reclaim as much space as possible, because you are short on filesystem space, or you want to move some files without moving empty blocks, or your backup size is too large. ALTER DATABASE DATAFILE … RESIZE can reclaim the space at the end of the datafile, down to the latest allocated extent. But if you try to get lower,…
By Franck Pachot . When you load data in direct-path and have the NOLOGGING attribute set, you minimize redo generation, but you take the risk, in case of media recovery, to loose the data in the blocks that you’ve loaded. So you probably run a backup as soon as the load is done. But what happens if you have a crash, with media failure, before the backup is finish? I encountered recently the situation but…
By Franck Pachot . Recently, I described the Partial Join Evaluation transformation that appeared last year in Oracle 12c. I did it as an introduction for another transformation that appeared long time ago in 10.1.0.3 but was not used by default. And even in the latest Oracle 12c patchset 1 (aka 12.1.0.2.0) it is still not enabled. But it’s there and you can use it if you set optimizer_features_enabled to 12.1.0.2.1 (that’s not a typo!)….
By Franck Pachot . 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…
By Franck Pachot . Do you remember last year, when 12c arrived with multitenant, David Hueber warned us about the fact that a single PDB can, under certain conditions, generate a complete system downtime? We are beta testers and opened a SR for that. Now one year later the first patchset is out and obviously I checked if the issue was fixed. It’s a patchset afterall, which is expected to fix issues before than bringing…
By Franck Pachot . In a previous post, I have described Adaptive Plans. Even if I prefer to show plans with the SQL Monitor active html format, I had to stick with the dbms_xplan for that because SQL Monitoring did not show all information about adaptive plans. This has been fixed in the Patchset 1 and I have run the same query to show the new feature. First, an adaptive plan can be in two…
By Franck Pachot . It’s just a patchset. The delivery that is there to stabilize a release with all the bug fixes. But it comes with a lot of new features as well. And not only the one that has been advertised as the future of the database. It’s a huge release. Let’s have a look at what’s new. First, it seems that it will be the only patchest for 12.1 Then, there is that…
By Franck Pachot . In a previous post I used X$KCCAGF to get more information about reclaimable archived logs in FRA, because there is a bug in standby (not opened) databases where archivelog deletion policy is ignored. I explained that the view V$RECOVERY_AREA_USAGE has only aggregated information about space reclaimable without the details about which files are reclaimable or not. Here I’ll explain how I came to X$KCCAGF and I’ll give the query to get…
By Franck Pachot . In a previous post about nproc limit, I wrote that I had to investigate the nproc limit with the number of threads because my Oracle 12c EM agent was having thousands of threads. This post is a short feedback about this issue and the way I have found the root cause. It concerns the enterprise manager agent 12c on Grid Infrasctructure >= 11.2.0.2 NLWP The issue was: ps -o nlwp,pid,lwp,args -u…
By Franck Pachot . Do you think that it’s better to write semi-join SQL statements with IN(), EXISTS(), or to do a JOIN? Usually, the optimizer will evaluate the cost and do the transformation for you. And in this area, one more transformation has been introduced in 12c which is the Partial Join Evaluation (PJE). First, let’s have a look at the 11g behaviour. For that example, I use the SCOTT schema, but I hire…
