ODTUG Kscope12 - Kscope12

Traditionally SQL tuning techniques involve gazing at a statspack or AWR report, identifying important wait events, and parameters that need attention. In a data warehouse environment, the techniques required are drastically different. Statements are rarely repeatable and the ones that are have complex execution plans involving Optimizer transformations and parallel execution. This session will discuss a straight-forward approach to tackling SQL tuning in a warehouse environment, and use real-world examples to demonstrate how you should navigate the complex execution plans found in a data warehouse environment and determine what steps are necessary to improve the performance.

During the optimization of a SQL statement, the Optimizer relies heavily on statistics to estimate the number of rows produced by each of the SQL operators. The quality of the statistics for the objects referenced in the statement greatly affects the quality of the plan. Statistics maintenance is a challenge all DBAs must face in order to prevent execution plan from becoming suboptimal. This presentation provides a detailed description of the Optimizer statistics and the features introduced in 11g to improve the quality and efficiency of statistics gathering, as well as strategies for managing statistics in various database environments.

In the debates between traditional SDLC ("waterfall") proponents and their Agile counterparts, probably the biggest hinge point is the question of database design. Agile practitioners value "working software over comprehensive documentation" and "responding to change over following a plan." Is Agile, then, the enemy of proper design? What does honoring these values mean to the data design process? This session answers these and related questions.

Agile or Structured Development? The War is On! Kscope has become interesting in that it is now a meeting point for the traditional database developers with focus on the stability of data models and the emerging agile, lean, x-treme developers, from the Java scene, with focus on short schedules and immediate success. This session will explain why true agility in business requires a structured approach to application development while the agile methodology will make it harder and harder to maintain applications by building up sand in the gears of applications and, therefore, eventually bring businesses to a grinding halt.

Many DBAs have been pigeonholed into strict operational roles in which they have little ability to participate in discussions about how applications access, process, and manage data. This artificial division has led to well-known battles and acrimony between DBAs and developers. Fortunately, new movements in software development and operations are emphasizing collaboration and people over processes. When done with a focus on these principles, Agile development and DevOps operations bring benefits to developers and DBAs alike. In this presentation, you will hear about beginning this transformation within a traditional DBA organization, from steps you can take to free up maintenance time to ways to provide demonstrable value to development teams.

This session will share: An overall architecture for a big data ecosystem, consisting of solutions like the RDBMS, Hadoop, and BI tools; integration points between these tools and systems and a brief look at their strengths and weaknesses and how to use the best of all worlds in your architecture; and how to effectively leverage analytical sandboxes for data discovery and agile development of data-driven solutions.

This session will define Big Data and its impact on your company. They will cut through the buzz words and define the technologies you should be paying attention to and that you should start leveraging in your company. Lastly the session will discuss some interesting new technologies for Big Data introduced at the recent Oracle OpenWorld.

Arthur C. Clarke wrote that "any sufficiently advanced technology is indistinguishable from magic." That is true, and it sometimes makes troubleshooting seem like art. But it is not art; it is process and this presentation uses a case study, such as resolving the ORA-03136 "connection lost" error, to demonstrate that process and to show how components such as ASH, typically thought of as performance tuning tools for DBAs, are also useful diagnostic tools for everyone, particularly developers. Attendees will learn a forensic, empirical approach to troubleshooting and see a demonstration of a complex troubleshooting task resulting in a non-intuitive, but empirically sound, resolution. This presentation should make you think of Active Session History as something like queryable trace information captured and stored within the database. Three things attendees will learn: 1. How the Active Session History views are populated, and what that means for how you use them; 2. What information is captured and stored within the Active Session History views; and 3. How Active Session History information is the next best thing to SQL trace information, and how it can be used for troubleshooting.

This session will overview why binding is extremely important with regards to performance, scalability, and even security, but quickly move into topics such as: Do I always want to bind? (Surprisingly, the answer is no.) What is bind variable peeking? Is it good or evil in disguise or both? So the developers don't bind, is cursor_sharing=force/similar appropriate system wide? (Emphasis will be on the reasons why setting cursor sharing at the instance level is not such a good idea). What is the real difference between cursor_sharing=force/similar and which should we use under what circumstances? The presentation will be practical, with examples and hard numbers you can use in your daily work.

Oracle Database 11g provides the industry's most powerful security solutions for protecting application data where threats are an everyday occurrence. This session takes a look at the implementation of an important subset of these capabilities, including Oracle Database Firewall, Oracle Database Vault, Oracle Audit Vault, and Oracle Advanced Security as well as features such as Oracle Virtual Private Database. It focuses on the performance impacts and capabilities of these offerings.

Before you can write the SQL for multi-table joins, it is a good idea to be aware of the many access paths the optimizer can choose to access a single table. Given a simple heap table with two B-tree indexes, the presenter can think of twenty-three different possible execution plans that the optimizer might be able to produce, depending on the index definitions and the available statistics. The presenter can come up with a further sixty-six by applying a single strategy for rewriting the SQL. This presentation will look at a few of the less well known options, and then show how to extend the range of possibilities.

There are many different table access paths available to the Oracle optimizer, and many different transformations it can use to rewrite your query to allow for a more efficient join strategy. Nevertheless there are things that the optimizer simply cannot do that human ingenuity can produce through careful engineering of SQL. If you are allowed to change the SQL, but are under pressure to leave the indexing untouched, there are various ways in which you can maximize the benefit you can get from the indexes. This presentation will demonstrate how to do a star transformation on Standard Edition - in the complete absence of bitmap indexes.

The optimizer is very good at transforming your query into something that you didn't write; sometimes, though, Oracle's transformation is a bad idea. Sometimes you can transform a query manually in ways that the optimizer cannot. This presentation examines a single, fairly simple query to see how many different things you can do to make it more efficient using strategies which the optimizer, at present, may not consider.

Oracle E-Business Suite Release 12.2 intends to bring near zero downtime patching by using edition-based redefinition. This presentation teaches the basics of EBR and illustrates the ideas by referring to how the E-Business Suite developers adopted it.

Maria Colgan, Oracle Corporation

This presentation shows how to implement one of the canonical data transformations used to illustrate MapReduce (sessionization) with only ordinary SQL and PL/SQL—and Oracle Database's built-in parallel execution system. It explains why it is quite proper to call the approach an example of the MapReduce programming model.

After this session, you will understand what has been done to enhance and extend the database architecture and functionality of Oracle Exadata and how to take maximum advantage of the new capabilities. The presentation examines in detail SQL offload processing, I/O resource management, Oracle Exadata flash cache, storage virtualization, integration with Oracle Database, and the hardware and software architectures of Oracle Exadata.

This session provides an open Q&A for attendees to pose questions about Oracle Database and Oracle Exadata to a panel of OakTable database experts: Cary Millsap, Maria Colgan, Jonathan Lewis, Dominic Delmolino, Tim Gorman,and Dan Norris. Maximum time will be devoted to answering questions from the audience, so be sure to bring your questions and prepare for some great discussions!

OEM is the standard for most DBAs to monitor and interact with their database environments. This session will go over how to get the most out of Oracle Enterprise Manager by utilizing scripts and code outside of the OMS repository to allow more functionality, better reporting, and enhanced management features. Trouble-shooting issues when they arise will be reviewed during this session as well.

Tom Disheroon, Oracle Corporation

Using Ning.com as a case study, this presentation guides you through performance tuning best practices, from database design to optimization, and provides you with tips and tricks for ultimately getting the highest performance out of your MySQL database. This session will cover: - Index Selection and Tuning - SQL Code Techniques - Schema Optimization - Server Configuration Options - Benchmarking standards and tools - Using MySQL Enterprise Monitor and MySQL Query Analyzer to identify the root cause of performance degradation.

You need to consider many things when writing good queries, but what can you learn by focusing on the SQL code only, ignoring other topics such as table structures, indexes, and EXPLAIN plans? It turns out that textual analysis of SQL code can reveal a surprising amount, and thus it can be helpful to run "lint checks" on your SQL. This talk will show you how to spot and correct bad patterns in SQL, some of which are MySQL-specific, but many of which are platform-agnostic. The examples are drawn from problems the presenter has seen in the wild. The talk will also include an introduction to the open-source pt-query-advisor tool, which can help you analyze MySQL queries.