[Roboprog]

A common technique for looking at a program (not a database query) is "stack sampling". A timer goes off N times a second, and records the call stack of the process/threads. Then, statistics are gathered from the set of call stacks.

E.g. - "60% of the time is in the sequence main() -> process_unit() -> validate_input() and the things called from there" or "45% of the time is in all of the call sequences which then lead back into write_line()" or things like that. Usually, you see patterns of a small number of slow functions that everything depends upon and/or, arranging the call stacks into a sort of "tree" of calls, a branch of the tree that you spend an inordinate amount of time in.

There are newer tools that provide graphical representation of this data as well (e.g. - show the tree as sort of a topographical map, with hotspots as peaks, and the call tree as geological strata)

Of course, if your program has a giganto routine that it never leaves, you might not learn much -- "do_all_the_work_inline() is executing 95% of the time!", unless you start looking at things at the line number level. Blech.

[Roboprog]

For an (SQL) database, you would want to look at something called a query planner (e.g. - "explain" command).

It describes the order that tables are accessed, which indices (if any) are used for each access, "and so on".