Monday, November 23, 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

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments