10g optimizer

Another day the 10.2.0.1.0 face issues with 10g optimizer

Issue:
Same Query is not slow

Fix:
Put '/*+ RULE */ hints, even thought RULE is no longer supported, this has trully fixed.

Also note same db in office, one verrion 10.2.0.1

Update in parallel mode

Change overview and monitoring

alter table mytable parallel 5;

-- As system:
select pid, status, server_name from v$px_process;


My request for change:

Hi,

Since we are upgrading to v3 for many of our customers, so far Customer_A and Customer_2 and Customer_Big in progress.
One thing clearly stood out was that the script "rel_2_0_4_field_answers_upgrade.sql" took around 1hr for Customer_B, 6hr for Customer_A and a massive 12hr for Customer_Big.

I would like to propose a change to this script (hopefully it will speed up the script without changing any biz-logic), due my lack of knowledge of the application and data model, I am not 100% sure if my change impacts any business logic.

Can someone please take this proposal forward and see if we can go ahead with this, extensive testing required.

Since yesterday, I have done two test runs with Customer_Big (slim db from last Friday, note 7.5 million rows on field_answers), both showed similar results.

Old script took: 4 hour (or 3hr 55min)
New script took: 30 minutes

Please allocate a developer to look into this suggestion, ideally the one who wrote the original script.
For your information this script was our biggest blocker for the Customer_Big migration.

Proposed New Script (using parallel execution):

ALTER TABLE "FIELD_ANSWERS" ADD "ID" VARCHAR2(20);
ALTER TABLE "FIELD_ANSWERS" ADD "TYPE" VARCHAR2(10);

alter table field_answers parallel 5;

UPDATE field_answers set
id='FN' || lpad(keygen('FN'),8,'0'),
type=decode(fk_app_profile,null,decode(fk_hire_profile,null,decode(fk_requestid,null,decode(fk_profile,null,'SAVED_PS','P'),'R'),'PSH'),'PSA')
WHERE id is null;

alter table field_answers parallel 1;


Current Live Script:

ALTER TABLE "FIELD_ANSWERS" ADD "ID" VARCHAR2(20);
ALTER TABLE "FIELD_ANSWERS" ADD "TYPE" VARCHAR2(10);

DECLARE
CURSOR c1 IS SELECT rowid, decode(fk_app_profile,null,decode(fk_hire_profile,null,decode(fk_requestid,null,decode(fk_profile,null,'SAVED_PS','P'),'R'),'PSH'),'PSA') as fatype FROM field_answers where id is null;
faid varchar2(10);

BEGIN
FOR i IN c1 LOOP
BEGIN
faid:='FN' || lpad(keygen('FN'),8,'0');
EXECUTE IMMEDIATE 'UPDATE field_answers set id=''' || faid || ''', type=''' || i.fatype || ''' WHERE rowid = ''' || i.rowid || '''';
END;
END LOOP;

END;
/


WebLogic app to deploy

Run below command to create the war file (nidb-app.war):

jar cvf nidb-app.war test_app.jsp test_db.jsp test_driver.jsp includes/UTF-8.inc WEB-INF/web.xml

WEB-INF/web.xml
< ?xml version="1.0" encoding="UTF-8"?>
< !DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<>
<>nidb_test
<>niDB Test App

<>
<>test_app.jsp
< /welcome-file-list>

< /web-app>



includes/UTF-8.inc
<% response.setHeader("Content-Type", "text/html; charset=UTF-8"); response.setContentType("text/html; charset=UTF-8"); request.setCharacterEncoding("UTF-8"); %>

Date time convert

select to_char(sysdate,'YYYYMMDD HH24:MI:SS') as "Eastern Daylight"
,to_char(new_time(sysdate,'EDT','GMT'),'YYYYMMDD HH24:MI:SS') dual

Adding data to CLOB fileds

Test table to try CLOBS

create table a (
id number primary key,
name varchar2(32),
info clob
);

insert into a values(1, 'Nazrul', 'Notest');
insert into a values(2 'Abu', 'You can add here upto 3999 char via sqlplus');

Good to use LOB function to do CLOB work so you can add upto 4gb of data.

DECLARE
v_text_loc CLOB;
v_offset INTEGER;
v_buffer VARCHAR2(100);
BEGIN
select info into v_text_loc from a where id = 15;
v_offset := DBMS_LOB.GETLENGTH (v_text_loc) + 1;
v_buffer := 'My txt The End.';
DBMS_LOB.WRITE (v_text_loc, length(v_buffer), v_offset, v_buffer);
END;
/

select '*'||info||'*' from a where id = 15;

About the Stars

Abu Qatadah mentioned Allah’s Statement "And We have adorned the nearest heaven with lamps," and said,
"The creation of these stars is for three purposes, i.e. as decoration of the (nearest) heaven, as missiles to hit the devils, and as signs to guide travellers. So if anybody tries to find a different interpretation, he is mistaken and just wastes his efforts, and troubles himself with what is beyond his limited knowledge"

Sahih Al Bukhari Vol 4, Chap 3, P 282.

MySQL groupby functions

Great fetaure of MySQL groupby function GROUP_CONCAT

select concat(DATE_FORMAT(date, '%a %d/%m/%Y'),' ',time,';',GROUP_CONCAT(total_gateways SEPARATOR ';'))
from tbl_concurrent_users where company_name = 'rabobank2'
group by date, time,company_server order by date,time

Good link to reference: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html