LIQY@XE> declare
2 begin
3 insert into t1 values (100);
4 dbms_output.put_line(SQL%ROWCOUNT);
5 COMMIT;
6 dbms_output.put_line(SQL%ROWCOUNT);
7 end;
8 /
1
0
PL/SQL procedure successfully completed.
In 8i the output is the same , however behavior in 10g & 11g is as above.
I think it is related to COMMIT.
Saturday, September 05, 2009
Tuning at the meeting
Near the end of business hour, my manager called me to attend a meeting related to slowness of data warehouse project.
without any info provided, during the briefing from the Vendor's DBA, lots of figure almost make me sleep. Maybe I was tired after whole day's hard work.
Finally, I realized :
1. The slowness is in development instead of production.
2. The partition key is not used.
3. The index is chosen. The cost shown in TOAD is 130. good value .?
Gathered more info from in-house developer, almost 1/6 rows are accessed in the partition.
Then was asked PGA, hash hint, nest hint ...
The join is in between a small table and a big partition. Feeling that FULL Table Scan should help.
Advise developer to hit the partition directly . And did a test of select count(*) from t partition(part-name), the elapsed time is about 4minutes.
Force to use FULL hint, took about the same time. However the cost shown to vendors is 500k. He looks not willing to try. instead, trying other hints, however needs to google syntax . I want to leave the meeting room, then I went to toilet.
When I came back, they were trying the FULL hint.
Run 4 concurrent sessions to hit different partitions, took the same timing , with 1k+ rows processed per second.
Application manager was so happy , asked everybody "happy with this ? " Saw some smile.
When I stepped the room, leaving 7 persons there continue testing happily more sessions. 6 Thanks to me, except the vendor's DBA.
Feeling COOL, it was almost 7:30pm.
Stressful if I didn't tune it on time. everybody was looking at me... even GM attended the meeting.
moreover, I use sqlplus defeated the TOAD while checking the execution plan.
without any info provided, during the briefing from the Vendor's DBA, lots of figure almost make me sleep. Maybe I was tired after whole day's hard work.
Finally, I realized :
1. The slowness is in development instead of production.
2. The partition key is not used.
3. The index is chosen. The cost shown in TOAD is 130. good value .?
Gathered more info from in-house developer, almost 1/6 rows are accessed in the partition.
Then was asked PGA, hash hint, nest hint ...
The join is in between a small table and a big partition. Feeling that FULL Table Scan should help.
Advise developer to hit the partition directly . And did a test of select count(*) from t partition(part-name), the elapsed time is about 4minutes.
Force to use FULL hint, took about the same time. However the cost shown to vendors is 500k. He looks not willing to try. instead, trying other hints, however needs to google syntax . I want to leave the meeting room, then I went to toilet.
When I came back, they were trying the FULL hint.
Run 4 concurrent sessions to hit different partitions, took the same timing , with 1k+ rows processed per second.
Application manager was so happy , asked everybody "happy with this ? " Saw some smile.
When I stepped the room, leaving 7 persons there continue testing happily more sessions. 6 Thanks to me, except the vendor's DBA.
Feeling COOL, it was almost 7:30pm.
Stressful if I didn't tune it on time. everybody was looking at me... even GM attended the meeting.
moreover, I use sqlplus defeated the TOAD while checking the execution plan.
Subscribe to:
Posts (Atom)
