Member Avatar for hari_bk

Hi How to see the diffrence in terms of resources or response time between ALL_ROWS and FIRST_ROWS hints? Can we track the diffrence anywhere?

Thanks

  • 2 Contributors
  • 1 Reply
  • 69 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by jim mcnamara
Member Avatar for jim mcnamara

First off, hints can be overridden by the Oracle optimizer. Just so you know.

If you want to track performance do this:
1. Before you run your SQL
ALTER SESSION SET SQL_TRACE TRUE;
run your sql
ALTER SESSION SET SQL_TRACE FALSE;
2. Now find where the trace file is:
select value from V$PARAMETER where NAME='user_dump_dest';
3. go to that directory, find the most recent file. Trace files end in .trc
4. There is a program tkprof, it's part of Oracle. Locate it.
Then run tkprof <tracefilename.trc> <outputfile>

The information you want is in the output file.

However, because Oracle caches everything, times will be misleading if you run the identical script again. Find a way to get 500 rows, then
when you run again, find 500 different rows - to avoid the effect of caching.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.