In an earlier article on investigating indexes I described some methods for looking at the internal structure of an index to get an idea of its current state of health. Although this type of knowledge helps you to make informed decisions about which indexes might need special treatment (like being permanently dropped, perhaps) it doesn’t immediately answer questions like: “what’s… Read more
In Oracle Database 12c, you can set a column in table as invisible either during CREATE TABLE or modifying existing table via ALTER TABLE command. By default, table columns are always visible. When you make it invisible, the COL# column in COL$ dictionary is updated to 0, and so is not included in the “SELECT *” or “INSERT INTO VALUES”… Read more
In the first article of this series, we learned what query parsing is and what impact it has on query performance. In this part of the series, we shall continue the discussion and look at Soft Parsing of the query. Query Processing Workflow Revisited As we saw in the previous installment, a query has to undergo Hard Parsing at least… Read more
Recently I wanted to create a policy managed database in my 12.1.0.2 flex cluster setup, but free servers are not available. Hence I had to unregister an administrator managed RAC database (amdbh12) to make the servers available. Having completed my testing, I dropped the policy managed database and then wanted to drop the database amdbh12 using DBCA… Read more
In this fifth article in our series on JSON and the Oracle database we’ll be pulling the curtains back on the magic tricks that can convert your boring old relational data to JSON. OK, maybe that’s overselling it a little. Obviously there’s no magic involved; indeed some may say it’s anything but. But before we get to all of that,… Read more
Indexes are expensive to maintain so we want to get the greatest benefit we can from them and make sure that Oracle uses them whenever it’s appropriate. Sometimes, though, the optimizer seems to ignore indexes that we think would be very effective and when this happens it’s often because the statistics that Oracle has gathered do not reflect the quality… Read more
There’s a possibility you’ve parachuted into this series on JSON and Oracle at this point. If you are already familiar with what JSON is and how it is created, and are only interested in discovering how to wrestle JSON data from an Oracle database, then you’re welcome, grab a seat. However, if you see the words JSON and Oracle, and… Read more
Introduction Oracle Database 10g Release 1 introduced Automatic Storage Management (ASM), a new framework for managing Oracle database files. ASM provides a foundation for highly efficient storage management with direct I/O, redundancy, striping and load balancing. Files stored on ASM are evenly distributed across disks in a disk group and mirrored according to the policies defined in the disk grou… Read more
Rebuilding indexes is an activity that you shouldn’t need to do often. There are always a few special cases – like when you’ve moved or recreated a table or if you’ve done a massive delete on a table – when it’s probably a reasonable idea but, in general, there are very few cases where there’s any great benefit to b… Read more
Cloning of a Pluggable Database (PDB) in Oracle Multitenant is a great feature and is very useful. Oracle Database 12c Release 1 has introduced various enhancements to the cloning of Pluggable Databases to overcome the limitations imposed by the initial release of Oracle Database 12c (12.1.0.1). In Oracle 12.1.0.2c: PDBs can be hot cloned, i.e. you don’t need to put the source PDB… Read more
Welcome to part three in this series of articles about Oracle and its uneasy romance with JSON. Uneasy? Well, that’s a bit of a spoiler; so far it’s been smooth sailing. Part 1 was a precis of JSON, while Part 2 delved into the reasons for Oracle’s involvement in the area. So far so good; but now we’ve got to… Read more
In the initial release of Oracle 12c (12.1.0.1c), a non-Container Database (non-CDB) could be converted to a pluggable database (PDB) using any of the following methods: Using DBMS_PDB Using datapump (expdb, impdp) Using GoldenGate replication The 12.1.0.2 patchset has introduced the ability to clone a remote non-CDB as a PDB directly over the network. Remote Cloning of a Non-CDB is a… Read more
The Oracle database has many features that are not spoken of frequently and a recent note by Connor McDonald reminded me that there are a couple of notes about optimising Text indexes that have been on my “must write” list for several years. If you’re not familiar with what text indexes can do for you, here’s a thumbnail sketch: creat… Read more
Introduction When it comes to tuning a badly-performing query, there are many things that need to be checked. There may be poor query design causing the query to run slowly. There could be an issue with the underlying hardware such as CPU or IO which is bringing the performance of the query down. There could be stale statistics or missing… Read more
In the previous installment of this series I produced some figures highlighting the main differences between doing a large delete by tablescan and doing a large delete by index range scan. Depending on the data patterns involved the correct choice of strategy could make a significant difference to the number of random I/Os, the volume of redo generated, and t… Read more
In this short series of articles, we’ll be talking about JSON and its integration with the Oracle database. The first part was an introduction to JSON itself, and was, admittedly, very light on Oracle. But now it’s probably time to bring Oracle more into the story and talk about how the database has adapted to handle this new format. Or, actually,… Read more
Hi, meet my friend JSON. Chances are you’ve heard of it, perhaps you have a vague idea of what it’s for – something to do with data-interchange – perhaps you even suspect that the J stands for Javascript… or is that Java? The rise in JSON’s popularity has been pretty meteoric, from its early days back in the year 2001… Read more
Offline backups (also known as Cold or consistent backups) are taken when the database is in a consistent state, i.e. the database has been shut down with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands. As part of a consistent shutdown, redo has been applied to the data files and the System Change number (SCN) in the data f… Read more
As data in tables is updated and / or deleted, pockets of empty space can be created, leading to fragmented free space. This free space is wasted space as it is insufficient to hold new data. Moreover, it can impact performance of the database as a higher number of sparsely-populated blocks need to be visited. Oracle 11g introduced online segment… Read more
For many people working with Oracle database, SCN (System Change Number) is a topic that interests them greatly – but despite that it’s a fascinating topic, it’s confusing as well. In this article we will learn the nitty-gritty of SCN: what it is, where it is used, and how it works. It’s worth mentioning that this is not and cannot… Read more
