Comparing Hints

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 402,900 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,083 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Oracle advertiser: Programming Forums

Thread Tools Display Modes

Views: 1567 | Replies: 1

•

Join Date: Aug 2006

Posts: 2

Reputation: hari_bk is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

hari_bk

Offline

Newbie Poster

Comparing Hints

Aug 3rd, 2006

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

•

Join Date: May 2004

Posts: 177

Reputation: jim mcnamara is on a distinguished road

Rep Power: 5

Solved Threads: 9

jim mcnamara jim mcnamara is offline

Offline

Junior Poster

Re: Comparing Hints

Aug 11th, 2006

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.