Unable to understand SQL Explain Plan

Question

I am currently working on a query optimization which is taking a long time to run. When I googled it I found that we can check the query performance by using sql Explain Plan, below is the plan I got for my query but I am unable to understand what exactly what it says.!

 SELECT STATEMENT ALL_ROWS Cost: 13 Bytes: 187 Cardinality: 1 
        15 NESTED LOOPS Cost: 13 Bytes: 187 Cardinality: 1 
            12 NESTED LOOPS Cost: 11 Bytes: 163 Cardinality: 1 
                9 NESTED LOOPS Cost: 10 Bytes: 146 Cardinality: 1 
                    6 MERGE JOIN CARTESIAN Cost: 8 Bytes: 59 Cardinality: 1 
                        2 TABLE ACCESS BY INDEX ROWID TABLE QUAD.GROUP_ Cost: 4 Bytes: 27 Cardinality: 1 
                            1 INDEX SKIP SCAN INDEX (UNIQUE) QUAD.IX_5BDDB872 Cost: 3 Cardinality: 1 
                        5 BUFFER SORT Cost: 4 Bytes: 32 Cardinality: 1 
                            4 TABLE ACCESS BY INDEX ROWID TABLE QUAD.USER_ Cost: 4 Bytes: 32 Cardinality: 1 
                                3 INDEX SKIP SCAN INDEX (UNIQUE) QUAD.IX_C5806019 Cost: 3 Cardinality: 1 
                    8 TABLE ACCESS BY INDEX ROWID TABLE QUAD.IGIMAGE Cost: 2 Bytes: 87 Cardinality: 1 
                        7 INDEX RANGE SCAN INDEX QUAD.IX_BE79E1E1 Cost: 1 Cardinality: 1 
                11 TABLE ACCESS BY INDEX ROWID TABLE QUAD.IGFOLDER Cost: 1 Bytes: 17 Cardinality: 1 
                    10 INDEX UNIQUE SCAN INDEX (UNIQUE) QUAD.SYS_C00117581 Cost: 0 Cardinality: 1 
            14 TABLE ACCESS BY INDEX ROWID TABLE QUAD.IMAGE Cost: 2 Bytes: 24 Cardinality: 1 
                13 INDEX UNIQUE SCAN INDEX (UNIQUE) QUAD.SYS_C00117585 Cost: 1 Cardinality: 1 

Please let me know how it works and is there anything wrong with this output?

select ig.largeimageid, ig.groupId, ig.createDate, ig.modifiedDate, ig.folderId, ig.name, ig.imageid,
ig.description, im.type_, im.height, im.width, im.size_, 
g.name groupname, u.screenname cecuserid, u.firstname, u.lastname, 
fo.name folderName, fo.description folderDesc 
from quad.igimage ig,quad.image im, quad.group_ g, quad.user_ u, quad.igfolder fo 
where ig.groupid=  g.groupid 
and u.userid = ig.userid 
and fo.folderid=ig.folderid 
and ig.largeimageid= im.imageid
and u.screenname='xyz'
and g.friendlyurl = '/xyz';

Answer 1

Oracle uses the Optimizer in order to determine the most efficient execution plan. Keep in mind that it makes the decisions based on statistical information. This means that there must be adequate stats. The execution plan shows the detail steps to execute your statement. So, the SELECT of the first row is your actual query. The Cost of this select is 13 which is actually the cost of the nested operations. Your query's cardinality is 1. The four key elements of the execution plan are the following:

1. Cardinality: Estimation on the number of rows of each operation.
2. Access Method: The your data are accessed. May be a table scan or via index.
3. Join Method: Can be (sort-merge, hash etc). It is in fact the type of your table join.
4. Join Order: The order in which joins are performed to tables.

By breaking down the statement and investigating on the above elements, you have a better notion of how the Oracle optimizer chooses the most effective plan. If you want to delve deeper in cost and cardinality take a look at this post . Nested rows show the operations that were made in order to execute your query. You can see your joins along with their cost.

Nested Loops: Take a look at NESTED LOOPS in your execution plan: As the name declares, for every row resided in the first table, Oracle assesses ALL the rows in the second one(which is actually the inner table). Nested Loops are used when joining a little amount of data. The second table should be also efficiently accessed.

SORT MERGE JOINS on the other hand are more efficient in larger data sets.

Access Methods: One interesting information is the access of the table: There you can see if index was used for access. If there is a large portion of data to be selected and there is no relative index, you may see FULL TABLE SCAN. In that case all rows from table are read and rows that don't meet the predicated criteria are filtered out. This operation may increase the cost of the statement. The INDEX RANGE SCAN and a table lookup by ROWID, is sum of the cost of scanning the index, and the cost of accessing the table by ROWID. In general oracle gets the rowids basically from WHERE clause or through index scan. This can lead you to create a new index, if full access table is performed.

Looking into your Explain Plan, seems that Oracle optimizer avoids a FULL SCAN. The cost of this is reading all the rows of the table. This is an indication of a slow query.

For a detailed documentation you can look at Oracle's Documentation