Oracle - Row Source Operation

Overview

Generally, in Performance Tuning thoery, Row Source & Record set are two different terminology which some time can be mis-perceived or mis-interpretted. According to me, ROW SOURCE is a superset which has more potential information related to performance statistics which helps to diagnose, troubleshoot in query tuning. On the contrary, RECORDSET is simply a set of rows/records fetched in a particular operation of an execution plan.

Even some Performance Tuning document/book talk about OPTIMIZER which decides the execution plan based on the ROW SOURCE. ROW SOURCE that is being referred here is just record set, i.e. number of rows that may result out of an operation in an execution plan.

Having said what is RECORDSET, ROW SOURCE provides additional information related to reads, writes and importantly "Elapsed Time" for each operation in an execution plan. ROW SOURCE contains execution statistics from performance perspective and is actually associated with an execution of SQL Query.

Where can I find this Row Source information?

Usually this Row Source information is accessed by generating a TKPROF output on a session trace file.
Fo eg. You can trace a session using 10046 event.

Note : Focus is retained to ROW SOURCE hence I am not providing more information on how to trace a session.

The below information is a copy paste.



Rows       Row Source Operation
-------     ---------------------------------------------------
0              LOAD TABLE CONVENTIONAL (cr=14 pr=6 pw=0 time=0 us)
199          NESTED LOOPS (cr=0 pr=0 pw=0 time=43281016 us cost=1 size=548 card=1)
39800      MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=103272 us cost=1 size=496 card=1)
199          HASH JOIN (cr=0 pr=0 pw=0 time=3960 us cost=1 size=466 card=1)
199          HASH JOIN (cr=0 pr=0 pw=0 time=594 us cost=1 size=349 card=1)
199          FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=297 us cost=0 size=310 card=1)
400          FIXED TABLE FULL X$KCBFWAIT (cr=0 pr=0 pw=0 time=199 us cost=0 size=3900 card=100)
200          FIXED TABLE FULL X$KCFIO (cr=0 pr=0 pw=0 time=995 us cost=0 size=11700 card=100)
39800      BUFFER SORT (cr=0 pr=0 pw=0 time=34899 us cost=1 size=3000 card=100)
200          FIXED TABLE FULL X$KCCTS (cr=0 pr=0 pw=0 time=99 us cost=0 size=3000 card=100)
199          FIXED TABLE FIXED INDEX X$KCCFE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=52 card=1)


New Feature in 10g

However, starting from 10g onwards, Oracle has provided the following two views using which we can use to obtain the "Row Source Operation" information without tracing.

a. V$SQL_PLAN_STATISTICS
b. V$SQL_PLAN_STATISTICS_ALL

Note : Remember that Oracle will populate these views with these performance statistics only when using STATISTICS_LEVEL=ALL


Eg.
alter session set STATISTICS_LEVEL=ALL;

Example.
select * from table(dbms_xplan.display_cursor('',NULL));
col a form 999999 head "Rows"
col b form a65 head "Row Source Operation"
select
last_output_rows a,
operation||' '||object_name||' (cr='||to_char(last_cr_buffer_gets)||' pr='||to_char(last_disk_reads)||' pw='||to_char(last_disk_writes)||' time='||to_char(last_elapsed_time)||')' b
from v$sql_plan_statistics_all
where sql_id='' and chil_number=
order by id;


How to interpret this Row Source?

I am taking the below simple example to explain this. This example shows only one operation out of an entire execution plan.

Rows           Row Source Operation
-----------   ---------------------------------------------------
10000000   TABLE ACCESS FULL EMPLOYEE (cr=3000 pr=100000 pw=100 time=21000000)

My interpretation from this above example is
"Oracle took 21 seconds (From 9i onwards this number is represented in microseconds) to fetch 10 million rows"

What is the USE of Row Source?

Generally this ROW SOURCE is used to pin point which OPERATION has consumed more time and is an expensive.OPERATION here means the individual operation in an execution plan.

For Eg.
select OPERATION_ID,OPERATION||' 'OBJECT_NAME
from v$sql_plan where sql_id='ild_number=;

Let us assume a query is running long and you are expected to tune this query. However this is a BIG query running into two pages. It might be difficult to tune this query as you need to know the starting point of the problem. In this situation, Row Source can help to identify the operation which is taking a long time.

You need to find out the Row in the Row Source Operation whose "time" is big and drill down from there.

How DATA Dictionary is created in Oracle?

I feel silly when I presumed the below information was already available in the blog/internet. However later I realized that it is NOT and hence it prompted me to add this blog.

Question : What is DATA Dictionary in an Oracle Database?


Answer : DATA Dictionary is also called METADATA. Oracle documentation tells that METADATA is created by running catalog.sql. While this is partially TRUE, the catalog.sql creates only the view/synonyms and few additional package/procedure/functions..also few more objects. However it certainly doesn't create the actual metadata. These views are created to access the metadata in a readable format. Metadata doesn't reside in the VIEWs rather it resides in the $(dollar) tables. For example, whatever information that you see by querying dba_objects view is majorly sourced from the dollar table called obj$. Likewise dba_tables view is sourced from tab$

In summary all the metadata resides in the DOLLAR table and they are not views.

Few examples,
obj$ - For objects
tab$ - For tables
ts$ - For tablespace
file$ - For datafiles
seg$ - For segments

Question : How & When DATA Dictionary is created in an Oracle Database?


Answer : DATA Dictionary is actually created during the Database creation time. After NOMOUNTING the instance you run the CREATE DATABASE command. This command, after creating the SYSTEM datafile it calls a SQL file ?/rdbms/admin/sql.bsq which creates the metadata

Different ways to invalidate a cursor in memory for Oracle database

Purpose : Whilst you may have a various different situation and need to invalidate a cursor from the oracle cache/memory at the instance level, I always had the need after pinning a SQL profile.

Note : Pinning a plan through SQL profile doesn't call for an explicit hard parsing.

Simple method/way to invalidate a cursor

1. Flush the SHARED_POOL

Eg.
alter system flush shared_pool;

2. Delete or Gather statistics of the related objects (table/index) which are referenced in the cursor (execution plan) using DBMS_STATS

3. Grant or Revoke privilege on the related objects (table) which are referenced in the cursor

Eg.
I always use the following commands together on the tables which are referenced in the SQL query/cursor

grant select on scott.emp to system;
revoke select on scott.emp from system;

4. Alter the related objects (table/index) which are referenced in the cursor using DDL command.

5. You can also PURGE the cursor from the shared pool

Eg.
exec dbms_shared_pool.purge(',','C');

The above methods helped me in the situation which I described in the PURPOSE section.

However the below alternate methods may also be considered depending on your situation.

6. You can even add a hint to the SQL query to create a new sql_id. Hence it will be a fresh parse.

Eg.
select /* query1 */ empno, ename from emp;
select /* query2 */ empno, ename from emp;


7. Ofcourse, always database BOUNCE will never let you down :-) Just an extreme step.

Keywords : parse, hard, soft, invalid cursor, invalidate cursor, memory, cache, oracle