SQL Tuning
SQL is running slow
Database is running slow.
Tuning - to improve performance of the database to run faster.
Goal to get tune?
Good Throughput - CPU
Best response time/elapsed time/execution time
CPU Elapsed Time
100% 15min Worst
1% 1s Best Performance
Using Cost based Optimization , the cost is defined on
CPU and Response time.
Now we to identify the cost the sql.
What happens when SQL Statement been executed?
Three stages
Parser - does check syntax and semantic analysis
Fetch -
Execute - Will execute the plan and send result to user
Fetch : OPtimizer will choose best shortest execution plan using
cost based optimization with dictionary stats.
The plan received by Row Source generator.
then fetch the records
note: Dictionary stats
Number of rows,columns,indexes,row length.
There are many factors dependent on slowness of performance.
Backup jobs - RMAN/DP
DML Bulk Jobs -Developers - last night
Memory - AMM -
Network
Poor Coding(procedures/Packages)
Indexes - fragmentation -
Stats
storage
multiplexing - same disk
Dictionary stats
----------------
Dictionary stats are
number of rows,row length,columns,indexes
If there are DML changes in the last night with 1 million
records are deleted. Will optimizer knows the stats currently
got deleted ? No
These stats to be collected manually every day night. So with
the updated stats, the optimizer will generate best execution
plan
sql>EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'SCOTT',CASCADE=>TRUE);
How we identify the stats gathered?
from dba_tables (last_analyzed)
sql>select table_name,last_analyzed from dba_tables where owner like
'SCOTT';
If someone complained that query running slow,no changes
did on the query. Verify gather stats.
Purpose of Tuning?
What happens while sql query executed?
What are goals to tune?
What is optimizer?
How to collect/gather stats? and purpose?
How to check stats gathered?
Optimizer - can control using parameter
---------
Will choose the shortest exection plan.
using parameter optimizer_mode
can set the value to all_rows/first_rows_n
n is multiples of 10
sql>alter system set optimizer_mode='first_rows_10' scope=both;
default all_rows
sql>show parameter optimizer_mode
Manually check the execution plan using cost based optimization.
with tools. To identify the cost of plan for a query
explain plan
or
auto trace
1. Auto trace ( introduced from 10g)
sql>set autotrace traceonly
sql>select * from scott.emp;
sql>set autotrace off
2. using explain plan tool ( Being still used)
will store execution plan in plan_table
sql>explain plan set statement_id='j1' for select * from scott.emp;
sql>@?/rdbms/admin/utlxpls.sql
How to identify the load on the server ?
What is causing slow on the server?
Using unix top command
will identify the load average and top pid consuming more CPU and mem.
$top
Load average < 10 (best performance)
Idle - CPU idle - No activity - > 70%
Find PID based on CPU/Mem of oracle commands.
using pid from v$session,v$process can find the sid,serial#,sqlid,username
machine,terminal,program,module
Top
pid
sid,serial from v$process,v$session
using sid
find sql_text from v$sql_area
then use explain plan tool
to find the cost and factor causing slow.
If found rman / dp job... impacting db..100% kill it.
sql>alter system kill session '&sid,&serial#'immediate;
THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME
