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: 66286 SkillBuilders
Learn how to generate execution plan in oracle database in many ways
Views: 3611 OrSkl Academy
Learn an Oracle Database 12c new performance feature - Adaptive SQL Plans. During execution, Oracle Database can switch the SQL to a new plan. A very powerful corrective measure! But if you don't know about it , how can you possibly tune SQL in Oracle Database 12c? Time to learn 12c!
Views: 5569 SkillBuilders
Maciej Pilecki, Project Botticelli Execution plan reuse is one of the most important aspects of building well-performing and scalable SQL Server solutions. But it's also the one that is often overlooked during the design phase and becomes very difficult to troubleshoot later. In this session we will discuss in details the following aspects of SQL Server execution plans: compilation, re-compilation, parameterization, caching, reuse and aging. You will leave this session with full understanding of how to improve your server's performance by increasing execution plan reuse (and how to avoid reuse in case this is necessary).
Views: 688 PASStv
SQL Server Unlocked Series- Query Execution Plans In this video, we will be discussing query execution plans and how to use them properly. In this video, Morelan starts out with an analogy relating SQL Server to your kid. I know this sounds pretty far-fetched, but he has a point, so give it a chance. He goes on to describe how you want SQL Server to perform to the best of its ability, same as you would want your kids to do, hence why we use query execution plans. Additionally, If you lose something such as your keys, you probably want a tool that shows you how to find that item, if you set up your SQL server right, this is just the tool- not to find the keys but to find data. All-in-all SQL server can save you time if you use it right and you know how to use it right. This mini-series is a good starter on how to do so. Have a look at the video, and if you want to learn more, take our class Developer 2012 Volume 3 Video 9.1. See more at: http://www.joes2pros.com/joes2pros/courses Full Blog: http://joes2prosblog.social27.com/
Views: 4715 Joes2Pros SQL Trainings
Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. 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 or merchantability, fitness for a particular purpose, and non-infringement.
Views: 15446 Oracle Learning Library
Oracle Explain Plans-Explain Plan Hints and Reading Plans http://www.informit.com/promotions/webcast-understanding-oracle-explain-plans-140966 Watch an excerpt from trainer Dan Hotka on Explain Plan Hints and Reading Plans. The full video 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: 401 InformIT
#sangam18 #aioug #oracle Sangam18 At A Glance Sangam, the largest independent Oracle user’s group conference will feature over 150+ in-depth technical sessions presented by expert speakers from inside and outside of Oracle. Over 1000+ database administrators, developers and architects professionals are expected at the conference from December 7th-8th 2018 at The Oterra, Bengaluru. This is our moment of glory and time for you to join us ! Arup Nanda has been an Oracle DBA for over 20 years with experience spanning all aspects from Modeling to performance tuning and Exadata. He speaks frequently, has authored about 300 articles, co-authored 5 books, blogs at arup.blogspot.com and mentors new and seasoned DBAs. Sessions Database Security in a Single Day in the Cloud This session attendees will learn some simple but highly effective steps they can implement in just one day that will eliminate a lot of vulnerability and make the database secure against 90% of the common attacks. Should I Drop Indexes in Exadata Cloud In this session you will learn what storage indexes are in Exadata, how they work (and don’t), how they work along with traditional indexes, how to gradually evaluate the effect of indexes instead of a sudden change, how to compensate for lack of indexes and read execution plans. In this session you will learn how the blocks get into the buffer cache, what buffer cache chains are, how they are managed, how cache buffer chain latches are used and when they cause significant performance issue, how DBWR and checkpoint processes work, what causes the buffers to be written back to the disk, the role of commits and rollbacks in buffer movement between buffer cache and disks and much more.
Views: 199 AI OUG
How to Solve Row Lock Contention in Oracle Database - Performance Tuning - Oracle DBA Solve Row Lock Contention & Long Running Query in Oracle Database - Performance Tuning Oracle DBA - Performance Tuning Row Lock Contention Please Like, Comment, Subscribe and Share... Boxcut Media.
Views: 7895 BoxCut Media
Learn how to tune Oracle SQL statements! You'll need to know how to read an execution plan. See all FREE lessons http://www.skillbuilders.com/sql-tuning-for-beginners-free-tutorial
Views: 807 SkillBuilders
Ever open a huge query plan to track down a performance issue and instead find yourself overwhelmed by the sheer number of objects on the screen? How do you figure out which part of the plan.
Views: 4614 Fidela Aretha
Check Out Our Performance Tuning Blog - http://blog.pragmaticworks.com/topic/performance-tuning This session will demonstrate how SQL Server execution plans can be used to identify problems with the database design, or the TSQL code, and address those problems. The session takes the user through various common issues such as poor or missing indexes, badly written code and generally bad query performance, demonstrating how to identify the issues involved using execution plans. The session will then demonstrate different methods for addressing the issues and show how the fixed query’s execution plans differ. Multiple methods for accessing execution plans including graphical, DMV’s, and trace events are demonstrated. All this is meant to lay a foundation for a general troubleshooting approach to empower the attendee to make their own queries run faster and more consistently.
Views: 222 Pragmatic Works
Running an EXPLAIN PLAN to generate EXECUTION PLAN in oracle database is possible with PLAN_TABLE. But this video will give you much more insights of what is this PLAN_TABLE. Learn how is PLAN_TABLE called as global temporary table. Need Oracle database training? WhatsApp to +919951696808 visit www.orskl.com/training
Views: 3143 OrSkl Academy
Kevin Kline goes over how to do basic query tuning. Then he dives into 3 real world query tuning examples that are taken from Answers.SQLPerformance.com. The examples are SSMS vs. Plan Explorer, Massive MERGE, and the Case of the Log Shipping Mystery. Find more information about our products: Plan Explorer: http://www.sqlsentry.com/plan-explorer
Views: 19366 SentryOne
This video is part of LearnItFirst's Transact-SQL Programming: SQL Server 2008/R2 course. More information on this video and course is available here: http://www.learnitfirst.com/Course161 Many of the ideas in this video will be revisited in chapters 5 and 11, so expect mostly basics here - details will come in later videos. Using the query from the last video, trainer Scott Whigham discusses execution plans. How can you uncover the steps SQL Server went through to arrive at the results? What is SHOWPLAN? Highlights from this video: - What does the execution plan tell us? - How can you look at the execution plan? - Estimated Execution Plan vs. Actual Execution Plan - How does SQL Server choose indexes? - Analyzing and understanding graphical execution plans - Determining costs of the components of execution - Using SHOWPLAN and much more...
Views: 86467 LearnItFirst.com
Oracle 11g SQL Plan Management, a.k.a. Baselining, will solve the major issues of change control. We can give you an absolute guarantee - if you configure this as part of your upgrade - that SQL statement execution (performance) will never regress. You may also like our new and free Oracle 12c video tutorials, https://www.skillbuilders.com/free-oracle-database-tutorials . This tutorial is particularly relevant: it provides a review of baselining then reveals 12c performance features https://www.skillbuilders.com/free-oracle-database-tutorials/oracle-12c-sql-performance-features-agenda-session/
Views: 10280 SkillBuilders
Optimizer needs statistics to create an execution plan for each query.
Views: 58 ORACLE-DBA Workshops
The third part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we take a look at the Cross-Session PL/SQL Function Result Cache introduced in Oracle 11g. For more information see: https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#oracle-11g-caching https://oracle-base.com/articles/11g/cross-session-plsql-function-result-cache-11gr1 Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Auckland Oracle DBAs Meetup : Website: http://www.meetup.com/Auckland-Oracle-DBAs-Meetup/ Sponsors: - DBvisit: http://www.dbvisit.com/ - Datacom: http://www.datacom.co.nz/ - Oracle New Zealand: http://www.oracle.com/ - Pythian: http://www.pythian.com/ - Enterprise IT: http://www.enterpriseit.co.nz/ Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 3095 ORACLE-BASE.com
Understanding that plans can age out of cache is easy enough. Older plans aren't used, so they get removed. However, a bunch of other things can cause plans to get removed from cache. Once a plan has been removed, it has to be compiled again, which can be expensive. If you're seeing plan cache thrashing, you're going to need a way to understand why plans are being removed from cache. Watch this video to learn how. #sqlserver #dataplatform #extendedevents
Views: 71 Grant Fritchey
Oracle Hints Tutorial for improving performance APPEND PARALLEL JOIN INDEX NO_INDEX SELECT /*+ FIRST_ROWS(10) */ * FROM emp WHERE deptno = 10; SELECT /*+ ALL_ROWS */ * FROM emp WHERE deptno = 10; SELECT /*+ NO_INDEX(emp emp_dept_idx) */ * FROM emp, dept WHERE emp.deptno = dept.deptno; SELECT /*+ INDEX(e,emp_dept_idx) */ * FROM emp e WHERE e.deptno = 10; -- SELECT /*+ INDEX(scott.emp,emp_dept_idx) */ * FROM scott.emp; SELECT /*+ AND_EQUAL(e,emp_dept_idx) */ * FROM emp e; SELECT /*+ INDEX_JOIN(e,emp_dept_idx) */ * FROM emp e; SELECT /*+ PARALLEL_INDEX(e,emp_dept_idx , 8) */ * FROM emp e; SELECT /*+ LEADING (dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno; SELECT /*+ PARALLEL(8) CACHE (e) FULL (e) */ * FROM emp e ; SELECT /*+ PARALLEL FULL (e) */ * FROM emp e ; SELECT /*+ PARALLEL USE_MERGE (emp dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno; -- SORT Merge Join SELECT /*+ PARALLEL USE_HASH (emp dept) */ * FROM emp, dept WHERE emp.deptno = dept.deptno; -- Hash Join SELECT /*+ PARALLEL */ * FROM emp e ; INSERT /*+ APPEND */ INTO mytmp select /*+ CACHE (e) */ *from emp e; commit;
Views: 8499 TechLake
Kimberly will discuss the impact on caching / performance of different methods of statement execution. Briefly, she will also compare / contrast this with stored procedures. In the end, you’ll know the best way to request data so that stable requests are cached and unstable requests are not (giving you the most optimal performance). Kimberly has been a Microsoft SQL Server MVP and a Microsoft Regional Director for over 10 years. She's an author, consultant, and top-rated speaker, having spoken at PASS, TechEd, SQLintersection, and numerous other events around the world. She has over 20 years of experience designing and tuning SQL Server solutions with a focus on optimizing procedural code, indexing strategies, statistics, and partitioning. When she's not tuning SQL servers, she's diving and photographing all sorts of underwater critters such as frogfish with her husband, Paul S. Randal. Hosted by Steve Cantrell DBAFundamentals
Views: 964 DBAFundamentals
Google Tech Talks June 27, 2007 ABSTRACT Data Grid-based infrastructures are being developed, deployed and used to achieve unlimited application scalability and continuous availability. This presentation focuses on Oracle Coherence Data Grid and how it's capabilities, which includes coherent in-memory caching, dynamic data partitioning, even processing, parallel query and process execution and on how these capabilities enable achievement of these goals and more. In this presentation we will address: · How Coherence capabilities function, such as coherent in-memory caching, dynamic data partitioning, and parallel query and process execution, and how they enable a new generation of grid...
Views: 35202 GoogleTechTalks
Oracle ACE Alfredo Krieg offers advice on tools for improving database performance in this 2 Minute Tech Tip recorded at the 2016 Great Lakes Oracle Conference in Cleveland, OH.
Views: 330 ArchBeat Archive
In this session Tanel will walk you through troubleshooting a yet another complex performance issue that he has faced in past. Again, the initial symptoms point to a different direction than the actual root cause, so a systematic approach was needed.
Views: 3876 Tanel Poder
Ross Leishman of DWS Ltd presents the principles of Understanding Indexes for SQL Tuning. The presentation includes an entertaining demonstration of Indexes featuring DWS Alumnus Jordan Thomas as a "Buffer Cache". DWS Ltd is a leading publicly listed Australian IT Services company, providing services to blue chip organisations since 1991. With a business philosophy based upon integrity, reliability and professional service delivery, DWS provides end to end IT solutions. www.dws.com.au
Views: 45453 DWS Ltd
This clip will show you how to check each Table size, Number of records, Disk usage in SQL Database. You can see how to write the query to get details of all tables in terms of size, records, space utilization and free space on disk for tables. http://daynamicsaxaptatutorials.blogspot.com This is axapta ERP blog for Technical and functional fields and includes Microsoft Dynamics Axapta tutorials and Dynamics Axapta Coverage. This blog also contains x++ code help for Ax developer and solution of technical and functional daily issues. This blog is specific for Microsoft dynamics programming. Enterprise portal, SharePoint services, business connectors and Enterprise Resource Planning applications and sql database.It will help to get Microsoft Business Solutions.
Views: 952 Santosh kumar Singh
Smart Scan is a wonderful capability, but you don't always get it. It's impossible for many execution plans, and this is a major restriction. If you think about what a Smart Scan actually does, it delivers individual columns, individual rows back to the instance. Now, a buffer cache can accept only blocks. Therefore, Smart Scan cannot possibly put those columns of rows into the buffer cache. It's simply not formatted appropriately. So, a Smart Scan has to return values directly into the session's PGA or, to put it another way, the only access method that can use Smart Scan is direct read. Well, what access methods can use direct read? There are only two, which are table full scan and index fast full scan. Any other access method, typically index range scan, table access by row ID, cannot use a Smart Scan. The second major issue, there are strict limitations of the type of objects that can be accessed through Smart Scan. It really is only heap tables. You can't use indexes. You can't use clusters. You can't use IOTs. Heap tables only. Perhaps hardest to track down and giving sometimes very erratic results is that Smart Scan can be interrupted by various conditions. You've met all the requirements for Smart Scan, directory and so on, got the right execution plan. The Smart Scan starts and then hits something that causes a problem. Issues that we know cause problems are, for instance, read consistency, also delayed block cleanout, change rows. Any of those issues and a few others mean that the storage tier will have to interrupt its Smart Scan, deliver complete blocks into that buffer cache, let your session then do what is necessary to the block, and only then can the Smart Scan proceed. Now, in order to maximize the use of Smart Scan, there may be quite a lot of work. Very often, you'll have to adjust your index structures. Making them invisible is a nice technique there. There are many, many, many parameters that can influence the likelihood of achieving a Smart Scan, and almost inevitably you're going to be rewriting a lot of hint SQL and putting hints in it to get the correct execution plans that can enable a Smart Scan to occur. This is all because of one fundamental problem; the optimizer is not in any way aware of the Exadata. The optimizer develops an execution plan in exactly the way it would without the Exadata storage. The use of Smart Scan, the awareness of Exadata comes at the next level down. The optimizer develops the plan through a normal pass and then passes it through to the SQL execution engine, and it's the SQL execution engine that determines, on a case-by-case basis, whether to use the Smart Scan. This means that you might develop a plan and execute the statement 50 times. Forty-nine times, you get a Smart Scan. The 50th time, for whatever reason, the SQL execution engine decides not to. This can result in somewhat erratic performance.
Views: 1695 SkillBuilders
This VTS session features learn about the SQL Tuning Advisor feature in Oracle Database, and how to use it effectively and interpret the results for superior performance in the context of the real world presented by a real-life DBA. Presented by Oracle Ace Director Arup Nanda.
Views: 8359 Database Community
Watch this full course here: http://bit.ly/1z97z87 When you need to query the SQL Server database from your application, what's the best way to do it? It might be easy to write dynamic SQL generated in your code, but stored procedures offer significant benefits related to code encapsulation, security, and performance. In this video excerpt from Developing and Deploying SQL Server ISV Applications, Erin Stellato gives you a close look at one of the performance benefits of stored procedures related to the plan cache. In the full course Erin covers the numerous choices ISVs must consider during application development, going beyond topics related to code, security, and design, and also discussing testing, recommendations, and upgrades. -~-~~-~~~-~~-~- Push your limits. Expand your potential. Smarter than yesterday- https://www.youtube.com/watch?v=k2s77i9zTek -~-~~-~~~-~~-~-
Views: 4041 Pluralsight
Statement Execution and the Plan Cache by Kimberly L Tripp
Views: 94 PASS Data Architecture Virtual Group
Dave Anderson of SkillBuilders.com/11g demonstrates how to improve query performance with the Oracle 11g Result Cache.
Views: 2504 SkillBuilders
Want to know what's so smart about Oracle Exadata Smart Flash Cache? This three minute video explains how Oracle Exadata Smart Flash Cache helps solve the random I/O bottleneck challenge and delivers extreme performance for consolidated database applications.
Views: 19190 Oracle Video
Presented by Steven Feuerstein, Oracle Corporation The function result cache, added in Oracle Database 11g, offers developers a way to dramatically reduce the amount of time it takes to retrieve data requested repeatedly by database sessions (specifically, when the same rows of a table are fetched much more frequently than they are changed). This cache is stored in the SGA, shared across all sessions in the instance, and automatically purged of "dirty data" by the Oracle Database. Every application running on 11.1 or higher should be taking advantage of this feature. Best of all, there is minimal impact on your code to start using this feature.
Views: 1083 ODTUG
Watch Oracle Master John Watson demonstrate why not all equivalent SQL statements are created equal. John demonstrates how SQL statements that produce equivalent result sets can have radically different response times, why this happens and common solutions. All five new video tutorials are available at SkillBuilders.com/Oracle-Tutorials 1. Introduction including the First Demonstration 2. SubQuery Factoring 3. Star Transformations 4. Aggregations 5. Summary Some experience analyzing Oracle execution plans is required to get the most from this tutorial.
Views: 6479 SkillBuilders
For complete professional training visit at: http://www.bisptrainings.com/course/Oracle-Fundamentals-and-PL-SQL-for-beginners Follow us on Facebook: https://www.facebook.com/bisptrainings/ Follow us on Twitter: https://twitter.com/bisptrainings Email: [email protected] Call us: +91 975-275-3753 or +1 386-279-6856
Views: 23185 Amit Sharma
This video shows how to trace parallel executions. Copyright © 2012 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: 3043 Oracle Learning Library
Understanding the different join types and where they apply is an important part of reading and understanding execution plans in SQL Server. I introduce the four joins; nested loops, hash, merge and adaptive. For more on adaptive joins, here's another video I made: https://youtu.be/Oxj3HilCnko #sqlserver #performancetuning #executionplan -~-~~-~~~-~~-~- Please watch: "Extended Events Live Data Window " https://www.youtube.com/watch?v=UDtXjewvqmM -~-~~-~~~-~~-~-
Views: 250 Grant Fritchey
Тема Доклада: Исполнительное ядро (Execution Engine) SQL Server Структура доклада: • Архитектура и прохождение запросов (connection, session, task, workers, thread) • Упрощение (simplification), алгебраизация (algebraization), оптимизация • Компиляция • Исполнение Докладчик: Александр Каленик Alexander Kalenik is Microsoft CIS technology lead, Senior Premier Field Engineer for areas of SQL Server and Windows Cluster. He is working for Microsoft almost 9 year. He has a big experience in SQL Server from 1991. He is author of books about SQL Server and author of 2 blogs on TechNet for areas SQL Server and Windows Cluster. Alexander is PHD in area of Computer Science.
Views: 45 Ukrainian Data Community Kyiv
Learn in depth about stored procedure in oracle,usage of SP in Database. Creating Stored Procedure using in, out ,in/out parameter and handling exception inside with example. Stored Procedure in Oracle, Stored Procedure in PL/SQL, Oracle Stored Procedure, PL/SQL Stored Procedure, What is Stored Procedure in pl/sql, How to use Stored Procedure in pl/sql, How to write a Stored Procedure in oracle, How to create Stored Procedure in pl/sql, Parameterized stored procedure in pl/sql, Benefit of using stored procedure in oracle, Advantages of Using Stored Procedures: Maintainability (because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier) Can be tested independent of the application Isolation of Business Rules,(Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application) Speed / Optimization ( Stored procedures are cached on the server Execution plans for the process are easily reviewable without having to run the application) Utilization of Set-based Processing (The power of SQL is its ability to quickly and efficiently perform set-based processing on large amounts of data; the coding equivalent is usually iterative looping, which is generally much slower) Security ( Limit direct access to tables via defined roles in the database, provide an “interface” to the underlying data structure so that all implementation and even the data itself is shielded. Securing just the data and the code that accesses it is easier than applying that security within the application code itself ). Drawbacks to Using Stored Procedures: Limited Coding Functionality Stored procedure code is not as robust as app code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive) Portability issue for complex and large Stored Procedure. Testing :Any data errors in handling Stored Procedures are not generated until runtime Cost :Stored Procedure development could potentially require a dedicated database developer. Some businesses will not allow developers access to the database at all, requiring instead a separate DBA. Alternatives to Stored Procedures: In-line or Parameterized Queries (These are written within the application code itself) Object Relational Mapping (ORM) (Provides an abstraction to the database without having to manually write data ) Linkedin: https://www.linkedin.com/in/aditya-kumar-roy-b3673368/ Facebook: https://www.facebook.com/SpecializeAutomation/
Views: 4970 Specialize Automation