Category: Tips and Tricks

Feed: Striving for Optimal Performance. The Autonomous Data Warehouse Cloud (ADWC) service provides a Service Console that can be used to monitor the service activity and to carry out a small number of administration tasks (e.g. changing some resource management rules). The documentation specifically says to use the ADMIN user to login. But, actually, any unlocked database user having the CREATE SESSION privilege can be used to login. This is not only useful, but, in my opinion, necessary. In fact, developers should be able to see the service activity without knowing the password of the ADMIN user. The documentation provides ... Read More

Feed: SQL Server Customer Advisory Team. Author: Arvind Shyamsundar. Team members from Microsoft AzureCAT (a.k.a. DataCAT / SQLCAT) will be presenting a full-day pre-conference on “Building A Scalable Data Architecture In Azure” at the upcoming DPS 2018 conference in Bengaluru, India. We are very excited to be part of the 4th edition of Asia’s largest Microsoft data-focused conference, with over 1000 attendees expected overall. The motivation for this full-day pre-conference training is to share with attendees typical scenarios and considerations in architecting and implementing solutions on Microsoft Azure. Whether you are migrating an existing on-premises data pipeline, or implementing a ... Read More

Feed: Striving for Optimal Performance. The Appendix A of the Using Oracle Autonomous Data Warehouse Cloud guide describes the DBMS_CLOUD package. Unfortunately, it documents only a subset of the subroutines. And, for some of them, the description could also be enhanced. Therefore, while I was testing all the subroutines the DBMS_CLOUD package provides, I took a number of notes. By the end of my tests, I got what I can call my personal reference guide to the package. Since it might help others, here it is… Introduction To interact with other cloud services, the Oracle Autonomous Data Warehouse Cloud service ... Read More

Feed: Striving for Optimal Performance. Partition-wise operations are not something new. I do not remember when they were introduced, but at that time the release number was still a single digit. Anyway, the aim of this post is not to describe the basics, but only to describe what is new in that area in 12c and 18c. The new features can be grouped in three categories: Partition-wise GROUP BY enhancements available as of version 12.2 Partition-wise DISTINCT enhancements available as of version 12.2 Partition-wise windowing functions enhancements available as of version 18.1 Before looking at the new features, here are ... Read More

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= ] [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 run as ... 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