Infrastructure at your Service

All Posts By

Franck Pachot

Franck Pachot

Oracle 12c – peak detection with MATCH_RECOGNIZE

By | Cloud, Database Administration & Monitoring, Oracle | 2 Comments

By Franck Pachot . This post is part of a series of small examples of recent features. I’m running this in the Oracle 20c preview in the Oracle Cloud. I’ll show a very basic example of “Row Pattern Recognition” (the MATCH_RECOGNIZE clause in a SELECT which is documented as “row pattern matching in native SQL” feature by Oracle”). You may be afraid of those names. Of course, because SQL is a declarative language there is…

Read More
Franck Pachot

Oracle 12c – pre-built join index

By | Cloud, Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . This post is part of a series of small examples of recent features. I’m running this in the Oracle 20c preview in the Oracle Cloud. I have created a few tables in the previous post with a mini-snowflake scheme: a fact table CASES with the covid-19 cases per country and day. And a dimension hierarchy for the country with COUNTRIES and CONTINENTS tables. This title may look strange for people used…

Read More
Franck Pachot

What is a serverless database?

By | AWS, Cloud, Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . After reading the https://cloudwars.co/oracle/oracle-deal-8×8-larry-ellison-picks-amazons-pocket-again/ paper, I am writing some thoughts about how a database can be serverless and elastic. Of course, a database needs a server to process its data. Serverless doesn’t mean that there are no servers. Serverless as not waiting for server provisioning The first idea of “serverless” is about provisioning. In the past when a developer required a new database to start a new project she had to…

Read More
Franck Pachot

Oracle 18c – select from a flat file

By | Cloud, Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . This post is the first one from a series of small examples on recent Oracle features. My goal is to present them to people outside of Oracle and relational databases usage, maybe some NoSQL players. And this is why the title is “select from a flat-file” rather than “Inline External Tables”. In my opinion, the names of the features of Oracle Database are invented by the architects and developers, sometimes renamed…

Read More
Franck Pachot

티베로 – The AWR-like “Tibero Performance Repository”

By | Database Administration & Monitoring | No Comments

By Franck Pachot . In a previous post I introduced Tibero as The most compatible alternative to Oracle Database. Compatibility is one thing but one day you will want to compare the performance. I’ll not do any benchmark here but show you how you we can look at the performance with TPR – the Tibero Performance Repository – as an equivalent of AWR – the Oracle Automatic Workload Repository. And, as I needed to run…

Read More
Franck Pachot

PostgreSQL Shared Buffers vs free RAM

By | Postgres | 3 Comments

PostgreSQL, like all other database engines, modifies the table and index blocks in shared buffers. People think that the main goal of buffered reads is to act as a cache to avoid reading from disk. But that’s not the main reason as this is not mandatory. For example PostgreSQL expects that the filesystem cache is used. The primary goal of shared buffers is simply to share them because multiple sessions may want to read a…

Read More
Franck Pachot

Oracle Standard Edition on AWS ☁ socket arithmetic

By | AWS, Cloud, Oracle | No Comments

By Franck Pachot . Note that I’ve written previously about Oracle Standard Edition 2 licensing before but a few rules change. This is written in May 2020. TL;DR: 4 vCPU count for 1 socket and 2 sockets count for 1 server wherever hyper-threading is enabled or not. The SE2 rules I think the Standard Edition rules are quite clear now: maximum server capacity, cluster limit, minimum NUP, and processor metric. Oracle has them in the…

Read More
Franck Pachot

Always free / always up tmux in the Oracle Cloud with KSplice updates

By | Cloud, Oracle | No Comments

By Franck Pachot . I used to have many VirtualBox VMs on my laptop. But now, most of my labs are in the Cloud. Easy access from everywhere. GCP There’s the Google Cloud free VM which is not limited in time (I still have the 11g XE I’ve created 2 years ago running there) being able to use 40% of CPU with 2GB of RAM: top – 21:53:10 up 16 min, 4 users, load average:…

Read More
Franck Pachot

20c: AWR now stores explain plan predicates

By | Oracle | No Comments

By Franck Pachot . In a previous post https://blog.dbi-services.com/awr-dont-store-explain-plan-predicates/ I explained this limitation in gathering filter and access predicates by Statspack and then AWR because of old bugs about reverse parsing of predicates. Oracle listens to its customers through support (enhancement requests), though the community (votes on database ideas), and through the product managers who participate in User Groups and ACE program. And here it is: in 20c the predicates are collected by AWS and…

Read More
Franck Pachot

The myth of NoSQL (vs. RDBMS) agility: adding attributes

By | AWS, Database Administration & Monitoring, NoSQL, Oracle, Postgres, SQL Server | 3 Comments

By Franck Pachot . There are good reasons for NoSQL and semi-structured databases. And there are also many mistakes and myths. If people move from RDBMS to NoSQL because of wrong reasons, they will have a bad experience and this finally deserves NoSQL reputation. Those myths were settled by some database newbies who didn’t learn SQL and relational databases. And, rather than learning the basics of data modeling, and capabilities of SQL for data sets…

Read More