Infrastructure at your Service

All Posts By

Franck Pachot

Franck Pachot

Google Spanner – SQL compatibility

By | Cloud | No Comments

By Franck Pachot . I have posted, a long time ago, about Google Spanner (inserting data and no decimal numeric data types) but many things have changed in this area. There is now a NUMERIC data type and many things have improved in this distributed SQL database, improving a bit the SQL compatibility. gcloud I can use the Cloud Shell, which is very easy – one click fron the console – but here I’m showing…

Read More
Franck Pachot

Optimizer Statistics Gathering – pending and history

By | Oracle | No Comments

By Franck Pachot . This was initially posted to CERN Database blog on Wednesday, 12 September 2018 where it seems to be lost. Here is a copy thanks to web.archive.org Demo table I create a table for the demo. The CTAS gathers statistics (12c online statistics gathering) with one row and then I insert more rows: 10:33:56 SQL> create table DEMO as select rownum n from dual; Table DEMO created. 10:33:56 SQL> insert into DEMO…

Read More
Franck Pachot

Password rolling change before Oracle 21c

By | Oracle | One Comment

By Franck Pachot . You may have read about Gradual Password Rollover usage from Mouhamadou Diaw and about some internals from Rodrigo Jorge. But it works only on 21c which is only in the cloud, for the moment, in Autonomous Database and DBaaS (but here I’ve encountered some problems apparently because of a bug when using SQL*Net native encryption). But your production is not yet in 21c anyway. However, here is how you can achieve…

Read More
Franck Pachot

DynamoDB Scan: the most efficient operation 😉

By | AWS, NoSQL | No Comments

By Franck Pachot . The title is provocative on purpose because you can read in many places that you should avoid scans, and that Scan operations are less efficient than other operations in DynamoDB. I think that there is a risk, reading those message without understanding what is behind, that people will actually avoid Scans and replace them by something that is even worse. If you want to compare the efficiency of an operation, you…

Read More
Franck Pachot

Oracle SPD status on two learning paths

By | Oracle | No Comments

By Franck Pachot . I have written a lot about SQL Plan Directives that appeared in 12c. They were used by default and, because of some side effects at the time of 12cR1 with legacy applications that were parsing too much, they have been disabled by default in 12cR2. Today, there are probably not used enough because of their bad reputation from those times. But for datawarehouses, they should be the default in my opinion….

Read More
Franck Pachot

Efficiently query DBA_EXTENTS for FILE_ID / BLOCK_ID

By | Oracle | 2 Comments

By Franck Pachot . This was initially posted to CERN Database blog on Thursday, 27 September 2018 where it seems to be lost. Here is a copy thanks to web.archive.org Did you ever try to query DBA_EXTENTS on a very large database with LMT tablespaces? I had to, in the past, in order to find which segment a corrupt block belonged to. The information about extent allocation is stored in the datafiles headers, visible though…

Read More
Franck Pachot

Amazon Aurora: calling a lambda from a trigger

By | AWS, Cloud | No Comments

By Franck Pachot . You may want your RDS database to interact with other AWS services. For example, send a notification on a business or administration situation, with a “push” method rather than a “pull” one from a Cloud watch alert. You may even design this call to be triggered on database changes. And Amazon Aurora provides this possibility by running a lambda from the database through calling mysql.lambda_async() from a MySQL trigger. This is…

Read More
Franck Pachot

Oracle write consistency bug and multi-thread de-queuing

By | Oracle | 3 Comments

By Franck Pachot . This was initially posted on CERN Database blog where it seems to be lost. Here is a copy thanks to web.archive.org Additional notes: – I’ve tested and got the same behaviour in Oracle 21c – you will probably enjoy reading Hatem Mahmoud going further on Write consistency and DML restart Posted by Franck Pachot on Thursday, 27 September 2018 Here is a quick test I did after encountering an abnormal behavior…

Read More
Franck Pachot

Recovery in the ☁ with Google Cloud SQL (PostgreSQL)

By | Cloud, Postgres | One Comment

By Franck Pachot . In a previous post I started this series of “Recovery in the ☁” with the Oracle Autonomous database. My goal is to explain the recovery procedures, especially the Point-In-Time recovery procedures because there is often confusion, which I tried to clarify in What is a database backup (back to the basics). And the terms used in managed cloud services or documentation is not very clear, not always the same, and sometimes…

Read More
Franck Pachot

Aurora Serverless v2 (preview) – CPU

By | AWS | No Comments

By Franck Pachot . This follows my previous post https://blog.dbi-services.com/aurora-serverless-v2-ram/ ‎which you should read before this one. I was looking at the auto-scaling of RAM and it is now time to look at the CPU Utilization. I have created an Aurora Serverless v2 database (please don’t forget it is the beta preview) with auto-scaling from 4 ACU to 32 ACU. I was looking at a table scan to show how the buffer pool is dynamically…

Read More