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

Partner Webcast - Big Data Appliance and Exalytics

Join us to learn more about Oracle Systems, Engineered Systems and Storage. Learn what we understand by “Cloud Insurance” through our Systems Webcast Series for EMEA...

We share our skills to maximize your revenue!
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

Datascape Podcast: Episode 16 – Recapping Oracle OpenWorld 2017

Pythian Group - Thu, 2017-10-19 08:26

Today we’re going to recap some of the most interesting concepts from the recently completed Oracle OpenWorld 2017 and to help us navigate this I have invited Simon Pane back to the show. Simon is an accomplished Principal Consultant, who has developed a multitude of complex solutions for Pythian clients. He leverages his understanding of the industry and technologies such as Oracle, SQL Server, Linux, Oracle Cloud, AWS and more, to propose timely solutions that best suit the needs of clients. Also joining us for the first time is Ivica Arsov. Ivica is an Oracle Certified Master 12c and 11g, and a recognized member of the Oracle ACE Program as an Oracle ACE Associate. He is a blogger and active contributor to the Oracle community and presents at many technology conferences. Known for his deep Oracle expertise and ability to troubleshoot quickly and efficiently, Ivica has the skills to solve problems quickly regardless of size and complexity. Keep listening to hear more!


Key points from this episode:

  • Simon tells us more about his career at Pythian and the work he is doing with Oracle.
  • Ivica shares with us on the work he is doing with Oracle while working at Pythian.
  • The atmosphere at OOW this year.
  • The common theme of automation and security.
  • Competitive threats versus security threats.
  • The new version of the Oracle database, 18c.
  • Improvements in reduction of danger surrounding patching by balancing risk.
  • The new autonomous feature and the NRX guarantee.
  • Elasticity and automatic scaling.
  • DBA’s focusing on tasks specific to the business in the world of automation.
  • The trade-off of simplicity versus index.
  • Machine learning being introduced with cyber security.
  • New features coming out with 18c and 19c releases and architecture versions.
  • Understanding how customers are at the mercy of vendors.
  • Certifications bundled to a number of versions.
  • Industry problems around tools and security assessment.
  • Hear more about Ivica and some of his favorites in the lightning round.
  • Simon tells us more about the UK Oracle User Group Conference taking place.
  • And much more!

Links Mentioned in Today’s Episode:

Oracle OpenWorld 2017
Simon Pane
Ivica Arsov
Pythian
Amazon 
Microsoft
Oracle
Microsoft Azure
Dataguard
Rac
The book, Transaction Processing: Concepts and Techniques
UK Oracle User Group

Categories: DBA Blogs

Dynamic SQL in CURSOR

Tom Kyte - Thu, 2017-10-19 01:46
Hi, I am trying to create a procedure that will display logs. It has an IN parameter which is the table nam, the cursor will SELECT data based on that parameter. I cannot compile my procedure. Hope you can help me. :) <code>CREATE OR REPLACE PROCE...
Categories: DBA Blogs

Instead of Triggers - where to use?

Tom Kyte - Thu, 2017-10-19 01:46
Dear Mr. Tom, 1) First of all when I tried creating an INSTEAD OF Trigger it gave me ORA-00439: feature not enabled: error Kindly explain why so....? 2) Where exactly would u advise me to use Instead Of trigger ...
Categories: DBA Blogs

How to enforce conditional unique on multiple columns

Tom Kyte - Thu, 2017-10-19 01:46
Hi, Tom, I have a table create table project (project_ID number primary key, teamid number, job varchar2(100), status number(1)); status=1 means it is an active project, otherwise it is archiv...
Categories: DBA Blogs

Merging 55 millions of records along with hash key

Tom Kyte - Thu, 2017-10-19 01:46
Hi Tom, I need to load my table with 55 millions of records using Merge statement 1. Those 55 millions records are being fetched from many different tables. For example T1,T2,T3 etc. 2. i have a date table from where i fetch business weeks ba...
Categories: DBA Blogs

Calling a linked procedure with out cursor parameter in another procedure

Tom Kyte - Thu, 2017-10-19 01:46
Hello, I need to have a procedure with out cursor parameter which I am calling it via a db link, inside a procedure, how can I implement that? procedure Myouterprocedure(..., my_out_cursor out cursor, ...
Categories: DBA Blogs

sqlcl command line tool warning

Tom Kyte - Wed, 2017-10-18 07:26
Hi I am using sqlcl quite a while and constantly get this null pointer exception. Any Idea Apr 15, 2016 9:28:22 AM oracle.dbtools.plusplus.JDBCHelper getOH WARNING: oracle.dbtools.plusplus.JDBCHelper.getOH(JDBCHelper.java:83) java.lang.NullP...
Categories: DBA Blogs

Merge statement

Tom Kyte - Wed, 2017-10-18 07:26
Hi Tom, consider this: create table test ( id number(10), username varchar2(100), reason varchar2(100), timestamp_ number(10), cnt number(10), CONSTRAINT test_pk primary key (id) ); insert into test(id,username,reason,timestamp_,cn...
Categories: DBA Blogs

Optimize Query

Tom Kyte - Wed, 2017-10-18 07:26
I want to optimize the following query SELECT t.merchant_id, t.brand_id, t.transaction_type, t.trns_currency_code, <b>COUNT (*) AS total_no_tx, SUM (t.mv_is_trnx_approved) AS approved_no_tx, ...
Categories: DBA Blogs

DDL for tables and constraints

Tom Kyte - Wed, 2017-10-18 07:26
Team, I am using SQL Developer 17.3 and SQLCL 17.3 for this demo. while doing DDL from SQLCL we got this <code> demo@ORA11G> show ddl STORAGE : ON INHERIT : ON SQLTERMINATOR : ON OID : ON SPECIFICATION : ON TABLESPACE : ON SIZE_BYTE_K...
Categories: DBA Blogs

Is there a way I can press a button on home page just after logging automatically in apex application

Tom Kyte - Wed, 2017-10-18 07:26
Hi, I have an apex application https://apex.oraclecorp.com/pls/apex/f?p=32888:1:4894003287217::::: and I want to click Search button on home page automatically every time once a user login to this application. I need to do this to submit the val...
Categories: DBA Blogs

Select and duplicates

Tom Kyte - Wed, 2017-10-18 07:26
Hello, I'd like to remove duplicates from ma table let say V1 is the key column. the key should be max 5 digits as a length Example: V1 01254T 50300 50300W 45662 Here I should keep the first one (it has no duplicates) , ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs