from a developer's view, I need to check if my query is efficient. I just want to see how much memory, processing power from the server, and number of seconds and number of records were used at the server to satisfy my query.
number of seconds and number of records can already be seen in Toad. just the memory and processor used.
I'm currently exploring on hammer db but I think I can use a more lightweight tool to get the resources used. Any suggestions?
Best tool for this is SQL trace.
There are several ways to enable SQL trace
https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof ,
but anyway you need access to dump folder on the server to get trace file.
(and ensure that session is closed before taking trace file because statistics is dumped to trace file only after cursor is closed).
If you don't have such access, you may try
select dbms_sqltune.report_sql_monitor(sql_id=>'[your_sql_id_here]', type=>'HTML', report_level=>'ALL') from dual
This will return CLOB with pretty formatted html file.
But this tool is less precise (granularity is 1 second) so it is efficient only for long-running queries. And query need to be monitored by Oracle.
(you may use /*+ monitor */ hint, but sometimes it doesn't help)
Same stats may be obtained from
GV$SQL_MONITOR
GV$SQL_PLAN_MONITOR
GV$SQL_PLAN
GV$ACTIVE_SESSION_HISTORY
GV$SESSION_LONGOPS
GV$SQL
views.