When it comes to writing efficient queries there are a few key concepts that need to be understood. One of them is the Oracle Cost-Based Optimizer (CBO). Although it's called a cost-based optimizer it's actually not the cost we need to focus on primarily to understand why the optimizer makes certain decisions. In this webinar, you will learn the basics of the CBO, see why it is crucial that the optimizer's picture of the data fits reality, why cardinality and selectivity estimates matter so muc
Views: 58402 Redgate Videos
Presented by Randolf Geist 01 August 2012 Summary Building on the previous Cost-Based Optimizer Basics webinar, in this almost "zero-slide" session we'll explore different aspects of the Cost-Based Optimizer that haven't been covered or only mentioned briefly in the 'basics' session. This is a continuous live demonstration including topics like: Clustering Factor, Histograms, Dynamic Sampling, Virtual Columns, Daft Data Types and more. If you've ever asked yourself why a histogram can be a threat to database performance and why storing data using the correct data type matters regarding Execution Plans then this session is for you. It is recommended, although not required, to watch the recording of the 'basics' webinar first. A live Q+A session with Randolf Geist will follow the presentation. For our complete archive, and to sign up for upcoming webinars please go to http://www.red-gate.com/oracle-webinars
Views: 6613 Redgate Videos
Oracle tutorial: Explain plan for query optimization in Oracle PLSQL oracle tutorial for beginners using execution plan to optimize query sql query analyzer sql query cost analysis https://techquerypond.wordpress.com This oracle tutorial show you how to use EXPLAIN PLAN in oracle. This video covers how to check cost of the query from DBMS_XPLAN.DISPLAY . You can find the cost of the query using the Using EXPLAIN PLAN FOR and based on the result you can optimize the query for faster performance. Subscribe on youtube: https://www.youtube.com/channel/UCpiyAesWNYOXSz5GPq8lbkA For more tutorial please visit #techquerypond https://twitter.com/techquerypond
Views: 10737 Tech Query Pond
Hello friends in this video i'm just showing to you what is optimizer statistics into oracle,optimizer help to improve the performance of sql statements during execution period.Oracle database #ORACLEWORLD #OracleOptimizerStatistics Unbeatable,Unbreakable Platform..
Views: 6819 Oracle World
Hello friends in this video i'm just showing to you what is optimizer statistics into oracle,optimizer help to improve the performance of sql statements during execution period. #ORACLEWORLD #OptimizerStatistics Oracle database Unbeatable,Unbreakable Platform.
Views: 2309 Oracle World
Video for my inverted classroom "Database Systems". The complete list of videos, slides, and additional material is (will be) available at http://datenbankenlernen.de Computer Science, Saarland University: Bachelor (in German): http://www.cs.uni-saarland.de/index.php?id=52&L=1 Master (in English): http://www.cs.uni-saarland.de/index.php?id=132&L=1 Ph.D./Grad School: http://gradschool.cs.uni-saarland.de/
Views: 5653 Prof. Dr. Jens Dittrich
This presentation describes the method to understand how oracle calculate cost during explain plan generation
Views: 705 debasis maity
My book "Patterns in Data Management" is now available both as an ebook or a print book (with color graphics!). See: http://amzn.to/1Ts3rwx This book is not a standard textbook on database techniques. This book was written extending and complementing preexisting educational videos (including this one). Video for my inverted classroom "Database Systems". The complete list of videos, slides, and additional material is (will be) available at http://datenbankenlernen.de Computer Science, Saarland University: Bachelor (in German): http://www.cs.uni-saarland.de/index.p... Master (in English): http://www.cs.uni-saarland.de/index.p... Ph.D./Grad School: http://gradschool.cs.uni-saarland.de/
Views: 11173 Prof. Dr. Jens Dittrich
In this tutorial you will learn advanced oracle tuning techniques for the optimizer.
Views: 459 DBA Pro
The query optimizer available in Greenplum Database (open source project name: GPORCA) is the industry’s first cost-based query optimizer for big data workloads. It can scale interactive and batch mode analytics to large datasets in the petabytes without degrading query performance and throughput. To test the query optimizer, please download the Greenplum Sandbox that is available on http://www.greenplum.org The open source project for the Pivotal Query Optimizer is at: https://github.com/greenplum-db/gporca
Views: 616 Greenplum Database
GPORCA is a modular, cost-based query optimizer based on 30 years of database research. Simply put, it takes in a parsed query statement and returns what it considers to be the fastest execution plan for the database. It combines the query with metadata (e.g. statistics, schemas, etc.) and information about the database cluster to generate the execution plan. GPORCA is portable and modular because it can work with more than one database and can easily support new database operators.
Views: 438 Greenplum Database
In this tutorial you will learn advanced oracle tuning techniques for the optimizer.
Views: 319 DBA Pro
In this free tutorial you will learn how to generate and read (interpret) an execution plan in Oracle Databases. See more FREE Oracle Tuning tutorials at http://skillbuilders.com/free-oracle-tutorials. Understanding what the Oracle Database does with your SQL is essential to tuning - and the execution plan is the key. Oracle Certified Master DBA John Watson will provide a brief introduction (4 minutes) - which includes John's tuning methodology, then demonstrate EXPLAIN PLAN, SQL*Plus AUTOTRACE and DBMS_XPLAN.DISPLAY_CURSOR. In the tutorial, John will teach you: - How to read an execution plan - Find the 1st step in the plan - Decipher the order of the steps in the plan - That EXPLAIN PLAN can be very misleading Prerequisites: To get the most from this tutorial, you should: 1 Know how to code SQL 2 Be familiar with SQL*Plus 3 Know - in very general terms - what an execution plan is. 4 Have a basic understanding of the Library Cache (this is where Oracle Database stores parsed SQL statements) 5 Have a basic understanding of the Cost Based Optimizer (this is the part of the database that parses your SQL, creates an execution plan. Hopefully the correct - most efficient - plan).
Views: 64320 SkillBuilders
Presto, an open source distributed SQL engine, is widely recognized for its low-latency queries, high concurrency, and native ability to query multiple data sources. Proven at scale in a variety of use cases at Facebook, Airbnb, Netflix, Uber, Twitter, Bloomberg, and FINRA, Presto experienced an unprecedented growth in popularity in both on-premises and cloud deployments in the last few years. Inspired by the increasingly complex SQL queries run by the Presto user community, engineers at Facebook and Starburst have recently focused on cost-based query optimization. In this talk we will present the initial design and implementation of the CBO, support for connector-provided statistics, estimating selectivity, and choosing efficient query plans. Then, our detailed experimental evaluation will illustrate the performance gains for several classes of queries achieved thanks to the optimizer. Finally, we will discuss our future work enhancing the initial CBO and present the general Presto roadmap for 2018 and beyond.
Views: 203 DataWorks Summit
Learn how to tune SQL - especially in Data Warehouse environments - with Constraints. Constraints provide critical information to the Cost Based Optimizer in the Oracle Database. Don't drop your constraints for query performance! In these 5 lessons, Oracle Certified Master DBA John Watson will demonstrate how constraints - unique, foreign key, not null - improve the execution plan and thus performance of SQL in an Oracle database. View all 5 lessons, free at http://www.skillbuilders.com/oracle-database-sql-tuning-with-constraints.
Views: 898 SkillBuilders
Connect with me or follow me at https://www.linkedin.com/in/durga0gadiraju https://www.facebook.com/itversity https://github.com/dgadiraju https://www.youtube.com/c/TechnologyMentor https://twitter.com/itversity
Views: 38532 itversity
Learn what's different about cost-based optimization in 11g Release 2. In this training Dave Anderson will guide you through some critical performance and SQL tuning enhancements and other surprises in the Oracle 11g cost-based optimizer. Through live demonstrations and lecture, you will learn about features such as adaptive cursor sharing, feedback-based optimization, new join techniques, DBMS_STATS enhancements, direct (PGA) reads for "large" full table scans, new hints and more. Presented at NYOUG by SkillBuilders' Founder and Oracle DBA, Dave Anderson.
Views: 6890 SkillBuilders
Learn how and why equivalent SQL statements can have a dramatic effect on performance. Certified Master J Watson demonstrates...See all our free Oracle Database tutorials at http://skillbuilders.com/free-oracle-tutorials. The Oracle Database cost-based optimizer (CBO) should recognize equivalent SQL statements and re-write them into the most efficient form. Well, nothing is perfect - not even Oracle Database. Sometimes the way you write your SQL can have a dramatic effect on performance. Presented by John Watson, Oracle Certified Master DBA. Some experience with SQL tuning is expected.
Views: 1906 SkillBuilders
This video clip, on the Oracle Query Optimizer, is taken from my www.pluralsight.com course "Optimizing SQL Queries in Oracle". Click here to learn more about this course: http://www.pluralsight.com/courses/optimizing-sql-queries-oracle?utm_source=youtube&utm_medium=video&utm_campaign=authordemo.
Views: 1960 sheepsqueezersYT
Hello friends in this video i'm just showing to you what is optimizer statistics into oracle,optimizer help to improve the performance of sql statements during execution period. #Optimizerplans #ORACLEWORLD
Views: 1538 Oracle World
SQL tuning is a very critical aspect of database performance tuning, and it requires years of frequent practice to master it. From the basic understanding of the Cost-Based Optimizer and the execution plans to more-advanced topics, there are concepts and practices that are fundamental to success. This session explains these fundamentals and how to monitor the entire database as well as individual SQL statements so that DBAs as well as developers can benefit. Developers can benefit from various free tools that will be presented, tools that can help a great deal in tackling SQL statements that seem to be performing poorly.
Views: 30 OracleAmericaLatina
Palestra sobre CBO, ENPO 2006 GPO GPO: GPO
Views: 139 Sergio Willians
Understanding Oracle Explain Plans-Explain Plan Syntax http://www.informit.com/promotions/webcast-understanding-oracle-explain-plans-140966 Watch an excerpt from experienced trainer Dan Hotka on Explain Plan Syntax. The full webcast shows you how to work with Explain Plans, then goes into considerable detail about exactly how Oracle arrives at an Explain Plan. Dan has a thorough understanding of how the Cost-Based Optimizer works, and shares that knowledge in this presentation. You will gain valuable insights as to how Oracle processes SQL, and many tips on how to improve SQL performance. This presentation is a must-see for anyone doing SQL tuning. http://www.informit.com/promotions/webcast-understanding-oracle-explain-plans-140966
Views: 582 InformIT
Apache Spark 2.2 ships with a state-of-art cost-based optimization framework that collects and leverages a variety of per-column data statistics (e.g., cardinality, number of distinct values, NULL values, max/min, avg/max length, etc.) to improve the quality of query execution plans. Leveraging these reliable statistics helps Spark to make better decisions in picking the most optimal query plan. Examples of these optimizations include selecting the correct build side in a hash-join, choosing the right join type (broadcast hash-join vs. shuffled hash-join) or adjusting a multi-way join order, among others. In this talk, we'll take a deep dive into Spark's cost based optimizer and discuss how we collect/store these statistics, the query optimizations it enables, and its performance impact on TPC-DS benchmark queries. Talk contributors: Zhenhua Wang (Huawei Technologies) and Wenchen Fan (Databricks) Session hashtag: #SFdd2
Views: 693 Databricks
Complete set of Video Lessons and Notes available only at http://www.studyyaar.com/index.php/module/28-query-processing-and-optimization Equivalence of Expressions, Equivalence Rules, Minimal set , Cost based optimizations http://www.studyyaar.com/index.php/module-video/watch/75-query-optimization-equivalence-of-expressions-and-cost-based-optimizations
Views: 5142 StudyYaar.com
Refer to http://www.sqlvideo.com/AllVideos/Seek-or-Scan---Cost-Based-Optimizer-in-SQL-Server for additional details. This SQL Server Video includes demos. You have the possibility to practice the hands-on examples while watching the video. SQLTest Tool (http://www.sqltest.org) simulates the hands-on labs for you, no registration necessary. For in-person workshops check out our schedule: http://www.sqlworkshops.com/schedule or contact us for a customized, onsite workshop http://www.sqlworkshops.com/contact To read some feedback from previous workshop participants, go to http://www.sqlworkshops.com/feedback
Views: 1564 SQLWorkshops
Themis Instructor John Mullins presents some details on interpreting Oracle Database Explain Plan output. For more information visit http://www.themisinc.com
Views: 45037 Themis Education
This is an example module from "Apache Spark™ Tuning and Best Practices," one of Databricks Academy’s 3-day Instructor-Led Training courses. See all the Instructor-Led courses Databricks Academy offers here: https://databricks.com/training/instructor-led-training In this video, Databricks Instructor Jacob Parr presents the Apache Spark Cost-Based Optimizer. He’ll first explain various optimizers and how they are used within Apache Spark, and then go into detail on the Cost-Based Optimizer, providing examples on actual data with code samples.
Views: 414 Databricks
https://bit.ly/2yLKfH5 Many customers want to limit licensing costs for Oracle and Microsoft software by limiting the number of licensed hosts. With vRealize Operations Host-Based Placement in 7.0 that can be as easy as setting your business intent with vSphere tags and then setting Workload Optimization to Automate. Learn how in this video.
Views: 345 VMware CloudManagement
Hello friends in this video i'm just showing to you what is optimizer statistics into oracle,optimizer help to improve the performance of sql statements during execution period. #Optimizerplans
Views: 1461 Oracle World
Information is key to the success of most organisations today and as such the more information you have about your customers the better business decisions you can make. T his means that databases (which hold the information) continue to grow in size and additional storage needs to be purchased on a regular basis to meet this growth. In this webcast we'll look at how Storage Optimization techniques can be used to reduce the Oracle database footprint and avoid the cost of new storage and/or reclaim some of that storage for use by other systems. We'll also take a look at some of the tools available to help you understand the likely benefit you will see from this reduced footprint and how we can work with you to look at the cost justification. Presented by Paul Dickinson and Horia Berca Copyright © 2014 Oracle and/or its affiliates. Oracle® is a registered trademark of Oracle and/or its affiliates. All rights reserved. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the "Materials"). The Materials are provided "as is" without any warranty of any kind, either express or implied, including without limitation warranties of merchantability, fitness for a particular purpose, and non-infringement.
Views: 2333 Oracle Learning Library
Learn how to use Oracle Virtual Private Database (VPD) to secure your data. See all lessons at http://skillbuilders.com/12c-TSDP-Tutorial First, Virtual Private Database. What is it? Answer: It's a declarative technique for enforcing security. Typically, it's based on user application context. That was the first limitation of 8i. Backed with 8i context were session-specific. The context variables were sort in the PGA. That means you simply couldn't use this in a web environment. However, global context, with use of 9i, Virtual Private Database became usable for web applications with connection pooling. But whether you're using it to client server or on a three tier environment, it's declarative technique rather than a programmatic technique. I can go behind the back of the application as a DBA and generate predicates to get appended to all SQLs. Select, insert, update, delete the end users. Issue the SQL and I will generate a predicate that filters the access. It's impossible to bypass. All users will have predicates appended to all their code. It doesn't matter whether they're using SQL Plus, Discoverer, Business Objects, Crystal Reports, eBusiness Suites, whatever they're using, I as DBA can filter their access to roles. The end result, different users see different data sets while running the same code and accessing the same tables, hence, the name Virtual Private Database. Different users will think they're seeing a totally different set of tables, but in fact it's one column database, one set of tables hosting a single application shared by all the users. The mechanism - you can apply VPD to tables, to views, to synonyms, and a declarative technique. Once the technique you write a function that generates the predicate, you associate the function with the table. From then on whenever the user issue SQL against that table the Cost-Based Optimizer rewrites the code. The Cost-Based Optimizer rewrites the user SQL to call the function to generate the predicate. The predicate is then appended to the SQL and it's the modified SQL that is parsed and executed. Let's see how this actually works. I'm working here by the way. This is just a perfectly normal 12.1 database. But what I'm going to demonstrate at this point will in fact work on 11g as well. I'm not going to be using any of the 12c enhancements to VPD for this little demonstration. I'll work in the scott schema. What do we have to do? We write a function to generate the predicate string. Create function. And I'll my function vpdf. The function must conform to a fixed specification. It must take two varchar arguments. One of the arguments is intended to be the schema to which the function will be applied. The other argument is the object within the schema to which the function will be applied. And it must return a varchar2, which is the generated predicate. Let's begin. Within this function you can do just about anything. There are some limitations. It's impossible for the function to query or update the table against which the VPD calls here is going to be applied or we violate the rules of purity that we come across so often with PL/SQL. But apart from that, it can do just about anything - including a number of dreadful side effects can be introduced by writing VPD functions. I'm going to do a very simple one on this one. I'll shall simple return immediately a predicate. What predicate? Ename, which is the ename column from this scott.emp table. Ename should equal sys_context. I'll go to the local context, my user end context, and just extract session user. We can see that John has tested this earlier today and therefore the object already was suggesting and he's just dropping it now and cleaning up the recreate. I hope you realize these really are live demonstrations. I'm not just running scripts. I've created a function. What's that function going to do? It's going to return this value here. Test it. Always test it and see what it's actually going to do. I'll select vpdf. I have to give it a couple of arguments. This one will do for now.
Views: 4479 SkillBuilders
Inventor of cost-based SQL optimizer, IBM fellow Pat Selinger, introduces the topic of Big SQL. Apply to join the IBM Big SQL Technology Preview at: http://bigsql.imdemocloud.com/
Views: 183 SQLforHadoop
Exclusive range of revision notes & video lessons available on our site |||--- ClicK LINK To ViSiT ---||| http://www.studyyaar.com/index.php/module/28-query-processing-and-optimization This video clip is part of module available at http://www.studyyaar.com/index.php/learning-program/7-database-management-system-part-2
Views: 81684 StudyYaar.com
Jonathan Lewis and Maria Colgan at OOW 2013
Views: 114 Helio Dias
Held on May 1 2018 If you are running an Oracle Database proof of concept or benchmark focusing on performance, what optimizer settings should you use? What statistics do you need to gather and how should you gather them? In this session Nigel Bayliss shows how you can get consistent results and avoid burning time chasing problems. Covers Oracle Database 12c Release 1 onwards. 3:55 The Adaptive Optimizer (12.1) 8:44 The Mechanics of Adaption 12:03 Adaptive Optimizer Settings 16:18 Recommended Defaults 18:53 12.1 Proof of Concept Recommendations 21:26 12.2+ Proof of Concept Recommendations 34:35 Regathering Statistics 42:24 Dynamic Sampling and Parallel 44:55 More General Recommendations AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists. https://asktom.oracle.com/ https://developer.oracle.com/ https://cloud.oracle.com/en_US/tryit music: bensound.com
Views: 191 Oracle Developers
The better the information that Oracle and SQL Server have about the data in a database, the better choices they can make on how to execute the SQL. Statistics are Oracle's and SQL Server's chief source of information. If this information is out of date, performance of queries will suffer. In their third live 'Oracle vs. SQL Server' discussion, Jonathan Lewis (Oracle Ace Director, OakTable Network) and Grant Fritchey (Microsoft SQL Server MVP) will look at statistics in Oracle and SQL Server. Do Oracle and SQL Server gather the same information? What does each optimizer use this information for? And how can Oracle and SQL Server administrators override the defaults for better (or worse) performance? These are just some of the questions that Jonathan and Grant will try to answer in another not-to-be-missed session. As before, this will be a live discussion with limited supporting slides, and will conclude with a Q+A session with Jonathan and Grant. Be prepared for a lively exchange that will not only entertain, but will teach you key concepts on Oracle and SQL Server. For our complete archive please go to http://www.red-gate.com/oracle-webinars
Views: 2365 Redgate Videos
Upper level undergraduate course in Database Systems, introduces basic concepts of data modeling, database querying and database system internals. Course notes at: http://www.cs.rpi.edu/~sibel/csci4380/spring2016/course_notes.html
Views: 5023 Sibel Adali
Understanding Explain Plans is an excerpt from Oracle SQL Performance Tuning for Developers: http://www.informit.com/store/oracle-sql-performance-tuning-for-developers-livelessons-9780134117027 6+ Hours of Video Instruction The focus of Oracle SQL Performance Tuning for Developers LiveLessons is to illustrate coding techniques that ensure a consistent response time between instances and releases of the Oracle database. This course works closely with performance tuning of actual SQL statements. Description In this video training, Dan Hotka starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course then goes in-depth on understanding and controlling the Explain Plan, which is how and in what order Oracle retrieves data. The discussion includes considerable detail, with SQL examples, on how the optimizers--both rule-based and cost-based, but mostly cost-based--make their decisions. Students will work with a variety of SQL statements, reviewing Explain Plans and making changes to make these SQL statements perform better. Lectures include index design, using hints and coding style to control the Explain Plans, and how to use useful tools such as index monitoring, SQL Trace, and the PL/SQL profiler. This LiveLessons course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used. This course includes Oracle10g, Oracle11g, and Oracle12c SQL tuning topics. Skill Level Intermediate Learn How To Read and understand Explain Plan content Review an Explain Plan and tell quickly if this is a good plan Understand a good index column candidate from a not-so-good candidate Quickly tell the likelihood if your SQL will use an existing index Use coding and a variety of Hints (directives) that can produce better performing SQL Execute and interpret SQL trace output Who Should Take This Course Oracle programmers Oracle database administrators who need additional training on SQL tuning Course Requirements Working knowledge of the SQL query language http://www.informit.com/store/oracle-sql-performance-tuning-for-developers-livelessons-9780134117027
Views: 3293 LiveLessons
Learn Oracle Performance Tuning with Real Time Activities. Watch this video to see how HARD PARSING issue can be rectified with simple resolution. This course will load the system and show you a real time approach to resolve performance issues on your environment. Get more details at : www.orskl.com/training Queries? WhatsApp: +919951696808 Get your hands dirty now and become performance tuning expert.
Views: 9629 OrSkl Academy