Category: Tips and Tricks

Feed: Striving for Optimal Performance. In the past, when I created a SQL patch, I always specified a small number of hints. Last week, for the first time, I created one with more than 100 of them. Given their number, I didn’t want to specify them manually. Instead, my goal was to create a SQL patch that contained the outline associated with a cursor stored in shared pool. For that reason, I executed the following PL/SQL block: DECLARE l_sql_id VARCHAR2(13) := '2q7d290pp5vmp'; l_name dba_sql_patches.name%TYPE := 'TEST'; l_hints CLOB; BEGIN dbms_lob.createtemporary(lob_loc => l_hints, cache => TRUE); FOR i IN (SELECT trim(extractValue(value(h),'.')) ... Read More

Feed: Striving for Optimal Performance. In the last weeks, I was running a number of tests based on the TPC-DS benchmark against Snowflake. One of the first thing I did is of course to create the TPC-DS schema and populate it. The aim of this blog post is to share some observations related to the population step. The data I loaded was the same I used for this blog post (i.e. it is a 1 TB TPC-DS schema). The only difference was that I had to split the input files. This was necessary because Snowflake cannot parallelize the load with ... Read More

Feed: Kamran Agayev's Oracle Blog. Few months ago, while performing storage migration I faced an interesting issue which could lead to potential downtime if I didn’t notice a hidden warning in the log file. The plan was to create a new ASM diskgroup in a normal redundancy with 2 disks from different storages and test the disk crash and confirm that there will be no data loss if one of the storages fail. After creating a diskgroup, creating a test tablespaces on it and corrupting the header of one disks, everything was ok and we decided to drop the diskgroup ... Read More

Feed: Striving for Optimal Performance. By default, the database engine automatically takes snapshots in the root container only. Such snapshots cover the root container as well as all open PDBs belonging to it. From version 12.2 onward, you can control whether the database engine automatically takes also PDB-level snapshots through the dynamic initialization parameter AWR_PDB_AUTOFLUSH_ENABLED. In case you want to enable that feature, you have to carry out two operations: Set the initialization parameter AWR_PDB_AUTOFLUSH_ENABLED to TRUE (the default value is FALSE) either in a specific PDB or, if you want to enable it for all PDBs, in the root ... Read More

Feed: Striving for Optimal Performance. From version 12.1.0.2 onward, for taking AWR snapshots, you have the choice between four AWR flush levels: BESTFIT, LITE, TYPICAL and ALL. If you check the Oracle Database documentation, you won’t find much information about the difference between them. The best you will find, in the PL/SQL Packages and Types Reference, is the following: The flush level can be one of the following: BESTFIT: Uses the default value depending on the type of snapshot being taken. LITE: Lightweight snapshot. Only the most important statistics are collected. This is default for a pluggable database (PDB) and ... Read More

Feed: Kamran Agayev's Oracle Blog. 24 hours passed – still at work. Struggling to start up the database which was corrupted during cell storage rolling restart procedure. And I’ve never seen some Oracle error messages that I saw today. So here what is happened: ################Exadata storage cell failure during so-called “rolling cell storage restart”. Data file headers are corrupted for some files just because of rolling restart of storage cells and it can’t read the mirror file in the normal redundancy diskgroup as well!!! Both are corrupted! SR created – but there’s no reply!################ Read of datafile '+###1/###i/datafile/###_6077.1015929889' (fno 1367) ... Read More

Feed: Striving for Optimal Performance. In this short post I would like to point out a non-obvious issue that one of my customers recently hit. On the one hand, it’s a typical case where the query optimizer generates a different (suboptimal) execution plan even though nothing relevant (of course, at first sight only) was changed. On the other hand, in this case after some time the query optimizer automatically gets back to the original (optimal) execution plan. Let’s have a look at the issue with the help of a test case… The test case is based on a range partitioned ... Read More

Feed: Kamran Agayev's Oracle Blog. Today, while creating an empty database in Exadata machine where there was enough free space and memory, we got the following error: SYS@TEST> startup nomount ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28 ORA-27301: OS failure message: No space left on device ORA-27302: failure occurred at: sskgpbitsper The problem wasn’t related with the space at all, even from the error message we see “No space left on device”. From the error output, I realized “OS system dependent operation:semget“, where “sem” means “semaphore“. Having enough free memory and space, the process ... Read More

Feed: Kamran Agayev's Oracle Blog. Python is getting more popular nowadays, because it is reliable and efficient, it has great corporate sponsors, and because of it’s amazing libraries that helps you to save time during the initial development cycle. It’s much more easy to connect to an Oracle Database from Python by using cx_Oracle module. To get more information about cx_Oracle module, check the following links: https://oracle.github.io/python-cx_Oracle/  https://cx-oracle.readthedocs.io/en/latest/installation.html In this blog post, I will show how to install Python and configure the environment and connect to the database. First of all, make sure you’ve an internet connection and install Python ... Read More

Feed: Striving for Optimal Performance. The contents of the V$SQL_CS_HISTOGRAM view is used by the SQL engine to decide when a cursor is made bind aware, and therefore, when it should use adaptive cursor sharing. For each child cursor, the view shows three buckets. It is of general knowledge that the first one (BUCKET_ID equal 0) is associated with the executions that process up to and including 1,000 rows, the second one (BUCKET_ID equal 1) with the executions that processes between 1,001 and 1,000,000 rows, and the third one (BUCKET_ID equal 2) with the executions that processes more than 1,000,000 ... Read More