In 11g, Oracle introduced the DBMS_STAT.DIFF_TABLE_STATS functions to help you compare two sets of statistics for a table along with all its dependent objects (indexes, columns, partitions).
There are three versions of this function depending on where the statistics being compared are located:
DBMS_STAT.DIFF_TABLE_STATS_IN_HISTORY (compares statistics for a table from two timestamps in the past)
DBMS_STAT.DIFF_TABLE_STATS_IN_PENDING (compares pending statistics and statistics as of a timestamp or statistics from the data dictionary)
DBMS_STAT.DIFF_TABLE_STATS_IN_STATTAB (compares statistics from a user statistics table and the data dictionary, from two different user statistics tables, or a single user statistics table using two different STATSIDs)
The functions return a report that has three sections:
Basic table statistics The report compares the basic table statistics (number of rows, blocks, etc.).
Column statistics The second section of the report examines column statistics, including histograms.
Index Statistics The final section of the report covers differences in index statistics.
Statistics will only be displayed in the report if the difference in the statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the functions (PCTTHRESHOLD); the default value is 10%. The statistics corresponding to the first source, typically the current table stats in the data dictionary, will be used to compute the differential percentage.
The functions also return theMAXDIFFPCT(a number) along with the report. This is the maximum percentage difference between the statistics. These differences can come from the table, column, or index statistics.
At one point or another during your career, you will face a situation where the optimizer picks the wrong join method. As tempting as it is to brute-force the plan you want via a hint, it’s always better to understand why the Optimizer made a decision and provide the necessary information to allow the Optimizer to select the plan you want by default.
In this short video below, I explain how the Optimizer determines the join method based on the cardinality of the table on the left-hand side of the join. I also provide a simple set of steps to help you identify the common problems that can cause an incorrect join method to be used and guidance on how to supply the necessary information, so the Optimizer will automatically select the appropriate join method.
Adaptive joins, introduced in 12c, can help address some cases where the Optimizer selects the wrong join method but not in all cases. It’s always better to know how to save yourself rather than relying on the built-in safety net.
The primary focus of conventional data security technologies like passwords, firewalls, and data encryption is to keep criminals out of your company and your data stores.
But what protects your data, especially your essential asset (contracts, property titles, account statements, etc.), from being modified or even deleted by folks who gain access to your systems legitimately or illegitimately (hackers)?
Crypto-secure Data Management
This is where Blockchain can help. Layering Blockchain technologies on top of conventional data security features provide an extra level of protection that prevents illicit modifications or deletes of data.
What is Blockchain?
When we think of Blockchain, many of us instantly think of decentralized peer to peer apps that only permit consensus-based data changes. However, adopting these apps requires new development methodologies, speciality data stores and potentially new business practices, which is complicated and expensive!
But if we take a closer look at Blockchain technologies, we see four critical components; immutability, cryptographic digests, cryptographic signatures, and distributed systems. Each part works to protect against a different aspect of illicit data changes performed using legitimate user credentials or by hackers.
Integrating these Blockchain technologies into the Oracle Database brings the critical security benefits of Blockchain to mainstream applications with minimal or no changes required. Providing the full functionality of the world’s leading database on crypto-protected data.
In the video below, Juan Loaiza explains how Oracle implemented Blockchain technologies in the Oracle Database and how they can be used to protect your essential business data. I’ve also included a brief description of these features under the video.
How do Blockchain technologies work in the Oracle Database?
To protect against illicit data changes made by rogue insiders or malicious actors using insiders’ credentials, Oracle has introduced Immutable tables (insert-only tables) in Oracle Database 21c (21.3).
With an Immutable table, it is possible to insert new data, but existing data cannot be changed or deleted by anyone using the database, even the database administrators (SYSDBA). It is also impossible to change an immutable table’s definition or convert it to an updatable table. However, an Immutable table appears like any other table in the database from an application’s point of view. It can store both relational data and JSON documents, and it can be indexed and partitioned or used as the basis of a view.
To protect against illicit changes made by hackers, Oracle has introduced Blockchain tables. Blockchain tables are immutable tables that organize rows into several chains. Each row, except the first row in the chain, is chained to the previous row via a cryptographic digest or hash. The hash is automatically calculated on insert based on that row’s data and the hash value of the previous row in the chain. Timestamps are also recorded for each row on insertion.
Any modification to data in a Blockchain table breaks the cryptographic chain because the hash value of the row will change. You can verify the contents of a blockchain table have not been modified since they were inserted using the DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS procedure.
Even with Immutable or Blockchain tables, data can be falsely inserted in an end user’s name by someone using stolen credentials. To address this vulnerability, Oracle allows end-users to cryptographically sign the data they insert using their private key that is never passed to the database.
Each end-user registers a digital certificate containing their public key with this database. This digital certificate allows the database to validate the end-users signature when new data is inserted. Even if a hacker manages to steal a valid set of credentials without the private key, the data insert signature won’t match and will therefore not be accepted.
It’s also possible for end-users to ensure the database has received their changes by requesting Oracle countersign the newly inserted data. Oracle returns a crypto-receipt to the user, ensuring nothing on the mid-tier can filter specific data to prevent it from being recorded.
Distributing Cryptographic Digest
Even with cryptographically chained rows, sophisticated cyber-criminals or authorities could illicitly change data via a large-scale cover-up, where the entire database is replaced. To detect such a cover-up, Oracle enables schema owners to sign and distribute the cryptographic digest for a blockchain table periodically. Remember, the digest can’t be used to infer the data in the table, but authorized users can use it to validate the chain and confirm their newly inserted data is present. The crypto-digest can be posted to an independent public store or blockchain, like Ethereum or sent out by email or made available via a REST API.
A cover-up can easily be detected by comparing the previously published digests to the current table content. Also, distributing the publicly across multiple independent services prevents an authority or cyber-attacker from deleting all the separate copies.
Getting Started With Blockchain
Both Immutable and Blockchain tables are free features of the Oracle Database. No additional licenses or software is needed to take advantage of these new table types, which are completely transparent to all new and existing applications.
Also, note Oracle has backported Immutable tables and Blockchain tables to Oracle Database 19c (19.11 and 19.10, respectively). Please check My Oracle Support for more details before attempting to use Blockchain tables in 19.10.
Over the last couple of months, the wonderful folks from Oracle Australia have been running a FREE virtual training event on what to expect from Oracle Database 19c and how to prepare to upgrade to the latest long-term support release of the Oracle Database.
The first 5 sessions covered everything you would want to know about how to upgrade and what you can expect from Oracle Database 19c including an in-depth look at the new Multitenant architecture. The sessions so far have been presented by some of the best Oracle speakers around including Mike Dietrich, Julian Dontcheff, Martin Bach, Richard Agnew, and Alex Blyth. All of the sessions so far have been recorded and are available for replay.
Under the Hood of an Autonomous Database
The final session in series is scheduled for August 11th at 10am AEST ( August 10th at 5 pm PST) and will be presented by Alex Blyth and myself. In our session, we plan to give you a peek under the hood of the Oracle Autonomous Database and provide you with a clear understanding of how this unique autonomous database works. We’ll share details on the exclusive combination of Oracle Database 19c features, best practices, and machine-learning algorithms used to deliver this family of cloud services. We’ll use live demos to show you how it can simplify your approach to data management with Oracle Database 19c and accelerate your transition to the cloud.
You can registers for this session here. Hope you can join us!
There is an on-going debate in our community about the best approach for developing cloud-native or data-driven apps. On one side, you have folks who say use a single-purpose “best-of-breed” database for each data type or workload you have. While the other half say, you should use a single converged database. So, which approach is right for you and your projects?
Let’s examine some of the pros and cons of each approach.
Single-purpose databases or purpose-built databases as they are often as known, are engineered to help solve a single or small number of problems. Given their narrow focus, they can ignore the tradeoffs usually required when trying to accommodate multiple data types or workloads. It also allows them to use a convenient data model that fits the purpose and to adopt APIs that seem natural for that data model. They offer less functionality than converged databases, and therefore, fewer APIs, making it easier to start developing against them. Their simplicity means they do a few things very well, but other things not at all. For example, a lot of single-purpose databases scale well, because they offer no strong consistency guarantees.
At first glance, single-purpose databases appear to be a good option. Developers are happy because they get exactly what they need to begin a project. However, when you look at the bigger picture, single-purpose databases can cause a lot of pain and end up costing more in the long run.
I got a lot of follow-up questions on what Query Block names are and how to find them, after my recent post about using SQL Patches to influence execution plans. Rather than burying my responses in the comment section under that post, I thought it would be more useful to do a quick post on it.
What are query blocks?
A query block is a basic unit of SQL. For example, any inline view or subquery of a SQL statement are considered separate query blocks to the outer query.
The simple query below has just one sub-query, but it has two Query Blocks—one for the outer SELECT and one for the subquery SELECT.
Oracle automatically names each query block in a SQL statement based on the keyword using the following sort of name; sel$1, ins$2, upd$3, del$4, cri$5, mrg$6, set$7, misc$8, etc.
Given there are two SELECT statements in our query, the query block names will begin with SEL. The outer query will be SEL$1 and the inner query SEL$2.
How do I find the name of a query block?
To find the Query Block name, you can set the FORMAT parameter to ‘+alias’ in the DBMS_XPLAN.DISPLAY_CURSOR command. This will display the contents of the OBJECT_ALIAS column in the PLAN_TABLE, as a new section under the execution plan.
The new section will list the Query Block name for each of the lines in the plan.
SQL_ID 4c8bfsduxhyht, child NUMBER0-------------------------------------SELECT e.ename, e.deptno FROM emp e WHERE e.deptno IN(SELECT d.deptno
FROM dept d WHERE d.loc='DALLAS')
Plan hash VALUE: 2484013818---------------------------------------------------------------------------| Id | Operation | Name |ROWS| Bytes | Cost (%CPU)|TIME|---------------------------------------------------------------------------|0|SELECT STATEMENT ||||5(100)|||*1| HASH JOIN SEMI ||5|205|5(20)| 00:00:01 ||2|TABLE ACCESS FULL| EMP |14|280|2(0)| 00:00:01 ||*3|TABLE ACCESS FULL| DEPT |1|21|2(0)| 00:00:01 |---------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIEDBY operation id):
2- SEL$5DA710D3 / E@SEL$1
3- SEL$5DA710D3 / D@SEL$2
Predicate Information (IDENTIFIEDBY operation id):
As you can see, @SEL1 is the Query Block name for the outer query, where the EMP table is used, and @SEL2 is the Query Block name for the sub-query, where the DEPT tables is used.
At the recent OOW European conference there was a lot talk about Converged Databases and how they can greatly simplify data-driven app development.
But if you missed the conference, you might find yourself wondering what exactly is a Converged Database and what is the difference between a Converged Database and an Autonomous Database?
So, I thought it would be a good idea to write a short blog post explaining what a Converged Database is and how it relates to the Oracle Autonomous Database.
What is a Converged Database?
A Converged Database is a database that has native support for all modern data types (JSON, Spatial, Graph, etc. as well as relational), multiple workloads (IoT, Blockchain, Machine Learning, etc.) and the latest development paradigms (Microservice, Events, REST, SaaS, CI/CD, etc.) built into one product.
By having support for each of these datatype, workloads, and paradigms as features within a converged database, you can support mixed workloads and data types in a much simpler way. You don’t need to manage and maintain multiple systems or worry about having to provide unified security across them.
You also get synergy across these capabilities. For example, by having support for Machine Learning algorithms and Spatial data in the same database, you can easily do predictive analytics on Spatial data. The Oracle Database is a great example of a Converged Database, as it provides support for Machine Learning, Blockchain, Graph, Spatial, JSON, REST, Events, Editions, and IoT Streaming as part of the core database at no additional cost.
A good analogy for a Converged Database is a smartphone. In the past, if you wanted to take a picture or video you would need a camera. If you wanted to navigate somewhere you would need a map or a navigation system. If you wanted to listen to music, you needed an iPod and if you wanted to make phone calls, you would also need a phone.
But with a smartphone, all of these products have been converged into one. Each of the original products is now a feature of the smartphone. Having all of these features converged into a single product inherently makes your life easier, as you can stream music over the phone’s data plan or upload pictures or videos directly to social media sites. Continue reading “What is a Converged Database?”
Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group – Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based optimizer, the statistics that feed it, the hints that influence it and key tools you need to exam executions plans.
The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the 5 topics.
Part 1 Understanding the Oracle Optimizer
The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.
Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. the majority of Oracle’s query transactions are now cost-based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations including:
With this family of cloud services, developers no longer have to wait on others to provision hardware, install software, and create a database for them. With ADB, developers can easily and instantly deploy an Oracle database without worrying about having to manual tune it or capacity planning. This allows developers to start developing in minutes and concentrate on solving business problems without all of the usual distractions.
ADB has the most advanced SQL and PL/SQL support accelerating developer productivity by minimizing the amount of application code required to implement complex business logic. It also has a complete set of integrated Machine Learning algorithms, simplifying the development of applications that perform real-time predictions such as personalized shopping recommendations, customer churn rates, and fraud detection.