[go: up one dir, main page]
Scribd
Upload
526 views14 pages

Mallikarjun Ramadurg Mallik034: Follow Me by Search Mallik034 at Youtube / FB / Linkedin / Twitter / Blogspot / Instagram

The document provides steps to troubleshoot and resolve performance issues with queries running in an Oracle database. It includes queries to identify the running queries, check for processing by looking at row counts, get the SQL hash value and text, explain plans, review AWR reports, and profiling as a last option. Statistics gathering, table moves, rebuilds, and identifying stale tables or differing data are suggested solutions.

Uploaded by

venkanna keesara
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
526 views14 pages

Mallikarjun Ramadurg Mallik034: Follow Me by Search Mallik034 at Youtube / FB / Linkedin / Twitter / Blogspot / Instagram

The document provides steps to troubleshoot and resolve performance issues with queries running in an Oracle database. It includes queries to identify the running queries, check for processing by looking at row counts, get the SQL hash value and text, explain plans, review AWR reports, and profiling as a last option. Statistics gathering, table moves, rebuilds, and identifying stale tables or differing data are suggested solutions.

Uploaded by

venkanna keesara
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 14

Mallikarjun Ramadurg Mallik034

Contents
What are the queries that are running?....................................................................................................................................................................................... 1
Get the rows fetched, if there is difference it means processing is happening...........................................................................................................................2
Get the sql_hash_value................................................................................................................................................................................................................. 3
Get the sql_Text............................................................................................................................................................................................................................ 4
Get the explain_plan..................................................................................................................................................................................................................... 4
Run AWR report and review......................................................................................................................................................................................................... 5
Solution......................................................................................................................................................................................................................................... 6
Profiling:........................................................................................................................................................................................................................................ 6

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

What are the queries that are running?


select sesion.sid, sesion.username, optimizer_mode, hash_value,
address, cpu_time, elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value and
sesion.sql_address = sqlarea.address and sesion.username is not null
/

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Get the rows fetched, if there is difference it means processing is happening


select b.name, a.value vlu from v$sesstat a, v$statname b where a.statistic# =
b.statistic#
and sid =&sid and a.value != 0 and b.name like '%row%'
/

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Get the sql_hash_value


select sql_hash_value from v$session where sid='&sid';

SQL> select sql_hash_value from v$session where sid='&sid'; Enter value for sid: 1075 old 1: select sql_hash_value
from v$session where sid='&sid' new 1: select sql_hash_value from v$session where sid='1075'

SQL_HASH_VALUE
--------------
928832585

Get the sql_Text


SQL> select sql_text v$sql from v$sql where hash_value =&Enter_Hash_Value; Enter value for enter_hash_value: 928832585

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Get the explain_plan


set lines 190
col XMS_PLAN_STEP format a40
set pages 100

select case when access_predicates is not null then 'A' else ' ' end || case when filter_predicates is not null then 'F' else
' ' end xms_pred, id xms_id,
lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step, object_name xms_object_name,
cost xms_opt_cost, cardinality xms_opt_card, bytes
xms_opt_bytes,
optimizer xms_optimizer from
v$sql_plan where
hash_value in (&SQL_HASH_VALUE) and to_char(child_number) like '%';

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Run AWR report and review

Solution
Based the cost u can decide what to be done.

1. One of the solutions is to analyse the statistics

exec fnd_stats.gather_schema_statistics('ALL');

2. Gather the dictionary stats

exec DBMS_STATS.GATHER_DICTIONARY_STATS;
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
execute dbms_stats.gather_schema_stats('SYS', method_opt=>'for all columns size 1', degree=>30,estimate_percent=>100,cascade=>true);
exec dbms_stats.gather_system_stats ('NOWORKLOAD');

3. Table move and Table shrink.

4. Rebuild index.

5. Final Option is Profiling:

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Profiling:
Run awr_sql_history_orig.sql gives all plans for same query >>> Supply sql_id for this.

 see if there is difference in the plans. Understand the plans. Eg : it may be picking different indexes. or it may be processing a different table first
(most indented operation).

Was any table stale?


Was there any difference in data between both runs?

Check with Application Team about when u find good plan, tag it and do sql profiling.
(use sql profiling only as a last option because oracle should have the liberty to pick better plan)

Note:
Also find why bad plan is getting picked.

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram


Mallikarjun Ramadurg Mallik034

---Time Remaining to complete the current task


set lines 150 col username format a20 col opname format a30 col
target format a40 select sid,opname,target,
to_char(start_time,'DD-MON-YY HH24:MI') START_TIME, time_remaining/60 "Time Remaining in Mins",username
from v$session_longops where time_remaining>1 order by time_remaining
/

--- what sessions are active this SQL will help


select sid,
to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time, username, type, status, process,
sql_address, sql_hash_value from v$session
where username is not null
/

Follow me by search mallik034 @ YouTube / FB / LinkedIn / twitter / BlogSpot / instagram

You might also like