09 January 2017
09 January 2017

Investigating Indexes – 2

0
0
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
12 December 2016
12 December 2016

Invisible Columns In Oracle Database 12C

0
3
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
08 December 2016
08 December 2016

Unpivot

0
0
One of the sessions at the recent annual UKOUG technical conference (Tech16) was a panel session on optimisation for which I had posted a request for a few advance questions, and one of the questions that appeared in my inbox was as follows: I have a report which is a union all of 27 versions of the statement: select ‘ColumnX_name’… Read more
30 November 2016
30 November 2016

Re-registering Databases In A Cluster With srvctl: Problems and Solutions

0
3
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
07 October 2016
07 October 2016

Explore Oracle Database In-Memory – Part 2

0
0
Oracle In-Memory is fully compatible with Oracle multitenant database architecture, allowing us to take advantage of fast In-Memory and low-cost storage. This multitenant architecture was also introduced in Oracle 12c, featuring multiple pluggable databases (PDBs) within a container database (CDB). This makes it easy to consolidate databases onto the cloud, deliver a high density of schema-based consolidation without making changes… … Read more
03 October 2016
03 October 2016

Hacking Indexes

0
0
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
29 September 2016
29 September 2016

Migrate Oracle Database To ASM Using ASMCMD

0
2
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
28 September 2016
28 September 2016

Investigating Indexes

0
0
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
21 September 2016
21 September 2016

Explore Oracle Database In-Memory – Part 1

0
0
The Oracle Database In-Memory option was introduced in Oracle 12c (12.1.0.2 patch set). By using the In-Memory option, businesses can benefit from better decisions made in real time, improved productivity, increased competitiveness and lowered costs. The Oracle In-Memory option is designed to fit both Data warehouse and mixed workload OLTP databases, and can be deployed with any existing application that%… Read more
20 September 2016
20 September 2016

Oracle Database 12.1.0.2c: Hot Cloning Of Pluggable Databases

1
6
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
05 September 2016
05 September 2016

Oracle Database 12.1.0.2c : Hot Cloning of Non-Container Databases

0
0
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
05 September 2016
05 September 2016

Text Indexes

0
0
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
05 September 2016
05 September 2016

Understanding SQL Query Parsing – Part 1

0
6
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
05 September 2016
05 September 2016

Massive Deletes – Part 4

0
2
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
02 September 2016
02 September 2016

JSON For Absolute Beginners: Part 2 – Why Use Oracle?

0
2
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
02 September 2016
02 September 2016

JSON For Absolute Beginners: Part 1- Introduction

1
8
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