Dba_hist_ SQL _plan, dbahistsqlstat

Dba_hist_ SQL _plan, dbahistsqlstat

I would like to express my gratitude for the help of elders who have prepared this article.

The applicable oracle database version is oracle 10 Gb or later.

 

This view is important because one column in this view is not found in the awrsqrpt report. This column is the filter_predicates column.

 

SELECT plan_hash_value,

TO_CHAR (RAWTOHEX (child_address), TO_NUMBER (child_number), id, LPAD ('', DEPTH) | operation, options, object_owner, object_name, optimizer, cost, access_predicates, filter_predicates from v $ SQL _PLAN WHERE SQL _id = 'bkcyk7bf1_t6 'ORDER BY 1, 3, 2, 4; Focus on the optimizer column and the filter_predicates column. If this SQL statement is not in the shared pool, run the following SQL statement: set linesize 500 set pagesize 500col formatted format encode id format 999999col operation format a30col options format a15col object_owner format a15col object_name format a20col optimizer format a15col cost format encode access_predicates format a15col encode format a15
SELECT plan_hash_value, id, LPAD ('', DEPTH) | operation, options, object_owner, object_name, optimizer, cost, access_predicates, filter_predicates FROM dba_hist_ SQL _plan WHERE SQL _id = 'fahv8x6ngrb50 'ORDER BY plan_hash_value, id; Focus on the filter_predicates column. -- This column can help you determine why indexes are not taken away. If the query result of this column is null, it is normal. If it is not null, you need to pay attention to the reason for not being null, as shown in:
How to view the execution plan of oracle SQL

1. Use the PL/SQL Dev Tool
1. directly File-> New-> Explain Plan Window. Execute SQL statements in the Window to view the Plan results. Among them, Cost indicates the cpu consumption, unit is n %, Cardinality indicates the number of lines executed, equivalent to Rows.
2. Execute the explain plan for select * from tableA where paraA = 1, and then select * from table (DBMS_XPLAN.DISPLAY) to view the oracle execution PLAN, the result is the same as that in 1. Therefore, we recommend that you use the 1 method when using the tool.
Note: The Command window of PL/SQL Dev does not support the set autotrance on Command. You can also use tools to view the information you see in the plan. Sometimes we need sqlplus support.

Ii. Use sqlplus
1. The simplest way
SQL> set autotrace on
SQL> select * from dual;
After the statement is executed, the explain plan and statistics are displayed.
The advantage of this statement is its disadvantage. When you use this method to view SQL statements that have been executed for a long time, you must wait until the statement is successfully executed before returning the execution plan, this greatly increases the optimization cycle. If you do not want to execute the statement but want to execute the plan, you can use:
SQL> set autotrace traceonly
In this way, only the execution plan will be listed, rather than the actual execution statement, greatly reducing the optimization time. Although the statistics are also listed, the statistics are useless because no statement is executed. If an error occurs while executing the Statement, the solution is as follows:
(1) users to be analyzed:
Sqlplus> @?
Dbmsadminutlxplan. SQL
(2) log on with the sys user
Sqlplus> @? Sqlplusadminplustrce. SQL
Sqlplus> grant plustrace to user_name;
--User_name is the analysis user mentioned above.

2. Use the explain plan command
(1) sqlplus> explain plan for select * from testdb. myuser
(2) sqlplus> select * from table (dbms_xplan.display );
The above two methods can only generate execution plans for the statements that are running in this session, that is, we need to know which statements are running very poorly, we aim to optimize this SQL statement only. In fact, in many cases, we only hear one customer complain that the system is running slowly, but we do not know which SQL is causing it. At this time, there are many ready-made statements to find the statements that consume more resources, such:
Select address, substr (SQL _TEXT, 1, 20) Text, buffer_gets, executions,
Buffer_gets/executions avg from v $ sqlarea
WHERE executions> 0 AND buffer_gets> 100000 order by 5;
Address text BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- -----------...... The remaining full text>

Does the execution of an SQL statement in oracle use indexes? What is the execution order? How can we see this?

Write the SQL statement and click F5 in plsql to see the explain Plan.