News
Entertainment
Science & Technology
Life
Culture & Art
Hobbies
News
Entertainment
Science & Technology
Culture & Art
Hobbies
- home
›- Julian Dontcheff's Database Blog
›- Software
Julian Dontcheff's Database Blog - Software
8 | Follower
Outliers, high-entropy and flex columns in JSON-to-Duality | Julian Dontcheff's Database Blog
Fun fact: by coffee consumption by country in 2024, Finland is #1 in the world. If you are familiar with JSON and still wondering about the meaning of the words in the title, then that's fine - these are relatively new concepts related to arguably the most important feature of Oracle Database 23ai: JSON Relational…
Using the PL/SQL Profiler in the Oracle Autonomous Database | Julian Dontcheff's Database Blog
The PL/SQL (Hierarchical) Profiler has been part of the Oracle database for quite some time but there is very little, close to none, information on how to use it in the Oracle Autonomous Database. This blog post is about how to bypass few hurdles in the Autonomous database for we lack OS access in ADB-S.…
Indexing and partitioning of JSON Collection Tables in Oracle Database 23ai | Julian Dontcheff's Database Blog
In the Oracle Database, a JSON Collection Table is a special table that provides JSON documents in a single JSON-type object column named DATA. JSON Collection Tables stores JSON documents. You can create such a collection by using the CREATE JSON COLLECTION TABLE command. Each document in a JSON Collection Table must have a document-identifier field, _id, at the top level, whose value…
Performance tuning of queries over JSON data in Oracle Database 23ai | Julian Dontcheff's Database Blog
Oracle Database 23ai offers 4 main ways of tuning SQL queries over JSON data: (1) indexes, (2) materialized views, (3) in-memory column storage, and (4) Exadata storage-cell pushdown. This blog post looks into how to index JSON. It is not a surprise to anyone that the Oracle Autonomous Database makes it extremely easy to index JSON…
The Oracle Database API for MongoDB in Oracle Database 23ai | Julian Dontcheff's Database Blog
The Oracle Database API for MongoDB (Mongo API for short) lets applications interact with collections of JSON documents in the Oracle Database using MongoDB commands. Oracle Database understands Mongo-speak. That is the purpose of Oracle Database API for MongoDB. Here is why and where you might use the MongoDB API: You have one or more applications…
The JSON-To-Duality Migrator in Oracle Database 23ai | Julian Dontcheff's Database Blog
The JSON-To-Duality Migrator is a new tool in Oracle Database 23ai that can migrate one or more existing sets of JSON documents to JSON-relational duality views. The migrator can be used for database migration from any document database using JSON documents or build a new application using JSON document as the migrator will automatically creating the necessary duality views.…
Disaster Recovery for the Oracle Autonomous JSON Database | Julian Dontcheff's Database Blog
“You can't prepare for everything - but you can prepare for anything.” - Paul Youngjohns Backup-Based Disaster Recovery is a low-cost DR option for databases with higher RTO tolerance. As Autonomous Data Guard is currently not supported for Autonomous JSON Database workloads, the natural questions is what are the alternatives: Option 1. The backup-based Disaster…
Performance Tuning in the Oracle Autonomous Database: Creating Indexes for JSON Collections | Julian Dontcheff's Database Blog
The Oracle Database and in particular the Oracle Autonomous Database offers multiple technologies to accelerate queries and improve database performance over JSON data, including indexes, materialized views, in-memory column storage, and Exadata storage-cell pushdown. Simple Oracle Document Access (SODA) is a NoSQL query language for storing, editing and deleting JSON documents. A SODA collection is…
The 5 major benefits of ECPUs in the Oracle Autonomous Database | Julian Dontcheff's Database Blog
Oracle Autonomous Database (ADB) recently introduced a more advanced ECPU billing metric and is now retiring the legacy OCPU billing metric for Autonomous Data Warehouse and Autonomous Transaction Processing in the next 12 months. Oracle recommend switching from the OCPU billing metric to the ECPU billing metric, which will not incur any downtime or service…
Some new PL/SQL features in Oracle Database 23c | Julian Dontcheff's Database Blog
PL/SQL stands for Procedural Language for SQL. PL/SQL is Oracle's procedural extension for SQL and is is loosely based on Ada (a variant of Pascal developed for the US Deptartment of Defense). PL/SQL has been available in Oracle Database since version 6 (first released in 1992) while stored PL/SQL procedures/functions/packages/triggers became available with version 7.…
How to migrate textual JSON data into the new JSON data type | Julian Dontcheff's Database Blog
Oracle Database (20c and later) supports JSON natively with all relational database features: transactions, indexing, declarative querying, views, etc. Oracle's native binary JSON format called OSON is the Oracle extension of the JSON format by adding scalar types (date and double) which are not part of the JSON standard. The SQL data type JSON uses format OSON. OSON…
LOB performance and is JSON the better option? | Julian Dontcheff's Database Blog
"Positive attitude plus effort equals performance." Tommy Tuberville However, tuning a database using lots of LOBs (Large OBjects) can be rather challenging even for experienced DBAs. The following case was about slow database performance. I was asked to have a look. What was perplexing for me was the fact that more than 95% of the…
Database Architecture changes in Oracle 23c | Julian Dontcheff's Database Blog
The new 23c version of the Oracle Database comes with 3 new database architecture features: all targeted towards both DBAs and Developers. There are also new Oracle certifications which might be of interest to both parties in the sense that after OCM 12c, there has not been any (or clearly upcoming) master certification with the…
3 small tricks for DBAs in Oracle Database 23c | Julian Dontcheff's Database Blog
Although the new Oracle Database 23c Free is in fact a Developer Release, there are still some interesting and useful DBA features. Here are 3 that I find simple and good to know: 1. Control PDB Open Order: DBAs can define a startup order or priority for each pluggable database (PDB) where the most important…
The Cloud Advisor and Cost Saving Opportunities in OCI | Julian Dontcheff's Database Blog
If you have a tenancy in OCI, you probably know that the cost can be controlled and managed in most cases by either shutting down the nodes and the databases when not being used (and this can be even automated) but there is more to that. The main OCI console page contains the basis information…
The 3 new PL/SQL packages in Oracle Database 23c | Julian Dontcheff's Database Blog
Oracle Database 23c comes with 3 new (supplied) PL/SQL packages which are: DBMS_HCHECK DBMS_SEARCH DBMS_SQL_FIREWALL Here is a short description of what they can be used for: 1. DBMS_HCHECK is a read-only and lightweight PL/SQL package that helps you identify database dictionary inconsistencies that are manifested in unexpected entries in the RDBMS dictionary tables or…
SQL Domains in Oracle Database 23c: an example | Julian Dontcheff's Database Blog
In Finland, vehicle registration plates consist of three letters and three numbers separated with a dash (e.g. ORA-600), there is actually a real car (a Saab) with this license plate. For 1000€ one can buy a vanity plate with 2-3 letters and 1-3 numbers. Often, in application forms, etc., you are asked to type your…
Oracle Full and Basic Database Management in OCI | Julian Dontcheff's Database Blog
In a recent Dynatrace study 56% of 1300 CIOs and IT leaders say traditional monitoring approaches must be replaced with a platform that can provide end-to-end observability across multiclouds. 57% said that multiple monitoring solutions make it hard to optimize performance. For almost 20 years Oracle databases (and not just Oracle) have been monitored mostly…
15 important things DBAs need to know about Oracle Zero Downtime Migration (ZDM) | Julian Dontcheff's Database Blog
Utopias are common in fiction, from Plato's "The Republic" in 370 BCE to the Federation in Star Trek. Does zero downtime exist only in power point presentations? Let us find out. Business services where companies make money or loose money depending on if the systems are up or down respectively are for example: online transactions, online authorizations,…
Oracle database discussion with ChatGPT | Julian Dontcheff's Database Blog
"Artificial intelligence is no match for natural stupidity" ― Albert Einstein Artificial stupidity is used as the antonym of artificial intelligence having two meanings: Machine learning algorithms make stupid mistakes while learning from the data. Artificial intelligence is dumbed down to make mistakes and look more human. Just have the patience to read the conversation below…
Oracle Cloud Guard and Hybrid Tablespace Encryption | Julian Dontcheff's Database Blog
"There are two types of encryption: one that will prevent your sister from reading your diary and one that will prevent your government" - Bruce Schneier What is Hybrid Tablespace Encryption? With the latest release updates of Oracle 19c (19.16 and above as 19.18 is the latest as of February 2023), it is now possible…
10 OCI tips for end users and administrators | Julian Dontcheff's Database Blog
The OCI console interface is changing rather often and new features are being added on monthly basis. Some features are very intuitive and for some one needs to go to the OCI documentation or MOS (links included below). Although using the OCI console and dashboard on daily basis, there are still few actions being performed…
The 10+6 Oracle PL/SQL pragmas | Julian Dontcheff's Database Blog
“What the pragmatist has his pragmatism for is to be able to say, Here is a definition and it does not differ at all from your confusedly apprehended conception because there is no practical difference.” ― Charles Sanders Peirce The word pragma origins from the ancient Greek word πρᾶγμα (prâgma) and means "a thing done, a fact".…
Pre-creating Oracle database users in PDBs and CDBs | Julian Dontcheff's Database Blog
"Every act of creation is first an act of destruction" Pablo Picasso In a previous blog post, I gave an example on how to copy a schema using a database link within the same (pluggable) database. Few people asked me about the errors during the Data Pump import, mostly because of a pre-created user: ORA-31684:…
Time to rebuild indexes in the Oracle database? | Julian Dontcheff's Database Blog
One of the most controversial topics among DBAs is whether to rebuild indexes or not. But it is so generic that the answer to the question depends on the database version, the index type and the reasons behind doing it. In most cases, we consider b-tree indexes for rebuilt. Note that b stands not for…
How to calculate the size of a database? | Julian Dontcheff's Database Blog
Often even DBAs have misalignment on what is meant by "database size". As data and databases grow with a rather high rate, it is important to understand the size of the database estate. Worldwide data is expected to hit 175 zettabytes by 2025, representing a 61% CAGR. Also, 51% of the data will be in data centers…
List of the Oracle Database 23c New Features | Julian Dontcheff's Database Blog
On October 18th 2022, Oracle announced the new long term release of 23c Beta. Here is a compiled list of the new features I am aware of: OLTP and Core DB: Accelerate SecureFiles LOB Write PerformanceAutomatic SecureFiles ShrinkAutomatic Transaction AbortEscrow Column Concurrency ControlFast Ingest (Memoptimize for Write) EnhancementsIncreased Column Limit to 4kManaging Flashback Database Logs…
How to view the version history of an Oracle database? | Julian Dontcheff's Database Blog
Often an Oracle database, even being say 19c, was initially created as 10g and upgraded with scripts over the years. From v$database and v$instance, we can find out the current version, the platform name and when the database was created but how to view the version history of that database? Often simple SQL statements can…
DBMS_AUTO_PARTITION: Automatic Partitioning in the Autonomous Database | Julian Dontcheff's Database Blog
Choosing a partitioning strategy is often far from straightforward. Range and interval partitioning are useful when organizing similar data types, most true for date and time data. Hash partitioning is useful for randomly distributing data across partitions based on a hashing algorithm, rather than grouping similar data. I remember how we managed to boost performance…
Database links within pluggable databases | Julian Dontcheff's Database Blog
Sometimes, you might need a database link between 2 schemas within the same (pluggable) database. Why? There are several reasons. Here is one: may be you want to refresh one schema from another using Data Pump via network link? This is very common practice for development databases. I will show in this blog how this…
Automatic Segment Advisor in the Oracle Database | Julian Dontcheff's Database Blog
"From my experience, the best advisors help in three ways: encourage you to look at the problem or opportunity from multiple angles; help you balance the tug of the short-term with important long-term priorities; and ask the tough questions you need to know to reach the best solution." Margo Georgiadis The Oracle Segment Advisor identifies…
User defined locks: enq: UL – contention | Julian Dontcheff's Database Blog
DBAs are familiar with most wait events, however there is one called "enq: UL – contention", which does not pop up that often: The foreground events above are from an Oracle 19c database. Clearly, we see a lot of user defined lock waits: enq: UL – contention. They come usually when the DBMS_LOCK package is…
2022: What’s new in the database world? | Julian Dontcheff's Database Blog
The big picture in the global database market is changing rapidly. It is an $80B market right now - the largest software market and growing in double digits year over year. Gartner has recently published the software market revenue numbers for 2021. In 2021, the revenue for managed cloud services is more than $39B –…
DBA_OPERATOR_ACCESS and SYSDATE_AT_DBTIMEZONE in ADB-S | Julian Dontcheff's Database Blog
In a blog post in 2020, entitled SYSDATE and Time Zones in the Autonomous Database, I covered the sysdate/systimestamp issue in ADB-S. Basically, you are allowed to change the database and session timezones in ADB, but this doesn’t change the SYSDATE and SYSTIMESTAMP in the timezones. So, the PL/SQL packages, procedure and functions and in particular…