- 27
- 151 694
Maria Colgan
United States
Registrace 19. 10. 2011
Welcome to my CZcams channel, where I share information on Oracle Database features and functionality to demonstrates how you can revolutionize Data Management by adopting Transformational Technologies!
This CZcams channel is used in conjunction with my blog sqlmaria.com, which has all of the code examples used in the videos in an easy to cut-and-paste format.
All content Copyright © 2021 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.
This CZcams channel is used in conjunction with my blog sqlmaria.com, which has all of the code examples used in the videos in an easy to cut-and-paste format.
All content Copyright © 2021 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.
How do I tune a SQL statement that isn't getting partition pruning
Partitioning is possibly one of the most powerful tools at your disposal when managing large volumes of data and improving the performance of queries that would otherwise scan and filter lots and lots of data.
However, it can be tricky to determine why it isn't helping when you thought it should. This video demonstrates how you can tell if you are getting the benefits of partition pruning and shares the solutions for the common scenarios where pruning is prevented from happening.
However, it can be tricky to determine why it isn't helping when you thought it should. This video demonstrates how you can tell if you are getting the benefits of partition pruning and shares the solutions for the common scenarios where pruning is prevented from happening.
zhlédnutí: 2 861
Video
How do I tune a SQL statement that uses a Nested Loops join instead of a Hash Join?
zhlédnutí 8KPřed 2 lety
At one point or another during your career as a database performance expert, you will face a situation where the optimizer picks the wrong join method. As tempting as it is to brute-force the plan you want via an index hint, it’s always better to understand why the Optimizer made the decision and provide the necessary information to allow the Optimizer to select the plan you want by default. In...
How do I tune a SQL statement when the Optimizer picks the wrong index?
zhlédnutí 6KPřed 2 lety
Understanding why the Optimizer makes a decision and providing the necessary information and access methods to allow the Optimizer to pick the plan you want by default can be challenging. But don't give in to the overwhelming temptation to use a hint to brut force the plan you want. In this short video, I explain why the Optimizer didn't choose the index folks were expecting, how to identify th...
How do I tune a SQL statement with the wrong Join Type
zhlédnutí 6KPřed 2 lety
It can be hard to know where to start when faced with a SQL statement that isn't performing as expected. In this video, I walk you through the necessary steps to find all the information you need to determine the root cause of the problem and provide you two alternative methods to solve the problem and improve the performance of the SQL statement.
How to determine the Join Order in an execution plan in Oracle
zhlédnutí 6KPřed 2 lety
The join order in an execution plan is the order in which the optimizer decides to join tables together in a multi-table SQL statement. Determining the join order in an execution plan and knowing if it's optimal and how to correct it if it's not is a tricky proposition for most of us. This video explains the simple set of rules the optimizer uses to determine the join order and provides two sim...
What Join Types or Join Methods Does Oracle Have?
zhlédnutí 11KPřed 3 lety
Do you need to tune a SQL Statement in an Oracle Database? Do you suspect the Optimizer has chosen the wrong join type? Well, it turns out the Oracle Optimizer has just three join methods to choose from when it comes to deciding how to join data from two data producing operators in an execution plan. In this video, we examine when each join method is considered and also look at the additional f...
Under The Hood of Oracle Autonomous Database
zhlédnutí 2KPřed 3 lety
In this session, I invite you to take a peek under the hood of the Oracle Autonomous Database and discover the power of Oracle Database 19c in action. I share our exclusive combination of database features, best practices and machine learning algorithms that make up this family of cloud services, so you can get a clear understanding of how our unique Autonomous Database Platform works. TOC What...
Oracle Optimizer Access Methods
zhlédnutí 8KPřed 3 lety
The Access Method - or access path - in an Oracle execution plan shows how data will be accessed from each table (or index) in your query. The Optimizer selects the Access from the nine different options available to it. This video describes the nine different Access Methods and explains when the Optimizer will choose each one. At the end of the video, you have an opportunity to play the role o...
How to fix Cardinality Problems in Oracle
zhlédnutí 6KPřed 3 lety
One of the most critical aspects of an Oracle Execution plan is the Cardinality Estimates, as they influence all of the Optimizer's other decisions. But what should you do if you find a Cardinality Misestmate in your execution plan? In this video, I explain the 5 leading causes of Cardinality Misestimates and what you can do to correct them. TOC Missing or stale statistics 0.18 Data Skews 0.37 ...
What are Cardinality Estimates in Oracle?
zhlédnutí 7KPřed 3 lety
A cardinality estimate is the estimated number of rows, the optimizer believes will be returned by a specific operation in the execution plan. The Optimizer determines the cardinality for each operation based on a complex set of formulas that use both table and column level statistics as input. It's considered the most important aspect of an execution plan because it strongly influences all of ...
How to Generate an Execution Plan in Oracle
zhlédnutí 8KPřed 3 lety
Although there are a number of tools that will display an Oracle Execution Plan for you, there really are only two ways to generate the plan. You can use the Explain Plan command or you can view the plan of a SQL statement currently in the Cursor Cache using the dictionary view V$SQL_Plan. This video gives you a brief but detailed description of both approaches. It also explains when to use eac...
What is an Execution Plan?
zhlédnutí 13KPřed 3 lety
Before you can learn to interpret what is happening in an Oracle execution plan, you need to understand what an execution plan actually is. This video provides a short but comprehensive overview of what an execution plan is and what information is displayed in each section. TOC What is an execution plan? 0:00 Understanding plan trees 1:09 Access Predicates 2:22 Filter Predicates 3:55 Note Secti...
How to read a Parallel Execution Plan in Oracle
zhlédnutí 8KPřed 3 lety
Parallel execution is the key to processing large volumes of diverse data quickly. But it can complicate the execution plan displayed as Oracle shares not only the operations needed to complete the query but all of the communication steps between the parallel server processes. In this video, I explain what these additional entries in the plan mean and how you can influence these choices. TOC Wh...
Developing and Deploying Data-Driven Apps
zhlédnutí 851Před 4 lety
We hear a lot about how businesses need to become data-driven in order to remain competitive. But what does that really mean? It means business need to understand their customer's needs and to quickly deliver value to those customers. The key to this is to take advantage of data-driven apps that allow you to understand and creating value from data in real-time. But what are data-driven apps and...
atp scaling demo
zhlédnutí 1,2KPřed 4 lety
In this video I show you how simple it is to manual scale an Autonomous Database when additional CPU resources are need to increase the transaction throughput rate for a JSON document workload with 128 concurrent users running on a 2 CPU database
Getting Started with Oracle Autonomous Database
zhlédnutí 636Před 5 lety
Getting Started with Oracle Autonomous Database
Provisioning an Autonomous Transaction Processing Database in the Oracle Cloud
zhlédnutí 496Před 5 lety
Provisioning an Autonomous Transaction Processing Database in the Oracle Cloud
Provisioning an Autonomous Transaction Processing Database
zhlédnutí 906Před 5 lety
Provisioning an Autonomous Transaction Processing Database
Downloading Database Credentials for an Autonomous Transaction Processing database
zhlédnutí 645Před 5 lety
Downloading Database Credentials for an Autonomous Transaction Processing database
Connecting to an Autonomous Transaction Processing database via SQLDeveloper
zhlédnutí 4KPřed 5 lety
Connecting to an Autonomous Transaction Processing database via SQLDeveloper
What is the impact on the role of the DBA with Oracle Autonomous Database
zhlédnutí 917Před 6 lety
What is the impact on the role of the DBA with Oracle Autonomous Database
Controlling where objects are populated into Memory on RAC
zhlédnutí 1,1KPřed 6 lety
Controlling where objects are populated into Memory on RAC
Using DBMS_XPLAN.DISPLAY_CURSOR to examine execution plans
zhlédnutí 35KPřed 6 lety
Using DBMS_XPLAN.DISPLAY_CURSOR to examine execution plans
Using SQL Monitor to Interpret Execution Plans in Oracle
zhlédnutí 10KPřed 6 lety
Using SQL Monitor to Interpret Execution Plans in Oracle
Oracle Database In-Memory Demo By Juan Loaiza
zhlédnutí 1,6KPřed 9 lety
Oracle Database In-Memory Demo By Juan Loaiza
Nice one
Hi, May I know is this technique will consume a lot cpu when query execute? is it will impact to whole system become slow?
How do you tune sql for OLTP systems?
Thanks maria , very nice lesson
Thank you
Thank you 00:00 Intro 00:55 Full Table Scan 02:20 Table Access by ROWID 03:15 Index Unique Scan 03:42 Index Range Scan 04:21 Index Skip Scan 05:11 Full Index Scan 05:59 Fast Full Index Scan 06:36 Index Join 07:11 Bitmap Indexes 08:03 Execution Plan Analysis 08:30 Examples
thank you
Thank you
I have just went through further all your explain plan videos! Great learning channel!
very informative and helpful! Thanks a lot , Maria!
Thanks Maria! What a learning!! Awesome!
I learned a lot.
Mind blowing Maria! You are superb!
Very useful.
First off the first SQL statement is literally terrible. 1) never use a WHERE clause to define joins order of operations WHERE is after join, in million row dataset you can exclude directly BEFORE it hits WHERE clause. This is literally horrific examples. NEVER use functions on indexes. Just use the indexes without functions. This person should be taken off CZcams. Please find other Creators. Holy god.
Hi Trevor, thanks for your comment, but I fear you missed the point of the video. The purpose of the video is to indicate how to tune a SQL statement rather than how to write the best SQL statement. Also, I suspect your knowledge of how the sophisticated Oracle Optimizer works is sadly lacking. Regardless of where you specify a join condition within a SQL statement, the Oracle Optimizer will first find the most efficient access methods to retrieve the data, applying the where clause predicates as either access or filter predicates and then join the data sets.
Excellent use on extended statistics
Hello Maria, Great content as always. I have a question: at 2:44 I still dont get it why the optimizer could not use the actual rows of 10K when he was able to calculate 1% of the 10K to calculate the cardinality estimate ;-)
Thank you for this content !!
Thanks for sharing
I love this Maria, you've made what can be a complex subject so easy to understand!
Excelent video
Pcwc is missing in inout section
Hi Amrit, PCWC stands for PARALLEL COMBINED WITH CHILD. In this case, the Parent operation is Line 7 of the plan or the distribution of blocks from the LINEORDERS table among the parallel server process, i.e., the children of this operation. On line 8 of the plan, we see the parallel server processes scan those blocks and apply the where clause predicate to those scanned rows. This is the Child operation. So, lines 7 and 8 are tied together, and PCWC and PCWP in the IN-OUT column indicate which line is the Parent and which is the Child.
Beautifully Explained! Thanks a lot!!
are you the creator of mariadb?
No, I'm not. But I do share the same name as the creator's daughter, who he named the database after.
Thanks a lot.
Thank you.
Thank you.
Really awesome!!, Please explain about gathering stats for tables and indexes.
Please tell us about why we need to gather stats?
to optimize performance and for compliance issues in corporates
Hi Maria, thank you for your videos, very interesting. Can you recommend a book on sql tuning?
Thank you for the great explanation!
is there a hint of an Irish accent there ?
Indeed there is! I'm originally from Dublin.
@@SQLMaria , can always detect one of our own 😁 Great content BTW!
It's a very good explanation.
Glad you liked it
Great video:)
Glad you enjoyed it
thank you
Hi and Tx! for your explanation, it's more easiest understand the internal functionality of a query
Glad it was helpful!
great MAria....save us this situation
Thanks!
Thanks, informative 👌🏻👍🏻
Great, thanks!
Maria, apart from internal data type conversion is there any other possible reason where partition pruning not used. I have issue where data type of partition column and filter is as date and query still doing table full scan(all partition scan)
Great explanation, great example, Maria. Keep up the good work!
Thank you! 😃
Thanks a lot for the clear explanation Maria ! I will check in my developpers code to find out this kind of mistake. Because, right now, I am adding my partition and subpartition name in the query to force prunning !🤓
Adding the partition name to the FROM clause will always result in pruning, but it requires detailed knowledge of both the schema and the data. For most, that is a luxury we don't have. Good luck with the code review! 😀
Thanks for the video Maria, it was well explained.
Glad you enjoyed it!
Nice video :)
Thank you very much for all your work! Is it a good practice to use a Bitmap type index for a column like cust_id? I assume that this column is like the primary key of the Customers table, so it will contain lots of disparate values (unique ones).
Hi Vivi, Honestly, no it's not a good practice to use a bitmap index on a column like the cust_id column, which has a lot of distinct values. A bitmap index is typically most effective on a column with a low number of distinct values.
@@SQLMaria Thank you
Are you the Maria behind MariaDB???
I'm afraid not Bud. MariaDB is named after the founder's daughter and not me. 🙂
Hi! Great content. I have a question: what exactly is the point of changing the join method if the cost of the new plan is 9x more expensive?
The original plan is slower and its cost is artificially low because the cardinality estimates are incorrect. By fixing the cardinality estimates the cost of both plans changes and the HASH Join plan which was 9X more expensive now becomes the cheaper of the two plans and provides better performance (shorter elapse time).
Hi Maria, ur explanation is extraordinary. I am learning Performance tuning from sites and tutorials, but everyone explaining with simple examples. You are giving valuable information. I wish to post more videos on PT.
Absolutely loved it.