Enable autotrace in sqlplus and check the execution plan. So far, DBA users are first granted the plustrace role, and then we can authorize plustrace to public. If
Enable autotrace in sqlplus and check the execution plan. So far, DBA users are first granted the plustrace role, and then we can authorize plustrace to public. If
1. How to enable the autotrace function of sqlplus? [Pl/SQL dev does not support this function]
Log On with the sys user and perform the following steps:
(1 )@? \ Rdbms \ admin \ utlxplan -- create a plan_table table
(2) There is no synonym for plan_table before 10 Gb, so create public synonym plan_table for plan_table must be added. [This step: option] Because: In 10 Gb, you don't need to create plan_table in your schema to explain plan. it uses the table plan_table $ belonging to SYS through a public synonym. the table is a session duration-based global temporary table. makes perfect sense!
(3) grant all on plan_table to public
(4 )@? \ Sqlplus \ admin \ plustrce
So far, DBA users are first granted the plustrace role, and then we can grant plustrace to public. If you need to restrict the Autotrace permission, you can change the public authorization to the specific user authorization. For example, grant plustrace to scott.
[If the preceding steps fail:
1.
1.) which user executes the set autotrace?
2.) Whether plan_table and plustrace are created and authorized as required
2.
If you have already done this as required and an error is returned, grant select any dictionary to your_user and verify whether the application can be used.
3.
Can't we re-build the drop plan_table?
]
Notice:
(1) The only limit of Explain is that the user must be the owner of the thing to be interpreted; otherwise, an error may be reported.
(2) In pl/SQL dev, we can use the explain plan for + select statement, and then execute select * from table (DBMS_XPLAN.display ).
Description of several common Autotrace options:
Set autotrace off: default value.
Set autotrace on: including query output, execution plan, and statistics
On has two optional parameters:
Explain: including query output and execution plan
Statistics: including query output and Statistics
Set autotrace traceonly: including execution plan and statistics