How to Find Slow SQL: Databases for Developers: Performance #9

[@prasanthcherukuri7955]

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.

[@TheMagicofSQL]

You've got it! :)

[@erisen960]

What a great and underrepresented channel.

[@TheMagicofSQL]

Thanks!

[@livingfantasy1240]

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

[@TheMagicofSQL]

Thanks!

[@santoshpanigrahi205]

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

[@TheMagicofSQL]

You're welcome; thanks for the appreciation!

[@IMdAbdulquadirKhan]

wow! you reply to everyone .

[@mostafayahia9647]

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

[@TheMagicofSQL]

I found Troubleshooting Oracle Performance by Christian Antognini to be an excellent guide when I read it antognini.ch/top/

[@ankitbhatt9956]

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

[@TheMagicofSQL]

Thanks Ankit, glad you find these videos useful :)

[@fahadnoor2579]

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.

[@TheMagicofSQL]

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

[@fahadnoor2579]

@@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?

[@TheMagicofSQL]

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"

[@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?

[@TheMagicofSQL]

In DBMS_MONITOR? You just need execute privs on this package

[@Wearewhatwehear]

Great!

[@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?

[@TheMagicofSQL]

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!

[@asanjeevgoud]

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

[@TheMagicofSQL]

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.

[@Nil-js4bf]

Had a good laugh at your analogy in this one.

[@TheMagicofSQL]

Great to lighten your day :)

[@a.useronly2266]

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

[@TheMagicofSQL]

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.

[@a.useronly2266]

@@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,

[@TheMagicofSQL]

I'm not sure what the issue is here, please clarify

[@a.useronly2266]

@@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

[@TheMagicofSQL]

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

[@superfreiheit1]

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

[@TheMagicofSQL]

Ultimately you've got to break it down, figuring out what joins with what. What exactly is it you're looking for?

[@superfreiheit1]

@@TheMagicofSQL I am looking for a method or way tool that helps understand complex queries. Breaking down? Starting with the lowest subquiry?

[@TheMagicofSQL]

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.

[@a.useronly2266]

Very nice 👍🏻

[@TheMagicofSQL]

Thank you 👍