Skills Update

Hi!

I'm going to be better at posting to my blog.  Watch for useful APEX and OBIEE information. 

I'll also post some useful information and scripts for starting and stopping Oracle SQL Trace (the 10046 trace).

Dan

Oracle10g+ Plan Table

Hi!

Oracle is now maintaining a PLAN_TABLE for us. This table is used extensively in the SQL tuning process. It is also used by Oracle's autotrace and other tools. I show various uses for it in my SQL Tuning course. Oracle's version is owned by SYS and is named PLAN_TABLE$. It does have public access.

To see if it is working correctly for you:

• Simply DROP your PLAN_TABLE.
• in sql*plus...'set autotrace on'
• execute ANY sql (select * from emp; for example...).
  

IF sql*plus complains about a missing Plan Table, have your administrator create a public synonym for SYS.PLAN_TABLE$. This is easier than using PLAN_TABLE$ in each of your scripts/TOAD/etc. Some tools such as SQL Developer, the PLAN_TABLE name is not adjustable.

Good luck!

Dan

Oracle11g Case Sensitive Passwords

A Change for Oracle11

Some of my best material comes to me in interesting ways. I usually use all lower case syntax with Oracle. I recently taught a class using an 11g database and some folks had issues logging in.

Oracle11g IS Case Sensitive on Passwords

Oracle11g is now case sensitive with passwords. When new accounts are created, the password is indeed case sensitive.

You can see the case sensitive setting in SQL+ by using ‘SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON’. People with DBA privileges can see the password status using ‘SELECT USERNAME, PASSWORD_VERSIONS from DBA_USERS;’. This list will show both migrated users and new users password status.

Migrating from older releases…

When migrating from older releases, the passwords are preserved and they are NOT case sensitive until they are changed, and they are only case sensitive only if the above SEC_CASE_SENSITIVE_LOGON is still set to TRUE.

Changing the Settings

Because I do training and I’m not interested in case-sensitive passwords, I ran the following 2 commands to disable this feature on my Oracle11R1 database.

Command prompt: orapwd file=orapwDB11Gb entries=100 ignorecase=y password=’’;

Connect as SYSDBA and enter:

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

Summary

Be prepared for this subtle change in Oracle11 logon behavior!

PL/SQL Conditional Compilation

Starting with Oracle10.2, Oracle now allows for PL/SQL to be coded including only certain lines of code:
· Different code for different releases of the database (selection directives)
· Checking INIT.ORA settings at compile time (inquiry directives)
· Checking for various parameter error conditions at compile time (error directives)

This coding technique was implemented in Oracle10r2 but has backward support back to Oracle9.2. Contact Oracle Support to learn how to include this technique for these earlier releases.

This coding technique can make procedures and functions database release independent! You can now have one version of the code that supports the legacy and latest features, compile with only certain INIT.ORA settings, or create error conditions based on parameters and code reminders at compile time.




Selection directives works with $IF $THEN $ELSE $ELSIF $END. At compile time, Oracle will only compile the code that tests true between these $IF directives. The DBMS_DB_VERSION package is very useful for release-specific code. This package allows you to interrogate the Oracle major and minor versions of the database at compile time. DBMS_DB_VERSION.VERSION gives the major database release (such as 10) and the DBMS_DB_VERSION.RELEASE gives the minor database release number such as 2 (…as in Oracle10r2). There are also bullions that will be set true for the release that is currently being used.


Inquiry Directives works with the $IF logic and is used to interrogate compiler settings.



Inquiry Directives example

Notice that the PLSQL_CCFLAGS can be set using any variables!




Standard PL/SQL errors can be invoked with a mix of these techniques. This might be useful to document that code has not been completed, reminders for programmers who are doing modular programming, etc. Notice the code at lines 14 thru 18.

These examples came from Oracle Magazine July / Aug 2006.

Summary

This technique will allow programmers and DBA’s to develop 1 script that will work on multiple Oracle platforms, particularly using that syntax that changes between releases of Oracle!

My 3-day Advanced PL/SQL Tips and Techniques has lab exercises using this new coding style.

Oracle11g PL/SQL Compiler Options

PL/SQL has traditionally been run-time interpreted. The compiler makes a p-code and this code is stored in the database along with the PL/SQL source code.



Oracle11g has expanded the internal PL/SQL optimizing compiler introduced in Oracle10g.


Oracle10g implemented an optimizing compiler. This compiler tries to eliminate unaccessed code from being compiled, looks for redundancies, and more.

· Level 0 – no optimization (Oracle9 compatible)
· Level 1 – eliminates unaccessed code from compiler output
· Level 2 – restructures code, aggressive optimization


Oracle11g has enhanced the optimizing compiler, adding a level 3 that streamlines inline code and calls to inline code.




The compiler can be invoked via the commands listed above.

Objects and compiler settings can be viewed using the USER_PLSQL_OBJECTS_SETTINGS (for current objects) and V$PARAMETER (to see the PL/SQL compiler settings at the database level). ALTER SESSION can change any of these settings for the duration of a connected session (ie: the session used to create and/or compile code).



The PL/SQL compiler can give useful warnings. These warnings can provide a variety of information such as performance tips to dead code to other coding issues. Follow the instructions above to implement and use.




Oracle does not allow for functions called from SELECT or DML statements to change the table that is referenced in the SELECT or DML. These functions also cannot issue a commit or an implied commit (such is the kind from a DDL statement).





Oracle can insure specific application behavior at the package level using these settings. These settings allow for specific behavior to be enforced when calling these routines either directly or indirectly from SQL statements.


Summary

Oracle continues to enhance the PL/SQL compiler. It is important to understand what the options are and how they can be used to enhance your applications. This section is an excerpt from my Advanced PL/SQL Tips and Techniques course.

SQL T - Trace File Analysis Tool

SQLT (SQLTXPLAIN) is a tool that inputs one SQL statement and outputs a set of comprehensive diagnostic files for SQL performance analysis and tuning.

***Note*** These notes are from the SQLT user documentation

Comprehensive HTML report that includes an enhanced explain plan, details about the tables accessed by the SQL, their indexes, their columns, CBO statistics including Histograms, partitions and subpartitions if applicable, initialization parameters in effect, stored outlines associated to the SQL, SQL profiles if any, child plans, metrics of SQL execution performance, observations, etc.

SQL T is available via Metalink note 215187.1. Download SQLT.zip and follow the directions to install it.

Since the tool is installed into its own schema and makes use of temporary objects for most of its data structures, it is light and moderate intrusive. The schema owner SQLTXPLAIN only gets granted a small subset of roles and attributes in order to generate SQL analysis results. This SQLTXPLAIN schema owner does not read application data itself.

· This SQLT tool installs into its own schema SQLTXPLAIN
· It does not install any objects into application schemas
· Its footprint is very small
· The RDBMS version can be 9i, 10g or 11g
· The OS can be UNIX, LINUX or WINDOWS
· Source is not wrapped (SQL and PL/SQL)
· It can be installed in RAC systems
· It installs by executing just one script sqcreate.sql

**Note** More information is available by running it as a SYSDBA.

SQLT inputs one SQL DML statement provided as one of these methods:
1. XPLAIN: As a stand-alone SQL in a flat text file.
2. XTRACT: As a memory-resident SQL, identified by its hash_value or sql_id.
3. XECUTE: As a stand-alone script that contains one SQL, together with the declaration and values of its bind variables.
4. XTRXEC: As a combination of XTRACT and XECUTE, this method gets a SQL from memory and executes both XTRACT and XECUTE.

The recommended methods are XECUTE and XTRACT. Try avoiding the XPLAIN method since the Explain Plan generated by it may not be accurate if your SQL contains bind variables.

SQLT runs various traces and collects information from a variety of sources including AWR.

The main.html doc…everything is hyperlinked and all information about this SQL statement is included. This document includes all stats, tablespace info, CBO explain plan info from the 10053 trace, and more!

This tool literally exposes everything you would want to know about a particular SQL statement and how it interacted with the Oracle RDBMS.

Index Monitoring

Oracle9i has introduced an easy method to see of indexes are even being used by your application.

Amazing as it sounds, I am finding an average of 10 indexes per table object, particularly in larger applications. Oracle probably is not using most of these indexes or only uses them occasionally such as at month end/quarter end/year end processing. These unused indexes are maintained and can make batch cycles run twice/three times as long as needed.

My SQL Performance Tuning class (being offered frequently via the Web…or we can schedule this at your company) goes into great detail on how Oracle uses indexes, how the optimizer decides which index to use, as well as this index-monitoring topic…which is an excerpt from my SQL Performance Tuning course guide.

Index Monitoring


Oracle has the ability to tell if an index has ever been used in a SELECT or sub-query. This technology uses the MMON process and has to be tripped on to populate the V$OBJECT_USAGE view.

SQL> desc v$object_usage
Name Null? Type
----------------------------------------- -------- --------------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)

SQL>

If the index then appears in a query explain plan, the USED column will be set to ‘YES’, otherwise it is ‘NO’. Make sure to connect as the object owner. Only objects owned by the connected user will be visible in this view.


This script can be used to turn on index monitoring for all indexes for a particular schema owner: Start_Index_Monitoring.sql …is a SQL creating SQL script that turns index monitoring on for each index for the schema owner
Index_Monitoring.sql produces a nice report (see below) of the counts.
1. Indexes that were used
2. Indexes that were not used
3. Number of indexes being monitored
4. Number of tables that have the indexes

Ask me for these scripts...

spool Index_Monitor_setup.sql
select 'Alter Index ' index_name ' monitoring usage;' from user_indexes;

spool off
start Index_Monitor_setup.sql

Portion of Index_Monitor_Setup.sql script.


select to_char(sysdate,'mm/dd/yy hh:mi:ss') Date_Time from dual;
select 'Table Count = ' count(*) from user_tables;
select 'Index Count = ' count(*) from v$object_usage;
Select 'Indexes Used = ' count(*) from v$object_usage where USED = 'YES';
select 'Indexes NOT Used = ' count(*) from v$object_usage where USED = 'NO';

Portion of Index_Monitor.sql Script


Index Monitoring Report Date: 02/14/10 10:33:37

Table Count = 27

Index Count = 23

Indexes Used = 3

Indexes NOT Used = 20

Index Monitor Report



Ask me for these scripts (Dhotka@Earthlink.net).

Eliminating unused indexes is batch cycle tuning. Indexes and table structures have 5 Oracle operations applied to each DML:
Lock acquired
Before image journalled in rollback segment
Actual change applied
After image journalled to archive log
Lock released

…for each object associated with the DML. If the index is not being used, then it should not be there as it is being maintained by Oracle.

*** True Story *** A shop in Denver had 98 tables and over 1100 indexes! I tripped on index monitoring and when I left 3 days later, Oracle had not used 100 indexes yet.

*** Tip *** The programmer should use this technique when testing an application and build scripts that create/drop indexes before month end/quarter end/year end processing.

Summary:
Index monitoring is a useful tool to eliminate unused indexes that will decrease batch cycle times and decrease insert/update times.
My SQL Performance Tuning class has a very interesting half-day lecture on indexes, how they are created, how Oracle uses them, when they are not used and why, and how 10g+ tablespace options has an adverse affect on index utilization.