DBA Blogs

Fragmentation

Tom Kyte - Sat, 2017-10-21 08:46
Can tables which only experience inserts and updates and not delete be fragmented.
Categories: DBA Blogs

Join with where clause

Tom Kyte - Sat, 2017-10-21 08:46
Hi there, I have an example below which I'm not sure how oracle execute the where clause. Select a.id, b.column_A, b.column_B, b.column_C from A left join B on a.id = b.id where b.column_C = 'Yes' My question is that do oracle execute the...
Categories: DBA Blogs

Are results from SELECT with no order by and with ROWNUM predicate used in WHERE condition stable?

Tom Kyte - Sat, 2017-10-21 08:46
Consider the following simple scenario: PREPARATIONS: 1) Let's say we have a table USERS with one COLUMN NAME: create table USERS(NAME varchar(100)); 2) Let's put some values there: insert into USERS(name) values('User1'); insert into USERS...
Categories: DBA Blogs

difference of explain plan, autotrace and tkprof

Tom Kyte - Sat, 2017-10-21 08:46
I have confusion about explain plan, autotrace and tkprof. When explain plain showing to us the prediction about how optimizer will work to produce result and tkprof show the real thing that happen, so how about autotrace. In autotrace there are two ...
Categories: DBA Blogs

About BLOB and BFILE

Tom Kyte - Sat, 2017-10-21 08:46
Hi Tom. We are designing an office automation application and we want to store image of letters and documents.Our organization is very large (it has 100 departments) and We have to use replications. Now we want to know is BLOB a proper type for sto...
Categories: DBA Blogs

Getting RMAN-05502 when trying to duplicate from backup

Tom Kyte - Sat, 2017-10-21 08:46
I have very little experience with Oracle. We are a SQL Server shop with one legacy Oracle 11.2 database that serves an obscure but critical function. As part of our testing routine, this database needs to be copied from our production instance into ...
Categories: DBA Blogs

Submitted two talks to Collaborate 18

Bobby Durrett's DBA Blog - Fri, 2017-10-20 19:52

I submitted my two talks to Collaborate 18 through IOUG. These are the same two that I submitted to RMOUG Training Days. Hopefully one of the talks will get accepted at one of the two conferences but I don’t know. The nice thing is that they are both professional development talks and a user group meeting might be more open to that sort of talk than a vendor (i.e. Oracle OpenWorld) conference. But, there is a lot of competition out there so we will see. Hopefully I will get to put some of my new Toastmaster skills to work at a conference early next year. 🙂

Bobby

Categories: DBA Blogs

ORA-04091: table is mutating, trigger/function may not see it

Tom Kyte - Fri, 2017-10-20 14:26
Hi, I am getting "ORA-04091: table is mutating, trigger/function may not see it" in my trigger. Please find the test case details as follows: <code>create table test(bug_number number, subject varchar2(50),ANALYZED_BY varchar2(50)); insert...
Categories: DBA Blogs

Neat and optimized code

Tom Kyte - Fri, 2017-10-20 14:26
I need the logic to derive the direction and line hour direction is Inbound if either one of org and dest or both falls under the jrny_in list and the rt list direction is outbound if either one of org and dest or both falls under the jrny_out ...
Categories: DBA Blogs

Performance - Index tablespaces with block size greater than data tablespace ?

Tom Kyte - Fri, 2017-10-20 14:26
Hi, I would like to know if it would it be good practice to create index tablespaces with block size larger than the data tablespace? Recently, a performance consultant company guided us to maintain indexes on tablespace with block size of 16K,...
Categories: DBA Blogs

Change a DBMS_SCHEDULER job's next run date

Tom Kyte - Fri, 2017-10-20 14:26
I have a job scheduled via DBMS_SCHEDULER. It is scheduled to run weekly. It has been running fine for some time now. All I want to do is alter the next_run_date. I cannot find any way to do this in the documentation. I can alter the original start d...
Categories: DBA Blogs

Dynamically Create Database Link

Tom Kyte - Fri, 2017-10-20 14:26
I have several scripts that are hard wired when creating a database link and all works well. The time has come though to take them to the next level and expand the user community. To do that the CREATE DATABASE LINK statement needs to be dynamicall...
Categories: DBA Blogs

Receive http requests with more than 32k

Tom Kyte - Fri, 2017-10-20 14:26
Hi Tom, on more question :) I'm using the utl_http package to exchange xml-data between different oracle databases. Until now i've requested the mod_plsql (until 11g) or the apache (from 12c) on the other database, posted the data with utl_http....
Categories: DBA Blogs

audsid

Tom Kyte - Fri, 2017-10-20 14:26
I am trying to track down the source (ie. machine and program) of a SQL that is erroring out with an ORA-00600. When the session produces a trace file, it identifies the session using the SID, serial# combination : *** SESSION ID:(1346.55751) 2017-10...
Categories: DBA Blogs

error ORA-01422 in select query

Tom Kyte - Fri, 2017-10-20 14:26
Hi, I am facing "ORA-01422: exact fetch returns more than requested number of rows" in my plsql procedure. Here are the details of test I am trying: create table cust_bug_metadata(product_id number, component varchar2(50),sub_component varchar...
Categories: DBA Blogs

Allocation memory for varchar2 index

Tom Kyte - Thu, 2017-10-19 20:06
Hello! I have read much information about allocation memory for varchar2 as pl\sql variable and column of table. So now we have memory problem (assign awr report) with a big report. Can u explain please: 1) type StringTable is table of varchar2(320...
Categories: DBA Blogs

Alternative for SQLPlusW

Tom Kyte - Thu, 2017-10-19 20:06
Hi All, I've been reading a lot on the internet and am searching for a replacement for sqlplusw.exe. The problem is that I've written a lot of scripts and with sqlplusw.exe you could set a path by reading in an sqlfile. The solutions I've seen sofar...
Categories: DBA Blogs

Java Oracle ETL using CursorExpressions

Tom Kyte - Thu, 2017-10-19 20:06
Hi, I am attempting to export a large amount of data from multiple separate tables from Oracle 11 into a NoSQL database via a Java app utilising JDBI. The data is being read from the following tables: store, store2, staff and product. The final...
Categories: DBA Blogs

How to write a SQL to join with multiple tables and select results in a query from any on of the table (like in queue)

Tom Kyte - Thu, 2017-10-19 20:06
Hi , I have following tables . I am getting orders data from various sources in table test_orders. Then joining the items to various table for revenue percentage and then send the orders along with revenue information to another 3rd party tool for re...
Categories: DBA Blogs

Python sortedcontainers has me thinking

Bobby Durrett's DBA Blog - Thu, 2017-10-19 16:23

I was looking at the Python sortedcontainers package and it got me thinking. It is a long convoluted story and I am not sure that I can explain it clearly in a short blog post. I tried to explain all this to my wife in the last few minutes as we were driving up to a friend’s house last night and I’m sure it was confusing the way I explained it. But, I’m hoping that I can capture some of the ideas that I have thought about in a way that will be useful to others.

I have studied some computer science topics that do not directly relate to my work with Oracle databases and my review of the sortedcontainers implementation documentation tied together several things that I have studied and related them back to my work with Oracle performance tuning. I have not tested sortedcontainers to make sure that it does everything the web site says it does. But, I think it is the best Python package for doing balanced tree type of structures in memory. An AVL tree or B-tree keeps data ordered so you can quickly search for a range of key values and get them out in sorted order. Normal Oracle indexes are a type of B-tree but on disk with blocks cached in memory when queries access them. AVL trees are binary trees so each node points to at most 2 children. B-tree nodes can have many children. Sortedcontainers seem to work like a balanced tree with 1000 or so max children per node. I think it makes efficient used of Python’s built-in list objects. It seems to work well with caching within the CPU. I have not carefully reviewed the theory and tested all this out to prove that it is right but it seems likely that it is. I think it seems convincing because it ties back to other computer science topics that I have studied and to my experience with Oracle performance tuning.

I have been slowly working through an algorithms class on MIT’s OCW website. I am on a section about AVL trees. So, I was looking around at AVL trees in Python. I noticed that Rosetta Code had an AVL tree topic but no Python example until I added one. I also looked around on PyPI for an AVL tree Python package. Based on my search, I thought that bintrees was the most mature, but its web page has a note saying “Use sortedcontainers instead”. So, that made me think that sortedcontainers was the best balanced tree option in Python. The algorithms class talks about how to prove that you can work with AVL trees in O(log n) time. The sortedcontainers performance documentation has a complex explanation of its big O complexity. Also, I think that my class will discuss some of the concepts used in the sortedcontainers analysis in future lessons. So, that motivates me to go forward.

The assembly language book that I worked through helped me understand how to write programs that run faster because they make better use of the x86-64 processor’s cache and registers. Its creator seems to have designed sortedcontainers with CPU caches in mind. Right or wrong, in my mind this ties back to memory caches that affect Oracle database performance. How much of Oracle tuning relates back to how systems cache database blocks in RAM and where? You have the database block cache of course. You also have operating system filesystem cache which you might bypass with direct I/O. You may have high-speed memory cache within your SAN’s storage server. I don’t know about today but in the past disk controller cards and even disk drives themselves had memory caches. You might say, joking, that “cache is king” in database performance. At least, you have to say it is important to understand when and where database systems cache disk blocks in memory to understand why you are getting the performance you are seeing.

So, I guess my mind connected sortedcontainers with my algorithms class and assembly language book. I also connected sortedcontainers back to Oracle performance tuning. It makes me feel that digging into some computer science training is not a waste of time. It helps me to step back from Oracle database specific study and get a little theory. Also, my database work is focusing more and more on the x86-64 architecture and the Linux platform so looking at computer science on the same platform that I use for work has clear benefits.

So, I’m concerned that I have not made this post helpful to people who read it. Is it just about my experience or does it have a point for other people? Maybe the point is that it can’t hurt for an Oracle DBA to learn some computer science. Maybe you are like me and studied C.S. in school many years ago. Maybe you have learned Oracle on the job and don’t have a C.S. background. Maybe the message for both of us from my story about sortedcontainers and my “Aha!” moment is that there are benefits to studying a little computer science even if it does not directly relate to your job. There is only so much time in an Oracle DBA’s day and you can’t learn everything, but maybe it is worth putting some time into learning some C.S. theory if you can.

Bobby

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs