Category: Tips and Tricks

Feed: Kamran Agayev's Oracle Blog. Posted by Kamran Agayev A. on May 10th, 2018 If you are a production DBA of mission critical system, then you might have already seen the following critical, I would say mortal messages in your alert.log file. When your database was up and running, you shutdown it and open and it fails to MOUNT the database and abort The database was hanged with millions of online transactions, and aborted. You start the instance, switch to the MOUNT mode, do some maintenance tasks and try to open the database and …. wait …. wait …. wait ... Read More

Feed: SQL Server Customer Advisory Team. Author: Dimitri Furman. Reviewed By: Ajay Kalhan, Borko Novakovic, Drazen Sumic, Branislav Uzelac In the current Azure SQL Database Managed Instance (MI) preview, when customers create a new instance, they can allocate a certain number of CPU vCores and a certain amount of disk storage space for the instance. However, there is no explicit configuration option for the amount of memory allocated to the instance, because on MI, memory allocation is proportional to the number of vCores used. How can a customer determine the actual amount of memory their MI instance can use, in ... Read More

Feed: SQL Server Customer Advisory Team. Author: Dimitri Furman. Reviewed by: Kun Cheng, Borko Novakovic, Arvind Shyamsundar, Mike Weiner Azure SQL Database Managed Instance is a new offering that provides an instance-based SQL PaaS service in Azure. If you are not yet familiar with this new Azure SQL Database capability, you can start with the What is Managed Instance documentation topic. Since early private preview of Managed Instance (MI), SQLCAT has been working with early adopter customers to help them evaluate MI as a new platform for their applications, gather their feedback, and improve the offering for everyone. Azure SQL ... Read More

Feed: Kamran Agayev's Oracle Blog. In this post I will talk about my journey on how to prepare and pass the 12c RAC and Grid Administration exam. About the exam Check the following link to get more information about the exam from Oracle University page: https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-068 The exam consists of 3 parts: – Oracle 12c ASM Administration– Oracle 12c Grid Infrastructure Installation and Administration– Oracle 12c RAC Administration I don’t want to scare you, but the exam is hard enough. The bad thing is – you fail the entire exam if you fail one of the sections. This means that ... Read More

Feed: Striving for Optimal Performance. If you run TKPROF without arguments, you get a complete list of its arguments with a short description for each of them (here the output generated by version 18.1.0): $ tkprof Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] table=schema.tablename Use 'schema.tablename' with 'explain=' option. explain=user/password Connect to ORACLE and issue EXPLAIN PLAN. print=integer List only the first 'integer' SQL statements. pdbtrace=user/password Connect to ORACLE to retrieve SQL trace records. aggregate=yes|no insert=filename List SQL statements and data inside INSERT statements. sys=no TKPROF does not list SQL statements ... Read More

Feed: Striving for Optimal Performance. The aim of this post is not to explain how the APPROX_MEDIAN function works (you find basic information in the documentation) but to show you the results of a test case I ran to assess how well it works. Here’s what I did… I started in the Oracle Database Public Cloud an instance of version 12.2. Then I created a table with several numerical columns (the name of each column shows how many distinct values it contains), loaded 150 million rows into it (the size of the segment is 20 GB), and gathered the object ... Read More

Feed: SQL Server Customer Advisory Team. Author: Dimitri Furman. Reviewed by: Rajesh Setlem, Mike Weiner, Xiaochen Wu With thanks to Joey D’Antoni (blog) for asking a great question that prompted this article. As any experienced DBA knows, supporting a very large database (VLDB) tends to be exponentially more complex than supporting smaller databases. The definition of VLDB has been shifting in recent years, but most DBAs would agree that complexities become significant starting with database size in the 3-5 TB range. A few obvious examples of tasks that are more challenging with a VLDB are backup/restore, consistency checking, HA/DR, partitioning, ... Read More

Feed: Striving for Optimal Performance. Today I faced a performance problem caused by a bad cardinality estimation involving a CDB view in a 12.1.0.2 multitenant environment. While solving the problem I did a number of observations that I try to summarize in this blog post. First of all, when checking the execution plan of a query already running for more than two hours, I noticed that, in the execution plan, neither the referenced CDB view nor one of its underlying objects were referenced. The following query (and its execution plan) executed while connect to the CDB illustrates (I also added ... Read More

Feed: Striving for Optimal Performance. At the beginning of December, at the UKOUG Tech17 conference in Birmingham (GB), I presented a comparison of the query optimizers of MySQL 8.0.3 and PostgreSQL 10.1. One of the things I talked about is their ability to handle subqueries. I summarized my findings with the following sentence: Simple sub-queries that are not correctly optimized were observed. It goes without saying that such a sentence leaves a lot of questions open. After all, it is just a summary. The aim of this post is to show you which subqueries I tested, and to compare my ... Read More

Feed: Striving for Optimal Performance. This is a short post to point out a problem I recently discovered and for which I couldn’t find information online. Hence, in case you hit it, thanks to this short post I hope that you’ll save some time. The documentation describes a procedure you can use to get rid of dangling triples stored in a semantic network. Simply put, you must execute a PL/SQL block like the following: BEGIN sem_apis.purge_unused_values(flags => 'PARALLEL'); END; Since one of my customers was/is concerned by the number of dangling triples that are stored in its databases, in October ... Read More