When tuning, SQL execution plan is very important for a DBA, in AnySQL I provide few ways to implement it.
Using "EXPLAIN PLAN ..." to get the plan of a execution plan, there is no "FOR" keywords compare to the command in SQL*Plus.
ASQL> EXPLAIN PLAN SELECT * FROM T_HASH;
SQLPLAN COST CARD KBYTE PS PE
------------------------------------------ ---- ----- ----- -- --
0 SELECT STATEMENT Optimizer=RULE 20 10000 59
1 0 PARTITION HASH (ALL) 1 8
2 1 TABLE ACCESS (FULL) OF T_HASH 20 10000 59 1 8
Display the execution plan from V$SQL_PLAN (current executing plan) by SQL hash value with the "ORA PLAN hash_value" command:
ASQL> ORA PLAN 821132411
SQLPLAN COST CARD KBYTE PS PE
----------------------------------------- ---- ----- ----- -- --
0 SELECT STATEMENT Optimizer=RULE 20
1 0 PARTITION HASH (ALL) 1 8
2 1 TABLE ACCESS (FULL) OF T_HASH 20 10000 59 1 8
3 rows returned.
Get the SQL text by a SQL hash value, and then run explain plan command to get the execution plan by "ORA XPLAN hash_value" command, will display both the SQL text and execution plan.
ASQL> ORA XPLAN 821132411
SELECT * FROM T_HASH
SQLPLAN COST CARD KBYTE PS PE
------------------------------------------ ---- ----- ----- -- --
0 SELECT STATEMENT Optimizer=RULE 20 10000 59
1 0 PARTITION HASH (ALL) 1 8
2 1 TABLE ACCESS (FULL) OF T_HASH 20 10000 59 1 8
You will always see hash value in STATSPACK or AWR(10g), these commands are designed for quickly response.