Oracle Tips and Tricks — David Fitzjarrell

December 17, 2008

It’s Moving Day

Filed under: General — dfitzjarrell @ 21:33

This seems to be a typical series of events when things aren’t going quite as nicely as originally planned:

“The time comes when someone is, well, unhappy with the performace of the database. For troubleshooting such complaints what tools are available? The most common, and least intrusive, is Statspack and it’s fairly easy to install; simply execute the spcreate.sql script, found in $ORACLE_HOME/rdbms/admin, as SYS and, presuming the installation is successful you’re ready to go. You’ll need to set a password and both the default and temporary tablespaces for this user (PERFSTAT), but you’re prompted for that information by the script (the log file is created after the password has been submitted, so that display is not included here):


Choose the Default tablespace for the PERFSTAT user
--------------------------------------------------- 
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE           
------------------------------ --------- ----------------------------           
EXAMPLE                        PERMANENT                                        
STATDATA                       PERMANENT                                        
SYSAUX                         PERMANENT *                                      
TOOLS                          PERMANENT                                        
USERS                          PERMANENT                                        

Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: tools

Using tablespace TOOLS as PERFSTAT default tablespace.                          


Choose the Temporary tablespace for the PERFSTAT user
----------------------------------------------------- 
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE             
------------------------------ --------- --------------------------             
TEMP                           TEMPORARY *                                      

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: 

Using tablespace TEMP as PERFSTAT temporary tablespace.                         


... Creating PERFSTAT user


... Installing required packages


... Creating views


... Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.

Notice that the TOOLS tablespace was chosen for PERFSTAT to use; any tablespace CAN be used for the PERFSTAT user, but I prefer to keep its objects out of the SYSAUX tablespace (the default set by the script). (But, you don’t need to use the TOOLS tablespace; more on that in a bit.)

Tables, sequences, synonyms and packages are created to enable you to gather statistics, instance-wide, with a simple command:

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

SQL>

Two or more snapshots are required to generate a report, and it’s recommended that a 15-minute interval elapse between snaps. Once you have sufficient snapshots generating a report is easier than falling off of a log (so to speak):

SQL> @?/rdbms/admin/spreport

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1198289520 ORCL                1 orcl



Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 1198289520        1 ORCL         orcl         BVL-44B85C84
                                               4D

Using 1198289520 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
orcl         ORCL                 1 17 Dec 2008 14:10     5
                                  2 17 Dec 2008 14:24     5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

Select the snaps to compare, choose a file name if you don’t want the default, and, voila! You have your report.”

So you’ve installed Statspack and used it quite a bit, and all of these statistics are stored somewhere (the PERFSTAT schema resides in the default tablespace you assigned to the PERFSTAT user, and in this case it’s TOOLS). But, what if you want to use a different tablespace for the PERFSTAT tables? What if you selected a tablespace you thought was unused by others but discover later the error of your ways? Or, gee whiz, what if you think a change of scenery for the PERFSTAT tables/indexes might make them happier and perform better? The task is fairly simple, really; provide the PERFSTAT user access to the new tablespace you want to use [I usually create a tablespace solely for PERFSTAT to use (STATDATA) so granting an unlimited quota on it doesn’t create problems later], then move the tables and indexes to that new tablespace:

SQL> connect / as sysdba
Connected.
SQL> alter user perfstat quota unlimited on statdata;

User altered.

SQL> connect perfstat
Password:
Connected.
SQL>

Then generate scripts to move the tables and rebuild the indexes and execute them as PERFSTAT. A sample index rebuild script is shown:


alter index STATS$IDLE_EVENT_PK rebuild tablespace statdata;
alter index STATS$INTERCONNECT_PINGS_PK rebuild tablespace statdata;
alter index STATS$MEMORY_RESIZE_OPS_PK rebuild tablespace statdata;
alter index STATS$MEMORY_DYNAMIC_COMPS_PK rebuild tablespace statdata;
alter index STATS$MEMORY_TARGET_ADVICE_PK rebuild tablespace statdata;
alter index STATS$IOSTAT_FUNCTION_PK rebuild tablespace statdata;
alter index STATS$IOSTAT_FUNCTION_NAME_PK rebuild tablespace statdata;
alter index STATS$DYNAMIC_REM_STATS_PK rebuild tablespace statdata;
alter index STATS$MUTEX_SLEEP_PK rebuild tablespace statdata;
alter index STATS$STREAMS_POOL_ADVICE_PK rebuild tablespace statdata;
alter index STATS$SGA_TARGET_ADVICE_PK rebuild tablespace statdata;
alter index STATS$PROCESS_MEMORY_ROLLUP_PK rebuild tablespace statdata;
alter index STATS$$PROCESS_ROLLUP_PK rebuild tablespace statdata;
alter index STATS$OSSTAT_PK rebuild tablespace statdata;
alter index STATS$OSSSTATNAME_PK rebuild tablespace statdata;
alter index STATS$RULE_SET_PK rebuild tablespace statdata;
alter index STATS$BUFFERED_SUBSCRIBERS_PK rebuild tablespace statdata;
alter index STATS$BUFFERED_QUEUES_PK rebuild tablespace statdata;
alter index STATS$PROPAGATION_RECEIVER_PK rebuild tablespace statdata;
alter index STATS$PROPAGATION_SENDER_PK rebuild tablespace statdata;
alter index STATS$STREAMS_APPLY_SUM_PK rebuild tablespace statdata;
alter index STATS$STREAMS_CAPTURE_PK rebuild tablespace statdata;
alter index STATS$SESS_TIME_MODEL_PK rebuild tablespace statdata;
alter index STATS$SYS_TIME_MODEL_PK rebuild tablespace statdata;
alter index STATS$TIME_MODEL_STATNAME_PK rebuild tablespace statdata;
alter index STATS$EVENT_HISTOGRAM_PK rebuild tablespace statdata;
alter index STATS$FILE_HISTOGRAM_PK rebuild tablespace statdata;
alter index STATS$THREAD_PK rebuild tablespace statdata;
alter index STATS$JAVA_POOL_ADVICE_PK rebuild tablespace statdata;
alter index STATS$PGA_TARGET_ADVICE_PK rebuild tablespace statdata;
alter index STATS$SQL_WORKAREA_HIST_PK rebuild tablespace statdata;
alter index STATS$SHARED_POOL_ADVICE_PK rebuild tablespace statdata;
alter index STATS$STATSPACK_PARAMETER_PK rebuild tablespace statdata;
alter index STATS$INSTANCE_RECOVERY_PK rebuild tablespace statdata;
alter index STATS$PARAMETER_PK rebuild tablespace statdata;
alter index STATS$SQL_PGASTAT_PK rebuild tablespace statdata;
alter index STATS$SEG_STAT_OBJ_PK rebuild tablespace statdata;
alter index STATS$SEG_STAT_PK rebuild tablespace statdata;
alter index STATS$SQL_PLAN_PK rebuild tablespace statdata;
alter index STATS$SQL_PLAN_USAGE_PK rebuild tablespace statdata;
alter index STATS$SQL_PLAN_USAGE_HV rebuild tablespace statdata;
alter index STATS$UNDOSTAT_PK rebuild tablespace statdata;
alter index STATS$INST_CACHE_TRANSFER_PK rebuild tablespace statdata;
alter index STATS$CURRENT_BLOCK_SERVER_PK rebuild tablespace statdata;
alter index STATS$CR_BLOCK_SERVER_PK rebuild tablespace statdata;
alter index STATS$DLM_MISC_PK rebuild tablespace statdata;
alter index STATS$RESOURCE_LIMIT_PK rebuild tablespace statdata;
alter index STATS$SQL_STATISTICS_PK rebuild tablespace statdata;
alter index STATS$SQLTEXT_PK rebuild tablespace statdata;
alter index STATS$SQL_SUMMARY_PK rebuild tablespace statdata;
alter index STATS$ENQUEUE_STATISTICS_PK rebuild tablespace statdata;
alter index STATS$WAITSTAT_PK rebuild tablespace statdata;
alter index STATS$SESSION_EVENT_PK rebuild tablespace statdata;
alter index STATS$SYSTEM_EVENT_PK rebuild tablespace statdata;
alter index STATS$SESSTAT_PK rebuild tablespace statdata;
alter index STATS$SYSSTAT_PK rebuild tablespace statdata;
alter index STATS$SGASTAT_U rebuild tablespace statdata;
alter index STATS$SGA_PK rebuild tablespace statdata;
alter index STATS$ROWCACHE_SUMMARY_PK rebuild tablespace statdata;
alter index STATS$ROLLSTAT_PK rebuild tablespace statdata;
alter index STATS$BUFFER_POOL_STATS_PK rebuild tablespace statdata;
alter index STATS$LIBRARYCACHE_PK rebuild tablespace statdata;
alter index STATS$LATCH_MISSES_SUMMARY_PK rebuild tablespace statdata;
alter index STATS$LATCH_PARENT_PK rebuild tablespace statdata;
alter index STATS$LATCH_CHILDREN_PK rebuild tablespace statdata;
alter index STATS$LATCH_PK rebuild tablespace statdata;
alter index STATS$TEMPSTATXS_PK rebuild tablespace statdata;
alter index STATS$FILESTATXS_PK rebuild tablespace statdata;
alter index STATS$DB_CACHE_ADVICE_PK rebuild tablespace statdata;
alter index STATS$SNAPSHOT_PK rebuild tablespace statdata;
alter index STATS$LEVEL_DESCRIPTION_PK rebuild tablespace statdata;
alter index STATS$DATABASE_INSTANCE_PK rebuild tablespace statdata;

Generating such scripts is also a fairly simple task — let SQL write your SQL for you [remember to connect as PERFSTAT before you run such scripts]:

select 'alter table '||table_name||' move tablespace <tablespacename>;'
from user_tables
where tablespace_name is not null;

and

select 'alter index '||index_name||' rebuild tablespace <tablespacename>;'
from user_indexes;

Spool the output from each to files and run the resulting scripts; make certain you have enough space in the destination tablespace for the objects else you won’t move all of your tables/indexes and you’ll need to generate new scripts to finish the tasks. Presuming all goes well the tables and indexes will be relocated to the desired tablespace and Statspack will remain in working order.

So, it isn’t really a problem to move the Statspack tables to another location, as long as you’re careful and plan ahead.

And this move doesn’t require a U-Haul.

Advertisements

December 10, 2008

We Have Reservations

Filed under: General — dfitzjarrell @ 19:55

A question was posed to google.com recently which stated:

“can we have a row named final in oracle table”

To answer that (presuming it was column, and not row, intended in the question) one must make a trip to V$RESERVED_WORDS:

SQL> desc v$reserved_words
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 KEYWORD                                            VARCHAR2(30)
 LENGTH                                             NUMBER
 RESERVED                                           VARCHAR2(1)
 RES_TYPE                                           VARCHAR2(1)
 RES_ATTR                                           VARCHAR2(1)
 RES_SEMI                                           VARCHAR2(1)
 DUPLICATE                                          VARCHAR2(1)
 
SQL>

The two columns of interest are KEYWORD and RESERVED. Let’s look for ‘FINAL’ in the view and see whether or not it’s reserved:

SQL> select keyword, reserved
  2  from v$reserved_words
  3  where keyword like 'F%'
  4  order by 1;
 
KEYWORD                        R
------------------------------ -
FACT                           N
FAILED                         N
FAILED_LOGIN_ATTEMPTS          N
FAILGROUP                      N
FALSE                          N
FAST                           N
FBTSCAN                        N
FIC_CIV                        N
FIC_PIV                        N
FILE                           N
FILTER                         N
 
KEYWORD                        R
------------------------------ -
FINAL                          N
FINE                           N
FINISH                         N
FIRST                          N
FIRST_ROWS                     N
FLAGGER                        N
FLASHBACK                      N
FLOAT                          Y
FLOB                           N
FLUSH                          N
FOLLOWING                      N
 
KEYWORD                        R
------------------------------ -
FOR                            Y
FORCE                          N
FORCE_XML_QUERY_REWRITE        N
FOREIGN                        N
FREELIST                       N
FREELISTS                      N
FREEPOOLS                      N
FRESH                          N
FROM                           Y
FULL                           N
FUNCTION                       N
 
KEYWORD                        R
------------------------------ -
FUNCTIONS                      N
 
34 rows selected.
 
SQL>

It’s in the list of reserved words, but it’s not actually reserved, meaning you CAN use it if you REALLY REALLY want to, but it’s recommended that you don’t. I wouldn’t use it. But, that’s the way I think.

What other words shouldn’t you use for column names/table names/view names/constraint names/sequence names/type names/…? Simply look at the list provided in, yes, V$RESERVED_WORDS. There are 1142 of them in 10gR2, 1733 in 11gR1; they’re listed for good reason, in my opinion, and the list should be heeded. And, yes, many of the words are not actually reserved (as indicated by the N in RESERVED) but it’s still a really good idea to not use them as, some day, Oracle may decide to actually reserve them and then your scripts won’t run:

SQL> create table from(
  2  final varchar2(400)
  3  );
create table from(
             *
ERROR at line 1:
ORA-00903: invalid table name

SQL>

And that would be a shame.

Reservations, anyone?

Blog at WordPress.com.