background preloader


Facebook Twitter

SQLDeveloper and a very expensive query. I’ve recently been reliably informed that this issue will be fixed in SQLDeveloper 4.0.

SQLDeveloper and a very expensive query

It should be landing here somtime very soon. If you want to install SQLDeveloper 4 on Mint/Ubuntu, you can find instructions here. The magic of the FA Cup. It’s one of those cliches that you are brought up to believe as an article of faith if you’re English. It is supposed to refer to the glorious unpredictability in a knockout cup competition where there is no seeding. This time, we’re going to turn our attention to SQLDeveloper and of the ways in which it attempts to allow users to avoid using the Diagnostic and Tuning Packs.

Disabling the Diagnostic and Tuning Packs – the documented approaches The Oracle licensing documentation itself makes mention of two ways of “disabling” these Packs. Oracle Enterprise Manager has options available to turn off Diagnostic and Tuning Pack features. Then, there is the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter, available from 11g. Look Mum, no packs ! Oh. Oracle Database Diagnostic and Tuning Packs – exactly what are you not licensed for ? DBMS_FEATURE_USAGE_REPORT and The Phantom AFTER SELECT Trigger. Splat !


Darth Debbie has just launched a surprise attack using her current favourite weapon. Never get into a snowball fight with that woman. Milton Keynes, like much of the UK at the moment, looks less like it’s leafy, quiet self and more like the Arctic Tundra at present. Being in a part of the country where normally, you can tell the changing of the seasons by the changing temperature of the rain, a little bit of snow always causes chaos. On the plus side, the national weather forecasters – The Met Office – issue colour-coded weather warnings which, at times like this include the always hilarious Yellow Snow Warning. Anyway, the reason for the Star Wars references in this post is that I was planning to cover the whole question of license monitoring with particular reference to the Diagnostic and Tuning packs.

It is a time of license audits. Queen Debbie ( she’s changed sides in this paragraph) is wondering just how geeky this is all going to get …. All interesting stuff. Options and Packs. This chapter describes the separately licensed Oracle Database options, management packs, and other products you can purchase to enhance the capabilities of Oracle Database in specific application environments.

Options and Packs

This chapter contains the following sections: You may not use the options, packs, or products described below without separately purchased licenses. The fact that these options, packs, or products may be included in product CDs or downloads or described in documentation that you receive does not authorize you to use them without purchasing appropriate licenses. Oracle Resource Manager and DBMS_RESOURCE_MANAGER (Oracle DB/EM Support) The Oracle Database Resource Manager (the Resource Manager) is an infrastructure that provides granular control of database resources allocated to users, applications, and services.

Oracle Resource Manager and DBMS_RESOURCE_MANAGER (Oracle DB/EM Support)

The Oracle Database Resource Manager (RM) enables you to manage multiple workloads that are contending for system and database resources. Resources are allocated to users according to a resource plan specified by the database administrator. The plan specifies how the resources are to be distributed among resource consumer groups, which are user sessions grouped by resource requirements. Oracle 12cR2 DBMS_FEATURE_USAGE_INTERNAL. Oracle DBMS_MANAGEMENT_PACKS AWR TUNING PACK DIAGNOSTIC PACK. AWR. A Tour of the AWR Tables. A Tour of the AWR Tables Dave Abercrombie, Principal Database Architect, Convio © 2008Northern California Oracle Users' Group (NoCOUG) Summer Conference 2008, August 21 2008 Introduced in version 10g, Oracle's Automatic Workload Repository (AWR) provides diagnostic information for performance and scalability studies, automatically recording a rich variety of database performance statistics.

A Tour of the AWR Tables

What's the best way to leverage this wealth of data? While you can run Oracle-supplied AWR reports, or use Oracle features such as the Automatic Database Diagnostic Monitor (ADDM), each Oracle database presents its own unique tuning challenges. In this paper, you'll learn how to work directly with AWR tables, using customized queries to improve insight into your own particular scalability issues. Topics include: Important AWR tables, their contents, how to join them, and their quirks and limitations.

Visualizing Active Session History (ASH) to produce charts. Preamble When deeply looking at my 11.1 Grid Control for a database that suffer performance issue I played a bit in option (removing CPU Cores, Baselines and displaying Foreground + Background) I got the well know below display: And noticed for first time that y axis unit is average active session and not a wait time in centiseconds or whatever which I would have personally done… but how do they do that ?

Visualizing Active Session History (ASH) to produce charts

This database is an Oracle Database 11g Enterprise Edition Release – 64bit Production running on Red Hat Enterprise Linux Server release 5.5 (Tikanga) and I asked myself how would I do to generate it by myself (obviously knowing that all figures are inside Oracle database). Without CPU Wait I started to work with V$WAITCLASSMETRIC_HISTORY table that I joined with V$EVENT_NAME to display wait class and remove idle events: I even, for the first time, used a DataWareHouse way of presenting figures using 11g PIVOT keyword: The list of categories is given with: