How to check the Execution plan from a shared pool?

When we are observing any performance issue of the present SQL statement then we need to check the execution path of the culprit SQL statement. If it was already executed then we can obtain from the shared pool memory region of SGA in Oracle.

Using the following trick, you can get the execution path of executed SQL from the memory region of SGA. Using this trick, you can identify those heavily loaded queries which affect database performance degrading. During running 24/7 online database, performance tuning is the big thing and issue.

- Advertisement -

Check the execution plan with which the SQL statement that has already been executed has been compiled, obtaining the data from the SHARED POOL (v$sql).

In V$SQL I can see the sql statements that have already been executed, so that I can obtain the identifier (V $ SQL.SQL_ID) of any sql statement, to later consult the execution plan using the dbms_xplan package (DBMS_XPLAN.DISPLAY_CURSOR (SQL_ID)).

SQL> connect system / fabiancrea
Enter password:
Connected.

SQL> select deptno, count (*) from scott.emp group by deptno;
DEPTNO COUNT (*)
30 6
20 5
10 3
SQL> select sql_id from v $ sql where sql_text = 'select deptno, count (*) from scott.emp group by deptno';
SQL_ID
cxyqkgukrtpd6

- Advertisement -

SQL> select * from table (dbms_xplan.display_cursor ('cxyqkgukrtpd6'));

PLAN_TABLE_OUTPUT
SQL_ID cxyqkgukrtpd6, child number 0

select deptno, count (*) from scott.emp group by deptno

Hash value plan: 4067220884

————————————————– ——————–
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
————————————————– ——————–
| 0 | SELECT STATEMENT | | | | 7 (100) | |
| 1 | HASH GROUP BY | | 3 | 9 | 7 (15) | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 42 | 6 (0) | 00:00:01 |
————————————————– ——————–
14
selected rows.

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

- Advertisement -