Borndba

In one of our DB, a delete statement was hanging.

delete from adv_temp.recs_type_966
where 1 = 1
and exists (
  select  *
  from adv_master.RECS_TYPE_966_VIEW r
  where r.plot_fk in (-1,5419,5420)
  and r.plot_fk between 5419 and 5420
  and r.p019 = recs_type_966.p019
  and r.p027 = recs_type_966.p027
  and r.f001 = recs_type_966.f001
);

Subquery got a view. The session which is executing was hanging, interestingly it was hanging on ‘SQL*Net message from client’. To me it looked like a merging issue. So i recommeded to include rownum >0 to the subquery. But it was rejected as this is a vendor code and we don’t have any control on this.
Meanwhile, we tried various options like changing cursor_sharing=exact, increasing pga_aggregate_target etc., But it didn’t work out.

When i insert a rule hint to the subquery, it worked fine.

delete from adv_temp.recs_type_966
where 1 = 1
and exists (
  select /*+rule*/ *
  from adv_master.RECS_TYPE_966_VIEW r
  where r.plot_fk in (-1,5419,5420)
  and r.plot_fk between 5419 and 5420
  and r.p019 = recs_type_966.p019
  and r.p027 = recs_type_966.p027
  and r.f001 = recs_type_966.f001
);

But again this not acceptable, since we can’t change the code.

Luckily we got the alternate solutions. We introduced the underscore parameter “_unnest_subquery”=false. It worked great.

It’s a dynamic parameter and we set it as spfile=both;

The batch program went fine without any issues.

In one of our DB, we drop and create a table during batch program. This program runs every day. Today we got a call from the user that they are encountering error

 ORA-00955: name is already used by an existing object

This error message was very clear. We immedately replied back saying that check the code, as it is trying to recreate the object which is already in the DB.

But actually it turned out to be a more interesting issue.

The table in question has been droped from the DB. When we query the dba_tables/obj$, this objects was not present.

However, when we try to give

    select count(*) from table_name;

We got a surprising

ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [21], [21], [2335770], [], [], [], []

We were initially puzzled why this error is coming from. Then we fixed this issue by flushing the shared pool.

I got a mail from one of our onshore DBA to take a relook into a dead lock trace file. He also proposed, that it could be due to itl,unique constraints or fk. He told us that he checked for fk where indexes are missing and found none.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0007001a-001aeb6d        21      23     X             20      51           S
TX-0004000c-001ab9d1        20      51     X             21      23           S
session 23: DID 0001-0015-000000A7      session 51: DID 0001-0014-00000253
session 51: DID 0001-0014-00000253      session 23: DID 0001-0015-000000A7

Rows waited on:
Session 51: obj - rowid = 00008808 - AAAJ08AAKAAAAAAAAA
  (dictionary objn - 34824, file - 10, block - 0, slot - 0)
Session 23: obj - rowid = 00008808 - AAAJ08AALAAAAAAAAA
  (dictionary objn - 34824, file - 11, block - 0, slot - 0)

There is something totally wrong with the tracefile. objn number 34824 lead us to a different table. There is no relation between that table and the current dead lock table. Moreover the statement which is causing this issue is “Insert” statement.

This is what my response to the onshore DBA. It turn out to be that, there are some missing indexes on foreign key. He failed to notice that.

ITL waits:
As you said ITL will not come for insert statement. Unless insert turns to update in index we will not be getting ITL waits on insert. But i don’t think it could be the reason here. There is a chance that if the table has trigger and trigger has autonomous transaction we can see deadlocks. But the table XXX_ACCOUNTS doesn’t have any trigger. INITRANS for table is by default 2. Oracle documentation is wrong in this respect. Simple block dump will show that it will use 2 itl slots.

Unique constraints:
There are few unique constraints on this table. But i don’t think it is to do with unique space violation. If a deadlock caused due to unique violation then in the deadlock trace file we will be seeing some like below

Rows waited on:
Session 7: no row
Session 8: no row

UnIndexed Foreign Key:
My analysis pointing towards this. My scripts shows that following columns which has fk constraint is not indexed.

STAT TABLE_NAME                     COLUMNS              COLUMNS
---- ------------------------------ -------------------- --------------------
**** XXX_ACCOUNTS                SRC_SYS_CODE
**** XXX_ACCOUNTS                ACC_TYPE_CODE
**** XXX_ACCOUNTS                CCY_CODE
**** XXX_ACCOUNTS                INST_CLASS
**** XXX_ACCOUNTS                LINE_OF_BUSINESS

I am attaching the scripts which i have used. If we index these columns probably this error might goaway.

Note:
The dead lock trace file is misleading us . For instance from the below content the block 0 is waiting on file 10 or file 11. This is absurd. No file will have block number 0. Hence the Dictionary object number what they are referring is not true. So table FDL_XYZ_LOG waiting on this insert is incorrect. Behaviour such as this might came in ASSM tablespace if we use deferrable constraints. Though the tablespace is ASSM, we are not using any deferrable constraints. Definite candidate for the bug.

A schedule export backup on one of our dev DB has failed due to below ora 600

ORA-00600: internal error code, arguments: [qmtInit1], [], [], [], [], [], [], []

As per metalink note:351650.1, they defined this as a bug and workaround is to restart the server.

I tried flushing the shared pool. It didn’t help.

There were some invalid objects, tried compiling but still didn’t help.

From the associated trace file of ora-600, this error was coming on the statement

Current SQL statement for this session:
SELECT URL, LOCAL, STRIPPED_VAL      FROM SYS.KU$_XMLSCHEMA_VIEW      WHERE OWNE
R_NAME = :OWNER

Though this object is valid, i tried recompiling it.

This worked. Our full export started working with out any issues.

This error comes while exporting cluster definitions. I was seeing some wait events like xdb initialization in the event@v$session.

   We have an requirement where we have to send a monthly report on space usage and other details to management. Our shops has more than 600 DB. It's very difficult to get into each DB and generate these report. Luckily for us we use 10g OEM grid. So we have developed a script which uses owa_sylk (refer asktom) to generate the excel sheet. I am giving some of the code which we use.

Size of all the DB:
          This script gives hostname,db wise details. The size will be equilent to sum(bytes) from dba_extents.

declare
    output utl_file.file_type;
    lv_target Varchar2(30) := 'ALL';
    lv_file_name varchar2(30) ;
begin

    lv_file_name := lv_target||'_DB_Size.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
    owa_sylk.show(
        p_file => output,
        p_query => 'select target_name DB_NAME,host_name Host,sum(trunc(tablespace_used_size/1024/1024/1024,2))size_GB'||
 '  from MGMT$DB_TABLESPACES group by host_name,target_name order by target_name ',
        p_sum_column =>
                 owa_sylk.owaSylkArray( 'N', 'N', 'Y'),
        p_show_grid => 'NO' ,
        p_user_heading => 'Size of ALL the DB as on '||sysdate);
    utl_file.fclose( output );
end;

Space usage of Arch location:
   This script generate space usage. This script may not work for all the environment, the pre-condition to it is, arch mountpoint should have "arch" keyword.
You need to create a table called RPT_ARCH_SIZES.

declare
    output utl_file.file_type;
    lv_host Varchar2(30) ;
    lv_file_name varchar2(30) ;
begin

    delete rpt_arch_sizes;

    Insert into rpt_arch_sizes select target_name,
     decode(instr(substr(mountpoint,instr(mountpoint,'/',-1)+1),'arch'),0,Upper(substr(mountpoint,instr(mountpoint,'/',-1)+1)),substr((substr(mountpoint,instr(mountpoint,'/',2)+1)),1,Instr(substr(mountpoint,instr(mountpoint,'/',2)+1),'/',1)-1)) database_name,
              mountpoint,
       trunc((sizeb/1024/1024/1024),2) Total_Size_GB ,
       trunc((usedb/1024/1024/1024),2)  Used_Space_GB,
       trunc((freeb/1024/1024/1024),2) Available_GB,
                            trunc((trunc((usedb/1024/1024/1024),2)/trunc                        ((sizeb/1024/1024/1024),2))*100,2) perc_Used
    from MGMT$STORAGE_REPORT_LOCALFS b
    where mountpoint like '%arch%';

    commit;

    lv_file_name := 'All_Storage_arch.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );

    owa_sylk.show(
        p_file => output,
      p_query => 'select target_name,upper(database_name) db_name,mountpoint,Total_Size_GB ,Used_Space_GB, Available_GB,perc_free Perc_used  '||
 ' from RPT_ARCH_SIZES order by perc_free desc ',

 p_show_grid => 'NO' ,

 p_user_heading => ' ARCHIEVELOG STORAGE REPORT OF ALL THE HOST as on '||sysdate);

 utl_file.fclose( output );

end;

Backup information of all the DB:
     The below script will give backup information. But in our environment, for some db the status and other columns are null. Need to look into that.

declare
    output utl_file.file_type;
    lv_target Varchar2(30) := 'All';
    lv_file_name varchar2(30) ;
begin

    lv_file_name := lv_target||'_Backup.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
    owa_sylk.show(
        p_file => output,
        p_query => 'select host,Database_name,status,end_time,output_device_type '||
 ' from MGMT$HA_BACKUP order by end_time desc',
   p_show_grid => 'NO' ,
        p_user_heading => 'BACKUP REPORT Of all DB as on '||sysdate);

    utl_file.fclose( output );
end;

DB Features:
     This gives the list of DB features.
declare
    output utl_file.file_type;
    lv_target Varchar2(30) := 'oemrep';
    lv_file_name varchar2(30) ;
begin

    lv_file_name := lv_target||'_db_feature_info.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
    owa_sylk.show(
        p_file => output,
        p_query => 'select Name,Currently_used,Description  '||
   '  from  MGMT$DB_FEATUREUSAGE  '||
   'where database_name=:HOSTNAME ',
          p_parm_names =>
               owa_sylk.owaSylkArray( 'HOSTNAME'),
       p_parm_values =>
                 owa_sylk.owaSylkArray( lv_target ),
        p_show_grid => 'NO' ,
        p_user_heading => 'DB feature info of '||lv_target||' as on '||sysdate);
    utl_file.fclose( output );
end;

Host Storage:
     This script will give host storage.

declare
    output utl_file.file_type;
    lv_host Varchar2(30) := 'cas2s040';
    lv_file_name varchar2(30) ;
begin

    lv_file_name := lv_host||'_Storage.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
    owa_sylk.show(
        p_file => output,
        p_query => 'select mountpoint,trunc((sizeb/1024/1024),2) Total_Size_MB ,trunc((usedb/1024/1024),2)  Used_Space_MB,   trunc((sizeb/1024/1024),2) Available_MB '||
 ' from MGMT$STORAGE_REPORT_LOCALFS '||
 'where target_name=:HOSTNAME',
          p_parm_names =>
               owa_sylk.owaSylkArray( 'HOSTNAME'),
       p_parm_values =>
                 owa_sylk.owaSylkArray( lv_host ),
        p_sum_column =>
                 owa_sylk.owaSylkArray( 'N', 'Y', 'Y', 'Y'),
        p_show_grid => 'NO' ,
        p_user_heading => 'STORAGE REPORT Of '||lv_host||' as on '||sysdate);

    utl_file.fclose( output );
end;

Redo log size:
       This scripts gives the redo log file size for the inputted DB. Replace "oemrep" with your db name.

declare
    output utl_file.file_type;
    lv_target Varchar2(30) := 'oemrep';
    lv_file_name varchar2(30) ;
begin

    lv_file_name := lv_target||'_db_redo_info.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
    owa_sylk.show(
        p_file => output,
        p_query => 'select group_num,members,thread_num,file_name,trunc(logsize/1024/1024,2) Redo_Size_MB,status '||
   '  from  MGMT$DB_REDOLOGS  '||
   'where target_name=:HOSTNAME ',
          p_parm_names =>
               owa_sylk.owaSylkArray( 'HOSTNAME'),
       p_parm_values =>
                 owa_sylk.owaSylkArray( lv_target ),
        p_show_grid => 'NO' ,
        p_user_heading => 'DB redolog info of '||lv_target||' as on '||sysdate);
    utl_file.fclose( output );
end;

DB features:
      DB features used in database. Change "oemrep" with your db name.

declare
    output utl_file.file_type;
    lv_target Varchar2(30) := 'oemrep';
    lv_file_name varchar2(30) ;
begin

    lv_file_name := lv_target||'_db_feature_info.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
    owa_sylk.show(
        p_file => output,
        p_query => 'select Name,Currently_used,Description  '||
   '  from  MGMT$DB_FEATUREUSAGE  '||
   'where database_name=:HOSTNAME ',
          p_parm_names =>
               owa_sylk.owaSylkArray( 'HOSTNAME'),
       p_parm_values =>
                 owa_sylk.owaSylkArray( lv_target ),
        p_show_grid => 'NO' ,
        p_user_heading => 'DB feature info of '||lv_target||' as on '||sysdate);
    utl_file.fclose( output );
end;

Error Message:
     Error message encountered by the DB in last 3 months. Replace "oemrep" with your db name.

declare
    output utl_file.file_type;
    lv_target Varchar2(30) := 'oemrep';
    lv_file_name varchar2(30) ;
begin

    lv_file_name := lv_target||'_Err_hist.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
    owa_sylk.show(
        p_file => output,
        p_query => 'select collection_timestamp,Metric_name,Error_message '||
 ' from MGMT$METRIC_ERROR_HISTORY '||
 'where target_name=:DATABASE_NAME order by 1',
          p_parm_names =>
               owa_sylk.owaSylkArray( 'DATABASE_NAME'),
       p_parm_values =>
                 owa_sylk.owaSylkArray( lv_target ),
   p_show_grid => 'NO' ,
        p_user_heading => 'Error REPORT Of '||lv_target||' for last 3 months');

    utl_file.fclose( output );
end;

DB init parameters:
       This script will give init.ora parameters for DB. Change "oemrep" with your dbname.

declare
    output utl_file.file_type;
    lv_target Varchar2(30) := 'oemrep';
    lv_file_name varchar2(30) ;
begin

    lv_file_name := lv_target||'_db_param.slk';

    output := utl_file.fopen( 'UTL_FILE_DIR', lv_file_name, 'w', 32000 );
    owa_sylk.show(
        p_file => output,
        p_query => 'select Name,Value,isdefault  '||
   '  from  MGMT$DB_INIT_PARAMS '||
   'where target_name=:HOSTNAME ',
          p_parm_names =>
               owa_sylk.owaSylkArray( 'HOSTNAME'),
       p_parm_values =>
                 owa_sylk.owaSylkArray( lv_target ),
        p_show_grid => 'NO' ,
        p_user_heading => 'DB parameter info of '||lv_target||' as on '||sysdate);
    utl_file.fclose( output );
end;

When you want to return a table in a function, Ref cursor is preferred compared to Plsql table.
If you return a ref cursor, result will be send to the end user in chunk instead of sending full result set. So to the end user, it will appear to be faster.
Additionally, if I was to return an array -- I would have to fetch all of the rows, allocate
storage in plsql for them, copy the data there and then send that data back over the network in one
big chunk where the client would have to allocate storage to hold the whole thing in memory.  With
a ref cursor -- i send back a pointer (don't have the overhead of allocating the storage in plsql
and copying all of the data into it).  I send rows in chunks -- not all at once (10 rows at a time
by default -- you control the size).  I don't have to allocate memory to hold the entire thing in
the client -- just a slice of it.

A ref cursor is infintely more scalable then sending a data structure back and forth for these
reasons.

Transparent Data Encryption, this a new feature introduced in 10gR2 in encrypting the sensitive column. I read something interesting on it's impact on performance and storage. Thought let me document it.

Overhead of encrypting or decrypting will be 5%. If a encrypted column is indexed, in the index the value is stored as cipher text. On storage front, the impact is huge, for every encrypted column there is additional overhead of 20bytes due to integrity check. TDE will pad encrypted value to 16 bytes also. Incase if the non-encrypted column requires 10 bytes, when you encrypt it will additionally take 6 bytes. If salt is specified when defining the column, it will additionally consume 16 bytes. 

So altogether if a column is encrypted it will require 33 and 48bytes of additional storage per rows.

So, to get encryption enabled, need to sacrifies a lot of performance and storage front.

Earlier to 9iR2, if you change the _spin_count it will affect the spinning of all the latches. This affects the CPU resource in particular. From 9iR2, you can assign a latch to classes, and each class can have different _spin_count. Let us assume that library cache latch is having high SLEEPS and if you like to tune only this latch you can assign library cache latch to a class. There are totally 8 classes. The X$KSLLCLASS (Kernel service lock latch class) view contains information about these classes.

sys@10.2.0.2.0> select indx,spin,yield,waittime from x$ksllclass;

      INDX       SPIN      YIELD   WAITTIME
---------- ---------- ---------- ----------
         0      16000          0          1
         1      16000          0          1
         2      16000          0          1
         3      16000          0          1
         4      16000          0          1
         5      16000          0          1
         6      16000          0          1
         7      16000          0          1

8 rows selected.

From above output, note INDX=1 and the corresponding spin. We will be assigning this class to library cache and change the spin count to 20000 from 16000.

sys@10.2.0.2.0> select latch#,name from v$latchname where name='library cache';

    LATCH# NAME
---------- --------------------------------------------------
       214 library cache

Latch number of "library cache" is 214.

sys@10.2.0.2.0> alter system set "_latch_class_1"=20000 scope=spfile;

System altered.

Assigning class_1 with 20000.

sys@10.2.0.2.0> alter system set "_latch_classes"="214:1" scope=spfile;

System altered.

Assigning the latch to class 1.

sys@10.2.0.2.0>

sys@10.2.0.2.0> shutdown abort
ORACLE instance shut down.
sys@10.2.0.2.0> startup
ORACLE instance started.

Total System Global Area   71303168 bytes
Fixed Size                  1287836 bytes
Variable Size              62916964 bytes
Database Buffers            4194304 bytes
Redo Buffers                2904064 bytes
Database mounted.
Database opened.

Bouncing the instance to make the changes effective.

sys@10.2.0.2.0> select indx,spin,yield,waittime from x$ksllclass;

      INDX       SPIN      YIELD   WAITTIME
---------- ---------- ---------- ----------
         0      16000          0          1
         1      20000          0          1
         2      16000          0          1
         3      16000          0          1
         4      16000          0          1
         5      16000          0          1
         6      16000          0          1
         7      16000          0          1

8 rows selected.

Please note the INDX=1, now the spin count=20000

sys@10.2.0.2.0> select a.kslldnam, b.kslltnum, b.class_ksllt
  2  from   x$kslld a, x$ksllt b
  3  where  a.kslldadr = b.addr
  4  and    b.class_ksllt > 0;

KSLLDNAM                                             KSLLTNUM CLASS_KSLLT
-------------------------------------------------- ---------- -----------
process allocation                                          3           2
library cache                                             214           1

The above query gives the output of to which class number the latch is assigned.

Freelists is list of blocks which can be used by segment on shortage of space. When delete occurs and freespace within the block goes below pctused, this block will be put in freelist. The block which is added last will be released first. Like B3 is added to freelist, then after a while B1 gets added then the chain looks like.

FL: B3
FL: B1 ==> B3.
On shortage of space B1 will be released before B3.

Type of Freelists:
Txfl  ==>  Transaction Freelist
Prfl  ==>  Process Freelist
Sgfl  ==>   Segment Freelist
Msfl  ==>  Master Freelist
Infl  ==> Instance Freelist

Txfl:
Each segment will have in minimum 16 Txfl, it grows as needed. During a transaction if space is required then oracle will first look into the txfl. At the same time if it wants to release the space it will give it to txfl.
Prfl:
Freelist allocated to a process. A process will not scan prfl allocated to other process.
Sgfl:
Synonym for Prfl.
Msfl:
It is introduced in later part of V6. It is preallocated Prfl which all processes can access known as Master Freeist.
Infl:
No idea on this.

Please note the algorithm when a transaction faces space issue,
• Search the space in it’s own Txfl. If it exist it will use otherwise
• Search the space in Prfl, it it doesn’t find then
• Search the space in Msfl. If it finds one it will copy the chunk to Prfl and use it otherwise
• Search the commited Txfl. If it finds one it will copy the chunk to Msfl from there it will be copied to Prfl. If if doesn’t find one
• Checks whether it can bump the high water mark level. It will bump 5 blocks at a time or defined by _bump_high_water_mark parameter. The added values will be passed to Prfl and used. If not
• Goes to fet$ and allocate a segment. Even if there is no space
• Errors out with unable to extent tablespace error.

Also note index block will be put to freelist only if the block is completely empty.
Event 10044 is used for tracing Freelist Undo operation and event 10045 for tracing freelist update operation -ktsrsp,ktsunl

Type of Event:
There are 4 types of events.
1. Immediate Dump Events
2. Error Stack Event
3. Change Behavior Event
4. Process Event

Immediate Dump Events:
This event generate trace file immediately in the udump area. These parameters cannot be issued in Parameter file. Some of the event involved are Trace_Buffer_On/Off,Hanganalyze,Latches,Processstate,SystemState,Instantiationstate,Refresh_Os_Stats,Crossic,ContextArea,Headdump,shared_server_state,Drop_Segments library_cache,full_dumps, granulelist, dump_all_comp_granule_addrs, buffers.
Error Stack Events:
If you need to capture the processes throwing out ORA error, you can use this event. This is very useful in diagnosing 4031, 942, 4020, 1555 etc., errors.
Change Behavior Event:
You need to be very careful before setting this event. This event can change the behaviors of oracle kernel and may render your db unsupported if used without approval of oracle support. There are n-number of events fails under this category. These events _generally_ doesn’t have levels associated with it. To name few are 10512 (disabling rollback shrinking by smon), 10170 changes the costing algorithm etc.,
Process Trace Events:
This is less/no harm events. This event is helpful in diagnosing behaviors of the process. Some of the events are 10046, 10053, 10032, 10033 etc.,

If you set an event, event information is stored in the PGA. Since it is stored in the PGA which is private to a session, you can’t find out event set in the other sessions. In case if you want to find out the events set in the current session, you can use below block.

set serveroutput on
declare
  event_level number;
 begin
  for i in 10000..10999 loop
     sys.dbms_system.read_ev(i,event_level);
     if (event_level > 0) then
        dbms_output.put_line('Event '||to_char(i)||' set at level '||
                             to_char(event_level));
     end if;
  end loop;
 end;
/

Pctused,freelist and freelist groups has become obsolete with introduction of ASSM.
The Pctfree is still valid because it is not a space management tool. It still used to tackle row migration.
There are 6 different freeness statuses when a tablespace is declared with ASSM.
0 = unformatted, 1 = logically full, 2 = 0-25% free, 3 = 25-50% free, 4 = 50%-75% free, 5= 75-100% free.
Theoretically a block is considered 90% full (FS=2), if the free space is 0-25%. However if the pctfree is declared more than 10% and freeness falls below pctfree, that block is considered full. This is irrespective of how much the block is full. You can track the behaviors in Event 10612 and Event 10613.

Recently i ventured into MYSQL. I did a installation and replication setup of mysql. Though i don’t entirely understand each steps, i believe it gave me a good start to this db. SQLSERVER and DB2 always on the card.

Uninstalling Any Previous MySQL Installation
============================================

1. Check the release:

On the server as root user:

# rpm -qa | grep -i mysql

2. Remove software
# rpm -e libdbi-dbd-mysql
# rpm -e MySQL-python
# rpm -e mod_auth_mysql
# rpm -e php-mysql
# rpm -e mysql-bench
# rpm -e mysql-server
# rpm -e freeradius-mysql
# rpm -e mysql-devel
# rpm -e mysql perl-DBD-MySQL MyODBC qt-MySQL

3. Remove user: mysql

# userdel mysql

Create MySQL User Account:
==========================

# cd /usr/local
# groupadd mysql
# useradd -c "MySQL Software Owner" -g mysql mysql
# passwd mysql

INstall Binaries:
=================

1. cp /tmp/mysql-standard-4.1.22-pc-linux-gnu-i686.tar.gz /data/.
2. cd /data (/usr/local)
3. gunzip mysql-standard-4.1.22-pc-linux-gnu-i686.tar.gz
4. tar xvf mysql-standard-4.1.22-pc-linux-gnu-i686.tar
5. mv mysql-standard-4.1.22-pc-linux-gnu-i686 mysql_4.1.22
6 cd /usr/local
7 ln -s /data/mysql_4.1.22 mysql
6. cd mysql

As root user
============

1. cd /usr/local/mysql
2. scripts/mysql_install_db --user=mysql

3.
[root@Primary mysql]# pwd
/usr/local/mysql
[root@Primary mysql]# chown -R root .
[root@Primary mysql]# chown -R mysql data
[root@Primary mysql]# chgrp -R mysql .

4. cd /usr/local/mysql/bin
5. ./mysqld_safe --user=mysql & (STARTS DB)
6. ./mysqladmin ping
7. ./mysqladmin version
8 ./mysqladmin -u root shutdown

Set Passwd (NOT REQUIRED)
===========================
cd /usr/local/mysql
./bin/mysqladmin -u root password 'mysql123'
./bin/mysqladmin -u root -h Primary.shans.net password 'mysql123'

===

[root@Primary bin]# ./mysqladmin version
./mysqladmin Ver 8.41 Distrib 4.1.22, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.1.22-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 30 sec

Threads: 1 Questions: 2 Slow queries: 0 Opens: 11 Flush tables: 1
Open tables: 5 Queries per second avg: 0.067

./mysqladmin version
./mysqladmin Ver 8.41 Distrib 4.1.22, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.1.22-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 23 sec

Threads: 1 Questions: 2 Slow queries: 0 Opens: 11 Flush tables: 1
Open tables: 5 Queries per second avg: 0.087

====

REPLICATION SETUP:

================================================================================
PRIMARY
================================================================================

First Create Replication DB
===========================

mysql> create database repldb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| repldb |
| test |
+----------+
3 rows in set (0.00 sec)

Create Table
============

mysql> use repldb;

CREATE TABLE Test_Table
(Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Test_Name VARCHAR(30),
Test_Date DATETIME,
Test_Giver VARCHAR(30));

INSERT INTO Test_Table
(Test_ID, Test_Name, Test_Date, Test_Giver)
VALUES
(NULL, 'Test','2000-01-01','Pramod');

mysql> describe Test_Table;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| Test_ID | int(11) | | PRI | NULL | auto_increment |
| Test_Name | varchar(30) | YES | | NULL | |
| Test_Date | datetime | YES | | NULL | |
| Test_Giver | varchar(30) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

mysql> select * from repldb.Test_Table;
+---------+-----------+---------------------+------------+
| Test_ID | Test_Name | Test_Date | Test_Giver |
+---------+-----------+---------------------+------------+
| 1 | Test | 2000-01-01 00:00:00 | Pramod |
+---------+-----------+---------------------+------------+
1 row in set (0.00 sec)

INSERT INTO Test_Table
(Test_ID, Test_Name, Test_Date, Test_Giver)
VALUES
(NULL, 'Test','2007-01-10','Nallaiya');

CREATE USER
===========

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%'
IDENTIFIED BY 'repl123';

FLUSH PRIVILEGES;

mysql> select * from mysql.user;

mysql> select * from mysql.user where user='repl_user'G
*************************** 1. row ***************************
Host: %
User: repl_user
Password: *A9CF6959A2DAECF248BA652C29231C5B78B13FF7
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: Y
Repl_client_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
1 row in set (0.00 sec)

mysql>

Shutdown DB
========================

# cd /usr/local/mysql/bin
[root@Primary bin]# ./mysqladmin -u root shutdown
[root@Primary bin]# ps -ef | grep mysql
root 12951 31519 0 22:59 pts/0 00:00:00 grep mysql
[root@Primary bin]#

Edit COnfig FIle
========================
cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf
chown mysql:mysql /etc/my.cnf

Add/Edit below parameters:
-------------------------
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=repldb
server-id=1

chmod 777 /var/log/mysql

LOck Tables
============

mysql>use repldb;
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 79 | repldb | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Take Dump FIle
==============

./mysqldump -u root -- repldb > /tmp/repldb.sql

UNLOck Tables
=============
mysql>use repldb;
mysql>unlock tables;
mysql>quit;

================================================================================
SLAVE
================================================================================

Create DB
=========

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.01 sec)

mysql> create database repldb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| repldb |
| test |
+----------+
3 rows in set (0.00 sec)

SCP DUmp FIle From Primary
===========================
[root@Primary bin]# scp /tmp/repldb.sql Secondary:/tmp/.
root@Secondary.shans.net's password:
repldb.sql

DUmp file into DB
=================
# cd /usr/local/mysql/bin
#./mysql -u root repldb < /tmp/repldb.sql

SHutdown DB
============
# ./mysqladmin -u root shutdown

COpy my.cnf from Primary to /etc/my.cnf
=======================================

Make below changes:

server-id=2
master-host=192.168.13.118
master-user=repl_user
master-password=repl123
master-connect-retry=60
replicate-do-db=repldb

# chown mysql:mysql /etc/my.cnf

Start MYSQL
==========

#./mysql

mysql> slave stop;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.13.118'
-> , MASTER_USER='repl_user'
-> , MASTER_PASSWORD='repl123'
-> , MASTER_LOG_FILE='mysql-bin.000001'
-> , MASTER_LOG_POS=79;
Query OK, 0 rows affected (0.01 sec)

MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
MASTER_USER is the user we granted replication privileges on the master.
MASTER_PASSWORD is the password of MASTER_USER on the master.
MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

Recently we had a scenario, where we need to migrate a oracle 7 db to oracle 9i. We have decided to go ahead with exp/imp method. Size of the Oracle 7 is 8-10gb. Space available in this box is 2.5gb. So we can’t take a complete export, as the size of the dump file may go beyond. Even the compress export using mknod is crossing 2.5 gb. Also there is no filesize/volsize option in oracle7 export utility.
We have did something like this.

 cd /tmp/data
 rm exp.dmp
 mknod exp.dmp p       # mkfifo on certain Unix flavours
 split -b1024m < /tmp/data/exp.dmp &
 exp scott/tiger file=/tmp/data/exp.dmp

Export will create a file of 1024m each, which we have to transfer to oracle9i box and do the import like,

 cd /tmp/data
 rm exp.dmp
 mknod exp.dmp p
 cat xaa xab xac xad > /tmp/data/exp.dmp &
 imp scott/tiger file=/tmp/data/exp.dmp commit=y tables=tableX

From 8, we can use filesize to generate multiple files of specific size. But here we may have to give in advance the number of file names. But you can use the below trick.
“file=xyz%u.dmp”

If you want to retreive rows in some order use “ORDER BY”. Though “GROUP BY”, forcing index might seem to return in sorted order, but it is not often.
“GROUP BY” doesn’t do character sorting, it actually does Binary Sort. There are many factors like partitioning, table organisation, optimizer_mode, DOP, indexes etc., affects the order by rows retrieved.
I remember seeing a note in metalink, where in future, index may not be stored in sorted order. This may not seems to be likely. However, the rows retrieved using index scan may not be in sorted order. Atleast with current Index full scan and Range scan it is not true.

satheeshb@10.2.0.2.0> drop table t;

Table dropped.

satheeshb@10.2.0.2.0> create table t ( pk1 int,pk2 int, nk1 int not null,
nk2 int not null, primary key (pk1,pk2));

Table created.

satheeshb@10.2.0.2.0> create index tx on t(pk1,nk1,pk2,nk2);

Index created.

satheeshb@10.2.0.2.0> insert into t values (1,1,2,2);

1 row created.

satheeshb@10.2.0.2.0> insert into t values (2,1,1,1);

1 row created.

satheeshb@10.2.0.2.0> analyze table t compute statistics
  2  ;

Table analyzed.

satheeshb@10.2.0.2.0>  set autotrace on explain
satheeshb@10.2.0.2.0> select nk1, nk2, pk1, pk2, count(*) a_count
  2  from t
  3  group by nk1,nk2,pk1,pk2;

       NK1        NK2        PK1        PK2    A_COUNT
---------- ---------- ---------- ---------- ----------
         2          2          1          1          1
         1          1          2          1          1

Execution Plan
----------------------------------------------------------
Plan hash value: 2968033836

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     2 |    16 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|      |     2 |    16 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | TX   |     2 |    16 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

So if you want the rows to be retrieve, the only way is actually to use “ORDER BY”. You have to bear the cost of sorting anyway.

If you are using Standby redo log files, RFS is the process which receives the change information from primary and apply to standby redolog files.

Size of standby redo log should be same that of primary redolog. RFS gets information only from LGWR of primary. Standby redolog files works only when the transmitter is defined as LGWR.

MRP is the process which will apply the changes from standby redolog files to standby database.

If your not using standby redo log files, RFS is the process which will create the archivelog files and MRP will apply to standby.

Addendum 20th oct:
There is one question for which still i am trying find answer is what is the purpose of LGWR in physical standby. Since RFS will be receiving the files and MRP will be applying file. Need to know

Excluding few tables while exporting or importing can be done by changing definition of export related dictionary tables. In 10g, expdp/impdp has got the feature to do so. Changing the dictionary definition is not supported by oracle.

There will be a view called exu10tab in catexp.sql. This sql file will be available in $ORACLE_HOME/rdbms/admin folder. Search for the view name “exu10tab”. The definition of view will be like below,

CREATE OR REPLACE VIEW exu10tab AS
SELECT *
FROM sys.exu10tabs t$
WHERE t$.secondaryobj = 0 ;

If you want to exclude Table “T” from the export add a where condition like below,

CREATE OR REPLACE VIEW exu10tab AS
SELECT *
FROM sys.exu10tabs t$
WHERE t$.secondaryobj = 0
and NAME NOT IN (‘T’);

Once export is completed, revert make to older setting.

If you have in pre 10g version, you have similar table like exu9tab or exu8tab.

There is an unsupported procedure “Deletefile” in Dbms_Restore_Backup. This procedure can delete the OS files. Make sure you grant this package to right users. This will be one more security violation if used improperly.

c:scripts>dir d:oracleproduct10.2.0adminaryaaudump1.trc
 Volume in drive D has no label.
 Volume Serial Number is 8C01-73AF

 Directory of d:oracleproduct10.2.0adminaryaaudump

10/13/2006  10:29 AM            11,574 1.trc
               1 File(s)         11,574 bytes
               0 Dir(s)   4,653,568,000 bytes free

Then from sqlplus

sys@10.2.0.2.0> Begin
  2  dbms_backup_restore.deletefile('D:oracleproduct10.2.0
adminARYAAudump1.trc');
  3  end;
  4  /

PL/SQL procedure successfully completed.

C:Documents and SettingsAryaa>dir D:oracleproduct10.2.0adminARYAAudump1.trc
 Volume in drive D has no label.
 Volume Serial Number is 8C01-73AF

 Directory of D:oracleproduct10.2.0adminARYAAudump

File Not Found

There is an unsupported way to trace gathering statistics using dbms_stats.

Usage will be,

satheeshb@10.2.0.2.0> exec dbms_stats.set_param('trace','3166');

PL/SQL procedure successfully completed.

satheeshb@10.2.0.2.0> exec dbms_stats.gather_table_stats(user,'T3',
CASCADE=>TRUE);

PL/SQL procedure successfully completed.

This will generate a trace file in udump area. This trace file will clearly shows what happens behind the scene during dbms_stats.

Various levels of set_param,

1 = use dbms_output.put_line instead of writing into trace file
2 = enable dbms_stat trace only at session level
4 = trace table stats
8 = trace index stats
16 = trace column stats
32 = trace auto stats
64 = trace scaling
128 = dump backtrace on error
256 = dubious stats detection
512 = auto stats job
1024 = parallel execution tracing
2048 = print query before execution
4096 = partition prune tracing
8192 = trace stat differences

Therefore, TRACE 3166 = 2048 + 1024 + 64 + 16 + 8 + 4 + 2.

There is a shuttle difference when Nologging is used with Archivelog database. Nologging should be given in Table level NOT in Query level. This is to tell oracle that you are serious in setting the Nologging option.

Database is in Archivelog Mode. Table "T" in Logging mode. 

satheeshb@10.2.0.2.0> insert /*+append*/ into t select * from t3;

4772728 rows created.

Statistics
----------------------------------------------------------
       3726  recursive calls
      10150  db block gets
       8933  consistent gets
       7729  physical reads
   64240476  redo size
        658  bytes sent via SQL*Net to client
        573  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
    4772728  rows processed

Note: That quite a lot of redo generated.


satheeshb@10.2.0.2.0> commit;

Commit complete.

satheeshb@10.2.0.2.0> truncate table t;

Table truncated.

satheeshb@10.2.0.2.0> insert /*+append*/ into t nologging select * from t3;

4772728 rows created.

Statistics
----------------------------------------------------------
       4606  recursive calls
      10152  db block gets
       9078  consistent gets
       7745  physical reads
   64289508  redo size
        663  bytes sent via SQL*Net to client
        583  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         37  sorts (memory)
          0  sorts (disk)
    4772728  rows processed

Note: Though the nologging is given in query level there is no impact
on redo generation. This is because the db is in Archivelog mode.
If the db was running in NoArchivelog mode, the above insert
statement would have generated much lesser redo.

satheeshb@10.2.0.2.0> commit;

Commit complete.

satheeshb@10.2.0.2.0> truncate table t;

Table truncated.

Now let us convert the table “T” to Nologging. This is to ensure that some responsible person with “ALTER” privillege can take the decision of enabling Nologging in Archivelog DB.

satheeshb@10.2.0.2.0> alter table t nologging;

Table altered.

satheeshb@10.2.0.2.0>  insert /*+append*/ into t select * from t3;

4772728 rows created.

Statistics
----------------------------------------------------------
       4201  recursive calls
      10155  db block gets
       8991  consistent gets
       7729  physical reads
     241244  redo size
        662  bytes sent via SQL*Net to client
        574  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
    4772728  rows processed

Note: There is huge difference in the “Redo size” generation. This extra check is required, as Nologging in Archivelog will invalidate the backup.

When there is switches in Primary will there be switches in Standby also? This question was itching me for sometime. Not able to get these detail anywhere. Tried asking few expert in DG. But didn’t get any convincing answer. Luckily got the answer to this question in corner of Oracle Doc 10g. “Physical standby db doesn’t have redolog associated with it”.

It means changes will be applied to physical standby datafiles directly from
archived log files or standby redolog files. So no redo generation in managed recovery stage. But when the standby is opened in read-only mode, there might be redo generation which will get into redolog. These redo may be from dictionary changes ( Not sure delayed block cleanout come into play in standby). Like oracle keep increasing the SCN even in read-only or there could be other internal reasons.

But redolog is required for logical standby as the db will be opened for read/write.

Before any changes getting applied to standby from standby redo log files, it has to be archived. However if you have enabled real-time apply the changes will get applied to datafiles from standby redolog file itself and oracle doesn’t wait for standby redo log to get archived.

To enabled real-time apply,

in physical standby,
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT;

in logical standby,
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

If you want to know whether real-time apply is enabled query recovery_mode.V$ARCHIVE_DEST_STATUS.

But need to check how real-time apply will work. Whether real time apply is 10g feature?

In 10g, oracle allows us to bring the HWM down. Prerequest; table should be in ASSM and compatible should be =>10.

I am using Dbms_Space package to show the effect of Shrink command. Please refer to dbms_space.sql script below.

During shrink there are 2 phases.
1. Compact the space. This involves moving all the rows to blocks in beginning of the segments. This will be done using insert/delete. These insert/deletes will be done in several small unit transactions. So, there will be locks in rows/blocks during this phase, but it will be short lived and won’t affect the concurrent transactions. Also please note any dml trigger will not get fired by this insert/delete operations as it happens internally.

2. Bringing the HWM down. This requires exculusive locks. This phase affects the availability. But this will be for very short interval. As it requires basically updating some records in dictionary.

--dbms_space.sql

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&1', '&2', '&3', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
         dbms_output.put_line('FS1 Blocks       = '||v_fs1_blocks);
         dbms_output.put_line('FS2 Blocks       = '||v_fs2_blocks);
         dbms_output.put_line('FS3 Blocks       = '||v_fs3_blocks);
         dbms_output.put_line('FS4 Blocks       = '||v_fs4_blocks);
         dbms_output.put_line('Full Blocks       = '||v_full_blocks);
end;
/

satheeshb@10.2.0.2.0> @dbms_space.sql  "SATHEESHB" "T3" "TABLE"
FS1 Blocks       = 0
FS2 Blocks       = 45
FS3 Blocks       = 45
FS4 Blocks       = 295
Full Blocks       = 7739
PL/SQL procedure successfully completed.
Note:
FS1 => Blocks which are 0-25% free space
Fs2 => Blocks which are 25-50% free space
Fs3 => Blocks which are 50-75% free space
Fs4 => Blocks which are 75-99% free space
Full Blocks => Blocks which are 100% free space

satheeshb@10.2.0.2.0> alter table t3 shrink space compact;
alter table t3 shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

Note: You need to enable row movement for the Shrink command to work. 

satheeshb@10.2.0.2.0> alter table t3 enable row movement;
Table altered.

satheeshb@10.2.0.2.0>  alter table t3 shrink space compact;
Table altered.
Note: The above statement is phase 1 of shrink operation. These moves the data to beginning of the segment using simple insert/delete. 

satheeshb@10.2.0.2.0> @dbms_space  "SATHEESHB" "T3" "TABLE"
FS1 Blocks       = 2
FS2 Blocks       = 0
FS3 Blocks       = 0
FS4 Blocks       = 402
Full Blocks       = 7720
PL/SQL procedure successfully completed.

satheeshb@10.2.0.2.0> alter table t3 shrink space;

Table altered.

Note: This is the Second phase of shrink operation. This involves exclusive lock. Only this stage affects the availablity. This will be quick phase, as it as to adjust some dictionary information.
 

satheeshb@10.2.0.2.0> @dbms_space  "SATHEESHB" "T3" "TABLE"
FS1 Blocks       = 2
FS2 Blocks       = 0
FS3 Blocks       = 0
FS4 Blocks       = 0
Full Blocks       = 7720
PL/SQL procedure successfully completed.

Index rebuild seems to make sense in some extreme condition. There were many heated discussion on index rebuild in oracle community. Some say height should be considered before rebuild , some other says blevel should be considered.
The function “sys_op_lbid” gets number of rows in each leaf block of index. By seeing how the rows are distributed across the leaf blocks, one can judge whether index needs rebuilding/coalesce or not.

I am still not convinced that rebuilding index is ever required. As there will be spike in redo due to index-splic block. LBID in SYS_OP_LBID should be “leaf block id”. I will test this when i have free time to burn.

If you set Plsql_Optimize_Level =2, oracle can rewrite your code to give best output. If you want your package to have the advantage of POL, define it while compiling itself. Since oracle will reqwrite the code during compilation stage and not during execution stage. I have noticed some package taking long time to compile when set to POL=2. So be aware of this little trap.

satheeshb@10.2.0.2.0> show parameter plsql_optimize_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_optimize_level                 integer     2

satheeshb@10.2.0.2.0> declare
  2    lv number;
  3  begin
  4    lv:=0;
  5     for i in (select rownum r1 from t3) loop
  6          i.r1 := i.r1;
  7          lv := lv+1;
  8          exit when lv=200;
  9     end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

satheeshb@10.2.0.2.0> select rows_processed,fetches from v$sql
  2* where lower(sql_text)='select rownum r1 from t3';

ROWS_PROCESSED    FETCHES
-------------- ----------
           200          2


Note the number of fetches above.

satheeshb@10.2.0.2.0> alter system set plsql_optimize_level=1 scope=memory;

System altered.

satheeshb@10.2.0.2.0> declare
  2    lv number;
  3  begin
  4    lv:=0;
  5     for i in (select no r1 from t3) loop
  6          i.r1 := i.r1;
  7          lv := lv+1;
  8          exit when lv=200;
  9     end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

satheeshb@10.2.0.2.0> select rows_processed,fetches from v$sql
  2   where lower(sql_text)='select no r1 from t3';

ROWS_PROCESSED    FETCHES
-------------- ----------
           200        200

satheeshb@10.2.0.2.0> 

See the spike in number of fetches when the optimize level is changed.

When “Plsql_optimizer_level=2″ the number of fetch is TWO, however for Plsql_Optimizer_Level=1 the fetch is 200. One interesting point to note here is per fetch 100 rows. Not sure why is that magic number Hundred..

If the sysdate of OS is by mistake is not correct, can lead to lot of confusion in database involving financial transaction. One way to prevent that is using Fixed_date parameter.

satheeshb@10.2.0.2.0> alter session set nls_date_format='dd/mon/yyyy hh24:mi:ss';

Session altered.

satheeshb@10.2.0.2.0> select sysdate from dual;

SYSDATE
--------------------
07/oct/2006 04:02:15

satheeshb@10.2.0.2.0> alter system set fixed_date='2001-11-01' scope=memory;

System altered.

satheeshb@10.2.0.2.0> alter session set nls_date_format='dd/mon/yyyy hh24:mi:ss';

Session altered.

satheeshb@10.2.0.2.0> select sysdate from dual;

SYSDATE
--------------------
01/nov/2001 00:00:00

satheeshb@10.2.0.2.0> alter session set  fixed_date='2001-11-01' scope=memory;
alter session set  fixed_date='2001-11-01' scope=memory
                   *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

This rises lot of questions regarding security. So never give “ALTER SYSTEM” privs to non-dba users. However alter session will not change this parameter.
Neverever give “ALTER SYSTEM” privs to developer account. There are many other security violation involving “ALTER SYSTEM’.

Freelist will not have unused blocks.

Only used blocks, which falls below pctused will get into Transaction freelist. If oracle not able find the blocks below hwm or freelist, it will bump the HWM level by 1 block for 5 blocks and there after by greater of 5 blocks or “_bump_highwater_mark_count” blocks (which defaults to 0) times the number of process free lists plus 1 (for the master free list), up to the number of blocks remaining in the extent.

Below is a small demonstration of only used blocks getting into Transaction Freelist. You need to use ASSM, as MSSM doesnt have Freelists,Freelist groups and PctUsed.

satheeshb@10.2.0.2.0> drop table t purge;

Table dropped.

Elapsed: 00:00:01.10
satheeshb@10.2.0.2.0> create table t tablespace system as select * from all_objects;

Table created.

Elapsed: 00:00:05.18
satheeshb@10.2.0.2.0> analyze table t compute statistics;

Table analyzed.

Elapsed: 00:00:01.33
satheeshb@10.2.0.2.0> select num_freelist_blocks from user_tables where table_name='T';

NUM_FREELIST_BLOCKS
-------------------
                  0

Elapsed: 00:00:00.08
satheeshb@10.2.0.2.0> delete t ;

49302 rows deleted.

Elapsed: 00:00:16.78
satheeshb@10.2.0.2.0> analyze table t compute statistics;

Table analyzed.

Elapsed: 00:00:01.39
satheeshb@10.2.0.2.0> select num_freelist_blocks from user_tables where table_name='T';

NUM_FREELIST_BLOCKS
-------------------
                677

Elapsed: 00:00:00.02

There are 3 kind of freelists in oracle, Master/Process/Transactions Freelist. The freelist shows in Num_Freelists_blocks is “Transaction Freelists”. Transaction Freelist is the one which you define in Create/Alter table command. Unused blocks will not get into Transaction Freelist, however i believe they get into Master/Process Freelist. Need to test the uses of Master/Process freelist.

If a tablespace is having more than one datafile and extend of an object will be allocated in round robin fashion to each of the datafiles. However there are various factors which will affect this round robin fashion like how other objects in this tablespace grows, autoextend on or off of a datafiles, any space getting available in the existing datafiles etc.,

satheeshb@10.2.0.2.0> create tablespace s datafile 'c:1.dbf' size 1m,
  2  'c:2.dbf' size 1m,
  3  'c:3.dbf' size 1m,
  4  'c:4.dbf' size 1m,
  5  'c:5.dbf' size 1m
  6* extent management local uniform size 64k;

Tablespace created.

satheeshb@10.2.0.2.0> select file_id from dba_data_files where tablespace_name='S';

   FILE_ID
----------
         5
         6
         7
         8
         9

satheeshb@10.2.0.2.0> drop table s1;

Table dropped.

satheeshb@10.2.0.2.0> create table s1 ( no number )
  2  tablespace s
  3* storage (initial 1m );

Table created.

satheeshb@10.2.0.2.0> select file_id,extent_id from dba_extents
  2* where owner=user and segment_name='S1' order by extent_id
satheeshb@10.2.0.2.0> /

   FILE_ID  EXTENT_ID
---------- ----------
         7          0
         8          1
         9          2
         5          3
         6          4
         7          5
         8          6
         9          7
         5          8
         6          9
         7         10
         8         11
         9         12
         5         13
         6         14
         7         15

16 rows selected.

satheeshb@10.2.0.2.0> drop tablespace s including contents and datafiles;

Tablespace dropped.

     The same could be true for DMT and LMT with uniform extent. When tried with LMT with autoallocate, it gave a different behaviour. It will be based on extent size. That is once the 64k extent is over and followed by 128k extents, same patter will be followed for 128k extents as it was followed in 64k. But again number factors to change this behaviour.

satheeshb@10.2.0.2.0> select file_id,extent_id,bytes from dba_extents
  2  where owner=user and segment_name='S1' order by extent_id
  3  /

   FILE_ID  EXTENT_ID      BYTES
---------- ---------- ----------
         7          0      65536
         7          1      65536
         7          2      65536
         7          3      65536
         7          4      65536
         7          5      65536
         7          6      65536
         7          7      65536
         7          8      65536
         7          9      65536
         7         10      65536
         7         11      65536
         7         12      65536
         7         13      65536
         7         14      65536
         8         15      65536

16 rows selected.

The checkpoing command in Alter table drop column will be used If a instance crashes while dropping a column in big table.

satheeshb@10.2.0.2.0> alter table s1 drop column id checkpoint 10;

Table altered.

You are basically checkpointing every 10 rows. So that it gets to the datafiles. One thing which i am not clear is whether it is object level checkpoint or instance level checkpoint. It may not be object level checkpoint, since i guess it is introduced in 10g.

Alternatively, if you want to restart the drop column command after restart you can use,

satheeshb@10.2.0.2.0> alter table s1 drop columns continue;

Table altered.

Note the columnS. If you give column, oracle will try to drop the column “continue”

Addendum on 8th oct.
The checkpoint here is not a real checkpoint. It is something like commit. It will go to redolog files and may or maynot got datafiles.

Like MS-Sql if you want to autoincrement a column you can use sys_guid from 9iR2. But this will not be in number instead it will be in raw.

satheeshb@10.2.0.2.0> drop table s1;

Table dropped.

satheeshb@10.2.0.2.0> create table s1 ( id raw(6) default hextoraw(substr(sys_guid(),1,12)) primary key,
  2  Descrip varchar2(10));

Table created.

satheeshb@10.2.0.2.0> insert into s1(Descrip) values ('first row');

1 row created.

satheeshb@10.2.0.2.0> insert into s1(Descrip) values ('second row');

1 row created.

satheeshb@10.2.0.2.0> select * from s1;

ID                  DESCRIP
------------        ----------
8628D8F81FD2        first row
6D695F7F7547        second row

satheeshb@10.2.0.2.0> commit;

Commit complete.

Generating explain plan using autotrace or dbms_xplain may not be correct when bind variable peeking comes to play. There is an interesting way to get the correct plan without executing the statement. Please see the below command.

satheeshb@10.2.0.2.0>  declare
  2    num   number := 0;
  3    --
  4    cursor c1 is
  5    select  * from t where n = num;
  6    begin
  7      dbms_session.session_trace_enable(true,false);
  8      open c1;
  9      close c1;
 10
 11    end;
 12    /

PL/SQL procedure successfully completed.

From tkprof output, you get the correct plan even without executing the query.

SELECT  *
FROM
 T WHERE N = :B1 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.05          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=5 us)

When sizeable portion of table to be updated which is better Update or CTAS?

Using update,

SQL> update t1 set no=no*1.1;

5000000 rows updated.

Elapsed: 00:24:00.08

Statistics
----------------------------------------------------------
      19775  recursive calls
    9813469  db block gets
    5038061  consistent gets
      56546  physical reads
 2306478592  redo size
        674  bytes sent via SQL*Net to client
        554  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

SQL>
Please note the time taken and the redo size.

Same using CTAS using append,
SQL> insert /*+append*/ into t2 select no*1.1 from t1;

5000000 rows created.

Elapsed: 00:00:13.31

Statistics
----------------------------------------------------------
       4585  recursive calls
      10666  db block gets
       9836  consistent gets
       8031  physical reads
     255516  redo size
        668  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         24  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

SQL>

SQL> insert into t2 select no*1.1 from t1;

5000000 rows created.

Elapsed: 00:01:12.43

Statistics
----------------------------------------------------------
       4311  recursive calls
      95857  db block gets
      19944  consistent gets
       7999  physical reads
   87332300  redo size
        684  bytes sent via SQL*Net to client
        567  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

SQL>

If you want to get the explain plan output from v$ table you can use the below query.

Satheeshb> create or replace view dynamic_plan_table
  2  as
  3  select
  4   rawtohex(address) || '_' || child_number statement_id,
  5   sysdate timestamp, operation, options, object_node,
  6   object_owner, object_name, 0 object_instance,
  7   optimizer,  search_columns, id, parent_id, position,
  8   cost, cardinality, bytes, other_tag, partition_start,
  9   partition_stop, partition_id, other, distribution,
 10   cpu_cost, io_cost, temp_space, access_predicates,
 11   filter_predicates
 12   from v$sql_plan;

View created.

Now, you can query any plan from the database with a single query:

Satheeshb> select plan_table_output
  2    from TABLE( dbms_xplan.display
  3                ( 'dynamic_plan_table',
  4                  (select rawtohex(address)||'_'||child_number x
  5                     from v$sql
  6  where sql_text='select * from t t1 where object_id > 32000' ),
  7                  'serial' ) )
  8  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| Id  | Operation                   | Name|Rows| Bytes |Cst(%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT            |     |    |       |         |
|   1 |  TABLE ACCESS BY INDEX ROWID| T   |291 | 27936 | 25   (0)|
|*  2 |   INDEX RANGE SCAN          | T_PK|291 |       |  2   (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">32000)

13 rows selected.

If cpu costing is not enabled, optimizer costs both single block read and multiple block reads equally. This results in in-efficient path. Over the years CBO costing methods evolved in these ways,
Oracle 7, cost is calculated based on simple io request.
Oracle 8i, cbo accounted size and time of io request.
Oracle 9i, cbo accounted cpu costs,size and time of io request.
From 10g, cbo accounted caching,cpu costs,size and time of io request. But caching effect is not enabled by default in 10g. You need to enable “_cache_stats_monitor” to account caching by CBO.

SQL> drop table t1;

Table dropped.

Elapsed: 00:00:00.31

SQL> create table t1 as
  2  select
  3             trunc(sysdate-1) + rownum/1440  d1,
  4             rownum                                          n1,
  5             rpad('x',100)                                   padding
  6  from
  7             all_objects
  8  where
  9             rownum <=  3000
 10  ;

Table created.

Elapsed: 00:00:00.18

SQL> alter table t1
  2     add constraint  t1_pk primary key (d1,n1)
  3     using index (create index t1_pk on  t1(d1,n1))
  4  ;

Table altered.

Elapsed: 00:00:00.13

SQL> execute dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31

SQL> declare
  2    i binary_integer :=0;
  3    lv  varchar2(100);
  4  begin
  5    for i in 1..10000 loop
  6      select  /*+   index(t1) */
  7             padding
  8      into   lv
  9      from   t1
 10  where      d1 >= trunc(sysdate)
 11   and       n1 = 2800;
 12   --   i := i+1;
 13     end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.07

SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.00

SQL> begin
  2    dbms_stats.set_system_stats('CPUSPEED',500);
   3    dbms_stats.set_system_stats('SREADTIM',5.0);
  4     dbms_stats.set_system_stats('MREADTIM',30.0);
  5     dbms_stats.set_system_stats('MBRC',12);
  6  END;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

SQL> alter system flush shared_pool;

System altered.

SQL> declare
  2    i binary_integer :=0;
  3    lv varchar2(100);
  4  begin
  5    for i in 1..10000 loop
  6      select /*+   index(t1) */
  7             padding
  8       into  lv
  9      from   t1
 10  where      d1 >=  trunc(sysdate)
 11  and        n1 = 2800;
 12   --   i :=  i+1;
 13     end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.69
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.75

with cpu costing enabled,Predicate Information (identified by operation  id):
---------------------------------------------------
2 - access("T1"."D1">=TRUNC(SYSDATE@!) AND "T1"."N1"=2800)
        filter("T1"."N1"=2800 AND "T1"."D1">=TRUNC(SYSDATE@!))

With cpu costing disabled,
Predicate Information (identified by operation  id):
---------------------------------------------------
2 - access("T1"."D1">=TRUNC(SYSDATE@!) AND  "T1"."N1"=2800)
       filter("T1"."D1">=TRUNC(SYSDATE@!) AND  "T1"."N1"=2800)
Note: cpu costing is off

Some interesting behaviour of Dual.

SQL> startup
ORACLE instance started.

Total System Global Area   71303168 bytes
Fixed Size                  1287836 bytes
Variable Size              62916964 bytes
Database Buffers            4194304 bytes
Redo Buffers                2904064 bytes
Database mounted.
Database opened.
SQL> select  * from dual;

D
-
X

SQL> alter database close;

Database altered.

SQL> select  * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
036B2174          0          1 X

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area   71303168 bytes
Fixed Size                  1287836 bytes
Variable Size              62916964 bytes
Database Buffers            4194304 bytes
Redo Buffers                2904064 bytes
Database mounted.
SQL> desc dual;
ERROR:
ORA-04043: object dual does not exist

SQL> select * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
036B2174          0          1 X

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> select 2*2 from dual ;

       2*2
----------
         4

1 row selected.

SQL> column 2*2 format 9
SQL>
SQL> select 2*2 from dual ;

2*2
---
  4

1 row selected.

SQL> column 2*2 format 5
SQL>
SQL> select 3 from dual ;

         3
----------
         3

1 row selected.

SQL> select 3 "2*2" from dual ;

2*2
---
  5

1 row selected.

SQL>

Also if you want to generate N-number of records using dual then use,

select rownum from dual connect by level <=100

RMAN backups all the blocks which are ever used, it means backups blocks above HWM of datafiles (Note it is datafile and not HWM of object).

1. I have created a tablespace and created a empty table. Then took a RMAN
tablespace level backup. The size was 96k
2. I have populated this table with 6million data and took RMAN tablespace level
backup. The size is 704mb.
3. I have deleted all the data, the backup size was 704mb.
4. Now i truncated the table to bring the HWM down, even this time the backup
size is 704mb.
5. Finally i droped the table, again this time the backup size is 704mb.

This tablespace has got only one object.

The reason is RMAN considers HWM of datafile and not objects. However this behaviour can be changed in 10gR2 using “Change tracking files”.

I have recently came across a interesting behaviour of optimizer_dynamic_sampling. If optimizer_dynamic_sampling <=3 and set in session or system level it is not getting effective. If the table is unanalyzed then it is getting sampled.

SQL> create table t1 (id1 number(3), id2 number(3));

Table created

SQL> insert into t1 values (1,33);

1 row created.

SQL> insert into t1 values (1,33);

1 row created.

SQL> insert into t1 values (7,34);

1 row created.

SQL> insert into t1 values (7,34);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ARYAA','T1');

PL/SQL procedure successfully completed.

SQL> alter session set optimizer_dynamic_sampling=2;

Session altered.

SQL> select * from t1 where id1=1 and id2=33;

ID1        ID2
---------- ----------
1          33
1          33

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID1"=1 AND "ID2"=33)

Statistics
----------------------------------------------------------
355       recursive calls
  0       db block gets
 65       consistent gets
  1       physical reads
  0       redo size
498       bytes sent via SQL*Net to client
380       bytes received via SQL*Net from client
  2       SQL*Net roundtrips to/from client
 11       sorts (memory)
  0       sorts (disk)

2 rows processed

SQL> select /*+ dynamic_sampling(t1 2) */ * from t1 where id1=1 and id2=33;

ID1        ID2
---------- ----------
1          33
1          33

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 12 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID1"=1 AND "ID2"=33)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
700      recursive calls
  0      db block gets
118      consistent gets
  6      physical reads
  0      redo size
498      bytes sent via SQL*Net to client
380      bytes received via SQL*Net from client
  2      SQL*Net roundtrips to/from client
  8      sorts (memory)
  0      sorts (disk)

2 rows processed

SQL>

There is an interesting thread on Fast Object Ckpt in google groups. Extent Checkpoint is from 7.2, then it converted as tablespace checkpoint and now in 10.2 as segment Checkpoint. It can be enabled or disable by _db_fast_obj_ckpt =TRUE/FALSE. During Extent checking times (oracle 7.2), there were impacts on performance. Because checkpoint queues where not introduced in 7.2.

While doing export with direct=y sometime oracle does a segment checkpoint, then does direct path read. This behaviour can be seen in 10g.
http://tinyurl.com/pdnvr

There is an general belief that sort_area_size is session level parameter. But actually it is operational parameter. That means, if the sort_area_size=5m, in a session you might actually use 5m/25m/50m etc.

Assume you open three cursor in a session which does huge sort. Each cursor will hold 5m sort_area_size.

In 9i Work_area_size=AUTO is valid only for dedicated server. If you are shared server, this parameter is not effective, you have to manually set *area_size. This behaviour is changed in 10g.

When you change pctfree or pctused, the value will get effective only to new blocks. However if oracle ever changes old blocks, that time these values will be set to pctfree and pctused.
However for initrans, only new blocks will be affected. If you want the existing block to use the new initrans, we need to rebuild the object again.

This is tried in 10.2.0.2,

SQL> select state,count(*) from x$bh group by state;

     STATE   COUNT(*)

---------- ----------

         1        940

         0          6

SQL> alter system flush buffer_cache;

System altered.

SQL> select state,count(*) from x$bh group by state;

     STATE   COUNT(*)

---------- ----------

         1          3

         0        943

SQL>

The STATE column of all the blocks will be set to Zero. While the buffers head which are pinned will remain same.

There is no Flush buffer_cache in 9i, you need to set the event to do the same.

ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;

create table x ( no number ) initrans 2 maxtrans 2 pctfree 0;
begin
 for i in 1..734 loop
   insert into x values (i);
 end loop;
end;
/
commit;

exec dbms_stats.gather_table_stats(user,'X');

first session,
SQL> update x set no=no where no=1;

1 row updated.
--don't commit
second session,
SQL> update x set no=no where no=2;

1 row updated.
--don't commit

third session,
SQL> update x set no=no where no=3;

The third session will hang due to insufficient itl. In 10g it will be waiting on “enq: TX – allocate ITL entry”. In previous release it was confusion as ITL wait will get added “enqueue” wait.

initrans is initial number of transaction slot require, additional slots are defined by maxtrans.

High number of maxtrans will compete for space with data layer. Earlier version consumes 23 bytes and latest version consumes 24 bytes, if you want to find out size a slot for your version run,

select type_size from v$type_size where description='TRANSACTION VARIABLE HEADER'
/
TYPE_SIZE
----------
24

ITL is valid only for UPDATE and DELETE. Not for INSERT,if there is no itl slot in a block, oracle will put the record into the other blocks which are in the freelist. Therefore there will not be any ITL contention for INSERT.

But if a table has index and if INSERT transalates into UPDATE in the index block, you might get ITL contention if the index block itl slot is full. If there is ITL shortage on index block during insert, then there will be block split.

ITL will have 3 parts, Transaction Id(TID),Undo DBA (uid) and SCN. If Delayed clean out happens, SCN part will be only set during this phase.

Maxtrans is deprecated in 10.1

In direct mode of export, BUFFER parameter is not valid. Instead use RECORDLENGTH. If you don’t specify RECORDLENGTH in direct mode export then i _guess_ it will only take 1k. You can set upto 65535 for RECORDLENGTH.

There is an hidden parameter in export command, which will enable us to trace the export operation. Otherwise it will be difficult to get the sid of the export command and trace that session.

exp trace=y and other command.

Trace file will get generated in udump area. Use tkprof to format this trace file.