Search

OakieTags

Who's online

There are currently 1 user and 40 guests online.

Online users

Recent comments

Affiliations

sql*plus

New Version Of XPLAN_ASH Utility

A minor update 4.01 to the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

These are the notes from the change log:

- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data

- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part of the PX_FLAGS column) on 11.2.0.2+

- Since version 4.0 added from 11.2.0.2 on the PX *MAX* DOP in the "SQL statement execution ASH Summary" based on the new PX_FLAGS column of ASH it makes sense to add a PX *MIN* DOP in the summary to see at one glance if different DOPs were used or not

New Version Of XPLAN_ASH Utility

A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post includes links to video tutorials explaining what the tool is about.

As usual the latest version can be downloaded here.

The new version comes with numerous improvements and new features. The most important ones are:

  • Real-Time SQL Monitoring info included
  • Complete coverage including recursive SQL
  • Improved performance
  • 12c compatible
  • Simplified usage

Last Successful Login Time in SQL*Plus in Oracle 12c

If you have been working with Oracle 12c, you may have missed a little something that appeared without mush fanfare but has some powerful implications. Let's see it with a small example--connecting with SQL*Plus.

C:\> sqlplus arup/arup

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 19 14:17:45 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Mon Aug 19 2013 14:13:33 -04:00

New Version Of XPLAN_ASH Tool - Video Tutorial

A new major release (version 3.0) of my XPLAN_ASH tool is available for download.

You can download the latest version here.

In addition to many changes to the way the information is presented and many other smaller changes to functionality there is one major new feature: XPLAN_ASH now also supports S-ASH, the free ASH implementation.

If you run XPLAN_ASH in a S-ASH repository owner schema, it will automatically detect that and adjust accordingly.

XPLAN_ASH was tested against the latest stable version of S-ASH (2.3). There are some minor changes required to that S-ASH release in order to function properly with XPLAN_ASH. Most of them will be included in the next S-ASH release as they really are only minor and don't influence the general S-ASH functionality at all.

New Version Of XPLAN_ASH Utility

A new version 2.0 of the XPLAN_ASH utility introduced here is available for download.You can download the latest version here.The change log tracks the following changes:- Access check- Conditional compilation for different database versions- Additional activity summary- Concurrent activity information (what is/was going on at the same time)- Experimental stuff: Additional I/O summary- More pretty printing- Experimental stuff: I/O added to Average Active Session Graph (renamed to Activity Timeline)- Top Execution Plan Lines and Top Activities added to Activity Timeline- Activity Timeline is now also shown for serial execution when TIMELINE option is specified- From 11.2.0.2 on: We get the ACTUAL DOP from the undocumented PX_FLAGS colu

Parallel Execution Analysis Using ASH - The XPLAN_ASH Tool

Preface

Note: This blog post actually serves three purposes:

  1. It introduces and describes my latest contribution to the Oracle Community,  the "XPLAN_ASH" tool

  • It accompanies a future OTN article on Parallel Execution that will be published some time in the future

  • It is supposed to act as a teaser for my upcoming "Parallel Execution Masterclass" that will be organized by Oracle University and can be booked later this year
  • Table Of Contents

    Introduction

    Real-Time SQL Monitoring Overview

    Real-Time SQL Monitoring Shortcomings

    Autotrace Polluting The Shared Pool?

    Introduction

    Another random note that I made during the sessions attended at OOW was about the SQL*Plus AUTOTRACE feature. As you're hopefully already aware of this feature has some significant shortcomings, the most obvious being that it doesn't pull the actual execution plan from the Shared Pool after executing the statement but simply runs an EXPLAIN PLAN on the SQL text which might produce an execution plan that is different from the actual one for various reasons.

    Now the claim was made that in addition to these shortcomings the plan generated by the AUTOTRACE feature will stay in the Shared Pool and is eligible for sharing, which would mean that other statement executions could be affected by a potentially bad execution plan generated via AUTOTRACE rather then getting re-optimized on their own.

    Lack of Index and Constraint Comments

    Something I’ve just reminded myself of is that under Oracle you cannot add a comment on an index or a constraint. You can only add comments on tables, views, materialized views, columns of those object types and a couple of esoteric things like Operators, Editions and Indextypes.

    Here is an example of adding comments to tables and columns:

    Missing information in SQL*Plus

    By preference I like to work against the databse using SQL*Plus. GUIs are fine and they are much better for developing code than using sql*plus and notepad (or vi or whatever ascii text editor floats your boat). However, for finding things out quickly and also storing what I find (via spool files) then sql*plus is much better. As screens have got larger, I have used a larger – and wider – sql*plus session to show more information at one time.
    But I have been having an issue with SQL*Plus not showing some information. I lose information on the right hand side of the screen.

    This is on the V10 client on Windows by the way, I’ve seen it on windows XP to Vista.

    Look at the below. It is quite a wide output and I have had to increase the standard 80 character linesize from 80 up to 120:

    {BTW if you are looking at this in a small browser winder, you might have to click on the screen shot to open it up in it’s own window, to see what I mean – it is quite wide, that is the point of the post – it should just fit in my over-wide blog layout :-) }

    set linesize 120

    AUDIT_PROGRAM            entry_dt ENTRY_TS     AUDIT_TEXT                                                    aud_val1   aud_val2
    ------------------------ -------- ------------ -----------------------------------------------------
    MM_STATS_GATHER          19-05-11 04.46.39.599 Index EVE_CAT_USER_FK_I Partition EVE_I_TOP                          0        100
    MM_STATS_GATHER          19-05-11 04.46.39.571 Index EVE_IDX Partition EVE_I_TOP                                    0        100
    MM_STATS_GATHER          19-05-11 04.46.39.556 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_TOP                        0        1
    MM_STATS_GATHER          19-05-11 04.46.39.519 Index EVE_CAT_BANK_FK_I Partition EVE_I_TOP                          0        100
    MM_STATS_GATHER          19-05-11 04.46.38.846 Table EVENTS Partition EVE_D_TOP                                     0        100
    MM_STATS_GATHER          19-05-11 04.46.32.497 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_07                  4,962
    MM_STATS_GATHER          19-05-11 04.46.20.787 Index EVE_IDX Partition EVE_I_2011_07                          437,500
    MM_STATS_GATHER          19-05-11 04.46.09.939 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_07               91,
    MM_STATS_GATHER          19-05-11 04.45.58.613 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_07                441,2
    MM_STATS_GATHER          19-05-11 04.45.44.534 Table EVENTS Partition EVE_D_2011_07                           927,950
    MM_STATS_GATHER          19-05-11 04.45.41.207 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_04                  6,324
    MM_STATS_GATHER          19-05-11 04.45.22.638 Index EVE_IDX Partition EVE_I_2011_04                          771,840
    MM_STATS_GATHER          19-05-11 04.45.17.024 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_04              168,
    MM_STATS_GATHER          19-05-11 04.45.05.463 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_04                848,0
    MM_STATS_GATHER          19-05-11 04.44.55.474 Table EVENTS Partition EVE_D_2011_04                         1,625,100
    MM_STATS_GATHER          19-05-11 04.44.47.100 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_01                    461
    MM_STATS_GATHER          19-05-11 04.44.23.829 Index EVE_IDX Partition EVE_I_2011_01                          800,020
    MM_STATS_GATHER          19-05-11 04.44.03.673 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_01              158,
    

    Note that for line 8 onwards there is no value for aud_val2. But then, some of the numbers in aud_val1 also look odd. But I’m getting output for some of the lines…

    I am in fact getting some of the lines truncated. It just seems to be a bug in the V10 sql*plus client and it might pass you by as the first few lines usually come out “full length”. This truncation only happens if you increase the linesize.

    To fix it, you need tell the “windows” sql*plus window how big your output now is. Click on the options and then environment menu items at the top left of the screen:


    Change the SQL*Plus environment

    Now set the screen buffer width to a bit more than it was, say by one character (and whilst you are at it, why not increasre the buffer length to 2000, the memory required is peanuts on today’s machines). I actually tend to set it to 140 to anticipate some of my more verbose scripts.


    Alter the screen buffer width to one or two characters more

    And confirm that you are happy to “truncate the buffer” – whoever coded this did not bother to check if you alter the values up or down – you can ignore this message if you are simply increasing the buffers.


    Accept the prompt about truncation

    Now when I run my sql statement I see the full output {Again, click on the image to see it’s full length to fully see how the problem is now fixed}:

    AUDIT_PROGRAM            entry_dt ENTRY_TS     AUDIT_TEXT                                                    aud_val1   aud_val2
    ------------------------ -------- ------------ ------------------------------------------------------- -------------- ----------
    MM_STATS_GATHER          19-05-11 04.46.39.599 Index EVE_CAT_USER_FK_I Partition EVE_I_TOP                          0        100
    MM_STATS_GATHER          19-05-11 04.46.39.571 Index EVE_IDX Partition EVE_I_TOP                                    0        100
    MM_STATS_GATHER          19-05-11 04.46.39.556 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_TOP                        0        100
    MM_STATS_GATHER          19-05-11 04.46.39.519 Index EVE_CAT_BANK_FK_I Partition EVE_I_TOP                          0        100
    MM_STATS_GATHER          19-05-11 04.46.38.846 Table EVENTS Partition EVE_D_TOP                                     0        100
    MM_STATS_GATHER          19-05-11 04.46.32.497 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_07                  4,962        100
    MM_STATS_GATHER          19-05-11 04.46.20.787 Index EVE_IDX Partition EVE_I_2011_07                          437,500          5
    MM_STATS_GATHER          19-05-11 04.46.09.939 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_07               91,395         20
    MM_STATS_GATHER          19-05-11 04.45.58.613 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_07                441,240          5
    MM_STATS_GATHER          19-05-11 04.45.44.534 Table EVENTS Partition EVE_D_2011_07                           927,950          2
    MM_STATS_GATHER          19-05-11 04.45.41.207 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_04                  6,324        100
    MM_STATS_GATHER          19-05-11 04.45.22.638 Index EVE_IDX Partition EVE_I_2011_04                          771,840          5
    MM_STATS_GATHER          19-05-11 04.45.17.024 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_04              168,840          5
    MM_STATS_GATHER          19-05-11 04.45.05.463 Index EVE_CAT_BANK_FK_I Partition EVE_I_2011_04                848,060          5
    MM_STATS_GATHER          19-05-11 04.44.55.474 Table EVENTS Partition EVE_D_2011_04                         1,625,100          1
    MM_STATS_GATHER          19-05-11 04.44.47.100 Index EVE_CAT_USER_FK_I Partition EVE_I_2011_01                    461        100
    MM_STATS_GATHER          19-05-11 04.44.23.829 Index EVE_IDX Partition EVE_I_2011_01                          800,020          5
    MM_STATS_GATHER          19-05-11 04.44.03.673 Index EVE_AGR_CNTRPY_FK_I Partition EVE_I_2011_01              158,600          5