Home
Search results “Select index hint oracle”
Oracle Hints Tutorial for improving performance
 
10:35
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: 4872 TechLake
SQL: Optimizer Hints Part-1
 
05:56
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: 19166 radhikaravikumar
SQL: Inroduction to Indexes
 
07:39
In this tutorial, you'll learn what is indexing!, why we need and how we can create indexes...
Views: 23573 radhikaravikumar
A Story of Indexes and Full Table Scans: Finding All the Red Sweets Part 1
 
08:43
"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: 7449 The Magic of SQL
ORACLE Analytic Functions for Data Warehousing 2/ Arabic
 
12:32
ORACLE Analytic Functions for Data Warehousing 2/ Arabic FIRST_VALUE last_VALUE row_number() --FIRST_VALUE and LAST_VALUE Analytic Functions select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE from employees order by hire_date; select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , min(HIRE_DATE) over ( ) first_HIRE_DATE from employees select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , FIRST_VALUE(HIRE_DATE) over ( ) first_HIRE_DATE from employees order by hire_date; select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , max(HIRE_DATE ) over ( ) last_HIRE_DATE from employees select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE , last_VALUE(HIRE_DATE ) over ( ) last_HIRE_DATE from employees order by HIRE_DATE drop table student_exam_result; CREATE TABLE student_exam_result (student_id number, exame_name varchar2(100), exame_try number, exame_results number ); insert into student_exam_result values(1,'calcluas',1,50); insert into student_exam_result values(1,'calcluas',2,66); insert into student_exam_result values(1,'calcluas',3,20); insert into student_exam_result values(2,'calcluas',1,66); insert into student_exam_result values(2,'calcluas',2,null); insert into student_exam_result values(2,'calcluas',3,7); insert into student_exam_result values(3,'calcluas',1,50); insert into student_exam_result values(3,'calcluas',2,98); insert into student_exam_result values(3,'calcluas',3,null); select * from student_exam_result select student_id, exame_name,exame_try,exame_results, last_value(exame_results) IGNORE NULLS over (PARTITION BY student_id, exame_name ) last_grade from student_exam_result order by student_id, exame_name, exame_try select student_id, exame_name,exame_try,exame_results, last_value(exame_results) IGNORE NULLS over (PARTITION BY student_id, exame_name ) last_grade, max(exame_results) over (PARTITION BY student_id, exame_name ) max_grade, avg(exame_results) over (PARTITION BY student_id, exame_name ) avg_grade from student_exam_result order by student_id, exame_name, exame_try ------------------------ select employee_id,first_name ,row_number() over(order by employee_id ) from employees select employee_id,first_name ,rownum from employees select employee_id,first_name ,department_id, row_number() over(partition by department_id order by employee_id ) from employees
Views: 551 khaled alkhudari
SQL: Explain Plan for knowing the Query performance
 
05:17
In this tutorial, you'll learn how to compare queries to know the better performance query..
Views: 92741 radhikaravikumar
Why Isn't My Query Using an Index?
 
47:01
“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: 262 Oracle Developers
Making Smart Scan for Exadata Work (Demonstration)
 
03:16
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: 3057 SkillBuilders
Calculate query performance with Explain Plan in Oracle PLSQL.
 
09:14
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: 3599 Subhroneel Ganguly
Get Column Names From a Specific Table  - SQL in Sixty Seconds #083
 
01:01
Question: How to get column names from a specific table in SQL Server? Answer: This is a very popular question and there are multiple ways to get column names of a specific table in SQL Server. Let us see various methods. You can choose the method which you prefer to use for your solutions. Click here to get related scripts discussed in this blog post. https://blog.sqlauthority.com/2017/06/29/sql-server-get-column-names-specific-table/
Views: 7028 Pinal Dave
B*Tree Index Fundamentals(Indexes in Oracle-1)
 
17:42
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: 48653 Anindya Das
Introduction to Basics of a Query Hint - SQL in Sixty Seconds #013
 
01:53
This video explains the fundamentals of the SQL Server Query Hints. Visit http://bit.ly/KH9u8I for Script, Quiz and further learning.
Views: 8599 Pinal Dave
When to use Oracle Database Bitmap Indexes Lesson 1
 
00:49
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: 472 SkillBuilders
SQL: Types of Indexing
 
05:23
In this tutorial, you'll learn how to make use of different indexes...
Views: 31515 radhikaravikumar
PL/SQL tutorial 57: PL/SQL Collection Associative Array in Oracle Database
 
08:05
Manish Sharma from RebellionRider presents you the latest PL/SQL tutorial on Collection ASSOCIATIVE ARRAY. In this video, you will learn – - What are Associative Arrays? - How to create Associative Arrays? - How to Insert & Update the data of Associative Array and - How to retrieve the data from an Associative array using two different ways? So if you are wondering whether associative array is a persistent form of collection or not; Is it bounded or unbounded in nature; why should we use this specific collection over others such as VARRAY or Nested Table; What are the differences or similarities between associative array and other collections? Then go ahead and watch the video to find out the answers to all these questions. Hope you will enjoy watching. Do make sure to Like and Share this post/video because that will help others in learning as well as help me and my channel in reaching out. This way we can educate more and more people for FREE. Most importantly SUBSCRIBE to the channel as many such interesting tutorials are coming your way. Thanks. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/associative-array Previous Tutorial ►While Loop: https://youtu.be/IIlc4Sr7kFE ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the About section Please please LIKE and SHARE my videos it makes me happy. Thanks for liking, commenting, sharing and watching more of our videos This is Manish from RebellionRider.com ♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS
Views: 11132 Manish Sharma
15 Hierarchical Queries
 
03:41
Views: 6438 Husnah Hasan
Optimizer Statistics
 
11:10
Optimizer Statistics watch more videos at https://www.tutorialspoint.com/videotutorials/index.htm Lecture By: Mr. Parth Panjab, Tutorials Point India Private Limited
DML Processing in an Oracle Database -  DBArch Video 8
 
09:07
This video explains the steps involved in processing a DML statement in an Oracle Database Server. 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: 47098 Ramkumar Swaminathan
OracleSQL#17 How Oracle SQL Query Work Internally
 
08:54
explaining how select SQL statement executes internally in Oracle database Or In other words how select statement retrieves data from the database Or ow a SELECT statement process query in Oracle Database Or Order of Query Execution In OracleSQL, SQL Parsing, SQL Optimization, row source generation, and SQL execution. In this series we cover the following topics: SQL basics, create table oracle, SQL functions, SQL queries, SQL server, SQL developer installation, Oracle database installation, SQL Statement, OCA, Data Types, Types of data types, SQL Logical Operator, SQL Function,Join- Inner Join, Outer join, right outer join, left outer join, full outer join, self-join, cross join, View, SubQuery, Set Operator. follow me on: Facebook Page: https://www.facebook.com/LrnWthr-319371861902642/?ref=bookmarks Contacts Email: [email protected] Instagram: https://www.instagram.com/lrnwthr/ Twitter: https://twitter.com/LrnWthR
Views: 204 EqualConnect Coach
Oracle LISTAGG Function
 
04:57
https://www.databasestar.com/oracle-listagg/ The Oracle LISTAGG function allows you to aggregate or combine string values from multiple rows into a single row. For example, it turns this data: FIRST_NAME Adam Brad Carrie Into this data: FIRST_NAME Adam, Brad, Carrie The same data is shown, but it’s in one row and not multiple. It’s a valuable string manipulation function, and once you know what it is and how it works, you’ll be able to use it in your queries as needed. So what does it look like? The function is: LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause] These parameters are: measure_expr: This is a column or expression that you want to concatenate the values of. In the example above, it was the first_name column. Mandatory. delimiter: This is the character between each of the measure_expr values. Optional, the default is a comma. order_by_clause: This is the order that the values from the measure_expr are listed. Mandatory. query_partition_clause: This allows you to use LISTAGG as an analytic function, allowing you to show LISTAGG in groups for different rows. One thing to be aware of is that the output of the Oracle LISTAGG function is limited to 4,000 bytes. If you get more than this, you’ll receive an error. The most common way around this that I’ve seen is to write a custom function and use a CLOB. Watch the video to find out more and see some examples. For more information about the Oracle LISTAGG function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-listagg/
Views: 2203 Database Star
Why do you need the Oracle Database Optimizer?
 
01:50
Presentations explains the essential idea of the Oracle Database Optimizer
Views: 4401 Jean-Francois Verrier
How to understand and use the query optimizer – Couchbase Connect 2016
 
50:50
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: 465 Couchbase
Hint DRIVING_SITE para otimizar SQL distribuído
 
09:32
Aprenda a otimizar SQL distribuído utilizando o hint DRIVING_SITE
Views: 240 Fabio Prado
How can I speed up a query if an index cannot help?
 
08:20
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
Views: 991 Connor McDonald
Estimated & actual execution plans with SQL Query Tuning | Pluralsight
 
06:31
SQL Server Performance: Introduction to Query Tuning | http://www.pluralsight-training.net/microsoft/courses/TableOfContents?courseName=query-tuning-introduction It takes more than "Select *" to make a great SQL query. In this video excerpt from Vinod Kumar's and Pinal Dave's new course SQL Server Performance: Introduction to Query Tuning you'll see how to view the execution plans for your queries including the differences between estimated and actual plans as well as how your query hints can affect the execution plan in unforeseen ways. In the full course, the two go on to cover topics such as indexing techniques, how order of tables and query hints affect execution, and how to use various tools to optimize queries. Visit us at: Facebook: https://www.facebook.com/pluralsight Twitter: https://twitter.com/pluralsight Google+: https://plus.google.com/+pluralsight LinkedIn: https://www.linkedin.com/company/pluralsight Instagram: http://instagram.com/pluralsight Blog: http://blog.pluralsight.com/ 3,500 courses unlimited and online. Start your 10-day FREE trial now: https://www.pluralsight.com/a/subscribe/step1?isTrial=True Estimated & actual execution plans with SQL Query Tuning | Pluralsight -~-~~-~~~-~~-~- Push your limits. Expand your potential. Smarter than yesterday- https://www.youtube.com/watch?v=k2s77i9zTek -~-~~-~~~-~~-~-
Views: 14334 Pluralsight
SQL: Inline Views
 
06:24
In this Tutorial, you'll learn the advantages of inline view and how to make use of it..
Views: 17546 radhikaravikumar
SQL: FBI(Functional Based Indexes)
 
04:11
In this tutorial, you'll learn how to create FBI...
Views: 12545 radhikaravikumar
Oracle SQL Performance Tuning for Developers: Understand Explain Plans
 
01:42
Understanding Explain Plans is an excerpt from Oracle SQL Performance Tuning for Developers: http://www.informit.com/store/oracle-sql-performance-tuning-for-developers-livelessons-9780134117027 6+ Hours of Video Instruction The focus of Oracle SQL Performance Tuning for Developers LiveLessons is to illustrate coding techniques that ensure a consistent response time between instances and releases of the Oracle database. This course works closely with performance tuning of actual SQL statements. Description In this video training, Dan Hotka starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course then goes in-depth on understanding and controlling the Explain Plan, which is how and in what order Oracle retrieves data. The discussion includes considerable detail, with SQL examples, on how the optimizers--both rule-based and cost-based, but mostly cost-based--make their decisions. Students will work with a variety of SQL statements, reviewing Explain Plans and making changes to make these SQL statements perform better. Lectures include index design, using hints and coding style to control the Explain Plans, and how to use useful tools such as index monitoring, SQL Trace, and the PL/SQL profiler. This LiveLessons course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used. This course includes Oracle10g, Oracle11g, and Oracle12c SQL tuning topics. Skill Level Intermediate Learn How To Read and understand Explain Plan content Review an Explain Plan and tell quickly if this is a good plan Understand a good index column candidate from a not-so-good candidate Quickly tell the likelihood if your SQL will use an existing index Use coding and a variety of Hints (directives) that can produce better performing SQL Execute and interpret SQL trace output Who Should Take This Course Oracle programmers Oracle database administrators who need additional training on SQL tuning Course Requirements Working knowledge of the SQL query language http://www.informit.com/store/oracle-sql-performance-tuning-for-developers-livelessons-9780134117027
Views: 3173 LiveLessons
Efficient Function Calls From SQL (Part 6) : Function-Based Indexes
 
02:20
The sixth part of a mini-series of videos showing how you can improve the performance of function calls from SQL. In this episode, we look at using function-based indexes to improve the performance of queries that use function calls in the WHERE clause. For more information see: https://oracle-base.com/articles/8i/function-based-indexes https://oracle-base.com/articles/misc/efficient-function-calls-from-sql#functions-in-the-where-clause Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Ronald Bradford : Blog: http://ronaldbradford.com/blog/ Twitter: https://twitter.com/RonaldBradford Cameo appearances are for fun, not an endorsement of the content of this video.
Views: 1387 ORACLE-BASE.com
Oracle SQL Performance Tuning for Developers: Introduction
 
04:14
This video is an introduction to Oracle SQL Performance Tuning for Developers: http://www.informit.com/store/oracle-sql-performance-tuning-for-developers-livelessons-9780134117027 6+ Hours of Video Instruction The focus of Oracle SQL Performance Tuning for Developers LiveLessons is to illustrate coding techniques that ensure a consistent response time between instances and releases of the Oracle database. This course works closely with performance tuning of actual SQL statements. Description In this video training, Dan Hotka starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course then goes in-depth on understanding and controlling the Explain Plan, which is how and in what order Oracle retrieves data. The discussion includes considerable detail, with SQL examples, on how the optimizers--both rule-based and cost-based, but mostly cost-based--make their decisions. Students will work with a variety of SQL statements, reviewing Explain Plans and making changes to make these SQL statements perform better. Lectures include index design, using hints and coding style to control the Explain Plans, and how to use useful tools such as index monitoring, SQL Trace, and the PL/SQL profiler. This LiveLessons course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used. This course includes Oracle10g, Oracle11g, and Oracle12c SQL tuning topics. Skill Level Intermediate Learn How To Read and understand Explain Plan content Review an Explain Plan and tell quickly if this is a good plan Understand a good index column candidate from a not-so-good candidate Quickly tell the likelihood if your SQL will use an existing index Use coding and a variety of Hints (directives) that can produce better performing SQL Execute and interpret SQL trace output Who Should Take This Course Oracle programmers Oracle database administrators who need additional training on SQL tuning Course Requirements Working knowledge of the SQL query language http://www.informit.com/store/oracle-sql-performance-tuning-for-developers-livelessons-9780134117027
Views: 3045 LiveLessons
Using Parallel and Cardinal Hint
 
07:44
This is a demo on using Parallel and Cardinal Hint to tune queries in ILM.
Views: 1252 Informatica Support
Oracle SQL Tuning - Oracle Execution Plans for Beginners
 
13:39
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/
Oracle SQL Tuning - How to Read Execution Plans (Explain Plan)
 
13:39
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: 62415 SkillBuilders
How to Detect Soft Corruption in Oracle 12c Database
 
18:19
Detection of Soft Corruption in 12c: To use the scripts below replace : , ",# and $ by proper bracket Lets Create a tablespace and small table. SQL: create tablespace DEMO1 datafile '/u02/tstdb1/TSTDB1/datafile/demo01.dbf' size 50M SQL: create table objects tablespace DEMO1 as select * from dba_objects; SQL: alter table objects add constraint pk_obj primary key #object_id$; SQL: create index idx_obj_name on objects#object_name$ tablespace demo1; Backup tablespace. RMAN: backup tablespace DEMO1; We need to put the DB in archivelog first Rebuild index with NOLOGGING option to simulate soft corruption later. RMAN: alter index idx_obj_name rebuild nologging; Confirm that we have datafiles that require backup because they have been affected with NOLOGGING operation. RMAN: report unrecoverable; Simulate corruption. RMAN: alter database datafile 5 offline; RMAN: restore datafile 5; RMAN: recover datafile 5; RMAN: alter database datafile 5 online; Query table with corrupted index and notice error. SQL: select count#*$ from objects where object_name like 'A%'; Let’s perform validation of datafile to check block corruption. RMAN: backup validate check logical datafile 5; Notice that we have 457 blocks marked corrupt but v$database_block_corruption view is empty. SQL: select count#*$ from v$database_block_corruption; Let’s query v$nonlogged_block view. set lines 200 set pages 999 select file#, block#, blocks,object#,reason from v$nonlogged_block; Will RMAN detect that we have corrupted blocks? RMAN: backup datafile 5; RMAN backup won’t fail due to NOLOGGING corrupt blocks and our backup will contain soft corrupted blocks. Let’s Identify corrupt segments using v$nonlogged_block view. set lines 2000 set pages 9999 col owner for a20 col partition_name for a10 col segment_name for a20 SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# , greatest#e.block_id, c.block#$ corr_start_block# , least#e.block_id+e.blocks-1, c.block#+c.blocks-1$ corr_end_block# , least#e.block_id+e.blocks-1, c.block#+c.blocks-1$ - greatest#e.block_id, c.block#$ + 1 blocks_corrupted FROM dba_extents e, V$NONLOGGED_BLOCK c WHERE e.file_id = c.file# AND e.block_id "= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 := c.block# UNION SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# , header_block corr_start_block# , header_block corr_end_block# , 1 blocks_corrupted FROM dba_segments s, V$NONLOGGED_BLOCK c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1 UNION SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# , greatest#f.block_id, c.block#$ corr_start_block# , least#f.block_id+f.blocks-1, c.block#+c.blocks-1$ corr_end_block# , least#f.block_id+f.blocks-1, c.block#+c.blocks-1$ - greatest#f.block_id, c.block#$ + 1 blocks_corrupted FROM dba_free_space f, V$NONLOGGED_BLOCK c WHERE f.file_id = c.file# AND f.block_id "= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 := c.block# order by file#, corr_start_block# / This is the best outcome to get if you notice corruption errors. All errors are related to index corruption so we could fix this problem rebuilding index. alter index idx_obj_name rebuild; Simply issuing "alter index rebuild" command won't work. We should mark index unusable to drop segment before rebuilding it or just rebuild index with online option. It is better choice to mark index unusable because you don't need additional space then, but I will simply rebuild index with online option and see what will happen. SQL: alter index idx_obj_name rebuild online; Index altered. SQL: select count#*$ from objects where object_name like 'A%'; No errors... but, let's validate datafile for corruption. RMAN: backup validate check logical datafile 5; Notice "Marked Corrupt" column. Hm... 457 like before. Don't worry, this is not new corruption. These are FREE blocks which will be reused and Oracle will automatically re-format those blocks. Query the v$nonlogged_block view again as given above. We could force re-formatting creating dummy table and inserting data to dummy table. Check Doc ID 336133.1. create table s # n number, c varchar2#4000$ $ nologging tablespace DEMO1; SQL: BEGIN FOR i IN 1..1000000 LOOP INSERT /*+ APPEND */ INTO sys.s select i, lpad#'REFORMAT',3092, 'R'$ from dual; commit ; END LOOP; END; / SQL: drop table sys.s purge; Notice that we don't have corrupted blocks any more. RMAN: backup validate check logical datafile 5;
Views: 130 OracleDBA
PL/SQL Tutorial : REF CURSOR with USING Clause
 
02:45
PL/SQL Tutorial : REF CURSOR with USING Clause SQL Tutorial SQL Tutorial for beginners PLSQL Tutorial PLSQL Tutorial for beginners PL/SQL Tutorial PL SQL Tutorial PL SQL Tutorial for beginners PL/SQL Tutorial for beginners Oracle SQL Tutorial
Views: 174 TechLake
SQL Hints, Tips, Tricks, and Tuning
 
56:18
What is the difference between inner, outer, cross and natural join? What is a correlated subselect? What is the advantage and what the disadvantage of an index. Is NOT and OR evil? When do I need normalisation and when denormalisation? All this questions and lots of other performance tips and tricks on SQL level will be given in the talk.
Views: 14635 Andrea Ross
Quickly Tuning SQL with SQL Developer
 
21:31
In this video, I show a few shortcuts I used to make quick tuning work of a problematic SQL statement in SQL Developer 4.1
Views: 9389 BPeaslandDBA
Hash Partition in Oracle
 
05:08
This course has been designed to impart the in-depth knowledge about the partitioning types and architecture in the Oracle RDBMS. For more information on this course, please visit https://www.koenig-solutions.com/oracle-database-12-c-implement-partitioning-training.aspx
Views: 51 Koenig Solutions
Oracle Database Hints Tuning Tips Presentation Video Oracle DB
 
05:28
For a quick reference on oracle visit http://PocketORACLE.blogspot.com
Views: 5022 TheEzguide
SQL Server 2017 Resumable Online Index Rebuild
 
05:48
This is a new feature in the pre release of SQL Server 2017. Resumable Online Index Rebuid will simplify Index Rebuild on Larger tables in PROD when needed
Views: 200 mithun roy
Lecture 5   Oracle Database Performance Tuning 1 - Guy Harrison
 
39:46
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: 30147 Quest University
Understanding Locking in SQL Server - SQL Server Tutorial
 
25:54
In this video you will understand what is locking in SQL Server? video gives brief overview Following: What is Resource types in locking of SQL Server? What is Lock Modes in SQL Server? It gives live view of looking at different type of lock modes as well as the resources types where the locks are placed. It describes in greater detail about RID, Key, Page, Extent, Table and DB Resource types in SQL Server Locking concept. It also explains Shared Locks, Update Lock, Exclusive Locks, Intent Lock, Schema Lock and Bulk Update locks in SQL Server. Blog post link for the video with script http://sqlage.blogspot.com/2015/04/understanding-locking-in-sql-server-sql.html Complete Step by Step List for DBA Tutorial Videos: http://sqlage.blogspot.com/search/label/SQL%20Sever%202014%20DBA%20Video%20Tutorial
Views: 53855 TechBrothersIT
How The Query Optimizer Works
 
01:58
http://bit.ly/1EJsBPK Code Above Is For Performance Tuning Course. Limited Number at this price.
Views: 2803 Mike
8. Use LOG ERRORS to suppress SQL errors at the row level.
 
06:23
The impact of a non-query DML statement is usually "all or nothing". If my update statement identifies 100 rows to change, then either all 100 rows are changed or none are. And none might be the outcome if, say, an error occurs on just one of the rows (value too large to fit in column, NULL value for non-NULL column, etc.). But if you have a situation in which you would really like to "preserve" as many of those row-level changes as possible, you can add the LOG ERRORS clause to your DML statement. Then, if any row changes raise an error, that information is written to your error log table, and processing continues. IMPORTANT: if you use LOG ERRORS, you must must must check that error log table immediately after the DML statement completes. You should also enhance the default error log table. Related blog post: http://stevenfeuersteinonplsql.blogspot.com/2016/03/nine-good-to-knows-about-plsql-error.html ======================================== Practically Perfect PL/SQL with Steven Feuerstein 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.
Oracle SQL Join Tutorials
 
19:48
Oracle SQL Join Tutorials. Different types of joins and examples. For Full Course Experience Please Go To http://mentorsnet.org/course_preview?course_id=4 Full Course Experience Includes 1. Access to course videos and exercises 2. View & manage your progress/pace 3. In-class projects and code reviews 4. Personal guidance from your Mentors
Views: 101625 Oresoft LWC
PL/SQL Tutorial | User Defined Exception | Pragma Exception_INIT in Oracle Database 11g
 
23:33
Learn in depth with example about creating user defined exception using Pragma Exception_INIT and Raise_application_error following handling of different types of system named and unnamed exception in details. How to handle exception in oracle database, How to handle user defined exception in oracle database, What is Pragma in Exception Handling, What is Pragma Exception_INIT in oracle, Types of exception available in oracle, How to use Raise_application_error in oracle, How to use custom exception in oracle, How to use pragma autonomous transaction, SQLERRM, SQLCODE, Raise_application_error, In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The 5 types of Pragma directives available in Oracle are listed below: * PRAGMA AUTONOMOUS_TRANSACTION * PRAGMA SERIALLY_REUSABLE * PRAGMA RESTRICT_REFRENCES * PRAGMA EXCEPTION_INIT * PRAGMA INLINE Details of System Exception: DUP_VAL_ON_INDEX ORA-00001 You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index. TIMEOUT_ON_RESOURCE ORA-00051 You were waiting for a resource and you timed out. TRANSACTION_BACKED_OUT ORA-00061 The remote portion of a transaction has rolled back. INVALID_CURSOR ORA-01001 You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor. NOT_LOGGED_ON ORA-01012 You tried to execute a call to Oracle before logging in. LOGIN_DENIED ORA-01017 You tried to log into Oracle with an invalid username/password combination. NO_DATA_FOUND ORA-01403 You tried one of the following: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package. TOO_MANY_ROWS ORA-01422 You tried to execute a SELECT INTO statement and more than one row was returned. ZERO_DIVIDE ORA-01476 You tried to divide a number by zero. INVALID_NUMBER ORA-01722 You tried to execute a SQL statement that tried to convert a string to a number, but it was unsuccessful. STORAGE_ERROR ORA-06500 You ran out of memory or memory was corrupted. PROGRAM_ERROR ORA-06501 This is a generic "Contact Oracle support" message because an internal problem was encountered. VALUE_ERROR ORA-06502 You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data. CURSOR_ALREADY_OPEN ORA-06511 You tried to open a cursor that is already open. Linkedin: https://www.linkedin.com/in/aditya-kumar-roy-b3673368/ Facebook: https://www.facebook.com/SpecializeAutomation/
DYN6: Method 4 dynamic SQL with DBMS_SQL (PL/SQL Channel)
 
25:48
When you don't know the number of columns you are selecting or the number of placeholders that need binding, you are faced with method 4 dynamic SQL. And the solution to this very generic scenario is the DBMS_SQL package. This video was taken from PLSQLChannel.com, originally recorded before Steven Feuerstein re-joined Oracle in March 2014. ======================================== Practically Perfect PL/SQL with Steven Feuerstein 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.
Oracle Performance Tuning - Read and interpret Explain Plan
 
17:43
Connect with me or follow me at https://www.linkedin.com/in/durga0gadiraju https://www.facebook.com/itversity https://github.com/dgadiraju https://www.youtube.com/c/TechnologyMentor https://twitter.com/itversity
Views: 36511 itversity

Heading for apa annotated bibliography
Investment banking cover letter uk
What is a good essay writing service
7 eleven employment application online
Civil service essay writing