The evolution of adaptive cursor sharing, starting with the need for the optimizer to be able to look (peek) at the value of a bind variable in order to determine the best execution plan.
blog: https://connor-mcdonald.com
twitter: https://twitter.com/connor_mc_d
Subscribe for new tech videos every week
Music: Night Owl (Broke For Free)
#oracle #performance

TO USE OR PRINT this presentation click : http://videosliders.com/r/184
==============================================================
All About Binds Thomas Kyte
,It’s All About Binds
,Agenda Performance
Is it just about sharing SQL (or is this really a parsing talk in disguise)
Scalability Security Do I always want to bind? What is bind variable peeking? Is it good or evil in disguise or a bit of both?
I’m binding, but it isn’t sharing – what’s up with that? So the developers don't bind is cursor_sharing = force/similar appropriate system wide? What is the real difference between cursor_sharing = force/similar and which should we use under what circumstances? ,Performance What is involved in all Parses
The “conventional” parse - syntax
Semantic check What about a hard parse Optimization (can you spell C.P.U…)
Row Source Generation And then we can finally execute it Soft Parse is lighter weight But it is called a “shared” pool, not “your” pool
Shared data structures have to be protected Optimization can be avoided Row Source Generation can be avoided Bind01.sql
,Performance Wonder if it might affect memory utilization?
Strange that count(*) is so low for that first query isn’t it. Unfortunate that sum(sharable_mem) is so high (and remember, it really is 10 times that amount) Bind02.sql
,Scalability But it runs fast enough and I’ll buy more memory
Does it really? Run bind03.sql
[email protected]&amp;gt; select 11/10000 from dual; 11/10000 ---------- .0011
,Latch Algorithm Loop for I in 1 .. 1 loop try to get latch if got latch, return if I = 1 then misses=misses+1 end loop INCREMENT WAIT COUNT sleep Add WAIT TIME End loop;
,More multi-user USERS NOBIND_CPU PARSE_MANY_CPU PARSE_ONCE_CPU ------- ---------- -------------- -------------- 1.00 .27 .07 .03 2.00 .72 .21 .09 3.00 1.46 .38 .13 4.00 2.59 .67 .27 5.00 3.20 .85 .34 6.00 4.20 1.01 .40 7.00 4.79 1.20 .51 8.00 5.74 1.44 .53 9.00 6.27 1.60 .64 10.00 7.16 1.76 .72
,Security Google sql injection
Funny thing happened during my last column create or replace procedure set_udump (p_udump in varchar2) as begin execute immediate 'alter system set user_dump_dest = '''||p_udump||''' scope=memory'; end; /
,Security Google sql injection
Funny thing happened during my last column create or replace procedure set_udump (p_udump in varchar2) as begin execute immediate 'alter system set user_dump_dest = '''||p_udump||''' scope=memory'; end; / begin set_udump('C:\ORA4\admin\ora4\udump2'' scope=memory utl_file_dir=''*'' scope=spfile user_dump_dest=''C:\ORA4\admin\ora4\udump2'); end;
,Security Google sql injection
Funny thing happened during my last column create or replace procedure set_udump (p_udump in varchar2) as begin if ( p_udump NOT LIKE '%=%' ) then execute immediate 'alter system set user_dump_dest = '''||p_udump||''' scope=memory'; else raise_application_error(-20000,'Sorry, but for safety reasons this procedure does not allow &amp;quot;=&amp;quot; in the parameter value'); end if; end;
,Do I always want to bind? Always say “Never say Never”
Never say “Always” You do not want to Over Bind
Always Bind Why…. ,Do I always want to bind? Over Binding
Compulsive disorder to eradicate all literals in SQL
Brought on by taking good advice to an illogical extreme Do we need to bind those? Might it be a bad thing to bind those? Begin for x in ( select object_name from user_objects where object_type in ( ‘TABLE’, ‘INDEX’ )) loop …
,Do I always want to bind? Always Binding
Data warehouse – no way.
When you run queries per second, yes. When you run queries that take seconds, maybe, maybe no. Conside

Anju Garg is an Oracle Ace Associate with over 12 years of experience in IT Industry in various roles. Since 2010, she has been involved in teaching and has trained more than a hundred DBAs from across the world in various core DBA technologies like RAC, Data guard, Performance Tuning, SQL statement tuning, Database Administration etc. She is a regular speaker at Sangam and OTNYathra.
She also writes articles for All Things Oracle.
She is passionate about learning and has keen interest in RAC and Performance Tuning. She shares her knowledge via her technical blog at http://oracleinaction.com/
ABSTRACT---
To improve optimizer estimates in case of skewed data distribution , histograms can be created. Prior to 12c frequency and height balanced histograms could be created.
if no. of buckets &gt;= NDV, frequency histogram is created and the optimizer makes correct estimates.
If no. of buckets &lt; NDV, height balanced histogram is created and accuracy of optimizer estimates depends on whether a key value is an endpoint or not.
The problem of optimizer misestimates in case of height balanced histograms is resolved to a large extent in Oracle Database 12c by introducing top-frequency and hybrid histograms which are created if no. of buckets &lt; NDV.
This webinar explores Pre as well post 12c histograms while highlighting the top-frequency and hybrid histograms introduced in Oracle Database 12c.

In this session we will look into some internals of Oracle background process communication and also some special types of wait events that most people aren’t aware of. We will use some exotic tracing for internals research and fun and some of this stuff is actually useful in real life too! I’m not going to reveal everything upfront, as this is a secret internals hacking session after all ;-)
We will use various techniques to research what the “reliable message” wait event is about and how reliable background process communication is orchestrated in Oracle.
This is a hacking session, not formal structured training, so I’ll just do free form demos and talk (probably no slides, just hacking stuff on the command line).
Also, check out my full-week Advanced Oracle Troubleshooting online training at https://blog.tanelpoder.com/seminar

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.

blog: connor-mcdonald.com
Highlights from the April DBA Office Hours session.
Office Hours is 100% free Q&A sessions held every month by Oracle experts to help you succeed with the Oracle suite of technologies.
Music: Smells Like Summer - Del (Vlog Music No Copyrighted)
Video Link: https://youtu.be/IrkMsqcOjGU

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.

The Video Explains the difference between Local Partitioned Indexes(Prefixed vs Non Prefixed Indexes). and Global Partitioned Index along with the challenges in maintaining Global partitioned Indexes when the underlying tables partitioned is dropped/truncated/Merged/Moved.
Local Partitioned Index Shares the same boundaries as the table and are in the same number as table partitions they are widely used in DSS and DWH systems. While Global Partitioned Index are predominantly used in OLTP systems

Hello friends in this video i'm just showing to you what is optimizer statistics into oracle,optimizer help to improve the performance of sql statements during execution period. #ORACLEWORLD #OptimizerStatistics
Oracle database Unbeatable,Unbreakable Platform.

Histograms are essential ingredient of Tuning SQL in the Oracle Database. In Oracle 12c there are new features related to Histogram. In this part of this histogram tutorial, Oracle Certified Master John Watson describes the importance of histograms. Histograms collect essential statistics such as data skew and thus accurately predict how many rows a predicate will select. This impacts critical decisions such as join order, join method and access method.

On May 8 2018, Can Tuzla, Senior PM for Multitenant covered a new isolation concept introduced in 12.2, called Lockdown Profiles. He reviewed isolation practices since 12.1 and explained the benefits of Lockdown Profiles with various use cases.
03:24 What is a lockdown profile?
10:27 Capabilities enabled by lockdown profiles
21:10 A working example
25:37 Q&A begins
AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists.
https://asktom.oracle.com/
Oracle Developers portal: https://developer.oracle.com/
Sign up for an Oracle Cloud trial: https://cloud.oracle.com/en_US/tryit
music: bensound.com

This video shows how to use SnapCenter to quickly and space efficiently clone an oracle production database to an alternate host running in a public cloud (AWS, Azure) or a private cloud.
Want to learn more? Visit the SnapCenter Resources page: https://mysupport.netapp.com/snapcenter/resources

Learn how to use Oracle Histograms! Histograms are essential ingredient of Tuning SQL in the Oracle Database. In this part of the tutorial, SkillBuilders' Oracle Certified Master John Watson describes the importance of histograms. Histograms collect essential statistics such as data skew and thus accurately predict how many rows a predicate will select. This impacts critical decisions such as join order, join method and access method. See all free Oracle Database tutorials from SkillBuilders at http://www.skillbuilders.com/free-oracle-tutorials.

Learn how to tune SQL with Oracle Histograms! In this part of the tutorial, SkillBuilders' Oracle Certified Master John Watson wraps up the points made and provides conclusions and advice - not to be missed! Watch all lesson, FREE, at http://www.skillbuilders.com/How-to-Tune-Oracle-12c-SQL-with-Histograms.