Keeping the DB running smoothly :Tips and Tricks for addressing Slowness & Long Running Transactions

The most common causes of slow performance are as Excessive round-trips from the application server to the database.
The main difference between that simple SELECT statement and database slowness is that the latter is a chronic ailment of your databases. Slowness can be attributed to multiple factors such as: - network problems. - missing indexes.
Long-running transactions are open transactions with no activity (that is, no new Logical Change Records (LCRs), rollbacks, or commits) for an extended period (20 minutes). A large transaction is an open transaction with a large number of LCRs.
The v$session_longops view allows the Oracle professional contract the amount of time that is used by long-running DLL and DML statements. You can view any SQL statement that executes for more than 6 absolute seconds (the "long running" threshold) using the v$session_longops view.
For more information visit:
asrblogger.com/
Join Telegram : t.me/asrBlogger
Scripts from Github : github.com/ani...
#oracledatabase #oracledatabasetutorial #performancetuning #databasetuning #performanceoptimization #asrblogger #asrbloggerdba

[@Daily-digital]

your videos are good enough, keep up the good work

[@anishkumarvideos]

Glad you like them! Thanks :)

[@Dancewithsaigite]

Nice one sir expecting more videos

[@anishkumarvideos]

Thanks for your comment, every saturday one video will be uploaded.

[@abhishekgarg9536]

Thank you sir . So beautifully explained..

[@anishkumarvideos]

Thank you :)

[@GauravSingh-bp8nw]

Nice one

[@wbtopg]

Just wow

[@balajichandramohan9707]

Hi there, good one. How would you tune a select query. Do you use sql tuning advisor or explain plan.

[@anishkumarvideos]

Tuning a SELECT query can involve several steps and tools, including the SQL Tuning Advisor and the EXPLAIN PLAN.
I personally use SQL Tuning Advisory as my first preference, but selecting tools is decided as per the problem summary.
Identify the Performance Issue:
Determine if the query is running slowly or causing performance bottlenecks.
Review the Query:
Ensure the query is written efficiently. Check for unnecessary complexity, redundant conditions, and suboptimal joins.
Use EXPLAIN PLAN:
Generate an execution plan using the EXPLAIN PLAN statement to understand how Oracle executes the query.
Look for full table scans, nested loops, and other potentially inefficient operations.
Syntax: EXPLAIN PLAN FOR your_select_query;
Analyze the Execution Plan:
Identify which parts of the query are consuming the most resources.
Check for missing indexes or suboptimal join methods.
Optimize Query Structure:
Simplify complex queries.
Rewrite subqueries as joins or vice versa if it improves performance.
Use appropriate indexing.
Add/Modify Indexes:
Ensure that the columns used in WHERE clauses, joins, and ORDER BY clauses are indexed appropriately.
Consider adding composite indexes if multiple columns are frequently queried together.
Statistics Collection:
Ensure that table and index statistics are up to date using DBMS_STATS.GATHER_TABLE_STATS and DBMS_STATS.GATHER_INDEX_STATS.
Use SQL Tuning Advisor:
Run the SQL Tuning Advisor to get automated suggestions for query optimization.
It provides recommendations on indexes, SQL profiles, and query restructuring.
Syntax: EXEC DBMS_SQLTUNE.create_tuning_task(sql_text => 'your_select_query');
Review Advisor Recommendations:
Assess the recommendations provided by the SQL Tuning Advisor and implement the ones that make sense for your context.
Hope it helped :)

[@balajichandramohan9707]

@@anishkumarvideos Thank you

[@cityofjoy8830]

What about slow uploading of files.

[@anishkumarvideos]

Slow uploading of files to an Oracle server can be caused by various factors.
To diagnose and resolve this issue, consider the following steps:
Network Bandwidth and Latency:
Check Network Speed: Ensure that the network connection between the client and the Oracle server is fast and reliable.
Latency: High latency can slow down file uploads. Use tools like ping or traceroute to diagnose network delays.
Server Resources:
CPU and Memory: Ensure that the server has adequate CPU and memory resources. High CPU or memory usage can slow down file uploads.
Disk I/O: Slow disk performance can affect file uploads. Check disk I/O performance using tools like iostat or vmstat.
Oracle Database Configuration:
Direct Path Load: For large file uploads, consider using direct path load methods such as SQL*Loader with the DIRECT parameter, which bypasses some of the overhead of conventional path loads.
BLOB/CLOB Data Types: If you are uploading large files as BLOB or CLOB, ensure that you are using efficient methods to write these large objects.
Database Parameters:
LOG_BUFFER: A small log buffer can slow down file writes. Increase the size of the LOG_BUFFER parameter if necessary.
DB_FILE_MULTIBLOCK_READ_COUNT: Adjust this parameter to optimize the number of blocks read in one I/O operation.
Hope it helped !! :)

[@surendraroy1758]

Is there any class your side conducting for performance tuning ?

[@anishkumarvideos]

Yes , we are going to publish full course on PT very soon !!!