The idea is simple, testing of backup database itself with production workload and environment. There are cases when we do major changes in database, for example, changing system parameters, upgrading to newer versions or even adding new indexes. But how could we simulate the production environment in new born system so that we can sleep at night ensuring changes was not harmful!
Here, RAT comes into play. The basic idea is, capture workload from production system, replay it on new system and analyze impact and performance.
In 10g Release 2, I don't have whole RAT (only capture is available) - replay and performance analysis features are available only in 11g!
What I could do, create a SQL Tuning Set in production database for a specified duration (like workload capture) and then use this in new system to see how Tuning Advisor and Access Advisor suggest after the changes. New execution plans might be dramatically good or bad!
The step are -
1. Creating SQL Tuning Set at source/production db
BEGIN
dbms_sqltune.create_sqlset(sqlset_name => 'PROD-TS', sqlset_owner =>'SYSTEM');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name => 'CREATE_STS_PROD-TS',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE bf VARCHAR2(71);
begin bf := q''#UPPER(PARSING_SCHEMA_NAME) = ''PROD'' AND ELAPSED_TIME >= 300000.0 #'';
dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name=>''PROD-TS'', time_limit=>''600'',
repeat_interval=>''5'',
basic_filter=>bf, sqlset_owner=>''SYSTEM''); end;',
enabled => TRUE);
END;
/
2. Packing Tuning Set to an exportable format
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => 'TAB_PROD_TS');
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'PROD-TS',
staging_table_name => 'TAB_PROD_TS');
END;
/
3. Export from source db
# expdp system directory=dump_dir dumpfile=prod-ts-20091220.dmp tables=TAB_PROD_TS
4. Import into target db
impdp system directory=dump_dir dumpfile=prod-ts-20091220.dmp
5. Unpack the SQL Tuning Set
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => 'PROD-TS',
sqlset_owner => '%',
replace => TRUE,
staging_table_name => 'TAB_PROD_TS');
END;
/
6. Run SQL Tuning Advisor and/or SQL Access Advisor to analyze query performance
We could easily do the analysis using EM on test database. This does not exactly fulfill the RAT things, still, something is always better than nothing!
***
Dec 18, 2009
The Rule of Three - When to Confront Someone!
I was reading this nice article of Harvard Business School. It was a very good one to read and seemed interesting for personnel issues! Preserving here :-)
When to Confront Someone - The Rule of Three
By Peter Bregman
"Should I bother to have the conversation with her? What do you think?" Mike*, a marketing director, was telling me about Anne, one of his employees, who had done a few things to frustrate him. She arrived late to a meeting with a client. Not that late - only ten minutes — still, it didn't look good.
Then, a few days later, she was supposed to email him some information by 4pm and didn't do it until 6pm. I know, he told me, not a big deal. He didn't really need it until the next morning. Still.
And then this morning he received a voicemail from her saying she wouldn't be able to make the conference call they had planned with a colleague in another office. The call was an internal matter. Nothing time sensitive. But she didn't give him a reason and that bothered Mike.
"None of these things are a big deal," Mike told me, "And she's a great employee. But I'm annoyed. Should I say something or shrug it off?"
I have a rule for dealing with these types of situations — times when I'm not sure if it's worth raising an issue. I need a rule because it's often hard to know if something's a big enough deal to address until it's too late and then, well, it's too late. It's already gotten out of hand. On the other hand if I jump on every single issue the first time it comes up then, well, I'll be out of hand.
The first time someone does something that makes me feel uncomfortable, I notice it. The second time, I acknowledge that the first time was not an isolated event or an accident but a potential pattern and I begin to observe more closely and plan my response. The third time? The third time I always speak to the person about it. I call it my rule of three.
If someone makes a joke about my consulting rates — maybe they say something like, "well, with rates like those, it's a good thing you add value (chuckle, chuckle)." I might laugh along with them but I notice my discomfort. The second time I smile but don't laugh. The third time I say "This is the third time you've joked about my rates — I know it's a joke but I also wonder if you feel like they exceed my value. If so, I'd like to talk about it with you."
If you come late to a meeting once, I notice. Three times? I bring it up.
The first time you demonstrate a lack of teamwork, I notice. The third time? I need to better understand your commitment to the group.
I always say some version of, "I've noticed something three times and I want to discuss it with you." That way we both know it's a trend.
Is it OK to talk to them about it the first time? Sure. You don't have to wait. But everyone slips once or twice. Just don't let it go three times without having a conversation. Three is a good rule of thumb because it allows you to act with confidence that it's not all in your head. And in these situations, confidence is critical to your ability to speak with authority.
"So," Mike said to me after I explained my rule of three, "are you saying I should talk to her about it?"
"I can't help but notice you've asked me that same question three times," I said.
"What do you think?"
[*Names have been changed.]
***
When to Confront Someone - The Rule of Three
By Peter Bregman
"Should I bother to have the conversation with her? What do you think?" Mike*, a marketing director, was telling me about Anne, one of his employees, who had done a few things to frustrate him. She arrived late to a meeting with a client. Not that late - only ten minutes — still, it didn't look good.
Then, a few days later, she was supposed to email him some information by 4pm and didn't do it until 6pm. I know, he told me, not a big deal. He didn't really need it until the next morning. Still.
And then this morning he received a voicemail from her saying she wouldn't be able to make the conference call they had planned with a colleague in another office. The call was an internal matter. Nothing time sensitive. But she didn't give him a reason and that bothered Mike.
"None of these things are a big deal," Mike told me, "And she's a great employee. But I'm annoyed. Should I say something or shrug it off?"
I have a rule for dealing with these types of situations — times when I'm not sure if it's worth raising an issue. I need a rule because it's often hard to know if something's a big enough deal to address until it's too late and then, well, it's too late. It's already gotten out of hand. On the other hand if I jump on every single issue the first time it comes up then, well, I'll be out of hand.
The first time someone does something that makes me feel uncomfortable, I notice it. The second time, I acknowledge that the first time was not an isolated event or an accident but a potential pattern and I begin to observe more closely and plan my response. The third time? The third time I always speak to the person about it. I call it my rule of three.
If someone makes a joke about my consulting rates — maybe they say something like, "well, with rates like those, it's a good thing you add value (chuckle, chuckle)." I might laugh along with them but I notice my discomfort. The second time I smile but don't laugh. The third time I say "This is the third time you've joked about my rates — I know it's a joke but I also wonder if you feel like they exceed my value. If so, I'd like to talk about it with you."
If you come late to a meeting once, I notice. Three times? I bring it up.
The first time you demonstrate a lack of teamwork, I notice. The third time? I need to better understand your commitment to the group.
I always say some version of, "I've noticed something three times and I want to discuss it with you." That way we both know it's a trend.
Is it OK to talk to them about it the first time? Sure. You don't have to wait. But everyone slips once or twice. Just don't let it go three times without having a conversation. Three is a good rule of thumb because it allows you to act with confidence that it's not all in your head. And in these situations, confidence is critical to your ability to speak with authority.
"So," Mike said to me after I explained my rule of three, "are you saying I should talk to her about it?"
"I can't help but notice you've asked me that same question three times," I said.
"What do you think?"
[*Names have been changed.]
***
Dec 8, 2009
Indexing referenced column of a table
Most of the databases, foreign Keys needs to be indexed otherwise deleting records from parent table will lock down child table(s). Sometimes this may also lead to a extensive locking and even deadlock situation!
Here is a convenient script I needed once to index all referenced columns of a table - say MEDICAL_CONTACT.
set pagesize 0
set linesize 200
SPOOL medical-contact-fk-index.sql
SELECT 'CREATE INDEX ' || a.table_name || '_' || c.column_name || '_I ON ' || a.table_name || '(' || c.column_name
|| ') ONLINE TABLESPACE INDX01_16K COMPUTE STATISTICS;'
FROM user_constraints a, user_constraints b, user_cons_columns c
WHERE a.r_constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND a.constraint_type='R'
AND b.table_name='MEDICAL_CONTACT';
SPOOL OFF;
Might needs to modify medical-contact-fk-index.sql to change index names if cross max length for names.
--
Here is a convenient script I needed once to index all referenced columns of a table - say MEDICAL_CONTACT.
set pagesize 0
set linesize 200
SPOOL medical-contact-fk-index.sql
SELECT 'CREATE INDEX ' || a.table_name || '_' || c.column_name || '_I ON ' || a.table_name || '(' || c.column_name
|| ') ONLINE TABLESPACE INDX01_16K COMPUTE STATISTICS;'
FROM user_constraints a, user_constraints b, user_cons_columns c
WHERE a.r_constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND a.constraint_type='R'
AND b.table_name='MEDICAL_CONTACT';
SPOOL OFF;
Might needs to modify medical-contact-fk-index.sql to change index names if cross max length for names.
--
Dec 7, 2009
Linking Oracle features
To turn Real Application Testing (RAT) ON, I need to re-link oracle executables with "rat_on" option/flag.
Here are the quick steps -
1. Shutdown Instance
2. Shutdown Listener and EM
3. Run the make file in $ORACLE_HOME/rdbms/lib/
# ls -l *.mk
-rw-r--r-- 1 oracle oinstall 107073 Nov 7 2008 env_rdbms.mk
-rw-r--r-- 1 oracle oinstall 27158 Nov 7 2008 ins_rdbms.mk
# /usr/ccs/bin/make -f ins_rdbms.mk rat_on ioracle
The make file will save the existing version in $ORACLE_HOME/bin appending a 'O' at the end (could be deleted later if there is a disk space pressure) -
# ls -l oracle*
-rwsr-s--x 1 oracle oinstall 129141416 Dec 1 06:05 oracle
-rwsr-s--x 1 oracle oinstall 129141416 Nov 17 04:05 oracleO
4. Start Instance and other services.
That's it!
There is another thing with 'relink' that might be needed in the following circumstances -
* New installation failed in relinking phase
* After OS upgrade
* Changes made to the OS system libraries
* Oracle patch applied with explicit relink instruction
"relink" command is available from $ORACLE_HOME/bin and "relink" takes the following parameters values -
all, oracle, network, client, client_sharedlib, interMedia, precomp, utilities, oemagent
From Oracle -
http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/cnfg_prd.htm#CHDECBHC
This is a sophisticated one, it could damage Oracle installations if not done properly!
***
Here are the quick steps -
1. Shutdown Instance
2. Shutdown Listener and EM
3. Run the make file in $ORACLE_HOME/rdbms/lib/
# ls -l *.mk
-rw-r--r-- 1 oracle oinstall 107073 Nov 7 2008 env_rdbms.mk
-rw-r--r-- 1 oracle oinstall 27158 Nov 7 2008 ins_rdbms.mk
# /usr/ccs/bin/make -f ins_rdbms.mk rat_on ioracle
The make file will save the existing version in $ORACLE_HOME/bin appending a 'O' at the end (could be deleted later if there is a disk space pressure) -
# ls -l oracle*
-rwsr-s--x 1 oracle oinstall 129141416 Dec 1 06:05 oracle
-rwsr-s--x 1 oracle oinstall 129141416 Nov 17 04:05 oracleO
4. Start Instance and other services.
That's it!
There is another thing with 'relink' that might be needed in the following circumstances -
* New installation failed in relinking phase
* After OS upgrade
* Changes made to the OS system libraries
* Oracle patch applied with explicit relink instruction
"relink" command is available from $ORACLE_HOME/bin and "relink" takes the following parameters values -
all, oracle, network, client, client_sharedlib, interMedia, precomp, utilities, oemagent
From Oracle -
http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/cnfg_prd.htm#CHDECBHC
This is a sophisticated one, it could damage Oracle installations if not done properly!
***
Dec 4, 2009
Software Design Principles
These are some of my all time favorite design principles I learned my earlier days as a developer. Still, whenever it comes in front of my eyes, I don't miss reading!
Don't Let Architecture Astronauts Scare You
http://www.joelonsoftware.com/articles/fog0000000018.html
KISS principle
http://en.wikipedia.org/wiki/K.I.S.S.
You ain't gonna need it
http://en.wikipedia.org/wiki/YAGNI
***
Don't Let Architecture Astronauts Scare You
http://www.joelonsoftware.com/articles/fog0000000018.html
KISS principle
http://en.wikipedia.org/wiki/K.I.S.S.
You ain't gonna need it
http://en.wikipedia.org/wiki/YAGNI
***
Dec 3, 2009
Evaluating Jailer - database subsetting tool
We need a small subset of production data out of hundreds of gigabytes - i,e. the records for a test user across all the tables where there is a FK reference. Having the small set of data for all tables, we could build a database instance on developers' machine quickly. The referential integrity was the main concern. It seemed that Jailer handles things well. It's an open source platform independent tool.
What is Jailer?
Jailer is a tool for database subsetting and sampling, schema browsing, and rendering. It exports consistent, referentially intact row-sets from relational databases. It removes obsolete data without violating integrity.
But the problem I faced - we don't have a common column (for example, user_id or agency_id for which I wanted to pull records from all tables) directly referenced. Even, it's slow with some 100 tables where we have huge amount of data.
However, I love the tool. It's a good one for database analysis and data sampling for small schema!
For details -
http://jailer.sourceforge.net/
***
What is Jailer?
Jailer is a tool for database subsetting and sampling, schema browsing, and rendering. It exports consistent, referentially intact row-sets from relational databases. It removes obsolete data without violating integrity.
But the problem I faced - we don't have a common column (for example, user_id or agency_id for which I wanted to pull records from all tables) directly referenced. Even, it's slow with some 100 tables where we have huge amount of data.
However, I love the tool. It's a good one for database analysis and data sampling for small schema!
For details -
http://jailer.sourceforge.net/
***
Nov 10, 2009
Proactive Tuning - finding out the full table scans
Let's do some query/index tuning where we could have some problems due to data growth over time. I am looking for those big tables for which queries are doing full scans. I love the following small but useful sqls to find out those areas of tuning -
SET LONG 1000000
COL object_name FORMAT A25
COL object_name FORMAT A25
COL sql_fulltext FORMAT A50
SELECT sp.object_name, dtab.num_rows, sa.sql_fulltext, sa.executions
FROM v$sql_plan sp
JOIN dba_tables dtab ON (dtab.table_name = sp.object_name)
JOIN v$sqlarea sa ON (sa.address = sp.address AND sa.hash_value =sp.hash_value)
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY sa.executions DESC;
Another faster sql version could be -
SELECT sp.object_name
,(SELECT num_rows FROM dba_tables WHERE table_name = sp.object_name AND owner = sp.object_owner) num_rows
,(SELECT sql_fulltext FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) sql_fulltext
,(SELECT executions FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) full_scans
FROM v$sql_plan sp
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY full_scans DESC;
From the results above, we could easily avoid those small tables where we have less number of records (say, < 30,000).
Now we need to apply both technical and business knowledge to find out why those sqls are doing full scans and if re-indexing would be helpful or something out of the box!
***
SET LONG 1000000
COL object_name FORMAT A25
COL object_name FORMAT A25
COL sql_fulltext FORMAT A50
SELECT sp.object_name, dtab.num_rows, sa.sql_fulltext, sa.executions
FROM v$sql_plan sp
JOIN dba_tables dtab ON (dtab.table_name = sp.object_name)
JOIN v$sqlarea sa ON (sa.address = sp.address AND sa.hash_value =sp.hash_value)
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY sa.executions DESC;
Another faster sql version could be -
SELECT sp.object_name
,(SELECT num_rows FROM dba_tables WHERE table_name = sp.object_name AND owner = sp.object_owner) num_rows
,(SELECT sql_fulltext FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) sql_fulltext
,(SELECT executions FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) full_scans
FROM v$sql_plan sp
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY full_scans DESC;
From the results above, we could easily avoid those small tables where we have less number of records (say, < 30,000).
Now we need to apply both technical and business knowledge to find out why those sqls are doing full scans and if re-indexing would be helpful or something out of the box!
***
Nov 6, 2009
Quick Steps for upgrading/patching database
These are few quick steps I performed to patch the database. Keeping here for future references -
1. Get the patch downloaded from metalink
2. Run the installation process with interactive mode - follow instruction in
the downloaded document
3. When above steps done, start database in upgrade mode
SQL> STARTUP UPGRADE
4. Run catalog upgrade script and monitor errors (if any)
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
5. SQL> SHUTDOWN IMMEDIATE
6. SQL> STARTUP
7. Recompile invalid PL/SQL packages -
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
We are done!
***
1. Get the patch downloaded from metalink
2. Run the installation process with interactive mode - follow instruction in
the downloaded document
3. When above steps done, start database in upgrade mode
SQL> STARTUP UPGRADE
4. Run catalog upgrade script and monitor errors (if any)
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
5. SQL> SHUTDOWN IMMEDIATE
6. SQL> STARTUP
7. Recompile invalid PL/SQL packages -
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
We are done!
***
Subscribe to:
Posts (Atom)