SQL> EXPLAIN PLAN FOR select pp.item_preprocid,pp.itemid,pp.type,pp.params,pp.step,h.hostid,pp.error_handler,pp.error_handler_params from item_preproc pp,items i,hosts h where pp.itemid=i.itemid and i.hostid=h.hostid and (h.proxy_hostid is null or i.type in (5,15,18)) and h.status in (0,1) and i.flags<>2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 231659486

--------------------------------------------------------------------------------
------------

| Id  | Operation           | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)
| Time     |

--------------------------------------------------------------------------------
------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |  1629K|   129M|       | 93840   (1)
| 00:00:04 |

|*  1 |  HASH JOIN          |              |  1629K|   129M|    51M| 93840   (1)
| 00:00:04 |

|*  2 |   HASH JOIN         |              |  1275K|    36M|       | 66168   (1)
| 00:00:03 |

|*  3 |    TABLE ACCESS FULL| HOSTS        | 19439 |   246K|       |   127   (0)
| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |    TABLE ACCESS FULL| ITEMS        |  3305K|    53M|       | 66031   (1)
| 00:00:03 |

|   5 |   TABLE ACCESS FULL | ITEM_PREPROC |  4225K|   213M|       | 12129   (1)
| 00:00:01 |

--------------------------------------------------------------------------------
------------



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PP"."ITEMID"="I"."ITEMID")
   2 - access("I"."HOSTID"="H"."HOSTID")
       filter("H"."PROXY_HOSTID" IS NULL OR "I"."TYPE"=5 OR "I"."TYPE"=15 OR
              "I"."TYPE"=18)
   3 - filter("H"."STATUS"=0 OR "H"."STATUS"=1)
   4 - filter("I"."FLAGS"<>2)

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - this is an adaptive plan

26 rows selected.






SQL> EXPLAIN PLAN FOR select i.itemid, i.hostid, i.status, i.type, i.value_type, i.key_, i.snmp_oid, i.ipmi_sensor, i.delay, i.trapper_hosts, i.logtimefmt, i.params, ir.state, i.authtype, i.username, i.password, i.publickey, i.privatekey, i.flags, i.interfaceid, ir.lastlogsize, ir.mtime, i.history, i.trends, i.inventory_link, i.valuemapid, i.units, ir.error, i.jmx_endpoint, i.master_itemid, i.timeout, i.url, i.query_fields, i.posts, i.status_codes, i.follow_redirects, i.post_type, i.http_proxy, i.headers, i.retrieve_mode, i.request_method, i.output_format, i.ssl_cert_file, i.ssl_key_file, i.ssl_key_password, i.verify_peer, i.verify_host, i.allow_traps, i.templateid, null from items i inner join hosts h on i.hostid=h.hostid join item_rtdata ir on i.itemid=ir.itemid where h.status in (0,1) and i.flags<>2;

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3479298806

--------------------------------------------------------------------------------
--------------------

| Id  | Operation               | Name             | Rows  | Bytes |TempSpc| Cos
t (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |  3261K|  1219M|       |   1
33K  (1)| 00:00:06 |

|*  1 |  HASH JOIN              |                  |  3261K|  1219M|       |   1
33K  (1)| 00:00:06 |

|*  2 |   VIEW                  | index$_join$_002 | 19439 |   151K|       |
87   (0)| 00:00:01 |

|*  3 |    HASH JOIN            |                  |       |       |       |
        |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   4 |     INLIST ITERATOR     |                  |       |       |       |
        |          |

|*  5 |      INDEX RANGE SCAN   | HOSTS_2          | 19439 |   151K|       |
41   (0)| 00:00:01 |

|   6 |     INDEX FAST FULL SCAN| SYS_C006801      | 19439 |   151K|       |
58   (0)| 00:00:01 |

|*  7 |   HASH JOIN             |                  |  3305K|  1210M|    88M|   1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
32K  (1)| 00:00:06 |

|   8 |    TABLE ACCESS FULL    | ITEM_RTDATA      |  3309K|    50M|       |  31
03   (1)| 00:00:01 |

|*  9 |    TABLE ACCESS FULL    | ITEMS            |  3305K|  1160M|       | 660
82   (1)| 00:00:03 |

--------------------------------------------------------------------------------
--------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("I"."HOSTID"="H"."HOSTID")
   2 - filter("H"."STATUS"=0 OR "H"."STATUS"=1)
   3 - access(ROWID=ROWID)
   5 - access("H"."STATUS"=0 OR "H"."STATUS"=1)
   7 - access("I"."ITEMID"="IR"."ITEMID")
   9 - filter("I"."FLAGS"<>2)

26 rows selected.