Search code examples
sqloracleoptimizationsql-execution-plan

Oracle explain plan letter scheme for rows and/or bytes

I have been unable to find a resource that can clearly define what these values mean. I have a dummy explain plan below that shows all the different letters I have seen for Oracle: 

Rows    |    Bytes

21P          10E  
11G          15G  
5M           7M

My question is what do the values G,P,E, and M mean? Further are there other values that I have not seen and if so what do they mean and what letters are they?

Solution

  • I think these stand for Mega- Giga- Peta- and Exa- which means there's something very bad(tm) with your SQL. Could you have missed a few cross joins on very large tables?

    Things to check:

    • ensure statistics are up-to-date on all tables and indexes involved
    • check that you have indexes (if possible) that facilitate your join (i.e. indexes on your joined columns on both tables ideally)
    • sanity-check the query for cross joins (joins from table A to table B with no "on" or "where" criteria)

    It could be that the Oracle optimizer is just confused, or it could be that your query is really returning an absurd amount of data, whether intended or not.