- 72
- 891 031
The Magic of SQL
United Kingdom
Registrace 11. 02. 2015
Hi, I'm Chris Saxon, part of Gerald Venzl's Oracle Developer Advocate team. It's our job to help you get the best out of Oracle technologies and (hopefully) have fun while doing so ;)
Here we celebrate the Magic of SQL. We'll show how you can use it to solve problems and perform SQL "magic". These skills will help you build better, faster applications using Oracle Database.
Subscribe to learn SQL, improve your skills and understand how Oracle Database works.
Take SQL quizzes and classes at Oracle Dev Gym devgym.oracle.com
Ask me your SQL questions on Ask TOM asktom.oracle.com
Read SQL blog posts at blogs.oracle.com/sql
Reach me on Twitter ChrisRSaxon
My personal site www.chrissaxon.co.uk/
Here we celebrate the Magic of SQL. We'll show how you can use it to solve problems and perform SQL "magic". These skills will help you build better, faster applications using Oracle Database.
Subscribe to learn SQL, improve your skills and understand how Oracle Database works.
Take SQL quizzes and classes at Oracle Dev Gym devgym.oracle.com
Ask me your SQL questions on Ask TOM asktom.oracle.com
Read SQL blog posts at blogs.oracle.com/sql
Reach me on Twitter ChrisRSaxon
My personal site www.chrissaxon.co.uk/
How to Find Slow SQL: Databases for Developers: Performance #9
In order to tune SQL, you need to know what the slow statement is. But often you're just told that "the database" is slow!
To find the slow components, you need a breakdown of how long each piece of code takes to execute. You can do this in Oracle Database using:
- A SQL trace
- The PL/SQL hierarchical profiler
This video shows you how to do these with Oracle SQL Developer.
The script to spool the trace file is:
-----
set serveroutput off
set pagesize 0
set echo off
set feedback off
set trimspool on
set heading off
set tab off
set long 1000000
spool c:\temp\student_trace.trc
select payload
from v$diag_trace_file_contents
where trace_filename = (
select substr (
value,
instr ( value, '/', -1 ) + 1
) filename
from v$diag_info
where name = 'Default Trace File'
)
order by line_number;
spool off
-----
Need help with SQL?
Ask us over on AskTOM: asktom.oracle.com
Twitter: ChrisRSaxon
Daily SQL Twitter tips: SQLDaily
All Things SQL blog: blogs.oracle.com/sql/
Test your SQL Skills on the Oracle Dev Gym: devgym.oracle.com/
============================
The Magic of SQL with Chris Saxon
Copyright © 2020 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.
To find the slow components, you need a breakdown of how long each piece of code takes to execute. You can do this in Oracle Database using:
- A SQL trace
- The PL/SQL hierarchical profiler
This video shows you how to do these with Oracle SQL Developer.
The script to spool the trace file is:
-----
set serveroutput off
set pagesize 0
set echo off
set feedback off
set trimspool on
set heading off
set tab off
set long 1000000
spool c:\temp\student_trace.trc
select payload
from v$diag_trace_file_contents
where trace_filename = (
select substr (
value,
instr ( value, '/', -1 ) + 1
) filename
from v$diag_info
where name = 'Default Trace File'
)
order by line_number;
spool off
-----
Need help with SQL?
Ask us over on AskTOM: asktom.oracle.com
Twitter: ChrisRSaxon
Daily SQL Twitter tips: SQLDaily
All Things SQL blog: blogs.oracle.com/sql/
Test your SQL Skills on the Oracle Dev Gym: devgym.oracle.com/
============================
The Magic of SQL with Chris Saxon
Copyright © 2020 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.
zhlédnutí: 15 842
Video
How to Make Inserts, Updates, and Deletes Faster: Databases for Developers: Performance #8
zhlédnutí 18KPřed 4 lety
How to Make Inserts, Updates, and Deletes Faster: Databases for Developers: Performance #8
How do nested loop, hash, and merge joins work? Databases for Developers Performance #7
zhlédnutí 66KPřed 4 lety
How do nested loop, hash, and merge joins work? Databases for Developers Performance #7
Count Millions of Rows Fast with Materialized Views: Databases for Developers: Performance #6
zhlédnutí 30KPřed 4 lety
Count Millions of Rows Fast with Materialized Views: Databases for Developers: Performance #6
Why Isn't My Query Using an Index? Databases for Developers: Performance #5
zhlédnutí 12KPřed 4 lety
Why Isn't My Query Using an Index? Databases for Developers: Performance #5
How to Create Database Indexes: Databases for Developers: Performance #4
zhlédnutí 25KPřed 4 lety
How to Create Database Indexes: Databases for Developers: Performance #4
What to Look for in Execution Plans: Databases for Developers: Performance #3
zhlédnutí 15KPřed 4 lety
What to Look for in Execution Plans: Databases for Developers: Performance #3
What are Optimizer Statistics? Databases for Developers: Performance #2
zhlédnutí 18KPřed 4 lety
What are Optimizer Statistics? Databases for Developers: Performance #2
How to Read an Execution Plan: Databases for Developers: Performance #1
zhlédnutí 60KPřed 4 lety
How to Read an Execution Plan: Databases for Developers: Performance #1
What Are Not Null Constraints? A Video Quiz
zhlédnutí 725Před 5 lety
What Are Not Null Constraints? A Video Quiz
Columns and Data Types: Databases for Developers #2
zhlédnutí 25KPřed 5 lety
Columns and Data Types: Databases for Developers #2
What is the Dual Table in Oracle Database? A Video Quiz
zhlédnutí 2,8KPřed 5 lety
What is the Dual Table in Oracle Database? A Video Quiz
How do SQL queries return correct results? Databases for Developers #18
zhlédnutí 3,6KPřed 6 lety
How do SQL queries return correct results? Databases for Developers #18
How to insert or update rows in one statement: Databases for Developers #17
zhlédnutí 9KPřed 6 lety
How to insert or update rows in one statement: Databases for Developers #17
Hierarchical SQL Queries: Databases for Developers #16
zhlédnutí 61KPřed 6 lety
Hierarchical SQL Queries: Databases for Developers #16
Union, Minus & Intersect: Databases for Developers #15
zhlédnutí 4,6KPřed 6 lety
Union, Minus & Intersect: Databases for Developers #15
How to pivot rows to columns (and back) in SQL: Databases for Developers #14
zhlédnutí 10KPřed 6 lety
How to pivot rows to columns (and back) in SQL: Databases for Developers #14
Analytic Functions: Databases for Developers #13
zhlédnutí 6KPřed 6 lety
Analytic Functions: Databases for Developers #13
How to sort rows with SQL: Databases for Developers #12
zhlédnutí 7KPřed 6 lety
How to sort rows with SQL: Databases for Developers #12
SQL Subqueries: Databases for Developers #11
zhlédnutí 8KPřed 6 lety
SQL Subqueries: Databases for Developers #11
Delete and Truncate: Databases for Developers #9
zhlédnutí 13KPřed 7 lety
Delete and Truncate: Databases for Developers #9
Update and Transactions: Databases for Developers #8
zhlédnutí 13KPřed 7 lety
Update and Transactions: Databases for Developers #8
Insert and Commit: Databases for Developers #7
zhlédnutí 17KPřed 7 lety
Insert and Commit: Databases for Developers #7
How to count, sum, and average rows in SQL: Databases for Developers #6
zhlédnutí 16KPřed 7 lety
How to count, sum, and average rows in SQL: Databases for Developers #6
How inner, outer, and cross joins work: Databases for Developers #5
zhlédnutí 20KPřed 7 lety
How inner, outer, and cross joins work: Databases for Developers #5
How to write SQL queries: Databases for Developers #4
zhlédnutí 19KPřed 7 lety
How to write SQL queries: Databases for Developers #4
I have to create index for a huge table (In Gigabytes) for a new column. But when we tried this, it is bringing down the system completely so we had to drop that. Is there any way we can create this index without downtime.
I'm unsure what "bringing down the system completely" means exactly. In Oracle Database you can CREATE INDEX ... ONLINE which allows the database to write to the table while creating the index which may help here.
@@TheMagicofSQL Thank you for the quick reply. 'Bringing down' - I was meaning that the CPU and memory were full due to the size of the table and was taking hours to complete. So is there a way to create index in a performant way on this table?
While a table in the gigabytes is big, it still shouldn't take hours to create an index on it. Creating an index will only use 1 cpu by default. So either the system only has a couple of cpus (in which case you should look at getting more) or it's running in parallel - reducing the parallelism will help. I would still start by using the ONLINE keyword when creating the index before trying anything else though. It might take a long time, but other operations should continue normally (unless your system does have to few CPUs for the workload)
@@TheMagicofSQL Thanks again. I shall try this and will update here on how it goes.
Excellent presentation, the best SQL course!! I love the creativity in your videos. Thank you so much!!
You're welcome, glad you appreciate these!
I like how its explained though I dont get most of it
What is it you're struggling to understand?
Refreshing
I am not able to understand the autotrace part where view part individual elapsed time is 728 micro seconds. How this was calculated , Please elaborate. Thankyou.
One more doubt , in 3rd sort operation why it dont have any elpsed time as you said in video.
I'm not sure exactly what you're referring to - please clarify. In any case, this value comes from the database measuring how long the operation took.
Again, I'm not sure exactly what you're referring to.
usefull explanation ! thanks a lot!
Well explained!
Perfect video! You can explain things that everyone can understand it!
.
Been doing MSSQL for 20 years and this was by far the best explanation of these joins. Thanks!
You're welcome!
Best explanation! Thank you sir!
❤
First time came across such good explanation of these 3 kind of joins. Kudos !!!
Thats the first time i understood how these joins worked... 😅
please remove the sound track from videos
We'll bear this in mind for future videos
excellent video
Crisp and accurate
Thank you so much for this video. It really helped me for my interview.
You're welcome; glad this helped you
Sir what is decimal presicion. Scale used how it store values.according size of values
"p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point. s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127." docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Data-Types.html#GUID-9401BC04-81C4-4CD5-99E7-C5E25C83F608
I am incredibly grateful to Chris for their invaluable tutorials on hash join, merge join, and nested loop. These concepts always seemed daunting to me when I encountered them in execution plans, but their clear and concise explanations have helped me gain a much deeper understanding. Their expertise and dedication to educating others are truly commendable. A big thank you for demystifying these complex topics and making them accessible to all!
You're welcome
色相
田木
Thanks bro , Excellent Explanation
Can we do fast refresh in complex materialized view? If its possible how to achieve it?
What do you mean by "complex"? There are lots of restrictions on what's possible for fast refresh docs.oracle.com/en/database/oracle/oracle-database/23/dwhsg/basic-materialized-views.html#GUID-505C24CF-5D56-4820-88AA-2221410950E7
Around 6min for complexity, do you use binary or decimal log? I believe it should be binary but one of your calculations (the one for Merge Join = 180) leads me to think you used decimal. Was this a mistake when using your calculator, or is it really meant to be decimal log?
Very nice video btw! Really enjoy the visual and step by step explanations
It was a decimal log. The numbers are just approximations to give some idea of complexity. Particularly for sorting the precise figures depend on the sort algorithm and order of the input data set. Whether you use LN or LOG, merge join comes out between nested loops and hash join in the example.
Makes sense! Thank you
Thanks a lot 🙏
Absolute gem!!
Thanks very much for the detailed study.
I love your teaching vibe!!!
Thanks, glad you enjoy these!
very simple explaining..good work bro
I'm an Oracle DBA for 30 years and your explanation is the best I ever seen !!! Congrats
You're welcome Peter - glad you found this useful!