OracleTom Kyte points out a new Oracle Q&A site oraqa.com. The site is young, but very active and covers many of the topics of interest to Oracle DBAs and developers.

database, database administration, database development, dba, oracle

OracleWhen you execute a query in an Oracle database, Oracle has to decide how to retrieve that data. That’s where the query optimizer steps in. The query optimizer makes decisions like which indexes to use, weather to perform a full table scan etc. based on the tables, columns, calculations and joins in a query. Oracle can do this quite efficiently with the cost-based optimizer, but it is important that there be accurate statistics available for it to use.

A short history

Originally the query optimizer worked on a static set of around 20 rules. These rules would be applied regardless of the size and type of data in a table.

Oracle 7 introduced the cost-based optimizer which can make more intelligent optimization decisions. By analyzing pre-gathered statistics on database objects the cost-based optimizer estimates the “cost” of processing several possible execution plans. The cost-based optimizer then chooses the cheapest execution plan and the database executes the plan.

In Oracle Database 7 through 9i either the cost-based or rule-based optimizer could be used. The rule-based optimizer is no longer included in Oracle 10g.

How can I tell which optimizer mode I am using

If you are running Database 10g or later, you are using the cost-based optimizer.

If you are running Database 7 through 9i you should check the optimizer_mode parameter.

SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE

RULE means your database is using the old rule-based optimizer. The good news is you do not have to gather statistics on your data. The bad news is your queries probably aren’t running as well as they could.

CHOOSE was introduced as a stop-gap between rule- and cost-based optimizers. If there are no statistics available, the rule-based optimizer will be used; however if statistics are available the query optimizer will default to cost-based mode.

FIRST_ROWS or ALL_ROWS will force your database to use the cost-based optimizer regardless of statistics, so you’d better gather them.

How can I gather statistics for my index, table, schema, database, etc.?

The DBMS_STATS package is used to gather statistics for the cost based optimizer. Historically the ANALYZE command would perform similar operations

Here are a few popular examples. Of course you should always consult the documentation for your Oracle distribution before using a new command.

These can be run through SQL*Plus, but you will probably want to automate them for more active databases. There are more options that I have chosen to show here, but these should be a good start.

DBMS_STATS.GATHER_DATABASE_STATS

This will analyze statistics for your entire database. It is likely to take quite a while (hours) and generally should not be necessary, but if you want to analyze the whole database this will do it with one command.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'JEMMONS')

This will analyze statistics for everything owned by the user ‘JEMMONS’. It is important to put the username in single quotes and all capitol letters. You can exclude the parameter and parentheses to analyze the current user’s statistics.

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'JEMMONS', tabname => 'ENROLLMENT_DATA')

This will gather statistics for a specific table and all its indexes. This may be a good idea on tables which change drastically on a regular basis.

EXECUTE DBMS_STATS.GATHER_INDEX_STATS(ownname => 'JEMMONS', indname => 'ENROLLMENT_CRSE_NUMB')

This will gather statistics on a specific index. If there is a need to drop and rebuild an index you could use this to re-analyze the index after rebuild.

The GATHER AUTO option can and should be added to the commands above after initial analysis. This will cause only objects with missing stats or more than 10% changed since last analysis (via insert, update or delete) to be analyzed. The resulting command should look something like the following:

EXECUTE DBMS_STATS.GATHER_DATABASE_STATS(options => 'GATHER AUTO')

How often should I gather statistics?

This is a question I cannot answer. I have schemas which do not change often that I may analyze once a month, others that I will gather new statistics on once a day. The GATHER AUTO option should be used to automatically gather missing and stale statistics rather than re-analyzing everything; however, sometimes there may be an advantage to re-analyzing an entire schema or database.

oracle, database, database administration, dba, database tuning

OracleIn no particular order, here are some of the other folks blogging good stuff about Oracle. Most are more up on current topics than I am since I mostly write how-to stuff so I can find it when I need it.

Rittman.net – Mark Rittman’s Oracle Weblog is a fantastic resource with a focus on data warehousing and business intelligence. One of the best sites by an individual.

PeteFinnigan.com – Pete Finnigan has a lot of security related info. A great resource!

AskTom.Oracle.com – While Ask Tom is not really a blog, there is a wealth of information here on most Oracle topics.

OracleDoug.Blogspot.com – Doug has a bit more casual site and, like me, talks about tech and non-tech topics.

OracleToday.Blogspot.com – Not a frequent poster, but some great nuts-and-bolts SQL and PL/SQL stuff.

ThinkOracle.Blogspot.com – Another blog with a potpouri of Oracle topics.

TKyte.Blogspot.com – The Tom Kyte Blog. Tom works for Oracle (see AskTom.Oracle.com above) and some of his Oracle stuff and peripheral interests and opinions spill over into here.

More are out there. Feel free to comment with your favorites and also check out some of my favorite Oracle web resources

oracle, dba, database, database administrator, rdbms, database security, data warehouse

OracleSidney V. commented on my post Performing Math on Oracle Dates that he was looking for a way to calculate the last weekday of a month. After some research I concluded that Oracle does not provide a function for this, so I wrote one.

I started with the last_day Oracle function to find the last day of the month. I’m using December 2005 as an example as the last day of the month was a Saturday.

SELECT last_day(to_date('12/2005','MM/YYYY')) FROM dual;

LAST_DAY(
---------
31-DEC-05

This gives us the last day of the month regardless of the day of the week. Now we rewind from that 7 days by subtracting 7 from the date so we can look at the last week in detail.

SELECT last_day(to_date('12/2005','MM/YYYY')) - 7 FROM dual;

LAST_DAY(
---------
24-DEC-05

This lets us start 7 days before the end of the month. Now we can look at this last week in detail. To see the last Monday of the month we use the next_day function to go forward from the last_day() - 7.

SELECT
next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day
FROM dual;

DAY
---------
26-DEC-05

Using this we could find the last Monday, Tuesday, etc. of the month. In this case we want to find the last weekday (Monday through Friday) of the month. We can accomplish this with a UNION of several of these statements.

SELECT day
FROM (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual);

DAY
---------
26-DEC-05
27-DEC-05
28-DEC-05
29-DEC-05
30-DEC-05

So we know when the last Monday, Tuesday, Wednesday, Thursday, and Friday of this month was, but which was latest? For that we’ll apply the SQL MAX function to the results of the above union. To do this we put the union above in the FROM part of the query.

SELECT MAX (day)
FROM ((SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual));

MAX(DAY)
---------
30-DEC-05

Now we have the date of the last weekday of the month. The Oracle to_char function can be applied to this result to get the date in a different format.

SELECT to_char(MAX (day), 'DAY, MM/DD')
FROM ((SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual))
;

TO_CHAR(MAX(DAY)
----------------
FRIDAY , 12/30

Now let’s try this logic on another date to make sure it’s sound. We’ll try today (Jan, 6 2006).

SELECT to_char(MAX (day), 'DAY, MM/DD')
FROM (
(SELECT next_day((last_day(sysdate) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Friday') AS day FROM dual)
)
;

TO_CHAR(MAX(DAY)
----------------
TUESDAY , 01/31

So the logic works, but this would be a lot of typing if you wanted to use it, so let’s make it into a function so we can use it anywhere in the database.

CREATE OR REPLACE FUNCTION last_weekday(month_check DATE DEFAULT sysdate)
RETURN date
IS
last_weekday_date DATE;
BEGIN
SELECT MAX(day) INTO last_weekday_date
FROM ((SELECT next_day((last_day(month_check) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Friday') AS day FROM dual));
RETURN last_weekday_date;
END;
/

Note: If you want other users to be able to use this function you have to grant them execute on it. Creating a synonym for it may also be usefull.

Now let's test drive the function we created. We assigned a default of sysdate so if we call it without any parameters it should return the last weekday of the current month.

SELECT last_weekday FROM dual;

LAST_WEEKDAY_DATE
---------
31-JAN-06

If we pass this function a date as a parameter it will return the last weekday of that month.

SELECT last_weekday(to_date('12/2005','MM/YYYY')) FROM dual;

LAST_WEEK
---------
30-DEC-05

Thanks Sidney for the challenge!

UPDATE: Oraboy writes with this algorithm. His code is much shorter than mine and likely more efficient. Thanks Oraboy!

With date_v as
(select last_day(to_date('&MM-&DD-&yyyyy','MM-DD-YYYY')) mydate from dual)
select max(date_v.mydate-(i-1)) last_weekday
from date_v,(select level i from dual connect by level < =3) where to_char(date_v.mydate-(i-1),'Dy') not in ('Sat','Sun')

oracle, sql, dba, database administration, database development

OracleI have posted about the Oracle OFA (Optimal Flexible Architecture) and recent changes to OFA for Oracle Database 10g, but I wanted to go into a more practical application of these rules to act as a quick reference.

This is not intended to be a complete explanation of the OFA standard. For more complete information refer to the OFA whitepaper. The OFA Standard Recommendations below are taken directly from the OFA whitepaper.

OFA recommendations 9-11 pertain to very specific installation types I have chosen to exclude them. The examples below are based on a UNIX environment.

OFA Standard Recommendation 1: Name all mount points that will hold site specific data to match the pattern /pm where p is a string constant chosen not to misrepresent the contents of any mount point, and m is a unique fixed-length key that distinguishes one mount point from another.

Typical Application: Name operating system mount points with the convention /u01, /u02, /u03 etc.

Options: Anything can be used instead of the leading ‘u’, however be careful not to use something which could eventually misrepresent the future contents of the directory.

OFA Standard Recommendation 2:Name home directories matching the pattern /pm/h/u, where pm is a mount point name, h is selected from a small set of standard directory names, and u is the name of the owner of the directory.

Typical Application: The original OFA whitepaper states the oracle user home directory be placed in a directory like /u01/app/oracle.

Options: This rule is typically overlooked. The oracle user is usually given a home directory which matches other UNIX users, such as /export/home/oracle/ or /home/oracle, however the directory /u01/app/oracle (or similar) should be created for software installation (see recommendation 4). The recommended home directory (/u01/app/oracle) is typically referred to as $ORACLE_BASE.

OFA Standard Recommendation 3:Refer to explicit path names only in files designed specifically to store them, such as the UNIX /etc/passwd file and the Oracle oratab file; refer to group memberships only in /etc/group.

Typical Application: Whenever possible, refer to the oracle user’s home directory as ~oracle and use other environment variables such as $ORACLE_HOME and $ORACLE_BASE instead of full paths. As an example, you would typically use $ORACLE_BASE/admin instead of /u01/app/oracle/admin.

Options: ~oracle, $ORACLE_BASE and $ORACLE_HOME are typically used as environment variables. Other variables such as $ORACLE_ADMIN can be set and used in a similar fashion.

OFA Standard Recommendation 4: Store each version of Oracle Server distribution software in a directory matching the pattern h/product/v, where h is the login home directory of the Oracle software owner, and v represents the version of the software.

Typical Application: Oracle server software should be stored in a directory below the home ($ORACLE_BASE) in the format $ORACLE_BASE/product/9.2. 9.2 should be replaced with the version of Oracle software installed.

Options: This could be on any of the /u01 sequence of partitions.

NOTE: For 10g Oracle has added another level to allow multiple installs of the same version of software. The new recommendation is to install software in a directory in the format of $ORACLE_BASE/product/10.1/db_1.

OFA Standard Recommendation 5:For each database with db_name=d, store database administration files in the following
subdirectories of h/admin/d:
• adhoc — ad hoc SQL scripts for a given database
• adump — audit trail trace files
• arch — archived redo log files
• bdump — background process trace files
• cdump — core dump files
• create — programs used to create the database
• exp – database export files
• logbook — files recording the status and history of the database
• pfile — instance parameter files
• udump — user SQL trace files
where h is the Oracle software owner’s login home directory.

Typical Application: Again the $ORACLE_BASE directory should be used for the home directory, resulting in a path of $ORACLE_BASE/admin/adump

Options: Not all these directories are always used; however, if there are multiple DBAs I highly recommend the adhoc and logbook directories.

OFA Standard Recommendation 6:Name Oracle database files using the following patterns:
• /pm/q/d/control.ctl — control files
• /pm/q/d/redon.log — redo log files
• /pm/q/d/tn.dbf — data files
where pm is a mount point name, q is a string denoting the separation of Oracle data from all other files, d is the db_name OFA Standard • 21 Oracle System Performance Group Technical Paper, September 24, 1995 of the database, n is a distinguishing key that is fixed-length for a given file type, and t is an Oracle tablespace name. Never store any file other than a control, redo log, or data file associated with database d in /pm/q/d.

Typical Application: Control, redo, and data files are typically stored in one or more of the /u01 series of partitions in a subdirectory called oradata then in a folder matching the database name. The resulting path should resemble /u01/oradata/orcl.

Only control, redo, and data files should reside in this path and they should have the appropriate extensions (.ctl, .log or .dbf respectively).

Control and redo files should have a fixed length number to identify them, such as control01.ctl or redo04.log.

Data files should contain the tablespace name and a fixed length number resulting in the format system01.dbf.

Options: Many place the database name in these files. This is redundant since the database name is already in the path to these files and should be avoided as it complicates changing the database name.

OFA Standard Recommendation 7: Separate groups of segments with different lifespans, I/O request demands, and backup frequencies among different tablespaces. For each Oracle database, create the following special tablespaces in addition to those needed for applications segments:
• SYSTEM — data dictionary segments only
• TEMP — temporary segments only
• RBS — rollback segments only
• TOOLS — general-purpose tools only
• USERS — miscellaneous user segments

Typical Application: Here a segment is a piece of data in the database associated with a table, index, or other database object. Basically you should represent the tablespaces listed above and any needed for the appropriate application. Do not put application data in any of these tablespaces, but instead create one or more tablespaces for each application.

Options: TOOLS and USERS tablespaces may be omitted, however it is likely you will want them in the long run.

NOTE: For database 10g Oracle has added a new tablespace called SYSAUX which contains all non-essential system components.

OFA Standard Recommendation 8: Name tablespaces connotatively with eight or fewer characters.

Typical Application: The eight character limit is not necessary; however, tablespace names should be kept fairly short. More importantly tablespace names should be indicative of their contents. WEBCT_DATA or BANNER_INDEX_SMALL are acceptable tablespace names, but DATA or INDEX would not be.

Options: There is a lot of flexibility here. Using a consistent convention at your site is key.

oracle, sql, dba, database administration, database development, database security, database, oracle security

« Previous PageNext Page »