Videos uploaded by user “SkillBuilders”
Oracle SQL Tuning - How to Read Execution Plans (Explain Plan)
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: 68020 SkillBuilders
How Oracle RAC Cache Fusion Works Part 1 of 6
Learn the mysteries of how Oracle RAC Cache Fusion works. In this lesson you will see the tutorial agenda plus a brief history of Oracle clustering (parallel server) and the problems associated with disk pinging. Cache Fusion is the secret to making Oracle Real Application Clusters (clustering) a scalable database platform. You will learn how cache fusion works, the differences between global cache current and consistent read transfers, the differences between two-way and three-way transfers and the associated wait events. Oracle Ace and author Brian Peasland provides a demonstration of global cache transfers in action by utilizing Oracle Trace. Prerequisites: Knowledge of Oracle Architecture including the concept of an Oracle instance, the buffer cache, how data is moved into the buffer cache and an understanding of wait events. Brian is a certified Oracle ACE who has been in the IT field for over 25 years and has worked as a computer operator, operations analyst, systems administrator, application developer and for more than half of his career as a database administrator. He holds a B.S. in Computer Science and a M.S. in Computer Science, specializing in database systems. Brian has been a member of SearchOracle.com’s Ask The Experts since 2001 and now contributes articles to the site. He regularly contributes to the My Oracle Support and Oracle Technet communities. He can be followed on Twitter on @BPeaslandDBA and maintains a blog at http://www.peasland.net. Brian is the author of “Oracle RAC Performance Tuning” on Rampant TechPress.
Views: 7373 SkillBuilders
How to use Oracle SQL ROLLUP and CUBE
Learn how the Oracle Database SQL extensions ROLLUP and CUBE provide additional aggregations for your data warehouse data.
Views: 2770 SkillBuilders
How to Convert a Non-Partitioned Table to Partitioned using Oracle Database 12c R2
Oracle Database 12c Release 2 New Feature! Learn how to convert a non-partitioned table to a partitioned table - Online! This new method - using ALTER TABLE MODIFY - is much simpler than DBMS_REDEFINITION. In this free tutorial from SkillBuilders and Oracle Certified Master DBA John Watson you'll see a demonstration of converting the table and the impact on the underlying table and indexes. See all our free Oracle Database Tutorials at http://www.skillbuilders.com/free-oracle-tutorials.
Views: 3730 SkillBuilders
Stop Rebuilding Global Indexes! Oracle 12c Global Index Maintenance
Stop rebuilding Global Indexes! Stop worrying about invalidating Global Indexes! Oracle 11g gave us "UPDATE GLOBAL INDEXES". 12c provides a huge improvement: orphaned_entries! In this tutorial, Oracle Certified Master John Watson demonstrates how 11g and 12c handle Global Index maintenance with orphaned entries and overnight maintenance job PMO_DEFERRED_GIDX_MAINT_JOB.
Views: 2688 SkillBuilders
Introduction to Advanced Ruby Programming Language from SkillBuilders.com/Ruby
Watch the entire FREE tutorial at http://www.SkillBuilders.com/Ruby-Free-Tutorials?id=12&w=web-development-with-ruby-on-rails. "Introduction to Ruby" talks aren't that exciting. Every language has strings, arrays, conditionals, and evaluation, and by now, everyone's sick of "build a CRUD app in 15 minutes" demos. Every language has its own special qualities that set it apart, and this talk focuses what makes the Ruby programming language different and special. We'll cover seven advanced language features and techniques, and we'll demonstrate how we use those to solve everyday problems. We'll talk about message passing, extending core language features, lambdas, and we'll even write a little code that writes code. If you've looked at Ruby and thought "What's the point?" then this talk is for you. Your instructor, Brian P. Hogan is an author, editor, trainer, and web developer who's been building web sites professionally since 1995.
Views: 11945 SkillBuilders
Groovy & Grails Tutorial - Part 1 - What is Groovy
Learn Groovy programming and the Grails platform! Groovy is part of the new generation of dynamic languages for the JVM. Watch the complete 54 minute FREE tutorial at http://skillbuilders.com/Groovy-Free-Tutorials?id=21&w=groovy-grails-free-tutorials. It's not intended to replace Java, but rather to make it easier, more flexible, and more powerful. Groovy is what Java would have been like had it been designed in the 21st Century. Groovy dramatically simplifies Java development, and also brings modern language concepts like builders, closures, and metaprogramming to existing Java systems. The purpose of this free tutorial is to demonstrate both how Groovy makes Java development quicker and easier, and also to show some of Groovy's more advanced features. The capabilities illustrated will include: Groovy beans Optional typing Easy access and processing XML data Unit and functional testing of both Groovy and Java code Simplified file and directory handling Templating Ant builders, Swing builders, and XML builders
Views: 31694 SkillBuilders
SQL Tuning How to Tune Joins - Lesson 1
How do you tune SQL Joins? Start with the most important ingredient - the join order! In this free tutorial by Oracle Certified Master DBA John Watson of SkillBuilders.com you will learn what is the best join order and how to get the optimal join order. Watch all Lessons, FREE, at http://skillbuilders.com/tuning-joins-optimal-join-order.
Views: 3487 SkillBuilders
How Oracle RAC Cache Fusion Really Works Part 5 of 6
Oracle ACE Brian Peasland demonstrates 2-Way vs 3-Way Block Transfers including a description of master, holders and requestors.
Views: 1753 SkillBuilders
How to use the Oracle SQL PIVOT Clause
PIVOT allows you to "flip a table on its side", i.e. Columns to Rows / Rows to Columns. Previously we did this with DECODE or CASE. As of Oracle 11g, Oracle Database includes the PIVOT clause (and UNPIVOT). Oracle expert Geoff Wiland from SkillBuilders will demonstrate PIVOT, UNPIVOT, including the use of aggregate functions.
Views: 11534 SkillBuilders
How Oracle RAC Cache Fusion Works Part 3 of 6
Learn How Oracle RAC Cache Fusion Works Really Works. Download Free Scripts at http://skillbuilders.com/How-Oracle-RAC-Cache-Fusion-Works. A continuation of the cache fusion demonstration, with a detailed look at session statistics and the raw trace file contents. You will learn about the "gc" wait events (Global Cache) including 'gc current block 2-way' and 'gc cr multi block request'.
Views: 3248 SkillBuilders
How Oracle RAC Cache Fusion Works Part 2 of 6
Learn how Oracle RAC Cache Fusion really works. Presenter Brian Peasland demonstrates, with v$statname, v$sesstat and Oracle trace (dbms_monitor.session_trace_enable) cache fusion in action. Free Scripts used in this tutorial are downloadable here http://skillbuilders.com/How-Oracle-RAC-Cache-Fusion-Works
Views: 3877 SkillBuilders
SQL Tuning with Oracle Database 12c Histograms
Learn the new 12c options for creating histograms. See all free video tutorials at http://www.skillbuilders.com/free-oracle-tutorials. In this free tutorial, Oracle Certified Master DBA John Watson demonstrates what histograms do (provide correct cardinality), the difference between histogram types (Frequency and Height Balanced). You will also learn the importance of the auto sample size algorithm in 12c and the new "Hybrid" and "Top Frequency" type histograms.
Views: 3865 SkillBuilders
What is Oracle ACFS - Lesson 1 - Intro to ASM Clustered File System
What is Oracle ASM Clustered File System? In lesson 1, Oracle Certified Master DBA John Watson reveals the somewhat confusing licensing history of ACFS and why Oracle uses it as the default file system for Oracle Database Appliances. Watch all free lessons in this tutorial at The white paper referenced in this video can be found at http://www.oracle.com/technetwork/database/database-appliance/overview/asm-acfs-migration-2379319.pdf Another helpful paper can be found at http://www.oracle.com/technetwork/database/database-technologies/cloud-storage/benefits-of-oracle-acfs-2379064.pdf.
Views: 3862 SkillBuilders
How to Tune SQL when You Can't Edit the Source Code, Part 1 of 2
Learn Oracle SQL Tuning! How do you add a hint to SQL you can't edit? How do you tune SQL if you don't have access to the source code? Sometimes, Oracle indexes, statistics, stored outlines, profiles or 12c directives will solve a tuning problem. However, as a last resort, you may want to add a hint. In this case DBMS_ADVANCED_REWRITE can be the solution. In this tutorial, Oracle Certified Master DBA John Watson of SkillBuilders demonstrates using DBMS_ADVANCED_REWRITE to change the SQL Oracle executes - without changing the source code!
Views: 1515 SkillBuilders
Four Steps to Oracle SQL Tuning  - A Methodolgy
Learn a predictable and repeatable methodology for tuning Oracle SQL statements. Just four steps that you should always follow when tuning an SQL statement. (Note this video does not contain examples of how to apply the four steps, just what the steps are.) Oracle Certified Master John Watson presents. John concludes with a brief overview of how SkillBuilders SQL tuning course provides the information you need to apply the four steps. Learn more about SkillBuilders SQL Tuning course http://skillbuilders.com/oracle-sql-tuning-training 1. What is Oracle doing? (explain plan, trace) 2. Why is Oracle doing it that way? (analyze the execution plan) 3. Is there a better way? Test! 4. If there's a better way, push the CBO towards the better way.
Views: 14653 SkillBuilders
What is Oracle Exadata Smart Scan (Demonstration)
How does Oracle Exadata Smart Scan work? What is Smart Scan. How to insure Oracle is offloading work to the Storage Tier? Learn this and more in this demonstration by OC Master John Watson of SkillBuilders. See all 11 lessons in this tutorial at http://skillbuilders.com/exadata-tutorials. John Watson: I want to show you very briefly how offload Smart Scan or flow processing can actually function. I'm working here just in the little demonstration Scott schema. I'll enable auto tracing so that we can see the effect on execution plans, which will also show me whether offload processing is indeed possible. So it's a simple query. I'll use a function here to check that to get offloaded as well as the column selection and the row selection and column projection. We'll have a predicate. Right. So there is the statement. It returned these three scalar values, as you would expect. How was this actually executed? There was a select statement, then it was executed, table access full. That's the only way to run that statement given the index on that table because in the middle of this, we see the key word, "storage." That key word tells me that this statement could indeed be offloaded to the storage tier. Furthermore, we see down here in the predicate information, "storage." That key word again tells me that the row filtering could have been done by the storage tier. So what happened is that the storage tier did not return every block of the empty* table to the database buffer cache, where my session would then have had to extract these values. All it returned to the instance was these three scalar values directly into the PGA of my server process. Now, there's also the initcap function there. Was that offloadable or not? There is a view "Read our SQL fm letter data," and that will tell us for each function whether or not it can be offloaded. So, just select its name, whether it's offloadable. Declare offloadable as yes and put in a sort order as well, and we see that in the current release, there are 393 functions that can be offloaded. Which ones? All the typecasting functions, truncations, trims. These are functions that get executed millions of times an hour in many, many environments. Note here two particularly important ones, balloon filters. The offload capability for balloon filters allows us to offload joins or facts to dimension tables. Those two in star schemas can be offloaded. The function I used was, in fact, initcap. Just for completeness, we'll scroll through all of these and we'll see that initcap itself is an offloadable function, and there it is along with many others. There are two classes of function that cannot be offloaded, and those are the aggregation functions and the analytic functions. I mention them just for completeness. There's an architectural issue there because aggregations and analytics potentially require access to all rows in the table. The row might be distributed across a cell. So it's a technical issue why they can't be offloaded, but virtually, all other functions can be offloaded with terrific drops in CPU usage, and combining that with the reduction of data traffic into the instances will give us vast performance improvements.
Views: 4029 SkillBuilders
Oracle Database SQL Tuning with Direct and Indirect Reads
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: 1708 SkillBuilders
How to Pivot (Flip) Tables with SQL CASE & DECODE
Pivoting tables (columns to rows, rows to columns) is a common data analysis requirement. Learn how to accomplish this with Oracle SQL CASE and DECODE
Views: 1961 SkillBuilders
SQL Tuning for Beginners Part 1 - Tuning Methodology and Basic Optimization Concepts
Get started with Oracle SQL Tuning! Designed for developers, DBA's and Analysts who are new to SQL Tuning, this free tutorial introduces a tuning methodology, basic tuning concepts. Part 2 provides a demonstration of explain plan and autotrace for getting an execution plan. Watch Part 2 and 3 - **FREE** - at http://www.skillbuilders.com/oracle-sql-tuning-for-beginners-tutorial. Presented by Dave Anderson and Oracle Certified Master John Watson. Attend 1-day class for beginners, online or on-premise: http://www.skillbuilders.com/oracle-sql-tuning-training-for-beginners
Views: 10720 SkillBuilders
What is Oracle ACFS - Lesson 4 - Format ACFS and Mount the File System
Lesson 4 of 4 - The basics of Oracle ACFS...What is it and how to configure the basics of ASM Clustered File System. Oracle Certified Master John Watson demonstrates....
Views: 1488 SkillBuilders
Introduction to Oracle APEX 5 - Module 1 - What is APEX
Get started on your way to being an APEX Developer! In this excerpt from SKillBuilders Introduction to Oracle APEX class, Oracle Certified APEX Expert provides a basic foundation, i.e. answers the question, what is APEX! See all modules in this tutorial, free at http://www.skillbuilders.com/Introduction-to-Oracle-APEX-5-Tutorial
Views: 3227 SkillBuilders
Oracle SQL Tuning - Explain Plan Lies?
Do you use EXPLAIN PLAN to tune Oracle SQL? Does it always "tell the truth", or does it "lie". (Maybe it's not the whole truth!) In this free tutorial from SkillBuilders' Oracle Certified Master John Watson, you will learn why the execution plan generated by EXPLAIN PLAN can be misleading and what to do about it. After a brief lecture, John demonstrates exactly why. You'll hear about dynamic sampling, adaptive cursor sharing (11g), adaptive execution plans (12c) and of course, bind variables. John demonstrates how bind variables cause misleading execution plans using dbms_xplan.display and dbms_xplan.display_cursor. To get the most from this tutorial, you should have some understanding of hard parse, soft parse, cardinality, histograms. See all SkillBuilders FREE Oracle Database tutorials at http://www.skillbuilders.com/free-oracle-tutorials.
Views: 3589 SkillBuilders
SQL Tuning for Beginners 2 - Review Challenge
In Part 2, Oracle Certified Master reviews the tuning challenge presented at the end of part 1. John uses Autotrace to demonstrate which query performs the best.
Views: 2917 SkillBuilders
What is Oracle Stretched RAC Clusters? aka Geo-Cluster, Extended Distance Cluster
Learn why a "stretched cluster" (also called extended distance clusters) for your Oracle Database can be better than RAC, Data Guard and Streams, and can provide a zero downtime, zero data loss environment. Stretched clusters can even be built on Oracle Standard Edition, saving money on licensing costs. Presented by John Watson, Certified Oracle Master DBA and SkillBuilders.
Views: 5193 SkillBuilders
Introduction to Oracle Application Express (APEX)
Oracle Application Express (APEX) is an easy to use platform for developing data-centric web based applications. Although it has been around for years, many people have yet to really discover and utilize its unique strengths. Those new to Apex often have many questions including: What can Apex do for me or my organization? What features are available "out of the box" and can I extend the product if needed? Is is secure and can it scale? This session will answer these questions, and many more, via a high level product overview which will include a live demo.
Views: 33488 SkillBuilders
Groovy For Java Developers by SkillBuilders.com/Groovy
Learn how Groovy programming makes Java development quicker and easier, and some of Groovy's more advanced features. The capabilities demonstrated will include: Groovy beans Optional typing Easy access and processing XML data Unit and functional testing of both Groovy and Java code Simplified file and directory handling Templating Ant builders, Swing builders, and XML builders From SkillBuilders.com/Groovy
Views: 16864 SkillBuilders
Oracle Database 12c Security - Oracle Virtual Private Database (VPD)
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: 4687 SkillBuilders
Oracle 12c SQL Tuning - Adaptive Execution Plans
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: 5671 SkillBuilders
How to Install Solaris 11 Kernel Zones - Lesson 1 Prereqs and Configuration
After a brief introduction, Certified Expert Mick Hosegood will present (with demonstrations) the hardware and software requirements for Solaris 11 Kernel Zones. This is lesson 1 of a 4 lesson tutorial. See all lessons, FREE, at http://www.skillbuilders.com/how-to-install-solaris-11-kernel-zones. Zones are an integrated part of the Solaris operating system which is owned and developed by Oracle. Both Solaris 10 and Solaris 11 support zones which up till now have been very lightweight virtual machines using the same kernel as is running on the host system known as the global zone. These are also sometimes referred to as containers. However, since Solaris 11.2, kernel zones have been available which allows you to run independent versions of the operating system with their own kernel within the global zone. This is the first time this has been possible, so you can maintain zones at different software level, different patch level, if you like, along with other facilities such as the ability to suspend and resume them perhaps on the same or different machine so you can migrate kernel zones, assuming you can allocate shared storage between systems.
Views: 1185 SkillBuilders
Oracle SQL Tuning Re-Optimizing Execution Plans with Cardinality Feedback
Cardinality feedback, introduced with Oracle Database 11g, has been significantly enhanced with 12c. Cardinality feedback allows the CBO to learn from a cardinality estimate mistake and re-optimize the execution plan. Learn more in this free SQL Tuning tutorial. See all free Oracle Database tutorials at http://www.skillbuilders.com/free-oracle-tutorials.
Views: 3245 SkillBuilders
How to Customize Calendars in Oracle APEX 5 Lesson 1 of 3
Watch Oracle Certified Expert Tyson Jouglet demonstrate how to customize calendars in APEX 5 in this free tutorial from SkillBuilders.com. Tyson’s focus in this presentation is how to control the aesthetics of your calendar to provide helpful visual queues for your calendar users. Tyson will be using the new calendar region in APEX 5 for this tutorial. See all 3 lessons, free, at http://skillbuilders.com/how-to-customize-calendars-apex-5.
Views: 11308 SkillBuilders
Oracle Exadata: Hybrid Columnar Compression (HCC) Introduction
Oracle Exadata Hybrid Columnar Compression Introduction Why is it so powerful? Well, in comparison to Oracle's previous compression options, hybrid columnar compression really does compress. Everything Oracle issued previously didn't compress at all. It did deduplication...Experience shows that virtually all data will be compressed by at least five or six times.
Views: 3964 SkillBuilders
How to Get Secure Web Services (SSL/HTTPS) and Oracle APEX Working - 4 of 4
After you've created the Access Control List (ACL) and added the digital certificates, test your secure web services call. To watch all 4 FREE lessons visit http://www.skillbuilders.com/how-to-get-oracle-apex-ssl-web-services-working.
Views: 1711 SkillBuilders
Mastering Oracle APEX Messages - Lesson 2
In this lesson learn how to create PL/SQL Success messages, dynamic messages and best practices. Visit http://www.skillbuilders.com/how-to-oracle-apex-messages to view all lessons, free.
Views: 3001 SkillBuilders
How Cache Fusion Really Works Part 4 of 6
Cache Fusion is the secret to making Oracle RAC a scalable database platform. This video is a continuation of the analysis of the raw trace file, the Global Cache Wait Event and the difference between Current Blocks and Consistent Read Blocks. See all lessons in this tutorial, Free, at http://skillbuilders.com/How-Oracle-RAC-Cache-Fusion-Works. Created by Brian Peasland, author of "Oracle RAC Performance Tuning" http://amzn.com/0986119415.
Views: 1907 SkillBuilders
Oracle Tuning Tutorial - Long Full Table Scans Part 2 of 5
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 ( Audience: Operating System Administrators, Storage Administrators, Oracle Administrators and Management responsible for Storage and / or Oracle Databases.
Views: 4609 SkillBuilders
Oracle LISTAGG Function Example and Demonstration
In this lesson Geoff dives into the explanation and demonstration of the LISTAGG aggregate function. See all the lessons in this tutorial - FREE - at http://www.skillbuilders.com/advanced-aggregations-with-oracle-sql.
Views: 2254 SkillBuilders
Oracle Exadata Smart Scan - Limitations and Best Practices
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: 1716 SkillBuilders
Advanced SQL SubQueries in Oracle Database
Learn to code advanced correlated SQL subqueries in the Oracle Database. From SkillBuilders.com/Oracle.
Views: 9407 SkillBuilders
Oracle Grid Infrastructure Tutorial Part 1 of 11
Watch all 11 lessons in this series - free - at http://www.skillbuilders.com/GridInfrastructureTutorial Why All Administrators Must Know Grid Infrastructure! It is still possible to run an Oracle database without installing Grid Infrastructure. But is it sensible? This webinar will outline how Grid Infrastructure can replace (or complement) third party products for RAID, network management, and high availability. At the same time, using these facilities can improve performance dramatically. Grid Infrastructure is the way Oracle is moving, and we want to show you that it is not necessarily complicated and can indeed make your life simpler. It can also save you money. Content: live demonstrations of configuring and using Grid Infrastructure services with release 11g. Audience: Oracle DBAs looking for entry level knowledge of what Grid Infrastructure can do. Presenter: John Watson, Oracle Certified Master and: * Oracle Exadata 11g Certified Implementation Specialist * Oracle Certified Master * Oracle Certified Professional, Database Administrator * Oracle Certified Professional, Application Server Administrator * Oracle Certified Expert: Performance Tuning; Real Application Clusters; Managing Oracle on Linux
Views: 7528 SkillBuilders
How to code PL/SQL BULK Collect by Steven Feuerstein
See all free Oracle tutorials at http://www.skillbuilders.com/oracle-tutorials. In this tutorial, Oracle ACE Director Steven Feuerstein sets the agenda AND introduces 4 techniques for improving the performance of your PL/SQL code.
Views: 2584 SkillBuilders
What is Oracle ACFS - Lesson 2 - Functionality and Use Cases
Oracle DBAs, system administrators and anyone charged with supporting Oracle Databases should learn what ACFS can do and how it can be used. In this lesson, Oracle Certified Master John Watson of SkillBuilders discuss ACFS functionality and provides several usage cases. Watch all lessons in this ACFS tutorial, FREE, at http://www.skillbuilders.com/what-is-oracle-acfs.
Views: 1723 SkillBuilders
How to Build Transparent Data Encryption on Oracle Standard Edition
View all our FREE database tutorials at http://skillbuilders.com/free-oracle-tutorials. Oracle Transparent Data Encryption (TDE) is a fantastic built-in security feature with Oracle Database Enterprise Edition. However, in this free tutorial, Oracle Master DBA John Watson will demonstrate a technique for building your own transparent data encryption - on Standard Edition!
Views: 6100 SkillBuilders
How to Stabilize Performance with Oracle 12c Baselines Part 1
Learn how Baselines prevent performance from degrading when changes happen in your Oracle Databases. See Part 2 at http://skillbuilders.com/oracle-12c-baselines-tutorial
Views: 814 SkillBuilders
Oracle Database 11g New Features Demo: Adaptive Cursor Sharing
Learn 11g new performance feature that should solve the problem of bind variables and data skew. Dave Anderson of SkillBuilders.com/Oracle.
Views: 5302 SkillBuilders
Mastering Oracle APEX Messages - Lesson 6
Learn how to include JavaScript client-side messaging and message theme hooks in Oracle APEX applications. See http://www.skillbuilders.com/how-to-oracle-apex-messages to view all lessons, 100% free.
Views: 1584 SkillBuilders
Get Started with Oracle APEX - Architecture
What are the components in an APEX application (e.g. browser, HTTP listener, APEX listener, database) and what is a workspace.
Views: 1124 SkillBuilders
Oracle APEX 5.0 with Font Awesome / Scalable Vector Icons
Watch all lessons in this tutorial at http://skillbuilders.com/apex-5.0-new-features-tutorial. In this lesson Tyson Jouglet, Oracle APEX Certified APEX Expert, will demonstrate the integrating Font Awesome (scalable vector icons) into an Oracle APEX 5.0 app. (It's a mystery what happened to lesson 4 :)
Views: 3804 SkillBuilders
Oracle 12c Dynamic Sampling
Learn how to improve cardinality and performance of SQL statements with Oracle Database 12c Dynamic Sampling. For you Spinal Tap fans, you can now set it to "11".
Views: 1015 SkillBuilders