Panorama: How to identify and evaluate SQL with different execution plans

How to identify changing execution plans in Panorama

Changing or alternating executions plans often contains the risk of unpredictable runtime for SQL-statements.

There are several ways in Panorama to find SQL statements with different execution plans:

1. Lookup into current SGA:

If you list SQLs from SGA (Menu „SGA/PGA details“ / „SQL-Area“ / „Current (SQL-ID)“), there’s the fifth column „P.“ in the table which shows the number of different plans in SGA for a SQL-statement (orange background if more than one plan occurred).


2. Lookup into SQL history:

If you list historic SQLs from AWR history (Menu „SGA/PGA details“ / „SQL-Area“ / „historic“), there’s also a column „P.“ in the table which shows the number of different plans within the choosen period for a SQL-statement.

3. SQL details page for period in history

At the SQL details page you find the number of different execution plans in the considered period at the right upper corner.


4. Complete time line of SQL

At the bottom of the SQL details page (current SGA or historic) you find a button „Complete time line of SQL“.

Clicking this button shows a list with the whole AWR-history of a SQL statement grouped by various time units.

This list also contains a columns „Plans“ with the number of different execution plans plans in this time slot.


5. Direct search for changing execution plans

In menu „Spec. additions“ / „Dragnet investigation“ you find a selection „Identification of statements with alternating execution plans in history“ at point 2.6.

This selection allows you to scan your system for SQLs with changing execution plan, sorted by relevance (difference between best and worst plan per execution).



How to evaluate differences between execution plans

Compare execution plans for a time period of the SQL statementAt the bottom of detail page for historic SQLs you have a button “Execution-Plan (n)”.

If your SQL has multiple plans in considered period, than this function shows you all these plans with some runtime statistics.
Differences between these plans are marked with orange or red background at line level.
So this supports you in finding the differences also for large execution plans.





Usage hint: To access a period with more than on execution in SQL details page you can use the list “complete time line of SQL” and group the history by “month”.
Than clock on column “Start time” and your SQL details page embraces the whole month.

Check possible reasons for multiple execution plans in SGA

At the bottom of current SQL detail page from SGA you have a button “Cursor sharing (n versions)”.
This function shows you the reasons for existence of separate child cursors for your SQL, which may also lead to different execution plans.

Comments

Post a Comment

Popular posts from this blog

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo...
Read more

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet...
Read more

Oracle-DB: Identify excessive logon/logoff operations with short-running sessions

Image
Creating and destroying database sessions in loop may lead to significant database load. This blog post contains more about quantifying this load. Applications should use connection pooling at client or database side to prevent excessive session creation. But how can I identify the client processes causing this sessions? Scan DBA_Audit_Trail to identify context info If auditing logon/logoff-operations is activated for your database, scan DBA_Audit_Trail. You can do this by 2 was: Alternative 1: Use my tool "Panorama" to evaluate audit trail Panorama is available here. Go to menu "DBA general" / "Audit Trail": Select start and end time for selection Filter column "Action" for "LOGON" Set "Grouping" to "Minute" Execute selection via button "Show audit trail" Add columns of your choice to diagram by context menu (right mouse button) This way you can identify the machine, DB-user ...
Read more