What queries are running in the database
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;
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%';
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
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 '%';
Based the cost u can decide what to be done.
One of the solutions is to analyse the statistics
No comments:
Post a Comment