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: 7441 TechLake
The Video Explains when should you create indexes. The difference between Simple and composite Index, Relevance of order in composite indexes and Index Scan Methods in detail. 1.Index Unique scan 2.Index Range Scan 3. Index Skip Scan 4. Fast full Index Scan 5. Full Index Scan If you have any questions just drop in a comment
Views: 7686 Tech Coach
"Why isn't Oracle using my index?!" is a common question people have when tuning SQL queries. In this episode Chris compares two methods for finding all the red candies from party bags he's prepared. He shows how these are like a full table scan and an index range scan. He goes on to compare the performance of these two approaches. He shows when a full table scan becomes more efficient than an index range scan and vice versa. ============================ The Magic of SQL with Chris Saxon 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: 8185 The Magic of SQL
Making Smart Scan Work - Demo I'll go through a simple example of the kind of thing we need to think about when trying to achieve Smart Scan. Let me just enable tracing, run a simple query, select and * from *. Now, there's my query. The result set comes back, and how is it executed? An index full scan with the PK* index. The optimizer is intelligent enough to know that my query can be satisfied purely by reading the index and therefore it didn't have to go to the table at all. It looks very good. Well, it wasn't. Index full scans are an operation that cannot be offloaded. So, to execute that statement, every block of that index was delivered into the buffer cache of my own database instance, and the compute node then has to do the work of extracting those values. What can we do about it? One solution would be to hint the code. I select and demand an index fast full scan, and now we see the magic word "storage." An index fast full scan is offloadable, because an index fast full scan can do direct reads. An alternative approach? Well, you might not want to hint many, many thousands of lines of code. An alternative approach would be to do it through DDL. For example, take that index, make it invisible. Now run my statements without any hint at all, table access storage full, and that was offloaded. Having made the index invisible, Oracle has no option but a full-table scan, and a full-table scan is offloadable. Now, this means I have three options of this very simple example. I can let the optimizer get on with what it wants to do, and then I use an index that I'm doing block serving into the buffer cache. I can hint the code, index fast full scan. That's probably the best option for performance, but it's also the most work. In this intervening case, I make the index invisible so that it will still be used of course for enforcing the primary key constraints, but it will not be visible to the optimizer and therefore influence the optimizer towards using plans that can be offloaded the Smart Scan. Making the choice between this and influencing the optimizer in the correct direction is an extremely difficult job, and it is not always easy to determine what is going to be the best solution.
Views: 3341 SkillBuilders
Storing dates as numbers can cause unexpected problems. In this video Chris looks at one possible issue: inconsistent query performance. He then shows methods you can use to improve performance, including function-based indexes and histograms. ============================ The Magic of SQL with Chris Saxon 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: 7244 The Magic of SQL
In this tutorial, you'll learn how to compare queries to know the better performance query..
Views: 97866 radhikaravikumar
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: 65082 SkillBuilders
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: 12027 Tech Query Pond
Nested Loops Joins are one of the primary physical join operators used by SQL Server. In today's episode we discuss how Nested Loops Joins work, as well as how to use that knowledge to troubleshoot a slow performing query's execution plan. Subscribe and turn on notifications to never miss a weekly video: https://www.youtube.com/c/bertwagner?sub_confirmation=1 Related blog post with code snippets: https://bertwagner.com/2018/12/11/visualizing-nested-loops-joins-and-understanding-their-implications/ Be sure to check out part 2 on merge joins: https://youtu.be/IFUB8iw46RI And part 3 on hash match joins: https://youtu.be/59C8c7p_hII Additional excellent blog posts that go further in-depth: https://blogs.msdn.microsoft.com/craigfr/2006/07/26/nested-loops-join/ https://sqlserverfast.com/epr/nested-loops/ Follow me on Twitter: https://twitter.com/bertwagner
Views: 622 Bert Wagner
As the name says, in this hacking session I'll demo how full table scans (and full segment scans) work and related stuff too, like direct path read checkpoints and ORA-8103 errors. It's an old video from 2011 so not with the best recording resolution, but should still be fun. As direct path reads go, some things have changed and should be clarified, perhaps the best explanation is here: https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-1 https://blogs.oracle.com/smartscan-deep-dive/when-bloggers-get-it-wrong-part-2
Views: 628 Tanel Poder
“Why isn’t my query using an index?” is a common question people have when tuning SQL. This session explores the factors that influence the optimizer’s decision to answer this question. It does so by comparing fetching rows from a database table to finding all the red M&Ms a packet, and contrasts using an index range scan and a full table scan. It also introduces the concepts of blocks and the clustering factor. The session offers a discussion of how these affect the optimizer's calculations, and includes a demo of how these concepts work in practice using real SQL queries. This session is intended for developers who want to learn the basics of how the optimizer chooses between an index range or full table scan. Speaker: Chris Saxon
Views: 284 Oracle Developers
This is the 2nd video from " Partitioning in Oracle " series, It explains how oracle stores and manages data. What is single Block IO and Multi Block IO ? Why full table scan is better than index access in few cases. The video is very elaborate, I have tried my level best to keep it as simple as possible
Views: 10208 Tech Coach
This is the first lecture of the Oracle Database Troubleshooting and Tuning Class. The Full course is available on Udemy at https://www.udemy.com/oracle-database-troubleshooting-and-tuning You can use Coupon Code YOUTUBETT for discount Our Upcoming Online Course Schedule is available in the url below https://docs.google.com/spreadsheets/d/1qKpKf32Zn_SSvbeDblv2UCjvtHIS1ad2_VXHh2m08yY/edit#gid=0 Reach us at [email protected]
Views: 1892 Ramkumar Swaminathan
Learn SQL Tuning! Tune full table scans! Part 1 of 5 videos in this series. See all 5 tutorials, free, at http://www.skillbuilders.com/tuning-long-full-table-scans Number Eight in the "Performance tuning Guide, Top Ten Mistakes Found in Oracle Systems" Long Full Table Scans is described as follows: "Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable." Actually, there are many cases where the full table scan is your friend. But whether they are good or bad for the performance of the SQL that invokes them, there may be implications (for better or for worse) for performance of other statements. This is particularly true in 11.2.x, where direct reads are possible for serial scans, and indirect reads are possible for parallel scans. Do you trust the optimizer? The change in behaviour in recent releases may need some investigation, and revisiting older code. As always with a SkillBuilders Tutorial, we shall illustrate the issues and solutions with live demonstrations using release 11g (126.96.36.199). Audience: Operating System Administrators, Storage Administrators, Oracle Administrators and Management responsible for Storage and / or Oracle Databases.
Views: 2944 SkillBuilders
See all 5 tutorials, free, at SkillBuilders.com/OracleTuningFullTableScans Number Eight in the "Performance tuning Guide, Top Ten Mistakes Found in Oracle Systems" Long Full Table Scans is described as follows: "Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable." Actually, there are many cases where the full table scan is your friend. But whether they are good or bad for the performance of the SQL that invokes them, there may be implications (for better or for worse) for performance of other statements. This is particularly true in 11.2.x, where direct reads are possible for serial scans, and indirect reads are possible for parallel scans. Do you trust the optimizer? The change in behaviour in recent releases may need some investigation, and revisiting older code. As always with a SkillBuilders Tutorial, we shall illustrate the issues and solutions with live demonstrations using release 11g (188.8.131.52). Audience: Operating System Administrators, Storage Administrators, Oracle Administrators and Management responsible for Storage and / or Oracle Databases.
Views: 4556 SkillBuilders
See all 5 tutorials, free, at SkillBuilders.com/OracleTuningFullTableScans. In summary, serial full table scans were always indirect before 11.2, parallel scans always direct before 11.2. These are the two critical parameters. That hidden parameter _serial_direct_read, remember, defaults to auto so your application may start behaving very differently as you upgrade from 11.2.01 or 11.2.02. Parallel_degree_policy, that defaults to manual. But again, it becomes an option when you go to 11.2 to enable the automatic facility which allows not only automatic tuning to the degree of parallelism but also enables the ability to make indirect reads when doing a full table scan.
Views: 1598 SkillBuilders
In this tutorial, OCM John Watson will - via demonstrations - debunk these myths: Myth #1: Oracle Database does not index NULL Myth #2: A search that includes wildcards can't use an index if the wildcard precedes the string. Myth #3: Oracle will not use a function-based index unless the FBI is coded in the predicate. Myth #4: Indexes always help. The more indexes the better. See http://skillbuilders.com/free-oracle-tutorials for gigabytes of free Oracle video tutorials.
Views: 16234 SkillBuilders
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: 1674 SkillBuilders
When is a Full Table Scan faster than an Index Scan? Watch Ross and Jordan act out an Oracle database reading and caching data via both methods, explaining the costs and benefits in simple and easy to understand terms. The demonstration is part of a talk by Ross Leishman of DWS Ltd on the principles of Understanding Indexes for SQL Tuning. The full lecture is at this link: https://www.youtube.com/watch?v=Z4hKomnGHFA 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: 2691 DWS Ltd
In this tutorial, you'll learn...what are optimizer hints and how to use it.. SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.
Views: 11613 radhikaravikumar
In this video, I will describe the 3 Join Operators SQL Server uses and the reason why it comes to the individual selections by the Optimizer. Please check my blog azizsharif.blogspot.com for more videos!
Views: 7625 azizsharif.blogspot.com
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: 6894 SkillBuilders
Learn some details about how the Oracle optimizer works with Themis instructor John Mullins.
Views: 618 Themis Education
Batman Arkham Knight Kidnap Vehicle Crash Site - Rescue Oracle Find Barbara Gordon Knight Kidnap Vehicle Crash Site Scan the crashed vehicle 0:16 Locate the vehicle's driver : 1:09 Examine the passenger seat 1:53 Locate the passenger door 2:22 identify where oracle landed 3:12 Examine the ballistic evidence 4:17 Find the clue left by Oracle while crawling away 5:56 Batman: Arkham Knight is an upcoming action-adventure video game being developed by Rocksteady Studios and published by Warner Bros. Interactive Entertainment for the PlayStation 4 and Xbox One video game consoles, and Microsoft Windows. Based on the DC Comics superhero Batman subscribe :http://www.youtube.com/user/qpgames?sub_confirmation=1 Batman: Arkham Knight Playlist https://www.youtube.com/playlist?list=PLWgRB5ZH1mmlSg7TnUUkR4u4f7gQAMibt Plus Google :https://plus.google.com/+qpgames Fanpage:https://www.facebook.com/fpgoodgames Twitter :https://twitter.com/QPGAMES_YT All Most Wanted, All Ar Challenges, All Riddler,All Trophies Achievement,
Views: 274180 QNGAMES
Part -1 : Introduction to Indexes, B*Tree Indexes, Index scan methods. Part -2 : B*Tree Index Types(different types of B*Tree indexes) Part -3: Using B*Tree Indexes(When use B*Tree indexes, factors related to B*Tree Access) Part -4 : Index Organized Table Part -5 : Bitmap Indexes Part -6: Partitioned Indexes Part- 7: Other type of Indexes based on characteristics & use. Part -8: Managing Indexes
Views: 49367 Anindya Das
This Tutorial will explain fundamentals of Oracle AUTOTRACE. Set up & Use AUTOTRACE. Review PLAN & Statistics generated by AUTOTRACE. Understanding Statistics details.
Views: 23085 Anindya Das
In an Oracle RAC environment, a SQL statement executed in parallel can run across all of the nodes in the cluster. For more information please visit https://www.koenig-solutions.com/oracle-database-12c-rac-administration-training.aspx
Views: 47 Koenig Solutions
Hints for Join Orders(USE_NL) - USE_NL 힌트는 테이블을 조인 하는 경우 중첩 루프 조인(Nested Loop Join)이 일어나도록 하는 힌트 문장이다. 중첩 루프 조인은 중첩반복 이라고도 하는데 하나의 테이블(outer/driving table)에서 추출된 ROW를 가지고 일일이 다른 테이블(inner/probed table)을 반복해서 조회하여 찾아지는 레코드를 최종 데이터로 간주하는 방법 이다. - EMP 테이블과 DEPT 테이블을 조인하는 경우 DEPT 테이블이 건수가 작다면 우선 이 테이블을 외부 루프로 해서 하나씩 읽으면서 이에 대응하는 EMP 테이블의 데이터를 추출 하는 경우가 중첩 루프 조인에 해당 한다. 이때 EMP 테이블의 경우 대부분 조인 칼럼에 인덱스를 이용하는 것이 성능상 좋다. 즉 중첩 루프 조인은 테이블 중 적어도 하나의 조인 컬럼에 대해 인덱스(or Hash Index)가 존재할 때 연관되는 방식으로 이 중첩 루프 조인에서 테이블 중 하나의 테이블 또는 중간 결과 셋을 대상으로 FULL SCAN이 일어나게 된다. 보통 USE_NL 힌트 구문은 ORDERED 힌트 구문과 같이 사용되는데 USE_NL이 취하는 인자는 FROM절에서 두 번째 나오는 테이블(비드라이빙 테이블, inner/probed table)을 명시해 주어야 한다. 인수로 사용되지 않은 첫 번째 테이블이 드라이빙 테이블(outer table)이 된다. Hints for Join Orders(ORDERED) 힌트는 FROM 뒤에 기술되는 테이블의 순서대로 조인이 일어나도록 해주는 구문이며 대부분 단독으로는 사용되지 않고 USE_NL(중첩 루프 조인을 유도), USE_MERGE(머지 소트 조인을 유도), USE_HASH(HASH 조인을 유도)등과 같이 사용된다. USE_NL/USE_MERGE/USE_HASH등의 인자로 사용되는 테이블은 FROM절에서 두 번째로 나타나는 테이블 이어야 하는데 FROM절에서 처음 나타나는 테이블이 드라이빙 테이블(OUTER/DRIVING TABLE)이 되고 나중에 나타나는 테이블이 PROBED TABLE(INNER TABLE)이 된다. 보통 드라이빙 테이블의 경우 데이터 건수가 적거나 조인 칼럼에 대해 인덱스가 없는 테이블이 주로 사용된다. 중첩 루프 조인(Nested Loop Join)이나 해시조인(Hash Join)의 경우 성능이 FROM절 뒤에 나타나는 테이블의 순서에 영향을 받지만 MERGE 조인은 영향을 받지 않는다. 머지 조인은 메모리에 올려서 정렬 시킨 후 병합하니까 실행 계획은 다르게 나올지 모르지만 성능에는 관계 없다.
Views: 110 이종철
For More Tutorials Related To Cisco,CCNA,Microsoft,Oracle,HP,Adobe,IBM,Java And Much More Please Visit This Site http://www.geteveryvideos.com/category/certification-tutorials/
Views: 57831 AllCertifications Tutorials
The video explains Reference partitioning and its advantages in Oracle with real project example. It builds on the limitations of reference partitioning in oracle 11g (Interval partitioning) and scenarios where you should implement reference partitioning
Views: 1889 Tech Coach
בסשן הזה אני מציג כיצד ניתן לשפר ביצועים של סריקת טבלה (Full Table Scan) באורקל ומדגים כיצד זה בפועל מתבטא ומתבצע בסביבת ייצור אמיתית. נתוודע למושג ה-High Water Mark, החשיבות שלו בעת Full Table Scan והעובדה ש-DELETE לא משפיע עליו. נראה כיצד נוכל לזהות אם יש בלוקים ריקים ומיותרים שאנו קוראים בעת סריקת הטבלה וכיצד ניתן לפתור את הבעיה.
Views: 321 Eran Koren
Explain plan is a wonderful utility in Oracle PL SQL. It helps you to understand how much cost a query takes to perform based on indexed table or table without index. In this oracle tutorial a full description is given on a table containing huge number of rows first based on index on a column and then without index.
Views: 3735 Subhroneel Ganguly
This tutorial will identify some use cases for Oracle bitmap indexes, including some of the more advanced capabilities. See all lessons, free, at http://www.skillbuilders.com/when-to-use-oracle-bitmap-indexes. Indexing your Oracle Database for best performance? There are cases, depending on data structures and queries, where b-tree indexes are not useful (e.g. scan access paths perform inadequately). In these cases, bitmap indexes may be a better solution. Bitmap indexes are a powerful tool, but they need to be used with care. Inappropriate use may cause problems worse than those they solve. The tutorial covers somewhat more advanced cases such as using bitmap join indexes to denormalize a snowflake schema, and to enable star transformations in queries that join fact tables to several dimension tables. This training will benefit any Oracle DBA administering a Data Warehouse or VLDB and "power" developers working in same. Instructor: Oracle Certified Master DBA John Watson, SkillBuilders
Views: 597 SkillBuilders
Oracle Database SQL Tuning tutorial. Learn what direct and indirect reads are and what impact they have on tuning SQL in Oracle Database. In this free tutorial from www.SkillBuilders.com, Oracle Master DBA John Watson will explain and demonstrate what direct / indirect reads are, pros and cons, why they can cause instability in the performance of your SQL (unpredictable response time), why stored outlines, SQL plan baselines and hints usually don't help. Perhaps most importantly, John will tell you what you can do about it. Intended Audience: Experience Oracle DBA's, developers and anyone with Oracle SQL tuning experience.
Views: 1655 SkillBuilders
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 lesson.
Views: 15266 SkillBuilders
Joining large tables in a Data Warehouse will often generate star transformations in the execution plan. So, what is a star transformation? And, what if you don't have Enterprise Edition - so you can't build Bitmap Indexes? In this free tutorial, Oracle Master John Watson will demonstrate star transformations in EE and how to make them work even without bitmap indexes - Standard Edition can save you thousands in licensing fees! This is Part 3 of a 5 Part series. View all video tutorials at SkillBuilders.com/EqualSQL.
Views: 4581 SkillBuilders
What are statistics? How often should you update them? Do you need a trace flag to make this easier? Join Kendra Little to find out. Table of Contents: 00:11 - Hi, I’m Kendra 00:35 - I always kinda hated Aristotle 01:22 - I learned about stats because of performance problems 02:48 - We had an “ascending date key” problem 04:33 - How we fixed it 05:27 - I got a little “golden hammery” 07:25 - We experimented creating stats 11:27 - We learned to drop stats when they didn’t help 12:32 - Maintenance became excessive 14:33 - “What happens if we stop running these stats update jobs?” 15:29 - I’ve found problems on small databases 16:57 - I realized:Updating stats to solve problems is essentially reactive 17:11 - Reactive solutions rarely satisfy 17:28 - I became conservative 19:19 - Trace Flag 2371 is going mainstream 20:05 - These days, I’m more moderate 20:38 - Moderate approach to updating stats 22:10 - Practically speaking… 23:45 - When jobs load batches of data 24:40 - If you diagnose slow queries due to out of date stats 26:08 - LittleKendra.com/recommends/stats
Views: 3482 Kendra Little's SQL Workbooks & Dear SQL DBA
Recently i took RMAN sessions to some group of people and as a part of it i explained various scenarios of rman restore and recovery operation. I tried to record the video for the purpose of the people who follow my blog. In this present session we will see various recovery scenarios mentioned below. 1) Complete database restore and recovery 2) Loss of System Datafile 3) Non-System datafile ( closed and Open ) 4) Restoring a tablespace ( closed and Open ) 5) Restoring a datafile if no backups 6) Restoring a datafile to different location 7) Restoring controlfile 8) restoring spfile 9) Restoring online redologs 10) Restoring temporary datafiles 11) Incomplete Recovery ( time based , Change based and SCN based ) 12) Recovering archived logs The following video demo gives you detailed explanation of each scenario and how to restore and recover under different scenarios. Setup: ===== Let me explain you the environment used for this practice. I am using a database with ORACLE_SID=orcl as my database for testing all the different scenarios mentioned above. I am using a recovery catalog for my rman configuration and its details are ORACLE_SID=rmandb and schema used for rman catalog data is rman and the tns alias to connect to catalog database is rmandb. I have both my databases orcl and rmandb in archive log mode and i also have backup for my database orcl. For all my restore and recover operations i am using the following syntax to connect to target database and catalog database. rman target / catalog rman/*******@rmandb
Views: 37973 Srikrishna Murthy Annam
SUBSCRIBE - http://www.youtube.com/yongyea Facebook - https://www.facebook.com/yongyea Twitter - http://www.twitter.com/yongyea Google Plus - https://plus.google.com/+YongYeaPlus/
Views: 32988 YongYea
Every flight has a flight plan. Every query has a query plan. You must have seen its text form, called EXPLAIN PLAN. Query optimizer is responsible for creating this query plan for every query, and it tries to create an optimal plan for every query. In Couchbase, the query optimizer has to choose the most optimal index for the query, decide on the predicates to push down to index scans, create appropriate spans (scan ranges) for each index, understand the sort (ORDER BY) and pagination (OFFSET, LIMIT) requirements, and create the plan accordingly. When you think there is a better plan, you can hint the optimizer with USE INDEX. This talk will teach you how the optimizer selects the indices, index scan methods, and joins. It will teach you the analysis of the optimizer behavior using EXPLAIN plan and how to change the choices optimizer makes. Speaker: Keshav Murthy, Director, Query Development, Couchbase Slideshare: http://www.slideshare.net/Couchbase/how-to-understand-and-use-the-query-optimizer Visit our website for more information: https://www.couchbase.com/
Views: 487 Couchbase
Learn 11g new performance feature that should solve the problem of bind variables and data skew. Dave Anderson of SkillBuilders.com/Oracle.
Views: 5216 SkillBuilders
China Sun Yat-sen University and Dell co-established course IT System Management Introduction Lecture 5 Oracle Database Performance Tuning - Guy Harrison The Oracle database is highly tuneable with many options for optimizing performance. However, the sheer quantity of tuning options can be overwhelming, and you can waste precious time unless you know which tuning activities are most likely to provide a return on your time investment. In this lecture we'll review some of the fundamental Oracle performance tuning practices and see how to use these in a ystematic way to improve Oracle performance. Topics will include workload and SQL optimization, identification and elimination of key contention points, configuring memory to avoid physical IO, and disk IO optimization. We will also see how to use commercial tools to assist in Oracle database tuning. This lecture will be based on materials from the lecturer's book on Oracle performance: http://product.china-pub.com/60833).
Views: 30307 Quest University
**Thank you for your donations** PayPal.Me/Missionbacktolove (PayPal) or Cash.Me/$MissionBackToLove11 (CashApp) Personal Readings inquire: Hello Twin Flame family, Thank you for your interest in a personal reading -- READINGS (Video Link) Mini Twin Flame Energy Reading $11.11 Twin Flame Energy Reading $33 Twin Flame Foundation Reading (YouTube layout) $44 Twin Flame Foundation Reading (YouTube layout) $55 Twin Flame Foundation Reading w/ In-depth guidance $66 *Emergency Reading Additional $20 to chosen reading ADDITIONAL READINGS One Question / Three Question $11.11 $33.33 Personal Reading $25 Life Purpose Reading $11.11 *Please note that my readings are not done to tell you who your Divine other is. I believe (at this time), that it is only your Heart/North that knows. Readings are read based off of energies between you and your true Divine Masculine or Feminine * Love, Peace & Light If interested, You can send the payment to: PayPal.Me/Missionbacktolove (PayPal) or Cash.Me/$MissionBackToLove11 (CashApp) *In the description of payment page, please add the service(s) you are requesting. Also, send me email payment confirmation, so that I can do my best to fit you in from when payment was placed and also have an email for you. [email protected] **Also, please do not send me any information about what is occurring in your journey, just send me if it is a DF or DM I will be referring to. Allow our guides to step in Your reading will be done within a timely manner, once payment confirmation is received. Once it is done, you should receive an email from Youtube and I will send one as well to this email, where you will be able to access it (Please make sure to be signed into your YouTube with the email provided through your reading inquiry, to be able to view it). If you would like it to be another email, please email it to me. Thanks again for your interest in the services offered. It is the greatest pleasure of mine to assist you on your journey back home to love. Many blessings to you, your journey and your union. ************ To book an Energetic Scan and Clearing Session with The Anam Cara Oracle, email her at [email protected] $33 for 30 minutes $66 for 1 hour To read about services and review testimonials, follow the link to her page, https://www.facebook.com/AnamCaraOracle/ She feels the energies in chakras whether they are spinning or stagnant. She feels blocks or issues and whether they seem solid or move. She senses pain and emotions from the recipient and sometimes their partner. People experience energetic shifts or guidance to help them prepare for energetic shifts if they are not quite ready.
Views: 2355 Twin Flame Family