Oracle tkprof output understanding.... exec and fetch counts are the same.

[Volker Borowski]

Hi Jack,

another idea, as 7.x might not be able to use the index with ">" and "<"

SELECT ... WHERE var BETWEEN low: and high: ;

Should take the index even in Oracle 7.x.

What the heck do they want with so many rows ?
If this table is queuelike (insert/delete) and you ave some GB to be read, consider to reorganize it, because it might be fragmented.
Select bytes from dba_segments, calculate an average row_length and do a select count(*). Multiply count(*) with average row-length and compare to bytes from dba_segments. If this shows a big diffrence (bytes significantly bigger), go for a reorg.

If you do not need all columns, but only a,b,c consider to create the index as

mx_timestamp,a,b,c because the range youe have for mx_timestamp apears not to be so small, an INDEX_FAST_FULLSCAN might reduce the IO, ...(although I do not remember, if Oracle is capable of fast_fullscans in 7.3).

In addition, I'd like to see the diffrent explains as well.

Volker

[Jack C. Mahaffey]

I'm getting real good at tracing active SQL on a HP-UX server. Does anyone have any guidance for finding the heavy hitters on a Windows NT, XP, 2000 server?

Can any performance counter be used to find the corresponding spid in v$process?

Note that we don't have the performance pack installed.

jack...

[Alwyn Santos]

select /*+ index(table_name index_name) */ rowid rowid_cid199, invent_hist.*
FROM invent_hist WHERE (mx_timestamp <> 0 and mx_timestamp < 962114678)

Should actually be...

select /*+ index(invent_hist index_name) */ rowid rowid_cid199, invent_hist.*
FROM invent_hist WHERE (mx_timestamp > 0 and mx_timestamp < 962114678)

* index_name should be changed to the index you want to use.


Also, to find poorly performing queries on NT or any oracle db...

select disk_reads,buffer_gets,executions,sql_text
from v$sqlarea
where disk_reads > 10000
or buffer_gets > 100000
order by disk_reads,buffer_gets
/

Concentrate on statements that have high disk read/execution ratio.