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

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?

============================
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.

[prasanthcherukuri]

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.

[erisen]

What a great and underrepresented channel.

[livingfantasy]

This series is really a quality content. Keep up the good work. Thanks !!

[santoshpanigrahi]

Thank you for these videos and the explanation. We understand the effort you and your team put into these small videos. really appreciate !!

[ankitbhatt]

your videos are very helpful and simple to understand and i really like your ideas to explain the complicated features ..Thanks

[mostafayahia]

Thank you so much for the great video, could you please recommend a good book for sql tuning to start with?

[betterthanamasterofone]

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?

[fahadnoor]

Hi, Thanks for wonderful video.
I have a question, can you explain this statement please? 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.

[Nil-jsbf]

Had a good laugh at your analogy in this one.

[bbuenopl]

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?

[asanjeevgoud]

Hi you have great stuff
May I know the how to reduce table index size.my DB 2 TB index was 600GB

[a.useronly]

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

[superfreiheit]

Hello, magic is there a good way to understand complex SQL queries? Do you have a video about this topic?