![]() ![]() The COMPARE_CURSOR function was introduced in Oracle 21c, but is undocumented at present. Reference_plan => cursor_cache_object('3vksfnydr3639', 0),Ĭompare_plan_list => plan_object_list(cursor_cache_object('8s0n2z0trbub4', 0)), ![]() We've included the simple and verbose version of the syntax, which result in the same report. So using the SQL_IDs from the two queries we ran before, and assuming CHILD_NUMBER of "0" for each, we can compare the plans as follows. The constructor method for this object accepts a SQL_ID and CHILD_NUMBER attributes.įINAL CONSTRUCTOR FUNCTION CURSOR_CACHE_OBJECT RETURNS SELF AS RESULT We use the CURSOR_CACHE_OBJECT object to return GENERIC_PLAN_OBJECT types for use as parameters. ![]() The COMPARE_PLANS function was introduced in Oracle 19c. Select /*+ full(emp) */ * from emp where empno = 7369 We repeat the query, but this time force a full table scan using the FULL optimizer hint. ![]() Predicate Information (identified by operation id): | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Select * from table(dbms_xplan.display_cursor) Since the EMPNO column is the primary key column, we see the primary key index is used to return the data. We query the EMP table for a specific value of the EMPNO column. Set linesize 120 pagesize 1000 long 1000000 Example Queries If you are using SQL*Plus or SQLcl, these setting may be useful. We connect to the test user and create the EMP table.Įmpno number(4) constraint pk_emp primary key,
0 Comments
Leave a Reply. |