Mar 29 2009

Firefox Tweaks to Speed Up Page Load

Category: Browser,Firefox — ittichai @ 7:07 pm

I found this blog post: A handful of Firefox tweaks that will double your browser speed through one of tweets that came my way yesterday. You may find it useful if you use Firefox browser like I do  and have encountered slowness when loading pages. Is it my imagination or does my Firefox load a lot more quickly after the changes have been made? :-)

There are, however, some limitations in some of the changes which are not mentioned in above post. For example, the pipelining is not supported by all servers. Some servers may even function incorrectly if they receive pipelined requests.

All other Firefox tweaks can be found at Tweaking Preferences of MozillaZine.

Enjoy blazing speed! Fingers crossed.

Tags: ,

Mar 27 2009

ILovePLSQLAnd.net

Category: PL/SQL — ittichai @ 4:03 pm

Steven Feuerstein created this “I LOVE PL/SQL, AND…” web site at ILovePLSQLAnd.net with the intention to collect the list of gaps/issues PL/SQL (his passion) currently has. In his recent post, he met with the PL/SQL developers at Oracle headquarter, and received confirmation that they really listen to inputs from his web site.

Sharing the same passion for PL/SQL, I’d like to help spreading the word about this effort. Please visit ILovePLSQLAnd.net, and give your inputs  or vote on the enhancements you feel would benefit you the most.

The fine print here is that there is no gaurantee, but you never know; your small voice can make a big difference.

Tags: ,

Mar 26 2009

Command Line Scripts for Database Replay

Category: 11g,Database — ittichai @ 10:47 am

One of new exciting features of Oracle 11g is the Real Application Testing (RAT). The RAT has two solutions – Database Replay and SQL Performance Analyzer (SPA) to address two different issues. Both have the same concept (capture then replay), but are scoped differently. Database Replay applies at database-level workload for all activities (exclusion is possible), but SPA is more granular at a specific SQL statement or its set.

Oracle extends the capture capability to the earlier versions. Note that the replay capability can only be done on Oracle 11g or higher. The minimum requirement to make 9i and 10g capable of capturing is stated in the Metalink note 560977.1 – Real Application Testing Now Available for Earlier Releases.

In our case, we would like to use the Database Replay and we do have Oracle 9.2.0.8 on Solaris which is the minimum required database version for capture, so the only patch needed is one-off patch number 6973309 (for non-Windows system).

Note that after patch, you may need to run catwrr.sql to create needed tables, views and package for workload capture.

Oracle provides sample of the command line interface scripts for Database Replay. Search Metalink for note 742645.1 – Database Replay: Command Line Interface (CLI) usage examples/scripts. I find it very useful because it is categorized based on execution tasks in order. This collection of scripts gives more flexibility and control especially when you want to automate tasks or where there is no Enterprise Manager interface for database replay (in 9.2.0.8/10.2.0.2 and 10.2.0.3).

Seven scripts provided in the db_replay_cli.zip file are self-explanatory and customizable based on your environments.

The first two scripts are executed on the capture system (in this case is 9i), and the rest on the replay system (11g).

1_start_capture.sql – Set of commands to create capture directory, create capture filters and start workload capture

2_finish_capture.sql – Set of commands to stop workload capture and export AWR

3_prepare_replay.sql – Set of commands to initialize replay, re-map connections and install replay parameters

4_start_replay_client.sql – Set of OS commands to calibrate and start replay clients

5_start_replay.sql – Command to start replay

6_reports.sql – Set of commands to import AWR, input arguments/data for capture/replay/ASH/AWR/Compare Period reports and generate these reports. In this script you can find text of PL/SQL procedure, which help you create reports easily with minimal input.

x_cancel_replay.sql – Command to cancel wokload replay in progress.

Tags: , ,

Mar 15 2009

Pop-up when hovering over tabular column

Category: APEX — ittichai @ 1:44 pm

I just answered APEX forum on the pop-up when hovering over tabular column. It is a quick solution.

1. Create Javascript functions as follows. The description of each function is self-explanatory in its name.

<script language="JavaScript" type="text/javascript">
<!--
function findPosX(obj)  // Get full right offset
{
    var curleft = 0;
    if (obj.offsetParent) {
        while (obj.offsetParent) {
            curleft += obj.offsetLeft;
            obj = obj.offsetParent;
        }
    }
    else if (obj.x)
        curleft += obj.x;

    return curleft;
}

function findPosY(obj)  // Get full top offset
{
    var curtop = 0;
    if (obj.offsetParent) {
        while (obj.offsetParent) {
            curtop += obj.offsetTop;
            obj = obj.offsetParent;
        }
    }
    else if (obj.y)
        curtop += obj.y;

    return curtop;
}

function ShowPopup(hv_item)
{
    var t1;  // First part of Text formatting
    var t2;  // Latter part of Text formatting
    dp = document.getElementById("DisplayPopup");

    t1 = "<table><tr><td width=200 align='center' bgcolor='gray'><font color='white'>";
    t2 = "</font></td></tr>";

    dp.innerHTML = t1 + hv_item.value + t2;

    // Set position of hover-over popup
    dp.style.top = findPosY(hv_item) - 10;
    dp.style.left = findPosX(hv_item) + 20;

    // Set popup to visible
    dp.style.visibility = "Visible";
}

function HidePopup()
{
    dp = document.getElementById("DisplayPopup");
    dp.style.visibility = "Hidden";
}
//-->

</script>

<div ID="DisplayPopup" style="visibility:hidden; position:absolute;"></div>

Note that above includes a DIV tag named DisplayPopup which will be used to store the pop-up text.

You can personalize the pop-up in the ShowPopup function. This includes font’s color, background color, pop-up width as well as X-Y offset position.

2. Call ShowPopup and HidePopup by onMouseOver and onMouseOut Javascript events respectively from the Element Attributes of the column you want to have pop-up shown.

In my sample here, I added the following Javascript events to both Ename and Job columns.

onMouseOver="ShowPopup(this);" onMouseOut="HidePopup();"

Tags: , ,

Mar 09 2009

asmcmd cp command on the test with transportable tablespace

Category: Database — ittichai @ 7:37 am

As you all know, the transportable tablepsace (TTS) enables users to unplug a set of tablespaces from one database, and plug it into another database. It is very efficient to move bulk data because only metadata and data files are involved in file movement – this can be much faster than export/import. More detailed information can be found in Oracle document.

We’ve been using TTS in 10g for many scenarios especially in the database refresh. However, we’ve never used it in database with ASM. For pre-11g with ASM, RMAN or DBMS_FILE_TRANSFER has to be used to move data out of ASM into file system or vice versa. With new feature of asmcmd cp command in 11g, I’d like explore whether or not this could be successfully used to move data files in/out ASM on our 11.1.0.7 database for the TTS export/import purpose.

Notes below are straight-forward step-by-step for TTS configuration. However, when copying data files, I use asmcmd cp instead. In the initial run, I’ve encountered ORA-01200 error during import saying that the actual copied file size is smaller than the correct size. It took me awhile to discover that when copying a data file larger than 2GB, the file was actually shrunk! This was filed as a bug by Oracle support. I will talk more about this at the end of this post. So all samples below are done with files smaller than 2GB. I’ve tested with 100 MB and 1.1 GB, both cases are working fine.

Assumption

The test_user has tables in the TTSASMCP_TS tablespace. One of them is testtb.

SQL> conn testuser
SQL> select count(*) from testtb;
COUNT(*)
----------
1000

Prerequisite

1. Make sure that this tablespace is self-contained for transportable.

SQL> exec dbms_tts.transport_set_check(TS_LIST=>'TTSASMCP_TS', incl_constraints=>TRUE);
PL/SQL procedure successfully completed.

If there are multiple transportable tablespaces, they can be listed with comma-separated in TS_LIST.

2. View whether or not there is any violation.

SQL> select * from transport_set_violations;
no rows selected

Start Transportable Tablespace Export using expdp

1. Make tablespace read-only.

SQL> alter tablespace TTSASMCP_TS read only;
Tablespace altered.

2. Locate data files for this tablespace.

col tablespace_name format a10
col file_name format a50

SQL> select tablespace_name, file_name from dba_data_files
where tablespace_name = 'TTSASMCP_TS';

TABLESPACE   FILE_NAME
----------   --------------------------------------------------
TTSASMCP_TS  +PROD_DG1/ORA11PD/datafile/ttsasmcp_ts.314.680796267

3. Create a directory to be used for export data pump.

SQL> create directory expdir as '/opt/oracle/admin/ORA11PD/exp';
Directory created.

SQL> grant read, write on directory expdir to system;
Grant succeeded

4. Perform export data pump.

$ expdp system directory=expdir dumpfile=ttsasmcp.dmp transport_tablespaces=TTSASMCP_TS include=triggers,constraints,grant

Export: Release 11.1.0.7.0 - 64bit Production on Friday, 06 March, 2009 9:51:12

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=expdir dumpfile=ttsasmcp.dmp
transport_tablespaces=TTSASMCP_TS
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/opt/oracle/admin/ORA11PD/exp/ttsasmcp.dmp
******************************************************************************
Datafiles required for transportable tablespace TTSASMCP_TS:
+PROD_DG1/ORA11PD/datafile/ttsasmcp_ts.314.680796267
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:10:08

5. Once completed, move the export’s metadata file and data file over to target database.

ASMCMD> cp +PROD_DG1/ora11pd/datafile/TTSASMCP_TS.314.680796267 /tmp
copying +PROD_DG1/ora11pd/datafile/TTSASMCP_TS.314.680796267 -> /tmp/TTSASMCP_TS.314.680796267

$ scp /tmp/TTSASMCP_TS.314.680796267 serverB:/tmp
$ scp /opt/oracle/admin/ora11pd/exp/ttsasmcp.dmp serverB:/opt/oracle/admin/ora11dv/exp/

6. Put the original tablespace back to read write mode.

SQL> alter tablespace TTSASMCP_TS read write;
Tablespace altered.

In this case, since both of our systems are on the same platforms, there is no need for platform conversion. If you’re interested in, information about cross-platform transportable tablespace can be found here and here.

Start Transportable Tablespace Import

1. Create a needed directory object for import data pump.

SQL> create directory expdir as '/opt/oracle/admin/ora11dv/exp';
Directory created.

SQL> grant read, write on directory expdir to system;
Grant succeeded

2. Move the data file to the data file location under ASM.

ASMCMD> cp /tmp/TTSASMCP_TS.314.680796267 +DEV_DG1/ora11dv/datafile/TTSASMCP_TS
copying /tmp/TTSASMCP_TS.314.680796267 -> +DEV_DG1/ORA11DV/datafile/TTSASMCP_TS

However, since ASM will automatically append file/incarnation pair to ensure uniqueness, you will need to list to get a new data file name.

ASMCMD> ls -s +DEV_DG1/ora11dev/datafile/TT*
Block_Size  Blocks      Bytes      Space  Name
16384    6401  104873984  106954752  TTSASMCP_TS.271.680796929

Please note that the following syntaxes won’t work. This is very inconvenient because it is common way of typing cp syntax in Unix. Hopefully it will be fixed in the later release.

cp /tmp/TTSASMCP_TS.314.680796267 +DEV_DG1/ora11dv/datafile/

or

cp /tmp/TTSASMCP_TS.314.680796267 +DEV_DG1/ora11dv/datafile/TTSASMCP_TS.314.680796267

You will receive this error

ORA-15046: ASM file name ‘+DEV_DG1/ora11dv/datafile/TTSASMCP_TS.314.680796267′ is not in single-file creation form.

3. Rum impdp

$ impdp system directory=expdir dumpfile=ttsasmcp.dmp transport_datafiles='+DEV_DG1/ora11dv/datafile/TTSASMCP_TS.271.680796929'

Import: Release 11.1.0.7.0 - 64bit Production on Friday, 06 March, 2009 14:25:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=expdir dumpfile=ttsasmcp.dmp
transport_datafiles=+DEV_DG1/ORA11DV/datafile/TTSASMCP_TS.271.680796929
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:25:50

Validation

SQL> conn testuser
SQL> select count(*) from testtb;
COUNT(*)
----------
1000

A bug found with asmcmd cp command

Normally when copying file, you must get the same file size :-) Sample here is with 104 MB file.

ASMCMD> ls -s tts*
Block_Size  Blocks      Bytes      Space  Name
16384    6401  104873984  106954752  TTSASMCP_TS.314.680796267

ASMCMD> cp TTSASMCP_TS.314.680796267 TTSASMCP_TS.backup
copying +PROD_DG1/ORA11PD/datafile/TTSASMCP_TS.314.680796267 -> +PROD_DG1/ORA11PD/datafile/TTSASMCP_TS.backup

ASMCMD> ls -s tts*
Block_Size  Blocks      Bytes      Space  Name
16384    6401  104873984  106954752  TTSASMCP_TS.314.680796267
16384    6401  104873984  106954752  TTSASMCP_TS.backup.313.680798709

Same with 1.1 GB.

ASMCMD> ls -s npi*
Block_Size  Blocks       Bytes       Space  Name
16384   70401  1153449984  1155530752  NPI_DATA.298.677341631

ASMCMD> cp NPI_DATA.298.677341631 NPI_DATA.backup
copying +PROD_DG1/ORA11PD/datafile/NPI_DATA.298.677341631 -> +PROD_DG1/ORA11PD/datafile/NPI_DATA.backup

ASMCMD>  ls -s npi*
Block_Size  Blocks       Bytes       Space  Name
16384   70401  1153449984  1155530752  NPI_DATA.298.677341631
16384   70401  1153449984  1155530752  NPI_DATA.backup.315.680916403

But for a file  larger than 2GB (21 GB in this sample), as you can see, the file size is smaller.

ASMCMD> ls -s co*
Block_Size   Blocks        Bytes        Space  Name
16384  1327249  21745647616  21747466240  CO_DATA.276.673697925

ASMCMD> cp CO_DATA.276.673697925 CO_DATA.backup
copying +PROD_DG1/ORA11PD/datafile/CO_DATA.276.673697925 -> +PROD_DG1/ORA11PD/datafile/CO_DATA.backup

ASMCMD> ls -s co*
Block_Size   Blocks        Bytes        Space  Name
16384  1327249  21745647616  21747466240  CO_DATA.276.673697925
16384    16529    270811136    272629760  CO_DATA.backup.313.680798663

I was told by Oracle support that this is a bug (#7828187) : Asmcmd CP Command Can Not Copy Files Larger Than 2 GB. Currently there is no patch available yet. The recommended work-around is to either use RMAN or dbms_file_transfer.copy_file to copy file.

Even though I still can’t use the asmcmd cp for a larger file copy until it is fixed, it is still very useful for making a backup of smaller files such as spfile.

If anyone encounters the same issue I’ve had, please  feel free to share your experience.

Tags: , ,