Percentiles, lies, and database performance monitoring

It is becoming increasingly popular these days to put response time percentiles into the SLAs and OLAs. But, unfortunately, most people do it wrong. In this presentation, we will explain the most common failure modes regarding performance metrics and how to handle situations when someone comes complaining with a 99th percentile graph.

Response times are often not normally distributed, and averages and dispersions are just garbage. The simplest way to make sense of such data is to use percentiles. Popular choices are 50th (median) and 99th percentiles. By definition, there can be values larger than 99th percentile, so adding 99th percentile to the SLA will hide outliers.

Oracle provides histograms for the wait events, for query response times it is still only averages. The only way to get a hold of the outliers is ASH or event 10046 traces. As always, these options come with their own challenges.

Getting to grips with JSON in the database

With Oracle 21c and 23c, JSON in the database is now a reality. Mixed relational-JSON databases are on the verge of becoming mainstream.

In this session, using practical examples in the Oracle Cloud, we'll look at:

Generating JSON objects from relational data using SQL and PL/SQL

• JSON_OBJECT
• JSON object types
• apex_json

SQL: Good and evil are the same thing

SQL is one of the most powerful languages. It gives us direct access to a database and allows us to manage the data: query, change, or delete it. We can join multiple tables, perform complicated calculations, and much more! That makes the possibility to use SQL directly from PL/SQL code one of the best features of Oracle. But, as Heraclitus said, Good and evil are the same thing - this possibility is one of the biggest problems in Oracle, too. Mixing SQL and PL/SQL code is so easy and tempting, that we do it all the time without giving it a lot of thought. The consequences, however, are serious and impact the clarity of the code, its testability, and the overall system performance.

In this talk, I will show that although SQL is a very powerful tool, it is not healthy for your system to have too much of it. I will show how you can get rid of some unnecessary SQL and therefore keep the good while avoiding the evil that comes with it.

Join me and see how to organize your database code to maximize the benefits of the powerful SQL and PL/SQL capabilities and avoid its hidden danger. Live coding and examples on demo!

SQL: Good and evil are the same thing

SQL is one of the most powerful languages. It gives us direct access to a database and allows us to manage the data: query, change, or delete it. We can join multiple tables, perform complicated calculations, and much more! That makes the possibility to use SQL directly from PL/SQL code one of the best features of Oracle. But, as Heraclitus said, Good and evil are the same thing - this possibility is one of the biggest problems in Oracle, too. Mixing SQL and PL/SQL code is so easy and tempting, that we do it all the time without giving it a lot of thought. The consequences, however, are serious and impact the clarity of the code, its testability, and the overall system performance.

In this talk, I will show that although SQL is a very powerful tool, it is not healthy for your system to have too much of it. I will show how you can get rid of some unnecessary SQL and therefore keep the good while avoiding the evil that comes with it.

Join me and see how to organize your database code to maximize the benefits of the powerful SQL and PL/SQL capabilities and avoid its hidden danger. Live coding and examples on demo!

Oracle The Most Mysterious Cases

It was on a cold day in November when the silence was broken by a desperate scream: ‘Kill it! Kill it!”. The boss immediately pushed the button and brutally terminated the session. It has happened again… The Performance was murdered. Boss flicked through the case files. There were no usual suspects. Where is the murderer hiding then? The desk was buried under the cases’ files similar to this one. The File that hasn’t been seen in production for 6 months, the whole team has been looking for it, but there was no progress, not even a clue… Another reported missing - Data has left the Source System but never reached the Destination server…Nobody has seen it again… This is too much for one person. And that’s why I need YOUR help! During the session, we will solve all the mysterious cases together and we will find out what is responsible for the strange behavior of the system. Is the source of the problem a huge design error or a small decision at one of the stages of development? How minor design decisions can affect the system's performance and vulnerability to errors? Who is the murderer? The answer may surprise you!

Game, Set and Match

The tagline of the Game of Set is: The Family Game of Visual Perception. It is a game of finding matching cards in a selected group of cards. Being a SQL guy, I read this as: SET: The GAME of MATCHing cards. In any database we can select SETs using MATCHing criteria. Where is the GAME? Well, for me, it is a game of trying to solve this puzzle using SQL. You will see how you can use the different SET operators in Oracle, how you can use JOINs to create a SET of cards, from which you can SELECT a puzzle and also find the SETs in that puzzle.

Using ORDS, REST webservices, PL/SQL and OAuth2 to Build, Deploy and Secure an API

In this presentation, through building practical examples, attendees will learn how to build a secured REST API to enable searching on their application.

The resulting technology-independent REST API will be usable by both Oracle and by non-Oracle environments alike.

Topics to be covered include:

• an architectural overview of the creation of ORDS web services in PL/SQL
• setting up ORDS for your database schema
• defining the search API in PL/SQL
• creating ORDS module and templates for the API
• management of HTTP return statuses and result pages pagination
• securing the API via OAuth2 using ORDS clients, privileges and roles.

Generiranje SVG datotek neposredno iz Oracle podatkovne baze

Na predavanju želim predstaviti API za generiranje SVG datotek neposredno iz Oracle podatkovne baze, izključno z uporabo PL/SQL paketa, katerega sem razvil za potrebe mojih projektov. Najprej bom predstavil SVG datoteke – njihovo zgodovino, namen, strukturo, način integracije z CSS in Javascript-om, potem pa uporabo PL/SQL API-ja in konkretne primere implementacije v Oracle APEX aplikaciji. API se je izkazal uporaben, ker za generiranje SVG datotek ne rabimo dodatna orodja ali programske jezike temveč samo PL/SQL znotraj Oracle podatkovne baze.