Tuesday, August 25, 2020

Oracle SQL Tuning

 

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

ITIL Process

ITIL Process Introduction In this Blog i am going to explain  ITIL Process, ITIL stands for Information Technology Infrastructure Library ...