Oracle Architecture

• Przemyslaw Adam Radowiecki (CERN)

Room: 222/R-001 The objective is to go through Oracle database physical and logical structures, highlighting the consequences of some Oracle's internal design choices for developers of database applications. The presentation defines Oracle related basic terms and illustrates them based on the database architecture. The following topics are discussed: • Database with its physical and logical structures (tablespace, segment, extent, block, database user, schema, user's quota) • Single instance (significant memory structures: buffer cache, shared pool) • Real Application Cluster (RAC) • Connecting to the database (TNS, database service) • SQL statements processing (hard and soft parsing, cursor, cursor sharing, execution plan)

Database and Application Design

• Katarzyna Maria Dziedziniewicz-Wojcik (CERN)

Room: 222/R-001 The primary goal of this presentation is to show how to design a scalable application using Oracle that performs. We will start with schema design that allows storing the data in a way that is efficient, scalable and makes those data easily retrievable. In that part focus will be put on ER model and best practices in schema design. Later we will explain in detail the SQL statements processing, with a number of tips/and tricks. The last part will talk about building a robust application using privileges, different connection types and number of other features.

Database Security

• Szymon Jozef Skorupinski (CERN)
• Daniel Gomez Blanco (CERN)

Room: 222/R-001 During Database Security Tutorial we will focus on Oracle Database security from developers point of view. We'll cover few topics, i.e. authentication, roles and privileges, auditing, encryption and SQL injection attacks.

SQL and Advanced SQL

• Marcin Blaszczyk (CERN)

Room: 222/R-001 SQL (Structured Query Language) is a programming language designed for managing data in relational database management systems. The aim of the tutorial is to provide an overview of the SQL language basic capabilities and several selected advanced SQL features available in the Oracle Database Management System. The main part of this tutorial is focused on the use of advanced SQL like: complex queries, analytical functions, set operators, views and temporary tables, materialized views, indexes and partitioning.

PL/SQL

• Zbigniew Baranowski (CERN)
• Stephan Petit (CERN)

Room: 222/R-001 PL/SQL is the Oracle's procedural extension of SQL. In this tutorial we will present the basic concepts behind PL/SQL and the general appearance of a PL/SQL program, covering general structure, control statements and basic units of the PL/SQL language. We will then focus on best scripting practices -things that each developer should know in order to write efficient code and avoid common traps.

Tuning

• Luca Canali (CERN)
• Eric Grancher (CERN)
• Dawid Wojcik (CERN)
• Chris Roderick (CERN)

Room: 222/R-001 In this session, you will learn how to understand what prevents your application from running as fast as you would like (wait event, SQL monitor, Oracle Enterprise Manager, etc.). In a second part, we will give share some indications on how to improve the performance (execution plan, table and index statistics, hinting, SQL baseline, etc.). You will also learn the key points of how to address complex performance issues in a systematic way and to address performance issues in a quantitative way. A few examples and common pitfalls will be presented.