Index Rebuild Tips...why???

This is an answer to an email I thought I'd share...the question is doing index reorgs to fix poor clustering factor...

partial email: "Dear Dan
I very much enjoyed your explain plan/indexing seminar earlier this year.

I am now in a situation where I need to apply some of this for real. I have run your INDEX_INFO.sql and found that half of our indexes are 'Fair' or even 'Poor'. On another slide, you recommend regular Index Rebuilds, even weekly in case of 'lots of DML'.

There are many places on the internet where Index Rebuilding is called a myth. They say it does not improve the clustering factor. Our DBAs chime in on that.

Can you point me to an final answer? "

As I discussed...clustering factor is not a 'fixable' thing...it is the relationship between the leaf blocks and how many table blocks they point to.  It is TOTALLY dependent on the order of the table and the index key...so...you rebuild the table so it is in a certain order...you will fix the intended index but what about the other indexes on that table?  IF one or two indexes had a good CF...they won't now!  So...CF is strictly the order of the table vs the indexing key...no myth...just the way it works.


...by the way...the Index_Info.sql script is yours for asking :)  It shows the clustering factor...which Oracle uses to help decide to use this index on a 'range scan' execution plan operation...download it today from www.DanHotka.com .

Dan Says:
I STRONGLY push index rebuilds to fix the index block splits on DML...using a low PCTFREE loads the index leaf blocks up...so...when you RANGE SCAN operations on them...you do fewer physical I/O.  No myth here...you do fewer physical I/O...and things run a bit faster...you rebuild indexes because you WANT to use a low pct free so you do fewer physical I/O operations when using the index...and you rebuild because with a low PCTFREE...you will get a lot of leaf block splits (when there isn't room for the entry...Oracle splits the leaf pointers across 2 leaf blocks...giving you 2 half full leaf blocks...), which causes more physical I/O when range scanning...more DML...more splits...so...you do reorgs to keep performance at its peak.

This is no myth :)  Doing fewer physical I/O operations improves SQL performance.  Hope this helps.

Large Exports out of APEX

Having problems creating large export files out of APEX?  Try these tips...

The question was APEX 4.0 would only create a 60mb .csv file...

Dan,

A lot of factors here. My best guess would be the web server, but it depends on how it is hosted.

I know we would hang our CSV generation if we used interactive reports due to size, but had a lot to do with our DB. It would create a session that would run for quite a while and then get lost by the web server.

We ended up doing a process that does a straight dump. My question would be, does the 60MB file correspond with a "time to generate". There may be something else going on.

Regards,
Tim

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.