I should remember this incident!
Last week, we added a feature for the mailing system in our application. We replaced the html text area with rich text editor. We used the open source TinyMCE (http://tinymce.moxiecode.com).
The rich text formatting feature is a good thing to have. Although some of the customers asked for it as a suggestion, there was no pressure! To utilize developer time better, I asked my team mate to integrate rich text for the module I am in charge. He did it quick as he is a good developer. Here we violated one of the good design principles - we should not add things until it is really needed (YAGNI).
We thought not to support things like link, tables etc at the very beginning for security reasons. Testing phase ran for about 5 days, and we finally released it in production system. Then the real story began! Some of the users were not able to copy formatted text from word processors to our newly added rich text box. Even if there was no table pasted (we did not support table), but it was showing wrong message to users that there was a table in the rich text.
Further investigation revealed that, for a version of MS word processor and for a specific browser version, the text comes with some tags when it was pasted to the pop up of our rich text editor (users need to paste rich text to a pop up, then need to click on 'add' button to finally add the text to the base form - this pop up cleans up unsupported formatting/tags using regular expressions on users final action 'add'). Here, our cleanup regular expressions failed to catch the comment tags came from word process.
Then we fixed and released in production.
The next day, another user reported the same thing even if we had fixed the known problem. Again, further investigation revealed that for another word processor, when user copy formatted text which was center aligned, some special tags came along with it which our validator did not catch!
Moreover, the page loading time increased about 500 ms. Another users told that spell checker that comes with the browser, did not get enabled by default inside rich text box for Firefox and IE! This is because, the text area had been replaced by the rich text's iFrame.
Life sucks on such situations!
Finally, we decided, we should not support copy-paste rich text from other word editors since we don't know how MS and other word processors will come up with new formatted tags in future. Users will only allowed to paste text as 'plain' text - we will remove all kinds of tags for other text editors. Then, they may like to add formatting inside our rich text editor using the available tools on bar. Actually, this was the actual requirements I told the developer to implement!
We would have to implement this feature at some point in future - seems took the pain a bit earlier :-)
***
Dec 24, 2009
Dec 21, 2009
Mimicking Real Application Testing (RAT) with SQL Tuning Set
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!
***
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!
***
Oct 8, 2009
Sample size (estimate_percent) affects correctness of database statistics
There are many arguments we can pass in dbms_stats.gather_table_stats procedure - "method_opt" and "estimate_percent" are two very important ones.
The default values (in 10g) for "method_opt" and "estimate_percent" are AUTO - meaning that Oracle will decide if Histogram Stats or No Histogram is appropriate for columns.
Sometimes, we customizes sample size to make stats collection job faster. This customization should be perfect otherwise a low sample size could result in non-representative statistics.
For example, when I collect statistics with 5% sample size, it does not create histograms for the two following columns.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TAB1', degree=>2, estimate_percent=>5);
SQL> SELECT column_name, num_buckets, num_distinct, sample_size, histogram
FROM user_tab_col_statistics
WHERE table_name='TAB1';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
------------------------------ ----------- ------------ ----------- ---------------
UPDATED 1 6382137 633875 NONE
CREATED 1 7858623 633875 NONE
VERSION 15 15 633875 FREQUENCY
ID 1 12677500 633875 NONE
TRASH_DATE 1 294401 221149 NONE
Now, when I increased the sample size to 50%, the procedure collected Height Balanced Histograms for the two columns. See below -
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TAB1', degree=>2, estimate_percent=>50);
SQL> SELECT column_name, num_buckets, num_distinct, sample_size, histogram
FROM user_tab_col_statistics
WHERE table_name='TAB1';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
------------------------------ ----------- ------------ ----------- ---------------
UPDATED 254 8929376 6347812 HEIGHT BALANCED
CREATED 254 9793629 6345628 HEIGHT BALANCED
VERSION 21 21 6341908 FREQUENCY
ID 1 12692852 6346426 NONE
TRASH_DATE 1 1217543 2208858 NONE
The different statistics could affect the execution plan for the queries where CREATED/UPDATED columns are used. So, these things should be carefully tested and sample size should not be underestimated. In fact, in my experience I have seen, larger sample size works better for big tables.
P.S. I planned to write a post on this probably in January this year, when I did the experiments on database statistics with histogram and no histogram options. Later became busy with other stuffs and forgot. Few days ago, I worked again to write custom scripts for collecting database stats. The sample size problem stroked - I set the sample size 50% in my scripts - working perfectly!
***
The default values (in 10g) for "method_opt" and "estimate_percent" are AUTO - meaning that Oracle will decide if Histogram Stats or No Histogram is appropriate for columns.
Sometimes, we customizes sample size to make stats collection job faster. This customization should be perfect otherwise a low sample size could result in non-representative statistics.
For example, when I collect statistics with 5% sample size, it does not create histograms for the two following columns.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TAB1', degree=>2, estimate_percent=>5);
SQL> SELECT column_name, num_buckets, num_distinct, sample_size, histogram
FROM user_tab_col_statistics
WHERE table_name='TAB1';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
------------------------------ ----------- ------------ ----------- ---------------
UPDATED 1 6382137 633875 NONE
CREATED 1 7858623 633875 NONE
VERSION 15 15 633875 FREQUENCY
ID 1 12677500 633875 NONE
TRASH_DATE 1 294401 221149 NONE
Now, when I increased the sample size to 50%, the procedure collected Height Balanced Histograms for the two columns. See below -
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TAB1', degree=>2, estimate_percent=>50);
SQL> SELECT column_name, num_buckets, num_distinct, sample_size, histogram
FROM user_tab_col_statistics
WHERE table_name='TAB1';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
------------------------------ ----------- ------------ ----------- ---------------
UPDATED 254 8929376 6347812 HEIGHT BALANCED
CREATED 254 9793629 6345628 HEIGHT BALANCED
VERSION 21 21 6341908 FREQUENCY
ID 1 12692852 6346426 NONE
TRASH_DATE 1 1217543 2208858 NONE
The different statistics could affect the execution plan for the queries where CREATED/UPDATED columns are used. So, these things should be carefully tested and sample size should not be underestimated. In fact, in my experience I have seen, larger sample size works better for big tables.
P.S. I planned to write a post on this probably in January this year, when I did the experiments on database statistics with histogram and no histogram options. Later became busy with other stuffs and forgot. Few days ago, I worked again to write custom scripts for collecting database stats. The sample size problem stroked - I set the sample size 50% in my scripts - working perfectly!
***
Oct 5, 2009
Optimizer glitch for partitioned table in version 10.2.0.4.0
Recently discovered that, partitioned tables are being accessed through global index rather than partitioned index, even though there are enough reasons to access via local/partitioned index.
Initially thought, as we turned off bind variable peek for execution plan stability (hidden system parameter _optim_peek_user_binds to FALSE), optimizer is not looking at the bind values and doesn't know which partition to access. So, accessing through a global index seemed logical. But there were other reasons!
At some point, we will need to access partitions through local index which is more efficient than accessing through global index. So I did more experiments on these. In my testing, I used literals instead of bind variables - but strangely, the execution plan did not change according to my expectation and was showing old plan with global index access!
The plan looked like -
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 750 | 1748 (2)| 00:00:02 | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT ORDER BY | | 5 | 750 | 1748 (2)| 00:00:02 | | |
| 3 | HASH UNIQUE | | 5 | 750 | 1747 (1)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 5 | 750 | 1746 (1)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 204 | 11424 | 1333 (1)| 00:00:02 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | TAC_LOGIN_ATOK | 11 | 275 | 23 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TAC_LA_LOGIN_ID | 74 | | 3 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| TAC_FORM_ATOK | 19 | 589 | 353 (1)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX RANGE SCAN | TAC_FA_ATOK_I | 38 | | 2 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | TAC_FORM_SUMMARY | 1 | 94 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 11 | INDEX UNIQUE SCAN | TAC_FS_UFID_PK | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Something must be wrong! Thought, it could be statistics or could be some other parameters.Rebuilt local index and recollected statistics (histogram/no histogram both), but still the same execution plan.
Then looked at metalink for any possible bug (this good practice I learned from my senior). Bingo! found a related issue reported but not the same thing. Let me share with you -
Bug No. 7210921 - STATISTICS WITH SUBPARTITIONS ARE NOT CORRECTLY INTERPRETED IN EXPLAIN PLANS
It explained that there are optimizer problems for partitioned tables - after generating execution plan by the optimizer, the execution plan shows wrong stats for partitions. Deleting table statistics was the workaround mentioned in metalink for the problem!
I thought, as the problem seems similar, what if I delete the partition table statistics?
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname =>'PROD', tabname =>'TAB1');
It helped!! The execution plan got changed and showed partitioned index access. I was not convinced to this solution of deleting table stats - specially for such a big table. There are other queries which would access the table and dynamic sampling (absence of statistics) could be another nightmare.
Then thought, what if I use earlier version of the optimizer?
SQL> alter session set optimizer_features_enable='10.2.0.3';
It worked - execution plan changed! The problem is with the 10.2.0.4.0 version.
If we don't want to change system level or session level settings for optimizer version (which may not be good for a large application), there are ways using hints to force particular optimizer version like this -
SELECT /*+ optimizer_features_enable('10.2.0.3') */ fs.form_type, fs.prov_id, fs.pgm_id, fs.client_id,
fs.created_by_id, fs.created_date, fs.notif_level, fs.state, fs.form_id, fs.title, fs.summary
FROM .......
Then the execution plan looked like follows, this was what I looked for -
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 8550 | 6160 (43)| 00:00:07 | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT ORDER BY | | 57 | 8550 | 6160 (43)| 00:00:07 | | |
| 3 | HASH UNIQUE | | 57 | 8550 | 6159 (43)| 00:00:07 | | |
|* 4 | HASH JOIN | | 57 | 8550 | 6158 (43)| 00:00:07 | | |
|* 5 | HASH JOIN | | 2376 | 129K| 4267 (51)| 00:00:05 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| TAC_LOGIN_ATOK | 126 | 3150 | 238 (2)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TAC_LA_LOGIN_ID | 859 | | 4 (0)| 00:00:01 | |
| 8 | PARTITION RANGE ITERATOR | | 1090K| 32M| 3882 (52)| 00:00:04 | KEY | KEY |
|* 9 | TABLE ACCESS FULL | TAC_FORM_ATOK | 1090K| 32M| 3882 (52)| 00:00:04 | KEY | KEY |
| 10 | PARTITION RANGE ITERATOR | | 13852 | 1271K| 1889 (23)| 00:00:02 | KEY | KEY |
|* 11 | TABLE ACCESS FULL | TAC_FORM_SUMMARY | 13852 | 1271K| 1889 (23)| 00:00:02 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------
Perfect! This solves our issue. Probably, I will use the hint solution in production.
Summary of solutions
1. Deleting stats for the partitioned table - not be a good approach, don't like
2. Using earlier optimizer version (10.2.0.3.0) for the database - not very recommended, their might be other fixes in the new version (10.2.0.4.0) even though this particular problem exists.
3. Using query hints to use earlier optimizer (10.2.0.3.0) - seems sensible to me and I planned to fix case by case.
***
Initially thought, as we turned off bind variable peek for execution plan stability (hidden system parameter _optim_peek_user_binds to FALSE), optimizer is not looking at the bind values and doesn't know which partition to access. So, accessing through a global index seemed logical. But there were other reasons!
At some point, we will need to access partitions through local index which is more efficient than accessing through global index. So I did more experiments on these. In my testing, I used literals instead of bind variables - but strangely, the execution plan did not change according to my expectation and was showing old plan with global index access!
The plan looked like -
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 750 | 1748 (2)| 00:00:02 | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT ORDER BY | | 5 | 750 | 1748 (2)| 00:00:02 | | |
| 3 | HASH UNIQUE | | 5 | 750 | 1747 (1)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 5 | 750 | 1746 (1)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 204 | 11424 | 1333 (1)| 00:00:02 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | TAC_LOGIN_ATOK | 11 | 275 | 23 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TAC_LA_LOGIN_ID | 74 | | 3 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| TAC_FORM_ATOK | 19 | 589 | 353 (1)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX RANGE SCAN | TAC_FA_ATOK_I | 38 | | 2 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | TAC_FORM_SUMMARY | 1 | 94 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 11 | INDEX UNIQUE SCAN | TAC_FS_UFID_PK | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Something must be wrong! Thought, it could be statistics or could be some other parameters.Rebuilt local index and recollected statistics (histogram/no histogram both), but still the same execution plan.
Then looked at metalink for any possible bug (this good practice I learned from my senior). Bingo! found a related issue reported but not the same thing. Let me share with you -
Bug No. 7210921 - STATISTICS WITH SUBPARTITIONS ARE NOT CORRECTLY INTERPRETED IN EXPLAIN PLANS
It explained that there are optimizer problems for partitioned tables - after generating execution plan by the optimizer, the execution plan shows wrong stats for partitions. Deleting table statistics was the workaround mentioned in metalink for the problem!
I thought, as the problem seems similar, what if I delete the partition table statistics?
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname =>'PROD', tabname =>'TAB1');
It helped!! The execution plan got changed and showed partitioned index access. I was not convinced to this solution of deleting table stats - specially for such a big table. There are other queries which would access the table and dynamic sampling (absence of statistics) could be another nightmare.
Then thought, what if I use earlier version of the optimizer?
SQL> alter session set optimizer_features_enable='10.2.0.3';
It worked - execution plan changed! The problem is with the 10.2.0.4.0 version.
If we don't want to change system level or session level settings for optimizer version (which may not be good for a large application), there are ways using hints to force particular optimizer version like this -
SELECT /*+ optimizer_features_enable('10.2.0.3') */ fs.form_type, fs.prov_id, fs.pgm_id, fs.client_id,
fs.created_by_id, fs.created_date, fs.notif_level, fs.state, fs.form_id, fs.title, fs.summary
FROM .......
Then the execution plan looked like follows, this was what I looked for -
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 8550 | 6160 (43)| 00:00:07 | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT ORDER BY | | 57 | 8550 | 6160 (43)| 00:00:07 | | |
| 3 | HASH UNIQUE | | 57 | 8550 | 6159 (43)| 00:00:07 | | |
|* 4 | HASH JOIN | | 57 | 8550 | 6158 (43)| 00:00:07 | | |
|* 5 | HASH JOIN | | 2376 | 129K| 4267 (51)| 00:00:05 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| TAC_LOGIN_ATOK | 126 | 3150 | 238 (2)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TAC_LA_LOGIN_ID | 859 | | 4 (0)| 00:00:01 | |
| 8 | PARTITION RANGE ITERATOR | | 1090K| 32M| 3882 (52)| 00:00:04 | KEY | KEY |
|* 9 | TABLE ACCESS FULL | TAC_FORM_ATOK | 1090K| 32M| 3882 (52)| 00:00:04 | KEY | KEY |
| 10 | PARTITION RANGE ITERATOR | | 13852 | 1271K| 1889 (23)| 00:00:02 | KEY | KEY |
|* 11 | TABLE ACCESS FULL | TAC_FORM_SUMMARY | 13852 | 1271K| 1889 (23)| 00:00:02 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------
Perfect! This solves our issue. Probably, I will use the hint solution in production.
Summary of solutions
1. Deleting stats for the partitioned table - not be a good approach, don't like
2. Using earlier optimizer version (10.2.0.3.0) for the database - not very recommended, their might be other fixes in the new version (10.2.0.4.0) even though this particular problem exists.
3. Using query hints to use earlier optimizer (10.2.0.3.0) - seems sensible to me and I planned to fix case by case.
***
Sep 19, 2009
Database performance problem - some common areas to look at!
Where there is a head, there is headache - similarly, if there is a database, there are performance issues! I accepted the truth long ago :-)
I experienced performance problems several times with our ever growing systems and the busy database which is also evolving at this stage.
Over the years, I have seen that, there are few areas which causes the performance problems most of the time. I thought I would write few things on that, for my reference specially. Those common areas are probably -
1. Problem queries: Most of the time, I have seen, some queries were eating up the system, taking significant amount of database time and resources. Tuning those top 3/4 queries, solves the problem.
2. Outdated/missing statistics: Up to date statistics are very vital for Oracle cost based optimizer. Stale statistics might cause database to crawl and lead to death!
3. Wait events: Waits are big threat for database health. There could be Row lock waits, Network waits, I/O waits, Concurrency waits and so on that could hold the resources and lead to a death situation at the end. We need to figure out how database is spending most of its time.
4. Full scans for big tables/missing indexes: There could be cases, queries are fetching too many rows by full tables scans and there are huge "consistent gets" causing the use of more cpu cycles. It might be the case that we missed an index or need to repartition tables.
5. Hard parsing: Developers are not from this planet (specially freshers), most of the time they don't think that there is a database. Developers could easily forget to use bind variables and that's why database is experiencing hard parses (look at hard parse %). So, this area should not be overlooked.
6. Bad data model: Rigid normalize model and too many 'OR' clauses in logic might also slow down systems. We should be careful about what we are doing and why.
7. Poor application/process design: The application design and flows could be bad which causes unnecessary query executions and database access. Reviewing those areas and correcting things helps a lot.
For example, due to poor process flows, there might be cases that users are bound to execute queries, say goes to dashboard (where dashboard contains a lot of queries) after each save operation - which could be eliminated. Also, we may execute static kind of queries (the queries those return same result most of the time) quite frequently which are good candidate for caching, and thins like that.
8. Poor Connection Management: Frequent connection establishment has a negative impact on database. This process is a resource intensive one. Efficient and correct use of connection pooling is very important for a stateless application system.
9. No data pruning: If we don't have partitioning and/or data pruning mechanism in the system, and deal with huge number of records grew over the years - this is not a good practice. Dealing with big tables without partitioning has always been a bad technique and causes performance problems for select queries, for index updates, unique constraints and maintenance.
Anything else - don't remember right at the moment!
I experienced performance problems several times with our ever growing systems and the busy database which is also evolving at this stage.
Over the years, I have seen that, there are few areas which causes the performance problems most of the time. I thought I would write few things on that, for my reference specially. Those common areas are probably -
1. Problem queries: Most of the time, I have seen, some queries were eating up the system, taking significant amount of database time and resources. Tuning those top 3/4 queries, solves the problem.
2. Outdated/missing statistics: Up to date statistics are very vital for Oracle cost based optimizer. Stale statistics might cause database to crawl and lead to death!
3. Wait events: Waits are big threat for database health. There could be Row lock waits, Network waits, I/O waits, Concurrency waits and so on that could hold the resources and lead to a death situation at the end. We need to figure out how database is spending most of its time.
4. Full scans for big tables/missing indexes: There could be cases, queries are fetching too many rows by full tables scans and there are huge "consistent gets" causing the use of more cpu cycles. It might be the case that we missed an index or need to repartition tables.
5. Hard parsing: Developers are not from this planet (specially freshers), most of the time they don't think that there is a database. Developers could easily forget to use bind variables and that's why database is experiencing hard parses (look at hard parse %). So, this area should not be overlooked.
6. Bad data model: Rigid normalize model and too many 'OR' clauses in logic might also slow down systems. We should be careful about what we are doing and why.
7. Poor application/process design: The application design and flows could be bad which causes unnecessary query executions and database access. Reviewing those areas and correcting things helps a lot.
For example, due to poor process flows, there might be cases that users are bound to execute queries, say goes to dashboard (where dashboard contains a lot of queries) after each save operation - which could be eliminated. Also, we may execute static kind of queries (the queries those return same result most of the time) quite frequently which are good candidate for caching, and thins like that.
8. Poor Connection Management: Frequent connection establishment has a negative impact on database. This process is a resource intensive one. Efficient and correct use of connection pooling is very important for a stateless application system.
9. No data pruning: If we don't have partitioning and/or data pruning mechanism in the system, and deal with huge number of records grew over the years - this is not a good practice. Dealing with big tables without partitioning has always been a bad technique and causes performance problems for select queries, for index updates, unique constraints and maintenance.
Anything else - don't remember right at the moment!
Sep 14, 2009
Wait event "resmgr:cpu quantum" :: resource manager settings and performance impact on database
On Monday morning, the first working day after the cutover from primary site to secondary site, we were experiencing unexpected problems on database server which was sitting idle for long time.
The load avg went high over 10. We are running on 8 core cpu box, this amount of load is totally unexpected, our regular load avg is 2-3 on a busy day.
I looked at the top queries and top events. There were waits associated with every queries. Queries were waiting for some scheduler activities... and the top queries are not the "actual" top ones I knew - the top list were being changed frequently based on users' current activities. The db server was crawling!
I found that the "resmgr:cpu quantum" was listed as top wait events and after running ADDM, I was confirmed seeing this - "Wait class Scheduler was consuming significant database time". It had become almost clear that some resource management settings are wrong/different on this database server.
ADDM analysis revealed - "Wait class Scheduler was consuming significant database time".
Now it's time to check those parameters. Yes!! I guessed it correct - two servers resource management parameters were not identical! Shocking!! The maintenance window was still running which activated the resource manager plan - this should not be the correct settings.
On the well performed server (where the application was running before the cutover), the values were -
SQL> select name, value
from v$parameter
where name in ('resource_manager_plan', 'resource_limit');
-------------------------------------- ----------
resource_limit
resource_manager_plan
And on the "problem server", the settings were like -
SQL> select name, value
from v$parameter
where name in ('resource_manager_plan', 'resource_limit');
-------------------------------------- ----------
resource_limit
resource_manager_plan
Ah - completely different, resource manager got activated here by setting the value for "resource_manager_plan" by the maintenance window which had been configured to run long hours. So, identified the root cause, after changing the value (no manager plan), all those scheduler wait events disappeared and database was behaving according to expectation - load average dropped!
SQL> alter system set resource_manager_plan='' scope=both;
System altered.
This SYSTEM_PLAN was not appropriate for the application schema. The maintenance window should have been finished by the time and we should not run on this resource manager plan after 8 AM in the morning in any circumstances.
Let's take a look what those parameter are all about -
RESOURCE_LIMIT: This parameter must be set to true to enforce resource limits assigned to a user through profiles.
RESOURCE_MANAGER_PLAN: Setting this parameter activates the resource manager. The resource manager is enabled by setting the resource_manager_plan initialization parameter to an existing plan. Note: When enabled, the DBMS Scheduler can automatically change the Resource Manager plan at Scheduler window boundaries. So keeping the window only open for a specified period of time (idle time) is a good thing.
Luck! always get volunteered to investigate such critical problems outside office hours (and very late at night for timezone difference) which is not appreciated by my family members always. I knew that these things were handled by the other database guy. Communication had always been a big issue for companies and being a global company, we are not exceptional!
The load avg went high over 10. We are running on 8 core cpu box, this amount of load is totally unexpected, our regular load avg is 2-3 on a busy day.
I looked at the top queries and top events. There were waits associated with every queries. Queries were waiting for some scheduler activities... and the top queries are not the "actual" top ones I knew - the top list were being changed frequently based on users' current activities. The db server was crawling!
I found that the "resmgr:cpu quantum" was listed as top wait events and after running ADDM, I was confirmed seeing this - "Wait class Scheduler was consuming significant database time". It had become almost clear that some resource management settings are wrong/different on this database server.
Top User Events
| Event | Event Class | % Activity | Avg Active Sessions |
|---|---|---|---|
| resmgr:cpu quantum | Scheduler | 92.26 | 149.47 |
| CPU + Wait for CPU | CPU | 6.63 | 10.73 |
ADDM analysis revealed - "Wait class Scheduler was consuming significant database time".
Now it's time to check those parameters. Yes!! I guessed it correct - two servers resource management parameters were not identical! Shocking!! The maintenance window was still running which activated the resource manager plan - this should not be the correct settings.
On the well performed server (where the application was running before the cutover), the values were -
SQL> select name, value
from v$parameter
where name in ('resource_manager_plan', 'resource_limit');
-------------------------------------- ----------
resource_limit
resource_manager_plan
And on the "problem server", the settings were like -
SQL> select name, value
from v$parameter
where name in ('resource_manager_plan', 'resource_limit');
-------------------------------------- ----------
resource_limit
resource_manager_plan
Ah - completely different, resource manager got activated here by setting the value for "resource_manager_plan" by the maintenance window which had been configured to run long hours. So, identified the root cause, after changing the value (no manager plan), all those scheduler wait events disappeared and database was behaving according to expectation - load average dropped!
SQL> alter system set resource_manager_plan='' scope=both;
System altered.
This SYSTEM_PLAN was not appropriate for the application schema. The maintenance window should have been finished by the time and we should not run on this resource manager plan after 8 AM in the morning in any circumstances.
Let's take a look what those parameter are all about -
RESOURCE_LIMIT: This parameter must be set to true to enforce resource limits assigned to a user through profiles.
RESOURCE_MANAGER_PLAN: Setting this parameter activates the resource manager. The resource manager is enabled by setting the resource_manager_plan initialization parameter to an existing plan. Note: When enabled, the DBMS Scheduler can automatically change the Resource Manager plan at Scheduler window boundaries. So keeping the window only open for a specified period of time (idle time) is a good thing.
Luck! always get volunteered to investigate such critical problems outside office hours (and very late at night for timezone difference) which is not appreciated by my family members always. I knew that these things were handled by the other database guy. Communication had always been a big issue for companies and being a global company, we are not exceptional!
Sep 11, 2009
How database response time could slow down web application responses
Ours is a large scale n-tier web application suite with a very busy database behind it. Recently, tech boss gave a nice lecture to another guy about the relations of database response and http request/response for a typical web application - I was loving the writeup!
It explains how slow database responses could cause increased load on web servers.
Look at the whole system as a black box. A user sends and HTTP
request to it, and then receives a response. Each request has to
generate a response. Users are independent of each other. i.e. if one
sees his browser not responding, the other will not be affected by it.
Before the response is generated, the application server has to do
some work and then send the response back. Not all requests will take
the same amount of time to process. But here is the crucial thing:
over a given time period, the number of requests (input) has to be
equal to the number of responses (output). Otherwise, you will see a
build-up of requests.
Now, during the peak hours of a high usage day, we have at least 1200
active users. They send 50 requests per second. So we need to
process 50 requests per second. On average, we are taking 20ms to
process each request. Now let's see what happens when the system
slows down. Assume the system becomes 5x slower. We process 10
requests per second.
After 1 second, we will have 40 in-process requests. After 5 seconds
we will have 200 in-process requests. Users who sent those requests
will see their browser "hung up". For simplicity, suppose they just
sit tight and do nothing. Remember, we have 1200 users. 200 of them
are now stuck. But the rest of the users are not aware of it and they
will continue their work.
After 10 seconds, we will end up with 400 in-process requests.
Now let's dig a little deeper into the black box. Here is the app
server and the DB server. For each request, the app server needs a
connection to the DB server. But there is an upper limit on the
number of connections between the app and DB servers. Let's assume
the upper limit is 300. We have accumulated 400 requests so far. 300
of them are being worked on in the DB. The rest 100 are "held up" by
the app server, waiting for a free ticket (connection) to the DB.
After 20 seconds, we have 800 pending requests. Of them 300 are being
worked in the DB, 500 are held up by the app server.
After 30 seconds, we have 1200 pending requests, one from each user.
The app server is holding up 900 requests.
How long can the app server hold up requests? Not for too long.
Within about 2 minutes, the browsers will start reporting errors to
the user saying something like "The server is not responding ...".
You see, the situation is already not good, even if users don't do
anything at all except their normal work.
In reality, users will not sit down tight. After 10-20 seconds of
irresponsiveness, they will start hitting reload. Each reload will
send another new request.
We enjoyed reading it :-)
It explains how slow database responses could cause increased load on web servers.
Look at the whole system as a black box. A user sends and HTTP
request to it, and then receives a response. Each request has to
generate a response. Users are independent of each other. i.e. if one
sees his browser not responding, the other will not be affected by it.
Before the response is generated, the application server has to do
some work and then send the response back. Not all requests will take
the same amount of time to process. But here is the crucial thing:
over a given time period, the number of requests (input) has to be
equal to the number of responses (output). Otherwise, you will see a
build-up of requests.
Now, during the peak hours of a high usage day, we have at least 1200
active users. They send 50 requests per second. So we need to
process 50 requests per second. On average, we are taking 20ms to
process each request. Now let's see what happens when the system
slows down. Assume the system becomes 5x slower. We process 10
requests per second.
After 1 second, we will have 40 in-process requests. After 5 seconds
we will have 200 in-process requests. Users who sent those requests
will see their browser "hung up". For simplicity, suppose they just
sit tight and do nothing. Remember, we have 1200 users. 200 of them
are now stuck. But the rest of the users are not aware of it and they
will continue their work.
After 10 seconds, we will end up with 400 in-process requests.
Now let's dig a little deeper into the black box. Here is the app
server and the DB server. For each request, the app server needs a
connection to the DB server. But there is an upper limit on the
number of connections between the app and DB servers. Let's assume
the upper limit is 300. We have accumulated 400 requests so far. 300
of them are being worked on in the DB. The rest 100 are "held up" by
the app server, waiting for a free ticket (connection) to the DB.
After 20 seconds, we have 800 pending requests. Of them 300 are being
worked in the DB, 500 are held up by the app server.
After 30 seconds, we have 1200 pending requests, one from each user.
The app server is holding up 900 requests.
How long can the app server hold up requests? Not for too long.
Within about 2 minutes, the browsers will start reporting errors to
the user saying something like "The server is not responding ...".
You see, the situation is already not good, even if users don't do
anything at all except their normal work.
In reality, users will not sit down tight. After 10-20 seconds of
irresponsiveness, they will start hitting reload. Each reload will
send another new request.
We enjoyed reading it :-)
Sep 10, 2009
Madness for Stale Statistics
Yesterday evening (my time zone), which is Wednesday morning for the application users on a different time zone, database was behaving strangely - literally crawling! As it was not my office hour, got an unofficial phone call from the monitoring guy. I gave a quick look at email threads and found that others were on the issue and they were looking and discussing things. Seeing that, I offloaded myself from the issue and went to bed - it was late at night on the month of holy "Ramadan". I hoped that we would survive and I would look at things on my next office hour.
Because of Murphy's law probably - I got phone call from my tech boss at around 2:20 AM :-)
Things were running slower than the other days. For last few days, database was consuming about 50% CPU on avg. But today, it was 70-80% and overall response time had been increased. But we did not do any changes in database recently.
I looked at AWR, ASH reports - ran my custom queries... everything were very confusing. Queries were taking longer time than previous. I was looking here and there... in the middle of the night, brain was functioning slowly.
Finally, after 15-20 min, to verify if we had statistics correct, I ran the following script -
SET SERVEROUT ON;
DECLARE
ObjList DBMS_STATS.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'PROD', objlist=>ObjList, options=>'LIST STALE');
Because of Murphy's law probably - I got phone call from my tech boss at around 2:20 AM :-)
Things were running slower than the other days. For last few days, database was consuming about 50% CPU on avg. But today, it was 70-80% and overall response time had been increased. But we did not do any changes in database recently.
I looked at AWR, ASH reports - ran my custom queries... everything were very confusing. Queries were taking longer time than previous. I was looking here and there... in the middle of the night, brain was functioning slowly.
Finally, after 15-20 min, to verify if we had statistics correct, I ran the following script -
SET SERVEROUT ON;
DECLARE
ObjList DBMS_STATS.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'PROD', objlist=>ObjList, options=>'LIST STALE');
FOR i IN ObjList.FIRST..ObjList.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(ObjList(
END LOOP;
END;
LOOP
DBMS_OUTPUT.PUT_LINE(ObjList(
END LOOP;
END;
/
What I saw, was not very expected, there were about 75 tables with outdated statistics!
I started collecting statistics table by table - the important ones first. Server load gradually decreased. Quickly, I collected stats with no histogram and 50% sample size - for us, big sample size works better.
SQL> exec dbms_stats.gather_table_stats(ownname=>'PROD',tabname=>'USER', cascade=>true, degree=>2, estimate_percent=>50, method_opt=>'FOR ALL COLUMNS SIZE 1');
It took about 2.5 hours to collect stats for about 150 tables. Another sleepless night!
What happened?
Statistics had been collected with the default dbms scheduler jobs with 1 day interval by weekday maintenance window. The sample size was also low - 5% only. Those were not very appropriate for us in these days. Tables were becoming stale quite frequently.
What I saw, was not very expected, there were about 75 tables with outdated statistics!
I started collecting statistics table by table - the important ones first. Server load gradually decreased. Quickly, I collected stats with no histogram and 50% sample size - for us, big sample size works better.
SQL> exec dbms_stats.gather_table_stats(ownname=>'PROD',tabname=>'USER', cascade=>true, degree=>2, estimate_percent=>50, method_opt=>'FOR ALL COLUMNS SIZE 1');
It took about 2.5 hours to collect stats for about 150 tables. Another sleepless night!
What happened?
Statistics had been collected with the default dbms scheduler jobs with 1 day interval by weekday maintenance window. The sample size was also low - 5% only. Those were not very appropriate for us in these days. Tables were becoming stale quite frequently.
Aug 10, 2009
Oracle SQL Loader: high speed data loading tool
Oracle SQL*Loader is a utility provided by Oracle to load bulk data into database tables. It's an integral feature of the databases, available in all configurations.
We had a situation where we need to build new tables with lots of records calculated from other tables. We simplified our access control structure and that needed significant migration effort. Initially we tried to write java programs which inserted records to the new tables. But it were taking too much time (about 2 hours to load some 80 million records) to load data into the new tables. We could not afford this amount of downtime since we have to be up and running on 24/7 basis. So, we had to device a way to minimize the downtime.
We tried to generate csv files using java programs which we had to load into database ultimately to build the new access control tables. Our new approach worked like a silver bullet! We were finally able to get all these done in just few minutes. The java program generated flat csv files (values in comma separated) in about 4 minutes and we loaded the csv data into tables using sql loader in just few seconds!
It is so fast - I loaded over 80 million data in just 25 sec!!
Three things needed in most cases for loading data using sql loader -
a) The control file
b) The external data file (might be a csv file)
c) The command to initiate loading
Here are the samples of those -
a) Control file: summary.ctl
UNRECOVERABLE LOAD DATA
INFILE 'summary.csv'
APPEND INTO TABLE tac_form_summary
fields terminated by "," optionally enclosed by '"'
(
UFID INTEGER EXTERNAL,
FORM_TYPE INTEGER EXTERNAL,
PROV_ID INTEGER EXTERNAL,
PGM_ID INTEGER EXTERNAL,
CLIENT_ID INTEGER EXTERNAL,
CREATED_BY_ID INTEGER EXTERNAL,
CREATED_DATE "to_timestamp(:CREATED_DATE, 'YYYY-MM-DD HH24:MI:SS.FF3')",
NOTIF_LEVEL INTEGER EXTERNAL,
TEST_MODE INTEGER EXTERNAL,
FORM_ID CHAR,
TITLE CHAR,
SUMMARY CHAR
)
b) Data file: summary.csv
2,1,100,199,0,892,"2005-05-26 09:42:52.000",2,0,"XEBC","Comm Log","Type: [N]"
c) The command - how to run
From command line, issue the command -
#sqlldr user/pass control=summary.ctl PARALLE=true DIRECT=true
Also, changing the default settings, you could try -
#sqlldr user/pass control=summary.ctl BINDSIZE=20971520 ROWS=10000 READSIZE=20971520
Loading options: Conventional vs Direct Path
With conventional loading (if DIRECT=TRUE not mentioned), SQL*Loader loads data into a bind array and passes it to the database engine to process with an INSERT statement and full UNDO and REDO will be generated for this.
With direct path loading, SQL*Loader reads data, pass the data to the database via direct path API. The API calls format it directly into data blocks in memory and then flushes these blocks directly to the datafiles using multi block I/O. Bypassing the buffer cache, UNDO and REDO makes it super fast!
Other setting which helps to improve performance in conventional loading -
* Default settings -
Bind array: 64 rows, maximum of 256000 bytes
Space allocated for bind array: 198144 bytes(64 rows)
Read buffer bytes: 1048576
Elapsed time was: 00:01:10.58
CPU time was: 00:00:03.58
* Improved settings -
Bind array: 10000 rows, maximum of 20971520 bytes
value used for ROWS parameter changed from 10000 to 6773
Space allocated for bind array: 20969208 bytes(6773 rows)
Read buffer bytes:20971520
Elapsed time was: 00:00:11.26
CPU time was: 00:00:03.98
Ref: Simple examples are shown here http://www.orafaq.com/wiki/SQL*Loader_FAQ
We had a situation where we need to build new tables with lots of records calculated from other tables. We simplified our access control structure and that needed significant migration effort. Initially we tried to write java programs which inserted records to the new tables. But it were taking too much time (about 2 hours to load some 80 million records) to load data into the new tables. We could not afford this amount of downtime since we have to be up and running on 24/7 basis. So, we had to device a way to minimize the downtime.
We tried to generate csv files using java programs which we had to load into database ultimately to build the new access control tables. Our new approach worked like a silver bullet! We were finally able to get all these done in just few minutes. The java program generated flat csv files (values in comma separated) in about 4 minutes and we loaded the csv data into tables using sql loader in just few seconds!
It is so fast - I loaded over 80 million data in just 25 sec!!
Three things needed in most cases for loading data using sql loader -
a) The control file
b) The external data file (might be a csv file)
c) The command to initiate loading
Here are the samples of those -
a) Control file: summary.ctl
UNRECOVERABLE LOAD DATA
INFILE 'summary.csv'
APPEND INTO TABLE tac_form_summary
fields terminated by "," optionally enclosed by '"'
(
UFID INTEGER EXTERNAL,
FORM_TYPE INTEGER EXTERNAL,
PROV_ID INTEGER EXTERNAL,
PGM_ID INTEGER EXTERNAL,
CLIENT_ID INTEGER EXTERNAL,
CREATED_BY_ID INTEGER EXTERNAL,
CREATED_DATE "to_timestamp(:CREATED_DATE, 'YYYY-MM-DD HH24:MI:SS.FF3')",
NOTIF_LEVEL INTEGER EXTERNAL,
TEST_MODE INTEGER EXTERNAL,
FORM_ID CHAR,
TITLE CHAR,
SUMMARY CHAR
)
b) Data file: summary.csv
2,1,100,199,0,892,"2005-05-26 09:42:52.000",2,0,"XEBC","Comm Log","Type: [N]"
c) The command - how to run
From command line, issue the command -
#sqlldr user/pass control=summary.ctl PARALLE=true DIRECT=true
Also, changing the default settings, you could try -
#sqlldr user/pass control=summary.ctl BINDSIZE=20971520 ROWS=10000 READSIZE=20971520
Loading options: Conventional vs Direct Path
With conventional loading (if DIRECT=TRUE not mentioned), SQL*Loader loads data into a bind array and passes it to the database engine to process with an INSERT statement and full UNDO and REDO will be generated for this.
With direct path loading, SQL*Loader reads data, pass the data to the database via direct path API. The API calls format it directly into data blocks in memory and then flushes these blocks directly to the datafiles using multi block I/O. Bypassing the buffer cache, UNDO and REDO makes it super fast!
Other setting which helps to improve performance in conventional loading -
* Default settings -
Bind array: 64 rows, maximum of 256000 bytes
Space allocated for bind array: 198144 bytes(64 rows)
Read buffer bytes: 1048576
Elapsed time was: 00:01:10.58
CPU time was: 00:00:03.58
* Improved settings -
Bind array: 10000 rows, maximum of 20971520 bytes
value used for ROWS parameter changed from 10000 to 6773
Space allocated for bind array: 20969208 bytes(6773 rows)
Read buffer bytes:20971520
Elapsed time was: 00:00:11.26
CPU time was: 00:00:03.98
Ref: Simple examples are shown here http://www.orafaq.com/wiki/SQL*Loader_FAQ
Aug 8, 2009
Managing TEMP Tablespace
In Oracle database, temporary tablespace (also known as temp segment) and UNDO tablespace (also known as UNDO segment) are two very important things.
As I decided to write important stuffs time to time I do with database, it's time to write few important things on TEMP tablespace. Today, I had to remember TEMP tablespace related stuffs again - one of my friends was facing problem with BLOB updates for long running transactions in his system.
There is no database guy who never saw this error message - ORA-1652: unable to extend temp segment by .. this was the case with my friend. He is not a database guy - so here I go. There was not enough temp space to support his long running transactions which were storing LOB data. So I made that auto extendable.
What is TEMP Tablespace?
It is a special tablespace, needed for oracle database to store temporary data/segments to support other operations. One database can have at most one active temp tablespace at a time, but of course temp tablespace can have one or more data files like other tablespaces.
Temporary segments are created in temp tablespace during -
* Large sort operation, when PGA_AGGREGATE_TARGET can't accommodate with the size. These include ORDER BY, GROUP BY, DISTINCT, MERGE JOIN, HASH JOIN or CREATE INDEX.
* When temporary table is used
* To store temporary LOB data
How to increase temp size
SQL> SELECT tablespace_name, file_name, bytes/1024/1024 MB, AUTOEXTENSIBLE
FROM dba_temp_files WHERE tablespace_name = 'TEMP';
TABLESPACE_NAME FILE_NAME MB AUT
----------------- -------------------------------- ------------ ------------
TEMP /data/orcl/temp01.dbf 100 NO
SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' RESIZE 500M;
If the file is not auto extensible, if we have enough disk space, we could set auto extend on and max size unlimited for that.
SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
How to shrink/reclaim space
There might be cases, one long running query or lob segment might increase the temp tablespace size significantly but we don't want to keep temp tablespace strict to that size as we may not need such large file in general.
The idea is to create another temporary temp tablespace (say temp2) and temporarily assign it to the database. Finally recreate the desired temp tablespace and reassign it and drop the temp things.
SQL> CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/data/orcl/temp2.dbf' SIZE 5M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/data/orcl/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
If database is not accessible by users, then we could do it in the following shortcut way -
SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' DROP INCLUDING DATAFILES;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/orcl/temp01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED;
Monitoring TEMP segments
I use this script to identify 'who' is eating up temp space and why -
set linesize 200
col sid_serial format a10
col sql_text format a40
col username format a10
col program format a25
SELECT sq.sql_text, ROUND(((b.blocks*p.value)/1024/1024),2)||'MB' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p,
v$sql sq
WHERE a.saddr = b.session_addr
AND b.sql_id = sq.sql_id
AND p.name = 'db_block_size'
AND b.tablespace = 'TEMP'
ORDER BY b.tablespace, b.blocks;
As I decided to write important stuffs time to time I do with database, it's time to write few important things on TEMP tablespace. Today, I had to remember TEMP tablespace related stuffs again - one of my friends was facing problem with BLOB updates for long running transactions in his system.
There is no database guy who never saw this error message - ORA-1652: unable to extend temp segment by .. this was the case with my friend. He is not a database guy - so here I go. There was not enough temp space to support his long running transactions which were storing LOB data. So I made that auto extendable.
What is TEMP Tablespace?
It is a special tablespace, needed for oracle database to store temporary data/segments to support other operations. One database can have at most one active temp tablespace at a time, but of course temp tablespace can have one or more data files like other tablespaces.
Temporary segments are created in temp tablespace during -
* Large sort operation, when PGA_AGGREGATE_TARGET can't accommodate with the size. These include ORDER BY, GROUP BY, DISTINCT, MERGE JOIN, HASH JOIN or CREATE INDEX.
* When temporary table is used
* To store temporary LOB data
How to increase temp size
SQL> SELECT tablespace_name, file_name, bytes/1024/1024 MB, AUTOEXTENSIBLE
FROM dba_temp_files WHERE tablespace_name = 'TEMP';
TABLESPACE_NAME FILE_NAME MB AUT
----------------- -------------------------------- ------------ ------------
TEMP /data/orcl/temp01.dbf 100 NO
SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' RESIZE 500M;
If the file is not auto extensible, if we have enough disk space, we could set auto extend on and max size unlimited for that.
SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
How to shrink/reclaim space
There might be cases, one long running query or lob segment might increase the temp tablespace size significantly but we don't want to keep temp tablespace strict to that size as we may not need such large file in general.
The idea is to create another temporary temp tablespace (say temp2) and temporarily assign it to the database. Finally recreate the desired temp tablespace and reassign it and drop the temp things.
SQL> CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/data/orcl/temp2.dbf' SIZE 5M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/data/orcl/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
If database is not accessible by users, then we could do it in the following shortcut way -
SQL> ALTER DATABASE TEMPFILE '/data/orcl/temp01.dbf' DROP INCLUDING DATAFILES;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/orcl/temp01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED;
Monitoring TEMP segments
I use this script to identify 'who' is eating up temp space and why -
set linesize 200
col sid_serial format a10
col sql_text format a40
col username format a10
col program format a25
SELECT sq.sql_text, ROUND(((b.blocks*p.value)/1024/1024),2)||'MB' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p,
v$sql sq
WHERE a.saddr = b.session_addr
AND b.sql_id = sq.sql_id
AND p.name = 'db_block_size'
AND b.tablespace = 'TEMP'
ORDER BY b.tablespace, b.blocks;
Aug 7, 2009
Understanding jdbc connection pool settings in WebLogic
Few days ago, I was looking at database and found that there were about 30-40 connection/sessions established per minute on average. This should not be the usual case with connection pooling in place.
Then looked at database listener.log to see which web server was requesting connections and at what rate. Found a pattern, after about each 10 min, there is a flood of connection requests from all the web servers. Now it's time to look at connection pool setting - what are there!
Weblogic Connection pool Settings
< jdbc-connection-pool-params >
< initial-capacity >10< /initial-capacity >
< max-capacity >150< /max-capacity >
< capacity-increment>10< /capacity-increment>
< shrink-frequency-seconds >600< /shrink-frequency-seconds >
< highest-num-waiters >300< /highest-num-waiters>
< connection-creation-retry-frequency-seconds >3< /connection-creation-retry-frequency-seconds >
< connection-reserve-timeout-seconds >30< /connection-reserve-timeout-seconds >
< test-connections-on-reserve >true< /test-connections-on-reserve >
< inactive-connection-timeout-seconds>1800< /inactive-connection-timeout-seconds>
< test-table-name>SQL SELECT 1 FROM DUAL< /test-table-name>
< /jdbc-connection-pool-params >
We set connection shrink time to 600 sec that means 10 min.
What does "ShrinkFrequencySeconds" parameter mean?
"The number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand. ShrinkingEnabled must be set to true for a connection pool to shrink."
So, after 10 min, connections were returned to database. But just after that, probably a bunch of http requests came into play with the web servers. Most of the case, it needs to establish connection to the database to serve the request. Since these requests were more than the initial connection pool value which is 10 (back to the initial connection numbers after shrinking), jdbc establishes a bunch of new connections again to serve those requests. This is how we had a flood of new connection request after about 10 min on avg.
Here we can find the description of all the connection pool parameters -
http://e-docs.bea.com/wls/docs81/config_xml/JDBCConnectionPool.html
Then looked at database listener.log to see which web server was requesting connections and at what rate. Found a pattern, after about each 10 min, there is a flood of connection requests from all the web servers. Now it's time to look at connection pool setting - what are there!
Weblogic Connection pool Settings
< jdbc-connection-pool-params >
< initial-capacity >10< /initial-capacity >
< max-capacity >150< /max-capacity >
< capacity-increment>10< /capacity-increment>
< shrink-frequency-seconds >600< /shrink-frequency-seconds >
< highest-num-waiters >300< /highest-num-waiters>
< connection-creation-retry-frequency-seconds >3< /connection-creation-retry-frequency-seconds >
< connection-reserve-timeout-seconds >30< /connection-reserve-timeout-seconds >
< test-connections-on-reserve >true< /test-connections-on-reserve >
< inactive-connection-timeout-seconds>1800< /inactive-connection-timeout-seconds>
< test-table-name>SQL SELECT 1 FROM DUAL< /test-table-name>
< /jdbc-connection-pool-params >
We set connection shrink time to 600 sec that means 10 min.
What does "ShrinkFrequencySeconds" parameter mean?
"The number of seconds to wait before shrinking a connection pool that has incrementally increased to meet demand. ShrinkingEnabled must be set to true for a connection pool to shrink."
So, after 10 min, connections were returned to database. But just after that, probably a bunch of http requests came into play with the web servers. Most of the case, it needs to establish connection to the database to serve the request. Since these requests were more than the initial connection pool value which is 10 (back to the initial connection numbers after shrinking), jdbc establishes a bunch of new connections again to serve those requests. This is how we had a flood of new connection request after about 10 min on avg.
Here we can find the description of all the connection pool parameters -
http://e-docs.bea.com/wls/docs81/config_xml/JDBCConnectionPool.html
Aug 6, 2009
Hibernate second level caching to reduce query executions
There are situations in web applications where some queries could be executed in almost every http request. If those queries return static kind of data, a good approach is caching to reduce database execution calls. Reduced execution means reduced CPU usage. So, related to database performance in anyway.
Recently, we have discovered that one of the static kind of query is quite frequently parsed and executed in database. The parsing had been increased as we experimentally disabled "Statement Caching" in WebLogic connection pool.
AWR output: 11:30 AM - 12:00 PM; Parse Calls in 30 min
The above query returns kind of static information, less frequently changed. The query has been generated from a Hibernate call on that db object. So, we could easily introduce a second level caching for that - it is something that caches individual values, not the instances of an object.
Here is a good article on Hibernate caching -
http://www.javalobby.org/java/forums/t48846.html
What we need to do to enable second level caching?
What I just did -
1. Added 'Cache' annotation to the db class (Title.java). We are using Hibernate 3, so there is no hbm.xml now.
2. Now for the corresponding db class, I added caching configurations in ehcache.xml. Ehcache is a widely used java distributed cache for general purpose caching for Java EE applications.
#Title.java (the db class)
@Entity
@Table(name = "title")
@SequenceGenerator(name = "Seq_Title",sequenceName = "title_seq")
@Cache(usage = org.hibernate.annotations.CacheConcurrencyStrategy.READ_WRITE)
public class Title extends Persistent {
private int id;
.................
.................
}
#ehcache.xml (ehcache configuration file)
< ehcache >
.................
.................
< name="net.service.common.db.Title"
maxelementsinmemory="1000"
eternal="false"
overflowtodisk="false"
timetoidleseconds="900"
timetoliveseconds="900" / >
< / ehcache >
To test if it works or not, I took one test database box and flushed shared pool to remove old stats. Then connecting to the database, ran the application which requires large invocation of the query.
It is working according to my expectation, just loaded new titles (by invoking the query) after each 15 min!
col sql_text format a40;
set linesize 200;
select sq.SQL_TEXT, st.PARSE_CALLS, st.EXECUTIONS,
round( 100*(1-( st.PARSE_CALLS / st.EXECUTIONS )),2) execute_to_parse
from v$sqlstats st, v$sql sq
where st.sql_id = sq.sql_id
and sq.PARSING_SCHEMA_NAME = 'PROD'
and sq.sql_text like 'select title0_.id%'
and st.EXECUTIONS <> 0
order by execute_to_parse;
SQL_TEXT PARSE_CALLS EXECUTIONS EXECUTE_TO_PARSE
---------------------------------------- ----------- ---------- -------
select title0_.id as id27_0_, title0_.cr 3 140 97.86
eated as created27_0_, title0_.updated a
s updated27_0_, title0_.version as versi
on27_0_, title0_.description as descript
5_27_0_, title0_.prov_id as prov6_27_0_,
title0_.title as title27_0_ from title
title0_ where title0_.id=:1
3 parse calls, 140 executions in 20 min!! I was requesting to load many titles, but it was actually serving from the cache. So, there will less executions for the query from now on :-). Also less parse calls since we are using "Prepared Statement Caching" in WebLogic connection pool.
Recently, we have discovered that one of the static kind of query is quite frequently parsed and executed in database. The parsing had been increased as we experimentally disabled "Statement Caching" in WebLogic connection pool.
AWR output: 11:30 AM - 12:00 PM; Parse Calls in 30 min
| Parse Calls | Executions | % Total Parses | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|
| 68,185 | 68,187 | 20.38 | dxyfqwzsn9727 | select title0_.id as id27_0_, ... |
The above query returns kind of static information, less frequently changed. The query has been generated from a Hibernate call on that db object. So, we could easily introduce a second level caching for that - it is something that caches individual values, not the instances of an object.
Here is a good article on Hibernate caching -
http://www.javalobby.org/java/forums/t48846.html
What we need to do to enable second level caching?
What I just did -
1. Added 'Cache' annotation to the db class (Title.java). We are using Hibernate 3, so there is no hbm.xml now.
2. Now for the corresponding db class, I added caching configurations in ehcache.xml. Ehcache is a widely used java distributed cache for general purpose caching for Java EE applications.
#Title.java (the db class)
@Entity
@Table(name = "title")
@SequenceGenerator(name = "Seq_Title",sequenceName = "title_seq")
@Cache(usage = org.hibernate.annotations.CacheConcurrencyStrategy.READ_WRITE)
public class Title extends Persistent {
private int id;
.................
.................
}
#ehcache.xml (ehcache configuration file)
< ehcache >
.................
.................
< name="net.service.common.db.Title"
maxelementsinmemory="1000"
eternal="false"
overflowtodisk="false"
timetoidleseconds="900"
timetoliveseconds="900" / >
< / ehcache >
To test if it works or not, I took one test database box and flushed shared pool to remove old stats. Then connecting to the database, ran the application which requires large invocation of the query.
It is working according to my expectation, just loaded new titles (by invoking the query) after each 15 min!
col sql_text format a40;
set linesize 200;
select sq.SQL_TEXT, st.PARSE_CALLS, st.EXECUTIONS,
round( 100*(1-( st.PARSE_CALLS / st.EXECUTIONS )),2) execute_to_parse
from v$sqlstats st, v$sql sq
where st.sql_id = sq.sql_id
and sq.PARSING_SCHEMA_NAME = 'PROD'
and sq.sql_text like 'select title0_.id%'
and st.EXECUTIONS <> 0
order by execute_to_parse;
SQL_TEXT PARSE_CALLS EXECUTIONS EXECUTE_TO_PARSE
---------------------------------------- ----------- ---------- -------
select title0_.id as id27_0_, title0_.cr 3 140 97.86
eated as created27_0_, title0_.updated a
s updated27_0_, title0_.version as versi
on27_0_, title0_.description as descript
5_27_0_, title0_.prov_id as prov6_27_0_,
title0_.title as title27_0_ from title
title0_ where title0_.id=:1
3 parse calls, 140 executions in 20 min!! I was requesting to load many titles, but it was actually serving from the cache. So, there will less executions for the query from now on :-). Also less parse calls since we are using "Prepared Statement Caching" in WebLogic connection pool.
Aug 5, 2009
Low 'Execute to Parse' ratio analysis
Suddenly, got a call from one of the Unix team members, one of our side applications was not responding properly.
I looked at database if something wrong going on there. Found something very interesting -
* Queries were taking longer time than previous (longer execution time than needed before 2 weeks)
* More CPU were being used.
* Execute to Parse ratio went down to 3%-5%. Earlier, we had this ration about 52%-65%. This could be an indication of a major problem.
* Got some stats from AWR - high number of sessions were being established during that problem hours. About 30 connections/sessions established per minute. Our normal range was 7-10 connections per minute. We do use connection pooling in Weblogic.
AWR Report showed -
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 99.53 | In-memory Sort %: | 100.00 |
| Library Hit %: | 99.64 | Soft Parse %: | 99.83 |
| Execute to Parse %: | 4.18 | Latch Hit %: | 99.98 |
| Parse CPU to Parse Elapsd %: | 91.48 | % Non-Parse CPU: | 99.42 |
The 'Execute to Parse' thing drew my attention first.
What is Execute to Parse ratio? What does it tell?
Every query needs to be parsed before it got executed. If some queries are quite frequently used, a good design will be reuse the parsed query. That means, it will not parse the same query again (provided that we are using bind variables), rather will reuse the existing parsed query and then execute it. In an ideal world, execute should be more than the parse. More parsing requires more CPU. That's why the concept of query reuse came in to play.
When execute is more than parse, the figure will be positive. The ratio goes down if there are more parses than the execute. Some of the queries are parsed, but less or never executed!
What may be the possible reasons for such low ratio?
This "Execute to Parse ratio" actually tells many things and seeks many areas to look into for further information. Causes could be one of the followings or something else -
* There might be no "Prepared Statement caching" in java layer or in jdbc connection pool
* There might be the case, before the execution of a query, the cursor was closed
* There might not be enough "session cached cursors" to hold the queries to reuse
* There might be the case where new queries were coming to play quite frequently
* There might be connection interruptions
I have to find out the queries those were being parsed but executed less during the problem hours -
set linesize 200;
set pagesize 1000;
col sql_text format a40;
SELECT sq.sql_text, st.executions_total, st.parse_calls_total
, round( 100*(1-( st.parse_calls_total / st.executions_total )),2) execute_to_parse
, st.executions_delta, st.parse_calls_delta
, round( 100*(1-( st.parse_calls_delta / st.executions_delta )),2) delta_ratio
FROM DBA_HIST_SQLSTAT st
, DBA_HIST_SQLTEXT sq
, DBA_HIST_SNAPSHOT s
WHERE s.snap_id = st.snap_id
AND s.begin_interval_time >= to_date('2009-08-04 11:30 am','YYYY-MM-DD HH:MI am')
AND s.end_interval_time <= to_date('2009-08-04 12:01 pm','YYYY-MM-DD HH:MI pm') AND st.sql_id = sq.sql_id AND st.parsing_schema_name='PROD' AND st.executions_total !=0 AND st.executions_delta !=0 ORDER BY delta_ratio;
The delta ratio is the current for the snapshot. So, I have a list of queries those parsed but less or not executed - I can now look at application code for a better understanding.
Tough time ahead!
Aug 3, 2009
Understanding Oracle Histograms
Before I forget, want to write a few words as I did some experiments recently with the histogram statistics and our complex queries.
Let me do it as a question-answer type approach.
What is Histogram in Oracle?
Histogram is an option for database statistics collection (introduced in 10g). It contains information about how the data of a table column is distributed. Histogram helps Oracle optimizer to determine whether certain values occur frequently, rarely or not at all. And based on those information, it can select an optimum access path for the query. For a big database, I found histogram stats are really really important.
If there are no histograms, it is assumed that the column values are equally distributed over the tables.
What does it do? What's the use?
I am aware of the two direct benefits -
* Histogram helps the optimizer to improve table join order: For example, if we have several table joins will million of rows whose final result set will be only few rows, Oracle tries to join tables together in such an order to make the result set cardinality (rows returned) of the first joins as small as possible.This has a huge performance impact on performing the rest of the operations since will it have to deal with less amount of rows in subsequent operations.
* Histogram helps the optimizer to find optimum access path - full table scan vs accessing via index: For example, there is an index on "user_type" (assume that we have 5% admins, 15% managers, 80% support type users). If we are looking for most of the support type users and the query returns 60% of the table rows, it will be more efficient to do a full table scan rather than accessing the 'support' users via "user_type_i" index. Histogram keeps those statistics to make a choice.
What are different types of Histogram?
There are three - two basic types + None.
* Height-balanced histograms: In a height-balanced histogram, the column values are divided into bands so that each band contains approximately the same number of rows. The useful information that the histogram provides is where in the range of values the endpoints fall.
If there are more different values in the column than the amount of buckets, a height balanced histogram is created. The column values are divided into intervals of the same size. In this case, an interval is assigned to each bucket.
The maximum number of histogram buckets is 254.
* Frequency histograms: For this type, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are when the number of distinct values is less than or equal to the number of histogram buckets specified.
If there are fewer different values in the column than the amount of buckets, this type of histogram is created. Each bucket contains one column value and the frequency of this column value.
* None, meaning no histogram statistics for the column
Two important views to find information about the histograms in database -
- USER_TAB_COL_STATISTICS
- USER_TAB_HISTOGRAMS
In our big system, for over 2,000 queries, I have found that oracle automatically collected histogram stats for some columns -
SQL> select count(column_name)
from user_tab_col_statistics
where histogram <> 'NONE';
COUNT(COLUMN_NAME)
------------------
1247
Some queries run much faster without any histogram information. For those, I collected statistics using the following options -
SQL> exec dbms_stats.gather_table_stats(ownname=>'PROD'
,tabname=> 'USER_INFO'
,method_opt=> 'FOR ALL COLUMN SIZE 1'
,estimate_percent=>50, cascade=>true);
Also, there are good number of queries those performs better if histogram stats available. For those kind of queries, I collected stats letting oracle decide the bucket size specifying 'AUTO' -
SQL> exec dbms_stats.gather_table_stats(ownname=>'PROD'
,tabname=> 'USER_INFO'
,method_opt=> 'FOR ALL COLUMN SIZE AUTO'
,estimate_percent=>50, cascade=>true);
Statistics are vital for database performance. If there are problem queries, one should analyze the execution plans throughly and tryout the statistics options suitable for those - case by case.
Let me do it as a question-answer type approach.
What is Histogram in Oracle?
Histogram is an option for database statistics collection (introduced in 10g). It contains information about how the data of a table column is distributed. Histogram helps Oracle optimizer to determine whether certain values occur frequently, rarely or not at all. And based on those information, it can select an optimum access path for the query. For a big database, I found histogram stats are really really important.
If there are no histograms, it is assumed that the column values are equally distributed over the tables.
What does it do? What's the use?
I am aware of the two direct benefits -
* Histogram helps the optimizer to improve table join order: For example, if we have several table joins will million of rows whose final result set will be only few rows, Oracle tries to join tables together in such an order to make the result set cardinality (rows returned) of the first joins as small as possible.This has a huge performance impact on performing the rest of the operations since will it have to deal with less amount of rows in subsequent operations.
* Histogram helps the optimizer to find optimum access path - full table scan vs accessing via index: For example, there is an index on "user_type" (assume that we have 5% admins, 15% managers, 80% support type users). If we are looking for most of the support type users and the query returns 60% of the table rows, it will be more efficient to do a full table scan rather than accessing the 'support' users via "user_type_i" index. Histogram keeps those statistics to make a choice.
What are different types of Histogram?
There are three - two basic types + None.
* Height-balanced histograms: In a height-balanced histogram, the column values are divided into bands so that each band contains approximately the same number of rows. The useful information that the histogram provides is where in the range of values the endpoints fall.
If there are more different values in the column than the amount of buckets, a height balanced histogram is created. The column values are divided into intervals of the same size. In this case, an interval is assigned to each bucket.
The maximum number of histogram buckets is 254.
* Frequency histograms: For this type, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are when the number of distinct values is less than or equal to the number of histogram buckets specified.
If there are fewer different values in the column than the amount of buckets, this type of histogram is created. Each bucket contains one column value and the frequency of this column value.
* None, meaning no histogram statistics for the column
Two important views to find information about the histograms in database -
- USER_TAB_COL_STATISTICS
- USER_TAB_HISTOGRAMS
In our big system, for over 2,000 queries, I have found that oracle automatically collected histogram stats for some columns -
SQL> select count(column_name)
from user_tab_col_statistics
where histogram <> 'NONE';
COUNT(COLUMN_NAME)
------------------
1247
Some queries run much faster without any histogram information. For those, I collected statistics using the following options -
SQL> exec dbms_stats.gather_table_stats(ownname=>'PROD'
,tabname=> 'USER_INFO'
,method_opt=> 'FOR ALL COLUMN SIZE 1'
,estimate_percent=>50, cascade=>true);
Also, there are good number of queries those performs better if histogram stats available. For those kind of queries, I collected stats letting oracle decide the bucket size specifying 'AUTO' -
SQL> exec dbms_stats.gather_table_stats(ownname=>'PROD'
,tabname=> 'USER_INFO'
,method_opt=> 'FOR ALL COLUMN SIZE AUTO'
,estimate_percent=>50, cascade=>true);
Statistics are vital for database performance. If there are problem queries, one should analyze the execution plans throughly and tryout the statistics options suitable for those - case by case.
Jul 31, 2009
Finding Stale Statistics in Oracle Database
Statistics are very important to generate efficient execution plans and thus directly related to database performance. We have nightly maintenance window, which runs for an hour, each alternate day.
As we are growing very fast, the 1 hour window sometimes can't cover all the tables and indexes.
Here is how we could find the stale/obsolete statistics - data for those objects have been modified more than 10%.
How to find?
SQL> SET SERVEROUT ON
SQL> DECLARE
ObjList DBMS_STATS.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'PROD', objlist=>ObjList, options=>'LIST STALE');
FOR i IN ObjList.FIRST..ObjList.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/
In my case, it were showing a couple of tables -
CRITICAL_INCIDENT - TABLE
EXTRA_TEMPLATE - TABLE
MODULE_ASSIGNMENT - TABLE
How to fix?
Simple - by collecting statistics for those objects -
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'PROD', tabname=>'EXTRA_TEMPLATE', estimate_percent=>50, cascade=>true);
More:
* The important view from where we could find the stale information and number of modifications done on a table is USER_TAB_MODIFICATIONS
* Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
As we are growing very fast, the 1 hour window sometimes can't cover all the tables and indexes.
Here is how we could find the stale/obsolete statistics - data for those objects have been modified more than 10%.
How to find?
SQL> SET SERVEROUT ON
SQL> DECLARE
ObjList DBMS_STATS.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'PROD', objlist=>ObjList, options=>'LIST STALE');
FOR i IN ObjList.FIRST..ObjList.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/
In my case, it were showing a couple of tables -
CRITICAL_INCIDENT - TABLE
EXTRA_TEMPLATE - TABLE
MODULE_ASSIGNMENT - TABLE
How to fix?
Simple - by collecting statistics for those objects -
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'PROD', tabname=>'EXTRA_TEMPLATE', estimate_percent=>50, cascade=>true);
More:
* The important view from where we could find the stale information and number of modifications done on a table is USER_TAB_MODIFICATIONS
* Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.
SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
Jul 28, 2009
Database Link and Public Database Link
Database link is a useful thing that I use time to time mostly to compare small tables among databases. We have both way (master-master configuration) replication in place, so sometimes, I need to check few things after small release to make sure some tables are in sync.
I thought, it is worth writing few words about this.
What is database link?
Officially, a database link is a schema object in one database that enables access to other objects on another database. I would like to say - it is kind of 'a connection cable' that establish connections between databases.
Side rule: If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.
In SQL statements in source database, we can refer to a table or view on the other destination database by appending @dblink_name (at sign then db link name) to the table or view name.
How to create database link?
For example, we want to access a destination database which is known as DX01.
CREATE DATABASE LINK DX01.DOMAIN.NET CONNECT TO prod IDENTIFIED BY xyz USING 'DX01';
Here,
* DX01.DOMAIN.NET is the db link name
* prod is the destination schema and xyz is the pass to access that. So we would give access through 'prod' schema on destination server.
* DX01 is the service name for the destination database, which should be found in tnsnames.ora file on the source database.
Now, I can compare tables and also issue other commands to access tables on DX01 remote database using the database link.
SQL> SELECT COUNT(*) FROM LOGIN@DX01.DOMAIN.NET;
Also, to compare tables if those are in sync or not -
SQL> SELECT * FROM LOGIN
MINUS
SELECT * FROM LOGIN@DX01.DOMAIN.NET;
Who owns the database link?
By default, with the create syntax shown above, the database user who is creating database link will own this link - no other user will be able to use this.
To get information of the existing database links in a database, need to query the following view -
SQL> SELECT * FROM DBA_DB_LINKS;
Public database link
There may be cases where I want to create only one database link and would like to use the link for all the schema in source database. Public database link is the answer. The PUBLIC key word is specified to create a public database link available to all users.
CREATE PUBLIC DATABASE LINK DX01.DOMAIN.NET USING 'DX01';
Now, for example, HR schema can use this link, PROD schema can use this link and others too in source database.
Side note: To create a public database link, we will need the CREATE PUBLIC DATABASE LINK system privilege.
I thought, it is worth writing few words about this.
What is database link?
Officially, a database link is a schema object in one database that enables access to other objects on another database. I would like to say - it is kind of 'a connection cable' that establish connections between databases.
Side rule: If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.
In SQL statements in source database, we can refer to a table or view on the other destination database by appending @dblink_name (at sign then db link name) to the table or view name.
How to create database link?
For example, we want to access a destination database which is known as DX01.
CREATE DATABASE LINK DX01.DOMAIN.NET CONNECT TO prod IDENTIFIED BY xyz USING 'DX01';
Here,
* DX01.DOMAIN.NET is the db link name
* prod is the destination schema and xyz is the pass to access that. So we would give access through 'prod' schema on destination server.
* DX01 is the service name for the destination database, which should be found in tnsnames.ora file on the source database.
Now, I can compare tables and also issue other commands to access tables on DX01 remote database using the database link.
SQL> SELECT COUNT(*) FROM LOGIN@DX01.DOMAIN.NET;
Also, to compare tables if those are in sync or not -
SQL> SELECT * FROM LOGIN
MINUS
SELECT * FROM LOGIN@DX01.DOMAIN.NET;
Who owns the database link?
By default, with the create syntax shown above, the database user who is creating database link will own this link - no other user will be able to use this.
To get information of the existing database links in a database, need to query the following view -
SQL> SELECT * FROM DBA_DB_LINKS;
Public database link
There may be cases where I want to create only one database link and would like to use the link for all the schema in source database. Public database link is the answer. The PUBLIC key word is specified to create a public database link available to all users.
CREATE PUBLIC DATABASE LINK DX01.DOMAIN.NET USING 'DX01';
Now, for example, HR schema can use this link, PROD schema can use this link and others too in source database.
Side note: To create a public database link, we will need the CREATE PUBLIC DATABASE LINK system privilege.
Jul 19, 2009
jdbc driver bug in 10g for batch updates
Recently we have discovered that, our batch updates not working for larger batch size. Then came to know that jdbc does not support batch size more than 65535 in 10g for the following versions-
The operations simply ignores rows after 65535. There was no errors for this types of serious problem!
The problem has been fixed in the versions -
10.2.0.2 Patch 3 on Windows Platforms 10.2.0.3 (Server Patch Set)
The operations simply ignores rows after 65535. There was no errors for this types of serious problem!
The problem has been fixed in the versions -
Apr 10, 2009
Reclaiming unused LOB space
There may be situation where big chunks of LOB data got cleaned up/deleted.
Few days ago, we had an issue with the LOB replication with our replication software. Then we tested DMLs time to time on the table containing LOB. Although, finally we deleted all the test data, it resulted occupying 39 GB space on the tablespace where we have the LOB data.
col sname format a30
col tab format a30
col mb format 99,999,999
set pagesize 100
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052400C00003$$ TEST_DATA 39,670
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$$ USER_IMAGE 9,313
I knew, I had only 1 MB of data on my TEST_DATA table at the moment.
SQL> SELECT SUM(DBMS_LOB.getlength(RESULT_DATA))/1024/1024 MB
FROM TEST_DATA;
MB
----------
1
Approach -1:
Then the following command had been used to shrink the unused space and reset the HWM. This feature is available from Oracle 10g.
SQL> ALTER TABLE TEST_DATA MODIFY LOB (RESULT_DATA) (SHRINK SPACE);
--- This may take few hours depending on the amount of unused space.
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$ USER_IMAGE 9,313
SYS_LOB0000052400C00003$$ TEST_DATA 1
I have reclaimed 39669 MB of space!
To shrink other non-lob tables -
ALTER TABLE test SHRINK SPACE;
Approach - 2:
Another approach to reclaim unused space might be moving the object to another tablespace. This is how the HWM could be reset.
SQL> alter table table_name move lob(lob_column) store as (tablespace tablespace_name);
We have to rebuild all the indexes associated to the table as those will be unusable after moving.
One thing, shrinking is allowed only for those segments which use Automatic Segment Space Management.
Few days ago, we had an issue with the LOB replication with our replication software. Then we tested DMLs time to time on the table containing LOB. Although, finally we deleted all the test data, it resulted occupying 39 GB space on the tablespace where we have the LOB data.
col sname format a30
col tab format a30
col mb format 99,999,999
set pagesize 100
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052400C00003$$ TEST_DATA 39,670
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$$ USER_IMAGE 9,313
I knew, I had only 1 MB of data on my TEST_DATA table at the moment.
SQL> SELECT SUM(DBMS_LOB.getlength(RESULT_DATA))/1024/1024 MB
FROM TEST_DATA;
MB
----------
1
Approach -1:
Then the following command had been used to shrink the unused space and reset the HWM. This feature is available from Oracle 10g.
SQL> ALTER TABLE TEST_DATA MODIFY LOB (RESULT_DATA) (SHRINK SPACE);
--- This may take few hours depending on the amount of unused space.
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$ USER_IMAGE 9,313
SYS_LOB0000052400C00003$$ TEST_DATA 1
I have reclaimed 39669 MB of space!
To shrink other non-lob tables -
ALTER TABLE test SHRINK SPACE;
Approach - 2:
Another approach to reclaim unused space might be moving the object to another tablespace. This is how the HWM could be reset.
SQL> alter table table_name move lob(lob_column) store as (tablespace tablespace_name);
We have to rebuild all the indexes associated to the table as those will be unusable after moving.
One thing, shrinking is allowed only for those segments which use Automatic Segment Space Management.
Mar 29, 2009
Oracle Technology Solutions - go beyond the database server!
Oracle changed it's identity!
Years ago, there was a perception - the words "Oracle" and "Oracle Database" were being used interchangeably. This is not the case anymore - we should remember that :-)
Today, I attended a whole day seminar organized for the Oracle partners in Bangladesh. There were three guys from Oracle Corporation conducted those five back to back sessions.
The sessions were basically based on the following five main product lines -
1. Oracle Technology Infrastructure
2. Oracle Fusion Middleware
3. Enterprise Security
4. Business Process Management and
5. Oracle Enterprise Management Solutions
Mr. Jonathan Tan, Senior Solutions Manager, Oracle Fusion Middleware, conducted the session on middleware architectures and various product lines Oracle provides. He also covered the Business Process Management products fits to Enterprise a.
Mr. Mazhar Ali, Sotutions Manager, SAGE West, covered a number of topics on Oracle Technology Infrastructures, Enterprise Security and Oracle Enterprise Management Solutions. The sessions were very informative and gave a very good sense about the variety of product lines for Enterprise solutions.
I found a number of interesting things to look at further -
* Information Lifecycle Management (ILM)
* Information Rights Management (IRM)
* Application Diagnostics for Java (AD4J)
* Application Testing Suite (ATS)
* SQL Performance Analyzer (SPA)
* Oracle Real Application Testing
* Oracle Coherence
And a lot more! So, Oracle is not just the database provider - rather it got all the products from the bottom to top on the stack of Enterprise product lines.
More information is available on Oracle biz site -
http://www.oracle.com/
Years ago, there was a perception - the words "Oracle" and "Oracle Database" were being used interchangeably. This is not the case anymore - we should remember that :-)
Today, I attended a whole day seminar organized for the Oracle partners in Bangladesh. There were three guys from Oracle Corporation conducted those five back to back sessions.
The sessions were basically based on the following five main product lines -
1. Oracle Technology Infrastructure
2. Oracle Fusion Middleware
3. Enterprise Security
4. Business Process Management and
5. Oracle Enterprise Management Solutions
Mr. Jonathan Tan, Senior Solutions Manager, Oracle Fusion Middleware, conducted the session on middleware architectures and various product lines Oracle provides. He also covered the Business Process Management products fits to Enterprise a.
Mr. Mazhar Ali, Sotutions Manager, SAGE West, covered a number of topics on Oracle Technology Infrastructures, Enterprise Security and Oracle Enterprise Management Solutions. The sessions were very informative and gave a very good sense about the variety of product lines for Enterprise solutions.
I found a number of interesting things to look at further -
* Information Lifecycle Management (ILM)
* Information Rights Management (IRM)
* Application Diagnostics for Java (AD4J)
* Application Testing Suite (ATS)
* SQL Performance Analyzer (SPA)
* Oracle Real Application Testing
* Oracle Coherence
And a lot more! So, Oracle is not just the database provider - rather it got all the products from the bottom to top on the stack of Enterprise product lines.
More information is available on Oracle biz site -
http://www.oracle.com/
Mar 21, 2009
Oracle User Profile and Resource Management
Sometimes we don't notice that there is something called 'User Profile' used to manage user resources and other important things. I would like to say in simple words - 'User profile' is some system parameters specific to a user.
The good use of profile could be password management for the user.
What is the profile assigned to a user?
When we create a database user, the default profile is assigned automatically.
SQL> SELECT x.username, x.profile
2 FROM dba_users x
3 WHERE username='SCOTT'
4 ORDER BY x.username;
USERNAME PROFILE
------------- -----------------------------------------------
SCOTT DEFAULT
For example, how many time(s) a database user/schema can attempt with incorrect login/password before the account got locked.
What are there in default profile?
SQL> SELECT p.resource_name, p.limit
2 FROM dba_users u, dba_profiles p
3 WHERE u.profile=p.profile
4 and u.username = 'SCOTT'
5 ORDER BY p.resource_name;
RESOURCE_NAME LIMIT
--------------------- ---------------------------
COMPOSITE_LIMIT UNLIMITED
CONNECT_TIME UNLIMITED
CPU_PER_CALL UNLIMITED
CPU_PER_SESSION UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PRIVATE_SGA UNLIMITED
SESSIONS_PER_USER UNLIMITED
How to create a profile?
SQL> CREATE PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 5;
If this new profile is assigned to 'SCOTT' user, the account will be valid for 10 days and the user could open 5 concurrent sessions.
SQL> ALTER USER SCOTT PROFILE TEMP_10_DAYS_USER;
The detailed Oracle document could be found here -
How to change a profile?
SQL> ALTER PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 10;
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm
The good use of profile could be password management for the user.
What is the profile assigned to a user?
When we create a database user, the default profile is assigned automatically.
SQL> SELECT x.username, x.profile
2 FROM dba_users x
3 WHERE username='SCOTT'
4 ORDER BY x.username;
USERNAME PROFILE
------------- -----------------------------------------------
SCOTT DEFAULT
For example, how many time(s) a database user/schema can attempt with incorrect login/password before the account got locked.
What are there in default profile?
SQL> SELECT p.resource_name, p.limit
2 FROM dba_users u, dba_profiles p
3 WHERE u.profile=p.profile
4 and u.username = 'SCOTT'
5 ORDER BY p.resource_name;
RESOURCE_NAME LIMIT
--------------------- ---------------------------
COMPOSITE_LIMIT UNLIMITED
CONNECT_TIME UNLIMITED
CPU_PER_CALL UNLIMITED
CPU_PER_SESSION UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PRIVATE_SGA UNLIMITED
SESSIONS_PER_USER UNLIMITED
How to create a profile?
SQL> CREATE PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 5;
If this new profile is assigned to 'SCOTT' user, the account will be valid for 10 days and the user could open 5 concurrent sessions.
SQL> ALTER USER SCOTT PROFILE TEMP_10_DAYS_USER;
The detailed Oracle document could be found here -
How to change a profile?
SQL> ALTER PROFILE TEMP_10_DAYS_USER LIMIT PASSWORD_LIFE_TIME 10 SESSIONS_PER_USER 10;
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6010.htm
Mar 17, 2009
Oracle Exadata Servers - For high performance database computing..
I had an opportunity last week to meet and discuss with one of the Oracle gurus and also top level Oracle professional Mr. Christopher G. Chelliah, Senior Director & Chief Architect, Asia Pacific Strategic Customers. He was visiting Dhaka for a business trip, I think.
Mr. Christopher gave a nice overview of Exadata - the high performance storage servers. He described the architectural design and some fringe benefits of the new product. I went there on behalf of another company (which is a sister concern of my present company) - we work in a national project. We were evaluating the high performance server for the probable National ID Databases.
Exadata is a complete hardware and software solution that Oracle targets for high performance data warehouses. This is a solution for both server and storage.
There are basically two components of Exadata.
1. The Server
2. The Storage
The server is clustered RAC, configured with multiple nodes, 8 nodes for us I think. The storage consists of 16 cells primarily, all these build the storage system which is basically another 16 database servers altogether!
The cool feature is, the database intelligence is distributed in server side and also in storage. Most of the data filtering are done on storage and the actual results are sent to server. It offloads the unnecessarily data transfer overheads of the network and also less processing for the actual server.
Some fringe benefits/features I marked are -
* Query predicate offloading feature
* Smart scan processing on storage server
* Reduced load on database servers - processings are distributed to storage side
* Optimized I/O and disk functionality
* Reliable hardware
* RAC with 8 nodes cluster for high performance and reliability
* High speed up and down link - 1 Gbps I think
* ASM for transparent storage management
And a lot more!
The technical stuffs could be found in Oracle's business site -
http://www.oracle.com/database/exadata.html
Mr. Christopher gave a nice overview of Exadata - the high performance storage servers. He described the architectural design and some fringe benefits of the new product. I went there on behalf of another company (which is a sister concern of my present company) - we work in a national project. We were evaluating the high performance server for the probable National ID Databases.
Exadata is a complete hardware and software solution that Oracle targets for high performance data warehouses. This is a solution for both server and storage.
There are basically two components of Exadata.
1. The Server
2. The Storage
The server is clustered RAC, configured with multiple nodes, 8 nodes for us I think. The storage consists of 16 cells primarily, all these build the storage system which is basically another 16 database servers altogether!
The cool feature is, the database intelligence is distributed in server side and also in storage. Most of the data filtering are done on storage and the actual results are sent to server. It offloads the unnecessarily data transfer overheads of the network and also less processing for the actual server.
Some fringe benefits/features I marked are -
* Query predicate offloading feature
* Smart scan processing on storage server
* Reduced load on database servers - processings are distributed to storage side
* Optimized I/O and disk functionality
* Reliable hardware
* RAC with 8 nodes cluster for high performance and reliability
* High speed up and down link - 1 Gbps I think
* ASM for transparent storage management
And a lot more!
The technical stuffs could be found in Oracle's business site -
http://www.oracle.com/database/exadata.html
Subscribe to:
Posts (Atom)