How to Find Slow SQL: Databases for Developers: Performance #9
ฝัง
- เผยแพร่เมื่อ 2 ก.ค. 2020
- 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. - วิทยาศาสตร์และเทคโนโลยี
Key Performance Tuning Principles:
1) *Scope* your changes to code that executes when performance is bad.
2) Measure how long it takes to execute each part of the code and focus your tuning efforts on *longest running*
3) *Minimize the number of calls* to the DB. [Use a simple stored procedure which contains all the sqls to be called. If seperate sql's are present then there will be multiple roundtrips to DB. ]
Thanks dude.
You've got it! :)
This series is really a quality content. Keep up the good work. Thanks !!
Thanks!
Thank you for these videos and the explanation. We understand the effort you and your team put into these small videos. really appreciate !!
You're welcome; thanks for the appreciation!
What a great and underrepresented channel.
Thanks!
your videos are very helpful and simple to understand and i really like your ideas to explain the complicated features ..Thanks
Thanks Ankit, glad you find these videos useful :)
Had a good laugh at your analogy in this one.
Great to lighten your day :)
wow! you reply to everyone .
Very nice 👍🏻
Thank you 👍
Great!
Thank you so much for the great video, could you please recommend a good book for sql tuning to start with?
I found Troubleshooting Oracle Performance by Christian Antognini to be an excellent guide when I read it
antognini.ch/top/
Thank you, great stuff! One question - does setting (and/or using) "session_trace_enable" require any special db role for the user, like autotrace does?
In DBMS_MONITOR? You just need execute privs on this package
Thank you for the video!
This is really an amazing feature.
But i'm getting a "Closed statement" after executing the Profile step on Sql Developer.
Could not find any explanation for that message on Oracle Support nor googling it.
Any clue on that?
Hmm, not sure. If you can put together an example showing what you're doing and post it to asktom.oracle.com we can look into it; make sure you include DB and SQL Dev versions!
Hi you have great stuff
May I know the how to reduce table index size.my DB 2 TB index was 600GB
Why do you need to reduce the size of the index? In general shrinking the index will get you small gains and it'll grow back to its current size over time.
Hi, Thanks for wonderful video.
I have a question, can you explain this statement please? "C:\Users\csaxon\Documents\Scripts\dfd-perf-9spool" since I don't see the trace file name listed from grid below which you are downloading. My question is where should I mention the file name which I want to download in this statement? This statement seems more likely a destination to download the file but seems like missing WHICH file to download.
It contains the query on v$diag_trace_file_contents with spool on/off before after it to download it's output to my machine
@@TheMagicofSQL Thank you for replying my query. So "C:\Users\csaxon\Documents\Scripts\dfd-perf-9spool" part your local computer location or the where this file is located on server? plus the above statement is the continuation query of above? If yes then can you help to write the whole query here please?
Yes, it's a script on my computer. You can find the contents at livesql.oracle.com/apex/livesql/file/tutorial_JN0XQTKBU5D2JMNDVMTRQCFIE.html in module 4 "Accessing SQL Trace Files"
Hi dear , thanks for your precious videos, I have a question which I can’t get answer so far and irrelevant to this video, if sql injection succeeds on oracle database, that can grab all data related to public role, like ALL_users and etc, how to mitigate this risk, please help
The all_* views only show objects the user has access to. The solution is to follow the principle of least privilege and only grant users the access they require and nothing else.
@@TheMagicofSQL Actually a very basic user has access to public role when created as user assume public role and All_* views or tables comes under public role, oracle doesn’t recommend to revoke something from public role, here don’t understand what to do,
I'm not sure what the issue is here, please clarify
@@TheMagicofSQL let say if we create oracle user named test and give privilege to connect the database only, by this, test is able to query all public views like ALL_USERS, tables,views, objects etc, in this case if hacker inject sql injection and succeeds , he able to dump valuable data from database, my question is how to deal this on database level, I hope you got my point,
Waiting u r reply:)
Just to mention one point that test user already exposed by sql injection but how to protect other information like schema names etc
The all* views only return the objects your database user has permissions to access. So if customer_table is in another schema, it only appears if the user you're connected as has select/insert/update/... privs on it
So the solution is as I described above: follow the principle of least privilege. Application users must own no objects and only have the minimum privileges needed on other schemas to do their job
Hello, magic is there a good way to understand complex SQL queries? Do you have a video about this topic?
Ultimately you've got to break it down, figuring out what joins with what. What exactly is it you're looking for?
@@TheMagicofSQL I am looking for a method or way tool that helps understand complex queries. Breaking down? Starting with the lowest subquiry?
I don't know of any tools that will help particularly. You've got to work through the joins to see how the tables relate.