Some time ago I had two questions about fixed objects statistics for which I couldn’t quickly find the answers. Questions are:
At the time of asking these question I’ve read CBO development team blog post on the topic, but still I was unable to answer them distinctly. Well, it appears the questions are simple and could probably be deduced after careful reading (first two questions for sure). The core phrase is:
The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks
Question – How can you have a single file in a single tablespace showing multiple free extents when there are no objects using any space in that file ? For example, from an 184.108.40.206 database:
SQL> select 2 * 3 from user_free_space 4 where 5 tablespace_name = 'TEST_8K' 6 order by 7 file_id, block_id 8 ; TABLESPACE_N FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------ ---------- ---------- ---------- ---------- ------------ TEST_8K 3 128 1048576 128 3 TEST_8K 3 256 1048576 128 3 TEST_8K 3 384 1048576 128 3 TEST_8K 3 512 130023424 15872 3 4 rows selected.
The answer in this case is simple – here’s what I did just before running my query:
I spent yesterday neatening up a few old articles. For the most part it is a bit of a dull process, but it has to be done every so often.
I was doing a little research for an upcoming presentation on Oracle 12c Adaptive Optimization and I came across a series of posts on Dynamic Sampling by Randolf Geist (one of my favorite Oracle smart guys). I could not find a complete list of the series of posts either through Google or the search function on the site where they were posted. I guessed at a couple of the url’s and got lucky. Thought it might be useful at some point in the future so I wrote them down on my blog (which is where I often keep private notes). But decided it might be useful to others so decided to go ahead and publish it.
Thank you for attending the Exadata Snapper (ExaSnapper) hacking session!
I have split the recording of this session into 3 pieces and uploaded to enkitec.tv. The ExaSnapper v0.7 beta that I demoed is also available now at enkitec.com (registration needed). See the links below.
For quick reference, here’s the syntax of running ExaSnapper – there are two modes, one is the before/after capture (think Tom Kyte’s runstats, but for exadata metrics) and the other is more like a DBA-monitoring mode, where you can just measure a few seconds worth of a long-running query runtime and get the IO and efficiency figures from there.
Here’s an excerpt from the install script documentation section:
By some strange coincidence, the “London Bus” effect perhaps, there have been three posts on the OTN database forum in the last couple of days relating to deadlocks; and they have prompted me to indulge in a little rant about the myth of Oracle and deadlock detection; it’s the one that goes:
“Oracle detects and resolves deadlocks automatically.”
Oracle may detect deadlocks automatically, but it doesn’t resolve them, it simply reports them (by raising error ORA-00060 and rolling back one statement) then leaves the deadlocked sessions stuck until the session that received the report resolves the problem or an external agent resolves the problem.
Consider the following example (which, I have to admit, I wrote without access to a live instance):
This blog entry is about modern Linuxes. In other words RHEL6 equivalents with 2.6.3x kernels and not the ancient RHEL5 with 2.6.18 kernel (wtf?!), which is the most common in enterprises unfortunately. And no, I’m not going to use kernel debuggers or SystemTap scripts here, just plain old “cat /proc/PID/xyz” commands against some useful /proc filesystem entries.
Here’s one systematic troubleshooting example I reproduced in my laptop. A DBA was wondering why their find command had been running “much slower”, without returning any results for a while. Knowing the environment, we had a hunch, but I got asked about what would be the systematic approach for troubleshooting this – already ongoing – problem right now.
Luckily the system was running OEL6, so had a pretty new kernel. Actually the 2.6.39 UEK2.
A customer of mine run out of memory due to much server processes of dedicated connected client sessions. As an alternative I tried to explain the options between DEDICATED and SHARED SERVER concepts as an initial attempt/workaround for the client software problems. Looking around on the internet for pictures and or newbie documentation, I found …
If you already downloaded snapper v4, then better re-download it again as the v4.03 also runs in SQL Developer!
Snapper used to require access to DBMS_LOCK, so it could sleep for X seconds between the “before” and “after” performance data snapshots. Now it is possible to get away without using DBMS_LOCK. Instead you will run Snapper twice, once for taking the “before” snapshot, then run your workload and then run Snapper again for taking the “after” snapshot and print the output.
So, the usual way of running snapper is this:
@snapper4 all 5 1 152
This would take 1 5-second performance snapshot SID 152′s V$ views.
With Snapper4 you can use the old way or just add BEGIN or END keywords to the 1st parameter, like this: