Read and monitor Oracle related blogs and news sources, all in one place.
Create table with NOLOGGING... not generate redo log (just generate redo log for data dictionary)
On DML INSERT/UPDATE/DELETE ... Oracle generated redo log on nologging mode not difference... on logging mode.
APPEND hints & sql(nologging) on table "LOGGING" mode "select logging from dba_tables" (NO) ... not difference (generate redo)If "alter table nologging" before, and then insert (append)So, Oracle will generate a minimal number of redo log... with INSERT /*+ APPEND */ when table be nologging ... select logging from dba_tables (NO)
$ time olsnodesAnd then ... solve this by delete * files at ORA_CRS_HOME/log/hostname/client/ path.
node01
node02
real 0m0.153s
user 0m0.082s
sys 0m0.068s
$ rm -f $ORA_CRS_HOME/log/node01/client/*What about idea?
$ time olsnodes
node01
node02
real 0m0.037s
user 0m0.017s
sys 0m0.014s
$ ls $ORA_CRS_HOME/log/node01/client/*
css.log
$ time olsnodes
node01
node02
real 0m0.045s
user 0m0.016s
sys 0m0.015s
$ ls $ORA_CRS_HOME/log/node01/client/*
css1.log css.log
When use "olsnodes" command successfully, it scans $CRS_HOME/log/hostname/client PATH and generates a new cssxxx.log file.metalink:729349.1 ...said it's a bug:
Olsnodes produces CPU spikes with many css*.log files in ORA_CRS_HOME/log/nodename/client directory
fixed in 11.1.0.7 and 10.2.0.4 CRS bundle Patch #2 onwards. Bundle Patch information is available in Note 756671.1
$ crsctl query crs softwareversionOK... No problem on 11.1.0.7
Oracle Clusterware version on node [test01] is [11.1.0.7.0]
$ ls $ORA_CRS_HOME/log/test01/client/ | wc -l
32738
$ time olsnodes
test01
test02
real 0m0.030s
user 0m0.015s
sys 0m0.014s
Tyler Muth has a useful addition to the date functions I have published here before.
If you ever want to know how long ago a date is and you want to display it in ‘human readable’ format you (and I) could use his function.
Like this:
select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;
select date_text_format(sysdate - 120) the_date from dual;
select date_text_format(sysdate - 365) the_date from dual;
--------------------------------------------------------------------
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago
One more for the toolbox.
Related posts:
Related posts brought to you by Yet Another Related Posts Plugin.
Related posts:
Related posts brought to you by Yet Another Related Posts Plugin.
) This has happened many times in recent months and lo-and-behold, ADDM either formed the same correct conclusions in a fraction of the time people had been 'analysing' things or found things that the initial analysis hadn't. (If that makes me sound slow, remember that ADDM has already done the job and maybe took a minute)select task_name, execution_end from dba_advisor_tasks where advisor_name='ADDM' and status='COMPLETED' and owner='SYS' order by execution_end desc;
select DBMS_ADVISOR.GET_TASK_REPORT('<task_name from previous query>', 'TEXT',
'TYPICAL', 'ALL', 'SYS')
from dual;
After doing my seminar in Spain last month, one of the attendees sent me a secret note about what the CBO would look like in Oracle 12g. Apparently it’s re-engineered from ground and completely different from anything we’ve seen before. It goes beyond being automatic, intelligent and auto-tuning, it’s actually edible too!
The New CBO
Thanks to John Ospino for sending me this insider-info ;)
Why is it, every time I fly on United Airlines they have oversold the flight? I have a seat allocated already so I’m OK, but there are a bunch of people here who have paid, but there is no seat for them.
They are now offering a night in a hotel, food and a first class flight tomorrow. No doubt the same will happen on that flight.
I have an amazing idea, why not count the seats on your plane and only sell that number of tickets you bunch of donkies!
The joys of traveling… 
Cheers
Tim…
The conference finished yesterday at about midday, so I went into town with Chris Muir and Marcel Kratochvil to check out the aquarium. I managed not to get sunburnt this time. Later we met up for some food and basically chilled out. I’ve added some more photos here.
I leave for San Francisco airport in a few minutes, then I have to look forward to an 11 hour flight, 2 hour wait, then a 1 hour flight. Lovely.
Thanks to the ladies and gents from OTN and the ACE program for getting me here and thanks to ODTUG for putting on a cracking conference. Not surprisingly, there was no mention of the new Oracle games console, which we are not allowed to mention…
Doing conferences is always a strange experience. They are a little daunting before you start, quite exciting while they are happening, then I get a post-conference lull. It’s like your brain says, “enough is enough”, and you close down for a few days. Having to travel home doesn’t help much.
Cheers
Tim…
<—- Blogroll Report 12/06/09 – 19/06/09
Here are technical highlights from Oracle Blogosphere with my keywords,
1- ORA-38029 for index creation
Randolf Geist – Locked table statistics and subsequent create index
2- How to start RAC instance in non-RAC mode
Chandra Pabba – ORA-29702 – Starting RAC instance in non-rac mode
3- How to set cardinality for pipelined and table functions
Adrian Billington – setting cardinality for pipelined and table functions
4- How to format index tree dump by SQL and RegEXP
Dion Cho – Simple formatting on index tree dump
5- Restrictions for aggregate materialized views
Rob Van Wijk – Fast refreshable materialized view errors, part three: aggregate MV’s
6- dbms_stats.copy_table_stats min/high value alteration bug
Amit Bansal – dbms_stats.copy_table_stats does not alter low/high value
7- Automatic top subheap dumping with heapdump
Tanel Poder Oracle memory troubleshooting, Part 3: Automatic top subheap dumping with heapdump
8- Views in Explain Plan output – no_merge
Jonathan Lewis – Explain View
9- Importance of Size for Autoextend on Next
Hemant K Chitale - AUTOEXTEND ON Next Size
10- Partition pruning problem for MIN/MAX of partition key column
Jeff Moss – No pruning for MIN/MAX of partition key column
11- ORA-02070 with VPD and DBMS_SCHEDULER
Amardeep Sidhu – DBMS_SCHEDULER, DBMS_RLS and SYS_CONTEXT
12- How to check RAC option in Oracle Binary
Surachart Opun – check RAC Option in Oracle Binary
13- Diagnosing paralel query and interconnect performance on RAC with UDPSNOOP
Riyaj Shamsudeen – RAC, parallel query and udpsnoop
Oracle Forums
1- Cache Fusion and Past Image
http://forums.oracle.com/forums/thread.jspa?threadID=917350
2-CBC Latch and Buffer Busy wait on same table.
http://forums.oracle.com/forums/thread.jspa?threadID=917866
3-Latch problem
http://forums.oracle.com/forums/thread.jspa?threadID=918767
This week I got to look at a system where a new version of an application had been rolled out. I came across a SQL statement that had taken a turn for the worse in terms of its average elapsed time. It was using an index I hadn’t noticed before, so I took a look to see if the index was new (it was - apparently it was added as part of the code roll out). So I got to wondering if there were any other statements that had suffered a backward movement in performance. So I wrote this little script called whats_changed.sql. It’s the same basic idea as one I wrote about earlier here called unstable_plans.sql. Both of these scripts are based on a calculated variance. The original unstable_plans script uses variance in average elapsed times for statements with multiple plans (i.e. it shows statements that have multiple plans which exhibit a significant difference in average elapsed time between plans). The whats_changed.sql script shows statements that exhibit significant differences in average elapsed time before and after a point in time (i.e. when you rolled out new code, or added an index, etc…) A short example may explain it better than I can:
$ !sql sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 25 22:24:35 2009 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> @whats_changed Enter Days ago: 3 Enter value for min_stddev: Enter value for min_etime: Enter value for faster_slower: SQL_ID EXECS AVG_ETIME_BEFORE AVG_ETIME_AFTER NORM_STDDEV RESULT ------------- ------------ ---------------- --------------- ------------- ------ an9114vtxukz2 17 1.25 5.00 2.1148 Slower 803u6vwz0ah1p 29 0.04 0.18 2.1552 Slower bvf3fxv3hatw7 2,390 0.03 0.14 2.4147 Slower 5sgs7q9pjnzg5 2 2.21 0.42 3.0130 Faster b0zkuv9qtcvnn 48 0.16 0.85 3.1500 Slower 9ws5c6p77d384 1,852 0.57 3.30 3.3609 Slower dy98ajwqc8s2x 15 1.32 8.34 3.7558 Slower bkka22vb5y7t6 596 0.19 1.26 3.8804 Slower 6qfwkqgvjkn2q 12 0.51 3.60 4.2641 Slower 05xcf43d9psvm 1,197 0.02 0.14 5.4263 Slower 8wgmc9w5ubtsy 183 0.49 4.32 5.4946 Slower fwfpuf1vfwfu2 6 0.02 0.22 5.6314 Slower 50c18dwvxq4sb 222 0.54 5.29 6.1586 Slower aukanfjd3d8fa 3 0.88 10.00 7.3496 Slower 44bq4r5z2xrsm 54 3.13 43.39 9.0946 Slower 0az7czjdw8z7j 110 0.62 0.02 17.5933 Faster f41agfq9qdhk6 3 0.24 8.06 22.6765 Slower 17 rows selected. SQL> -- or to see just the slower ones SQL> SQL> @whats_changed Enter Days ago: 3 Enter value for min_stddev: Enter value for min_etime: Enter value for faster_slower: Slower SQL_ID EXECS AVG_ETIME_BEFORE AVG_ETIME_AFTER NORM_STDDEV RESULT ------------- ------------ ---------------- --------------- ------------- ------ an9114vtxukz2 17 1.25 5.00 2.1148 Slower 803u6vwz0ah1p 29 0.04 0.18 2.1552 Slower bvf3fxv3hatw7 2,390 0.03 0.14 2.4147 Slower b0zkuv9qtcvnn 48 0.16 0.85 3.1500 Slower 9ws5c6p77d384 1,852 0.57 3.30 3.3609 Slower dy98ajwqc8s2x 15 1.32 8.34 3.7558 Slower bkka22vb5y7t6 596 0.19 1.26 3.8804 Slower 6qfwkqgvjkn2q 12 0.51 3.60 4.2641 Slower 05xcf43d9psvm 1,197 0.02 0.14 5.4263 Slower 8wgmc9w5ubtsy 183 0.49 4.32 5.4946 Slower fwfpuf1vfwfu2 6 0.02 0.22 5.6314 Slower 50c18dwvxq4sb 222 0.54 5.29 6.1586 Slower aukanfjd3d8fa 3 0.88 10.00 7.3496 Slower 44bq4r5z2xrsm 54 3.13 43.39 9.0946 Slower f41agfq9qdhk6 3 0.24 8.06 22.6765 Slower 15 rows selected. |
A couple of comments on the script:
And a brief explanation of what’s in the output:
So the above output indicates that only a few statements are significantly worse (avg. elapsed time 2X or greater) since our new code rollout, which occurred about 3 days ago. Statement 44bq4r5z2xrsm obviously sticks out with a new average elapsed time of 43+ seconds and about 9 times slower than before. This was the one that had picked up the new index. The rest were primarily due to increased physical i/o caused by some newly introduced statements which were doing a few million lio’s per execution and thus causing all the other statements to suffer slightly.
Let me know what you think. Your comments are always welcome.
Welcome to the 151st edition of Log Buffer, the weekly review of database blogs. We’re going to take a fast tour through the best blogs from the week gone by, beginning this time, with Oracle.
Jonathan Lewis writes, “It occurred to me recently that I might be making casual use of terms that weren’t necessarily very well known to the less experienced user. So I’ve decided to build a glossary of terms – and I’ll try to add to it from time to time whenever I have a few minutes.”
Jonathan might want to add “Method R” to the glossary. Cary Millsap was making it understood, as he shows in Profiling with my Boy: “Today I’m going to raise the stakes, because yesterday I think I explained Method R so that an eleven year-old could understand it.”
Vivek Sharma offers a thorough look at the Cost-Based Optimizer: Inefficient Input yields Inefficient Output. Vivek begins, “Cost Based Optimizer has always been a mystery for most of the Database Administrators and Developers. . . . Cost Based Optimizer has improved a lot in previous few versions. Therefore, it can be said that CBO is still undergoing some enhancements. With these enhancements, Oracle Users have accepted the fact that the Application Performance might degrade after version upgrade . . . ”
Randolf Geist had some info to share on the matter of locked table statistics and subsequent create index. “ . . . in 10g and later index statistics are generated along with an index creation . . . so a newly created index usually has computed statistics. 10g also introduced the option to lock table statistics. Now if you lock statistics in 10g in later . . . and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command.”
Rob van Wijk gave us part three of his series on fast refreshable materialized view errors: aggregate MV’s. “In the third part I’m going to examine all restrictions for aggregate materialized views, as described in the documentation. . . . So this will be quite a lengthy and even tedious post, as you can imagine by the list above … but for a good cause.”
In MySQL blogs, the MySQL Performance Blog announced Percona.tv. Ryan Loew writes, “We’ll be uploading technical screencasts, conference video, and anything else cool we can think up.”
The Open Query blog published Good Practice/Bad Practice: Table Aliases. “When writing queries, try making a habit out of using short table aliases, no matter how small the query is.” An example using the World DB follows, as does a worthwhile discussion.
Roland Bouman examined MySQL stored functions: the impact of DECLARE HANDLER on performance, responding to Get the error return value in a variable by Peter Gulutzan.
Ronald Bradford looked into using statpack with SHOW STATUS. He writes: “Mark Leith, on of the MySQL Support Team managers wrote some time ago a very nice utility I use often called Statpack. . . . Over time I’ve grown to love it’s simplicity, but notice a number of shortcomings. . . . This post is more about detailing those little annoyances that I’d like to improve, or see improved.”
Morgan Tocker writes, “I think 5.1 gets some bad press for not being a compelling upgrade. It’s not the big features that make the difference, but the subtle ones. I wanted to highlight some of the these that may make your life easier when it’s time to upgrade . . . ” The post is Hidden gems in 5.1.
Let’s turn to SQL Server now, starting with a post from Aaron Alton, The HOBT. He writes, “If you’ve been working with SQL Server for any length of time, you’ve probably seen a number of different variations of the UPDATE command. . . . Anyway, today’s blog post is more like a public service announcement. It seems that all too many developers are still dangerously unaware of the ability to create very ambiguous UPDATE statements in TSQL.” Click through for Tricky Updates: Weird, Wacky, and Wonderful UPDATE Syntax Variations in SQL Server.
Louis Davidson looked into the question of read/write ratio versus read/write ratio. That’s what it says, really.
Alexander Kuznetsov posted one of those skill-testing quizzes—calculating third Wednesday of the month with inline UDFs. Alexander begins, “Problems such as calculating third Wednesday of the month or the last day of the month are very common. Recently Peter Larsson posted a scalar UDF that solves problems such as ‘third Wednesday of the month’, and Uri Dimant added a comment with a solution by Steve Kass. Unfortunately, all these solutions use scalar UDFs and as such are very slow, so I decided to post some very fast inline UDFs that I find very useful.”
Linchi Shea also was working the paradoxes: Performance impact: Logical scan fragmentation — 100% may not be as bad as 85%. “In [a] previous post I asked whether 100% logical scan fragmentation is always worse than 85% local scan fragmentation for table/index scans. . . . The answer is no. 100% logical scan fragmentation is not always worse than 85% logical scan fragmentation in terms of table or clustered index scans. And the following is an example.”
Reading is cool. I learned this from the Fonz. But look at this, Jeremiah Peschka says there are circumstances when less reading is a good thing. I’m confused! What else does he say? “Less reading is a good thing when you’re reading from a physical disk.” Ahhh. “During some recent performance tuning, I stumbled across a little gem of a stored procedure that was producing between 4,000 and 11,000 physical reads every time it was called. . . . It returns a list of state abbreviations. I’m not proud to say that I wrote it, but I have to come clean: I wrote it. So, how did I fix this travesty of my youthful ignorance?” I bet he hits his server machine in just the right way.
In PostgreSQL blogs, David Wheeler and his readers discussed the pgTAP Set-Testing Update.
The Postgres OnLine Journal exposed a restore of functional indexes gotcha.
In the DB2 world, Craig Mullins advised: know your ISOLATION levels. Craig writes, “Did you know that DB2 provides a way to change the way that a program or SQL statement acquires locks? That way is known as the isolation level and it can be set to specify the locking behavior for a transaction or statement.”
Willie Favero published the second of a couple articles collecting resources for both DB2 z/OS and DB2 LUW.
That’s all for now. Please add your favourite blogs from this week to the comments. And of course, don’t forget that you can publish an edition of Log Buffer on your own blog. Just send me an email to get started.
Till next time!
RSS
Email