Search This Blog

Friday, 6 November 2020

oracle performace tunnning Basic

 when any users intimates us that

  1. This query is running slowly
  2. Daily, this query will retrieve data in 1 minute. But, today it is running since 2 hours.
  3. Load average is high
  4. CPU utilization of a process is high
  5. Users experiencing slowness

a.Get the top consuming PID’s . 

ps -eo pcpu,pid,user,args | sort -k 1 -r |head -10

b.  Now, you need to pick the SQL_ID mainly for proceeding further. You will be prompted for PID which you picked in above command.

set linesize 2000;
select s.sid,s.serial#, s.inst_id,p.spid, s.SQL_ID, t.SQL_TEXT, s.machine from gv$process p, gv$session s, gv$sqltext t where s.paddr = p.addr and p.spid=&processid and s.SQL_HASH_VALUE = t.HASH_VALUE;

 

c. 1. Using the sql_id, pick the tables involved in it. We are using explain plans.

select * from table(dbms_xplan.display_cursor(sql_id => ‘&SQL_ID’, format => ‘+ALLSTATS’));

c 2. Run below Oracle Provided script to get details. This is Sql Tuning Report. This report will give all the recommendations available. 

@?/rdbms/admin/sqltrpt.sql

Now, you will be having the tables list. Check when was latest time stamp of the table analyzed.

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, TO_CHAR(LAST_ANALYZED, ‘MM/DD/YYYY HH24:MI:SS’) FROM DBA_TABLES WHERE TABLE_NAME =’&TABLE_NAME’;

 

=> If the stats are not up-to-date, we need to gather stats.

 

select table_name, subpartition_name, global_stats, last_analyzed, num_rows from dba_tab_subpartitions where table_name=’&Tablename’ and table_owner=’&owner’ order by 1, 2, 4 desc nulls last;

select TABLE_OWNER, TABLE_NAME, PARTITION_NAME, LAST_ANALYZED from DBA_TAB_PARTITIONS where TABLE_OWNER=’&owner’ and TABLE_NAME=’&Tablename’ order by LAST_ANALYZED;

 

If table has NO partitions and if stats need to be gathered, use this

EXEC dbms_stats.gather_table_stats(‘&Owner’,’&Table’,cascade=>TRUE);

If table has partitions, we can gather stats on whole table. But, its better to gather stats for recently created ten partitions, use below

begin
SYS.DBMS_STATS.gather_table_stats
(ownname => ‘&Table_Owner’,
tabname => ‘&Table_Name’,
PARTNAME => ‘&partition’,
estimate_percent => 0.001,
DEGREE => 24,
granularity => ‘PARTITION’,
CASCADE => TRUE,
stattab => NULL,
statid => NULL,
statown => NULL
);
END;
/

 

=================================================== 

Run SQL Tuning Adviosor for the sql_id

————————————–
Statement with SQL_ID captured is taking long time, we need to set best Execution Plan for the SQL_ID.
So, we need to submit to Oracle Tuning Advisor(sqltrpt.sql) and then check the FINDINGS SECTION for Findings and Recommendations for the SQL_ID.Recommendations from sqltrpt.sql will be providing the best Explain Plan. We can implement these profiles/index rebuild/… suggested after checking with the SME of the database.

Location: $ORACLE_HOME/rdbms/admin/sqltrpt.sql

$sqlplus “/as sysdba”

Query to see current running sqls
set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/

Run SQL Tuning Advisor for the SQL_ID
SQL> @?/rdbms/admin/sqltrpt.sql

In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.

Command to Create and Implement SQL Profile in Oracle for the SQL_ID:
——————————————————————–

SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘<TASK_NAME>’,task_owner => ‘SYS’, replace => TRUE, FORCE_MATCH => TRUE);

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;

Command to Drop SQL Profile in Oracle for the SQL_ID:
—————————————————-

SQL> execute dbms_sqltune.drop_sql_profile(‘<SQL_PROFILE_NAME>’);

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

Command to Alter SQL Profile in Oracle for the SQL_ID:
—————————————————–

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(‘<SQL_PROFILE_NAME>’,’STATUS’,’DISABLED’);

If successful, you should see the following:
PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC;

If you don’t know the name of the SQL Profile then use the below query
SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like ‘%SELECT%TABLE%NAME%’;

Query
—–
The SQL_ID is not stored with the profiles.
You can see if a statement is using a profile by querying v$sql where sql_profile is not null.

select sql_id, child_number, plan_hash_value plan_hash, sql_profile,
executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl(‘&sql_text’,sql_text))
and sql_text not like ‘%from v$sql where sql_text like nvl(%’
and sql_id like nvl(‘&sql_id’,sql_id)
and sql_profile like nvl(‘&sql_profile_name’,sql_profile)
and sql_profile is not null
order by 1, 2, 3

/

 I have used below blogs:

https://dba12c.wordpress.com/2015/04/30/query-is-running-slow-2/

https://dilipdba.blogspot.com/search/label/SQL%20to%20check%20Stale%20statistics

https://dba12c.wordpress.com/2017/04/24/oracle-local-registry-olr/


 

No comments:

Post a Comment