Extended Character Data Type Columns In this release Oracle changed the maximum sixe of three data types In Oracle 12c if you set a VARCHAR2 to 4000 bytes or less it is stored inline, if you set it to more than 4000 bytes then it is transformed in extended character data type and stored out [...]
The aim of this post is not to explain how the APPROX_COUNT_DISTINCT function works (you find basic information in the documentation and in this post written by Luca Canali), but to show you the results of a test case I run to assess how well it works.
Here’s what I did…
I created a table with several numerical columns (the name of the column shows how many distinct values it contains), loaded 100 million (more...)
- Enterprise Manager Base Platform - Grid Control (OMS) 10.2.0.5.10 PSU.
- Enterprise Manager Base Platform - Grid Control (OMS) 184.108.40.206.11 PSU.
- Enterprise Manager Base Platform - Cloud Control (OMS) 220.127.116.11.1 PSU.
In addition to improving read I/Os, Oracle Exadata Write back flash cache also provides the ability to cache write I/Os directly to PCI flash. Exadata storage software version 18.104.22.168.1 is the minimum version required to use write back flash cache. Grid infrastructure and database homes must run 22.214.171.124.9 or later to use with Write-back Smart […]
However we ran into one problem the other week. I noticed that the test3 database was still in physical standby (more...)
Fellow Phoenicians (citizens of the Phoenix, Arizona area):
This is a reminder that tomorrow is the first meeting of the newly reborn (risen from the ashes) Arizona Oracle User Group. Here are the meeting details: url
I hope to meet some of my fellow Phoenix area DBAs tomorrow afternoon.
Oracle DataPump Enhancements Full Transportable Export and Import of Data In Oracle 12c you now have the possibility to create full transportable exports and imports. A full transportable export contains all objects and data needed to create a copy of the database. To create a fully transportable export of your database you need to specify [...]
The post OCP 12C – DataPump, SQL*Loader, External Tables Enhancements appeared first on Oracle DBA Scripts and Articles (more...)
A recent question on the OTN forum asked about narrowing down the cause of deadlocks, and this prompted me to set up a little example. Here’s a deadlock graph of a not-quite-standard type:
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00040001-000008EC-00000000-00000000 50 249 X 48 9 X TX-000A001F-000008BC-00000000-00000000 48 9 X 50 249 S
My session (the one that dumped the trace file) is 249, and I was blocked (more...)
Oracle 12c (version 126.96.36.199) offers the option of using in-memory processing to speed things along. Called the In-memory option it’s installed when you install the 188.8.131.52 software. Using it can make a considerable difference in processing speed, provided you have sufficient resources (RAM) available. Let’s revisit an older example, on Bloom filters, and see if Oracle processes things any faster in-memory.
Looking again at the Bloom filter example using (more...)
Online Partition operations Table Partitions and subpartitions can now be moved online. [crayon-5447caf7d0323514534927/] Compression options can also be added during an online partition move. [crayon-5447caf7d034e673459253/] Reference Partitioning Enhancements Truncate or Exchange Partition with Cascade option With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when [...]
I have way too many professional certifications for any reasonable semi honest human being. Along with two old Solaris admin certifications 7 and 8 I am ( besides an MBA degree ha ha ) OCP in 7.3 / 8.0 / 8i / 9i / 10g. At one point a ways back I was teaching all the OCP curriculum for a local community college (more...)
Here’s a zip of a script I modified today: zip
Here’s an example output:
QUERY_NUM SQL_ID PLAN_HASH_VALUE EXECUTIONS AVG_ELAPSED OPTIMIZER_COST AVG_FETCHES AVG_SORTS AVG_DISK_READS AVG_BUFFER_GETS AVG_ROWS AVG_CPU AVG_IOWAIT AVG_DIRECT_WRITES AVG_PHYS_READS AVG_PHYS_WRITES ---------- ------------- --------------- ---------- ----------- -------------- ----------- ---------- -------------- --------------- ---------- ---------- ---------- ----------------- -------------- --------------- 1 gxk0cj3qxug85 2051250004 39504258 31630.2852 15 4.71993394 .444248288 4.07294381 440.124393 41.3960784 3447.83137 28056.5262 0 .006406626 0 1 gxk0cj3qxug85 548353012 24360619 (more...)
by Craig Martin
So from my last post Brendan Furey pointed out one minor detail.. that my code was seriously flawed. It only returned the results from one of the criteria found. Picky, picky :) So this is my last effort.. Back up to 12 buffers, but still works when you change the parameters:
select /*+ ordered index_ffs(j job_id_pk) use_nl(jh) use_nl(e) use_nl(e2) use_nl(e3) use_nl(d) use_nl(l) */ distinct l.location_id, l.city from dual left outer (more...)
The SQL Mini-Challenge is now closed. The judges will be Kyle Hailey, Tim Gorman, and Iggy Fernandez. The winner will be announced in the fall issue of the NoCOUG Journal.
Correctness is the primary screening criterion that will be used by the judges. Submissions are expected to produce the same results as the original query even if the data changes. The following script has been constructed to test submissions:
update employees set first_name='Stevie', last_name='Wonder', hire_date=sysdate, job_id='AD_VP', department_id=50 (more...)
Table Enhancements Oracle 12c offers you to create invisible columns, these columns are not visible until you explicitly mention their names in the SQL statement. This functionnality allows developpers to make change to the database without conflicting with the existing application. To create an invisible column: [crayon-544665ad911af900442543/] You can’t create invisible columns on : External [...]
ADR enhancements In oracle 12c the Automatic Diagnostic Repository contains a new log directories with 2 subdirectories : DDL Debug The DDL log When you active the DDL logging in Oracle 12c using enable_ddl_logging=true, Oracle writes all DDL operations into the specific DDL log instead of writting it to the alert log. To enable DDL logging [...]
Emergency Monitoring Emergency monitoring is meant for extreme circumstances where it’s impossible for you to connect to the database because the database is hung. Emergency monitoring allows you to connect to the database in diagnostic mode and run a lightweight analysis to see what’s happening. You can access real-time performance data from ASH and access [...]
A recent posting on OTN reminded me that I haven’t been poking Oracle 12c very hard to see which defects in reporting execution plans have been fixed. The last time I wrote something about the problem was about 20 minhts ago referencing 184.108.40.206; but there are still oddities and irritations that make the nice easy “first child first” algorithm fail because the “depth” calculated by Oracle doesn’t match the “level” that you would get from (more...)