Friday, September 25, 2020
dbametrix
More
    Home Oracle Troubleshooting How to check execution plan from shared pool?

    How to check execution plan from shared pool?

    The blog post explains how to obtain execution plan of executed culprit high resource-consuming SQL from shared pool

    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.

    - Advertisement -
    dbametrix

    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.

    - Advertisement -
    dbametrix

    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

    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. These 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.

    Consider Reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments