Vsebinski sklop: DBA


dba track

K sodelovanju na SIOUG 2106 smo povabili priznane mednarodne in domače predavatelje, ki se nam bodo s strokovnimi in poglobljenimi predavanji pomagali poglobiti v skrivnosti delovanja Oracle baze.

Kdorkoli se ukvarja z administracijo Oracle baze ali pa se je ukvarjal z reševanjem problemov z uglaševanjem SQL stavkov na programski opremi Oracle, je gotovo slišal za enega najbolj zvenečih imen v Oracle srenji Jonathana Lewisa, ki bo letos osrednji predavatelj v Portorožu.

Abstract

The fastest way of performing a task is to avoid performing it at all - 2nd best is to reduce the number of times you perform the task. In this session I'll examine a small number of recent examples of production code where careful analysis of the requirements showed how to re-engineer to avoid redundant work, allowing significant performance improvements. The examples shown will vary in complexity from very simple to subtle and thought-provoking. The session will also include a couple of pointers to the benefit of investigating some of the newer, less commonly used, features of Oracle's SQL.

Abstract

Parallel execution plans are harder to read than serial plans because you really need to understand the impact of the order of operation, distribution mechanisms chosen, and (in recent versions of Oracle) the timing of the generation and use of Bloom filters. In this presentation we examine the basics of how parallel execution slaves work, and the way in which this can result in a massive difference between the apparent join order of an execution plan and the actual order of rowsource generation of that plan. We learn about "Table Queues" and "Data Flow Operations" and how they help us follow the order of operation, how the different distribution method can make a dramatic difference to performance, and how we can control them if it really becomes necessary.

Abstract:

Za zagotavljanje neprekinjenega poslovanja se navadno postavi rezervni sistem, ki čaka v pripravljenosti, da bo nudil storitve v primeru izpada primarnega sistema. Taki sistemi navadno večino časa tečejo v prazno, sploh kadar gre za geografsko oddaljene sisteme, ki varujejo primarne sisteme pred raznimi katastrofami. Poleg samih programskih storitev moramo varovati tudi podatke in omogočiti, da ima rezervni sistem v primeru preklopa na razpolago vse podatke. To pa je precej težje zagotoviti. Sploh v pri standardni ediciji Oraclove baze. Ali se da postaviti sistem, ki ne bi tekel v prazno, bi bil pripravljen za nudenje servisov v primeru okvare primarnega sistema, hkrati pa bi zagotavljal tudi, da se nič podatkov ne izgubi? Na predavanju bomo prikazali, kako je mogoče tak sistem postaviti.

Abstract:

SystemTap and dynamic tracing tools in general give administrators great control on their systems with the relatively little additional effort to learn the new tools. In this presentation I will show how to use SystemTap to modify data on fly and based on this functionality I will present how to build simple Oracle Database Firewall.

Abstract

 

Despite Oracle's strong commitment to make “Optimizer” the best piece of software, there will always be run-away statements causing headaches to developers and DBAs. So SQL tuning was, is and always will be an evergreen topic on conference talks. In this session I will try to explain my approach to SQL statement tuning which I have practiced in last 15 years.

Abstract

A lot of developers think that the performance of SQL statement depends merely on the quality of execution plan prepared by the Optimizer. This is only one side of the story. The other part of the story is even more important but unfortunately frequently totally neglected. It is related to the fact how efficient are the access paths to our data and the way how the SQL statement was coded. Writing efficient SQL statement requires some knowledge about SQL language intricacies, knowledge about the underlying data model and also some facts about the Optimizer. In this presentation we will discuss the required approach for preparing efficient and performant SQL statement. Of course we will also discuss available diagnostic mechanisms which can help us when things go in the wrong direction.

Abstract:

After troubleshooting a system with CPU-overload, we found it was mainly due to PL/SQL function-calls. The result was some interesting investigation into PL/SQL functions. We demonstrate how good intended use of pl/sql functions can turn into abuse. After the presentation, the audience will be able to avoid some of the mistakes.

Abstract

Everyone knows that execution plan is often not enough - that's why we monitor the wait events. But sometimes we have to dig deeper to gain the low level knowledge - strace, perf & gdb are tools that can reveal secrets of the Oracle Database and help us to resolve more complex performance problems by gaining a deeper understanding of the Oracle Database architecture.

Abstract

The live demo, presenting a real-life experience in penetration testing of the consolidated Exadata database environment for one of my customers. In the short session I'll try to show the threats of the consolidation era and how to make the environment more secure. The key aspect of my presentation will be escalating privileges from CREATE ANY INDEX to executing any command as an oracle user in operating system, through the SQL engine.

Abstract

Oracle Standard Edition is commonly used database for small and middle size companies. One of the typical problems with Oracle SE is lack of proper monitoring build in database as Oracle Automatic Workload Repository is available for Enterprise Edition. DBA's can use a Statspack to monitor database but in lot of cases there is a need to better monitoring granularity. This is an area where OraSASH can be used to establish monitoring on session level and provide like ?ASH? data for DBA. OraSASH is a free product created by Kyle Hailey and now maintained by myself on Github repository. It is a implementation of ?AWR/ASH? like repository. This session will guide DBA through OraSASH design, installation and maintenance process. OrsSASH is providing similar tables to AWR/ASH and same queries can be run against both repositories ? there will be examples how to monitor database and steps how to find out database bottlenecks.

Abstract

If you are maintaining a fleet of servers or many different customers, there is a list of tasks you have to do daily/weekly/monthly/etc. There are two possible solutions to that problem - you can spend your time doing to manually or you can spend your time to build an automated tool to help you with reoccurring task and after initial time/money effort you will have more time to learn new features or deal with problem you are more interested in. This presentation will show you how Ansible toolkit can be used in daily DBA work and how it will allow you to keep all environment configured in same way. In addition to that automated processes have a better quality (over a time) as if you fix your script this fix will be always there and you should not do same misconfiguration again. I will compare a Ansible with Enterprise Manager 12c, as well as to show in which area you should use you which tool. The following tasks will be covered: - Automated installation - Automated patching - Scripts

Abstract

As data volumes continue to increase, SQL performance and tuning remain as critical as ever. But it is not all about Explain Plans, using hints and adding indexes. How you structure your data and how you cluster your information together is vital to getting the best performance from your database - and it is often overlooked. By using Index Organised Tables, Cluster, Partitioning, normal indexes and other tricks, you can not only access your data quicker, you can greatly increase the use of your database's memory.

Abstract

How do you process a million records into your database a day? Or bulk load several Terabytes of historic data? SQL is "the fastest", PL/SQL gives you far more control. I've been designing and working with VLDBS for twenty years and I've made enough mistakes to learn a few things. In this presentation I will show, with examples from real projects, ways of using SQL and PL/SQL to bulk process data, critical considerations and real-world lessons on how to manage the loading of data.

Abstract

The presentation describes how to recover from disasters using RMAN. How to prepare for various failures that threaten your database. Testing and practising recovery procedures. How to diagnose problems and perform correct recovery actions. What to do when restore or recovery fails.

Povzetek

"Kakšen je vaš backup plan?" je najpogostejše vprašanje raznih IT revizorjev po podjetjih, še posebej v bankah.

Tako vprašanje nas vedno nekoliko spravi v zadrego. Naš backup plan je namreč "real time" backup plan, kar pa je revizorjem pogosto težko razložiti in njim še težje razumeti.

Backup se namreč posebej ne izvaja nikoli, prikrito pa kar ves čas, vzporedno z normalnim delom. Kljub temu naš "real backup" vsebuje vse običajne backup značilnosti:

  • možnost shranjevanja zelo velikih količin podatkov;
  • izvajanje (shranjevanje in vračanje) v neverjetno kratkem času;
  • kompresijo z uporabo deduplikacije;
  • hranjenje dolge zgodovine

in po novem, z opcijo PERFECT RECALL, tudi sprotno shranjevanje do vključno zadnje potrjene transakcije.

Abstract

The purpose of indexing is to give us precision access to the data we find interesting; better precision gives us greater efficiency, leading to better performance for queries. To get this precision, though, we have to maintain - in real time - the structures that give us that precision; and this slows down data maintenance. This conflict of interest makes it difficult to pick the best set of indexes when we design a system - how many indexes can we add to assist queries before the maintenance cost is too high; can we re-arrange the column order of an existing index or add extra column, rather than adding yet another index to the system.

The problems of indexing become much harder after a system has been in production for some time - is it safe to drop an index that doesn't seem to be useful, how can we even identify an index that might be safe to drop, will there be side effects that we hadn't predicted if we do drop the index, and if we add a new index for one reason will it result in other queries behaving badly.

In this presentation we review the way we think about indexes, and examine possible methods of getting more value for less work by making better use of the tools that Oracle has given us.