Checking the real cost of a query is very helpful with regard to performance tuning. Explain plan costs and other values are just estimates and technically speaking, they mean nothing; they are based on statistics only! On the other hand, you have to know how much resources does a particular query consumes. Check out the query below!
SELECT sq.sql_id
,sq.parsing_schema_name
,sq.command_type cmd_tp
,sq.executions execs
,sq.rows_processed rws_proc
--
,sq.buffer_gets b_gets
,CASE
WHEN sq.executions > 0 THEN round(sq.buffer_gets / sq.executions)
END AS b_gets_per_exec
,sq.disk_reads d_reads
-- time data
,sq.elapsed_time el_time
,sq.cpu_time cpu_time
,sq.user_io_wait_time uio_time
-- sql query
,sq.sql_text
,sq.sql_fulltext
-- program info
,sq.module
,sq.action
,sn.username
,sn.osuser
,sq.last_active_time
-- rest info
,sq.first_load_time
,sq.sorts
,sq.direct_writes
,sq.optimizer_cost
,sq.physical_read_requests
,sq.physical_read_bytes
,sq.physical_write_requests
,sq.physical_write_bytes
FROM v$sql sq
--
LEFT JOIN v$session sn
ON sq.sql_id = sn.sql_id
AND sn.status = 'ACTIVE'
--
WHERE 1 = 1
AND sq.parsing_schema_name = 'JOHN_DOE' -- user who executed the query
--AND sq.sql_id IN ('') -- check the exact SQL query by filtering out sql_id
--AND sq.last_active_time >= sysdate - 7 -- filter out only last 7 days
--AND lower(sq.sql_fulltext) LIKE '%ip.result_code%' -- locate the query by a piece of code
--AND lower(sq.sql_text) LIKE '%ip.result_code%' -- locate the query by a piece of code (from the excerpt)
Some columns are more important than others however, we cannot tell that one attribute is more important than the other. It is an overall consideration of costs and each case has to be evaluated on an individual basis. For the sake of clarity, I will briefly describe the most important fields.
- BUFFER GETS – Oracle stores data in forms of blocks. Objects (such as tables and indexes) are composed of multiple blocks (depends on the size). Blocks are usually smaller for OLTP ( 4KB ) where for OLAP they might from 8KB up to 32 KB). When Oracle needs to read a block it needs to read it from a buffer – that’s called buffer gets (see the SELECT statement processing article). As it is mentioned in the article Computer Architecture an application cannot read data directly from a disk. Thus, it has to be loaded in memory first. If it is there already, it will just read it from the buffer, it not it will load it into the buffer from disk (I/O operation). This value should be as low as possible. One of the key indicators for poor performing queries is a high buffer gets value. No one can say in general, how you lower this. You have to check the explain plan, find out what is causing it and optimize the query 🙂
- EXECUTIONS – this number indicates how many times was this particular query executed. If you can see the very similar query (especially if the change in filter values only) it means they you are experiencing hard parse 🙂 When the values for executions is more than one, the other values (i.e. buffer gets, el_time, uio_time, cpu_time, …) represent aggregated values (SUM)
- ROWS PROCESSED – this value represents how many rows were fetched by the query (in SQL clients the query might fetch only a sample set to quickly show results before fetching the complete data set)
- ELAPSED TIME – time how long it took the query to return results
- CPU TIME – CPU time used for fetching/executing/parsing the results
- USER IO WAIT TIME – represents time how long does it take to read and get data from blocks. You can picture it as a situation where you go to a coffee shop, order a coffee and the time you wait until you get your order is your <<wait time>> 🙂 It’s the same situation here. The most common cure here is the index.
- MODULE – a program you use to connect to the database (sql_client, excel, application, sql*plus, or just a direct connect)
- USERNAME – database user who executed the query
- OSUSER – OS (Operating system) username used for logging in the OS (Windows (LDAP mostly), UNIX, … credentials (no password, just the username))
- PHYSICAL READ REQUESTS – the number of physical read I/O requests
- PHYSICAL READ BYTES – the number bytes read from disks
- PHYSICAL WRITE REQUESTS – the number of physical write I/O requests
- PHYSICAL WRITE BYTES – the number bytes written to disks