Predicate Information (identified by operation id): | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Select /*+ INDEX (MYOBJECTS_19C,MYOBJECTS_IND) */ distinct object_type
SQL> select * from table (dbms_xplan.display_cursor) SQL> select /*+ INDEX (MYOBJECTS_19C,MYOBJECTS_IND) */ distinct object_type We have a table called MYOBJECTS_19C and we force the execution plan in this case to use an Index via the INDEX hint. Let us have a look at this feature with an example. We want to determine how adding a hint, changing a parameter, or creating an index will affect a plan.We want to compare the current plan of a query whose performance is regressing with an old plan captured in AWR.We can use DBMS_XPLAN.COMPARE_PLANS to generate a report in text, XML, or HTML format. With this new functionality, we can take a reference plan and compare it with some other execution plan – it could belong to a SQL statement already executed where we obtain the plan from the Cursor Cache or from the AWR history or an existing SQL Tuning Set – or could be a case where we want to test an how an existing plan will behave if we make some change like adding a hint or maybe creating an index. In Oracle 19c, a new sub-program called COMPARE_PLANS has been added to DBMS_XPLAN.
0 kommentar(er)
