DBA Blogs

I'm trying to fetch required data from a billion record table using the row number approch (i.e, select "ingest_ts_utc" from (SELECT to_char(sys_extract_utc(systimestamp), 'YYYY-MM-DD HH24:MI:SS.FF') as "ingest_ts_utc" ,ROWNUM as rno from XYZ.ABC ) ...

TeamTOM, I'm trying to use Sql Loader to load a text file into a normal Oracle table (as opposed to an external one). Unfortunately I only have insert rights to the table via a non default role. If I were just doing a simple insert statement in sq...

I have a schema called HCR_SCHEMA, in which I have one table called TEST_EMP, and I have a user say USER_A and granted him select on the TEST_EMP table. I connected as USER_A as tried to execute SELECT * FROM TEST_EMP. Obviosuly it threw me error ...

we have a following definition TYPE response_t IS RECORD ( doc XMLTYPE ); A local variable is defined with that record type lv_resp response_t; I want to print the value this local variable has stored.I tried ...

This has been giving me fits. I?m running 12.2.0.1 with no maintenance. In the test case below I create a view TEST_VIEW with three columns A,B,C returning one row. Both select from the view and export of the view work as expected. I ...

Oracle Exadata Database Machine has been critical for the digital transformation of many companies. But in many cases Oracle Exadata customers have to use multiple tools for monitoring,...

We share our skills to maximize your revenue!

Hello, Ask Tom Team. I am using a Microsoft SSIS ETL Package to insert data to Oracle (source and destination). I want to do an incremental load every day but I have to flag the rows that are already loaded in the destination. It is not just s...

Hi everyone, I have a problem with DB Link. I'm trying to create a dblink from and Oracle 12c (installed on Linux Server) and Oracle 11.2 R2 (installed on Windows Server). I've correctly created the dblink, but when i try to query the result is: "<b...

Hi Tom, I am just trying to understand if the table function will work with collections. Please find my below code. Script: <code>create table setm_students (id number, name varchar2(10)); create table setm_students_target as select * fro...

I would like to fetch data after joining 3 tables and sorting based on some field. As this query results into approx 100 records, I would like to cut the result set into 4, each of 25 record. and I would like to give sequence number to each r...

Hey Tom, First of all, I'm sorry if this has been asked already, I simply couldn't find an answer after days of googling. Here it goes: I've created a SP to load a text file into an external table on 10g as per instructed here and many more places...

Can you recommend best practices for keeping passwords hidden in command lines for impdp/expdp, shell scripts etc.?

Hi, I have been using Object View, User Defined Types in Oracle for sometime. I tried to follow the approach here. <code>https://docs.oracle.com/cd/A64702_01/doc/server.805/a58241/ch_ov.htm</code> I am wondering if we put all the data manipul...

We observed frequent wait event 'enq HW - contention' in performance reports with SQL includes LOB Objects. Lob object Details ? Object - WF_WORKFLOW Owner - IBMUCD What changes we can face if we convert LOB from basic to secure?...

I have three possible places where accounts data can be requested. On the MONTHLY USAGE, HISTORY USAGE or ENROLLMENT tables. The same account(s) could be on all three tables, or on the table(S) multiple times, but some accounts could be on one or ...

Tom, I was reading your book expert one-on-one and in Chapter 3: Locking and Concurrency, you have mentioned a scenario of lost update. I was trying to simulate that and I did not succeed in doing the test. I tried to update emp table in session...

Hello, I have the following data: Id AMAOUNT NO_PRATITION ID_STRATEGY 1 100 99 XXX 2 200 99 XXX 3 0 99 YYY 4 100 99 YYY 5 200 99 YYY 6 0 99 ZZZ 7 100 99 ZZZ 8 200 99 ...

Hi Tom, I came across a scenario today. table1 id integer name varchar table2 id integer name varchar designation varchar table3 id integer name varchar relation_status varchar I have the query as below which is working fine ...

I am learning about MySQL performance tuning. I read the Performance Schema chapter of the MySQL 5.7 manual and I have a MySQL 5.7.20 database running under Linux. I have a test table and I am running a simple query that does a full scan of the table and returns the sum of a couple of columns. The goal was to look at some Performance Schema tables to see what they would tell me about this simple query. I bounce the MySQL database service before running my test script so that the query pulls data from disk and not memory. A zip of my test script and its output is here: zip

I looked at the tables that had this pattern events_*_history_long. These are the four tables:

• events_statements_history_long
• events_transactions_history_long
• events_stages_history_long
• events_waits_history_long

There seems to be a hierarchical relationship among these tables something like this:

• statements->transactions
• statements->stages->waits->waits

There may be other relationships that my simple test did not capture. If you look at the zip you will see all the levels of the hierarchy that I explored but, in this case, the bottom level seems the most interesting. Here is the query that explores the bottom of the statements->stages->waits->waits path:

select * from events_waits_history_long
where
NESTING_EVENT_ID in 
(select EVENT_ID from events_waits_history_long
where
NESTING_EVENT_ID in 
(select EVENT_ID from events_stages_history_long
where
NESTING_EVENT_ID in 
(select EVENT_ID from events_statements_history_long
where SQL_TEXT like concat('%te','st%'))
and NESTING_EVENT_TYPE = 'STATEMENT')
and NESTING_EVENT_TYPE = 'STAGE')
and NESTING_EVENT_TYPE = 'WAIT'
order by EVENT_ID;

The output reminds me of a trace in Oracle. It shows the individual disk reads and waits for internal locks and mutexs.

+-----------+----------+--------------+-------------------------------------------+-----------------+---------------+---------------+------------+-------+---------------+-------------------------------+------------+-------------+-----------------------+------------------+--------------------+----------------+-----------------+-------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME                                | SOURCE          | TIMER_START   | TIMER_END     | TIMER_WAIT | SPINS | OBJECT_SCHEMA | OBJECT_NAME                   | INDEX_NAME | OBJECT_TYPE | OBJECT_INSTANCE_BEGIN | NESTING_EVENT_ID | NESTING_EVENT_TYPE | OPERATION      | NUMBER_OF_BYTES | FLAGS |
+-----------+----------+--------------+-------------------------------------------+-----------------+---------------+---------------+------------+-------+---------------+-------------------------------+------------+-------------+-----------------------+------------------+--------------------+----------------+-----------------+-------+
|        28 |    11162 |        11162 | wait/io/file/innodb/innodb_data_file      | fil0fil.cc:5778 | 5744476160960 | 5744479752316 |    3591356 |  NULL | NULL          | /var/lib/mysql/bobby/test.ibd | NULL       | FILE        |       139648405042752 |              203 | WAIT               | read           |           16384 |  NULL |
|        28 |    11163 |        11163 | wait/synch/mutex/innodb/buf_pool_mutex    | buf0lru.cc:1320 | 5744479934370 | 5744479974470 |      40100 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66655496 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11164 |        11164 | wait/synch/mutex/innodb/buf_pool_mutex    | buf0buf.cc:5150 | 5744480619278 | 5744480646546 |      27268 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66655496 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11165 |        11165 | wait/synch/sxlock/innodb/hash_table_locks | buf0buf.cc:5153 | 5744480749202 | 5744480858274 |     109072 |  NULL | NULL          | NULL                          | NULL       | NULL        |              70197752 |              203 | WAIT               | exclusive_lock |            NULL |  NULL |
|        28 |    11166 |        11166 | wait/synch/mutex/innodb/fil_system_mutex  | fil0fil.cc:1032 | 5744481202332 | 5744481236016 |      33684 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66654712 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11167 |        11167 | wait/io/file/innodb/innodb_data_file      | fil0fil.cc:5778 | 5744481464586 | 5744485206718 |    3742132 |  NULL | NULL          | /var/lib/mysql/bobby/test.ibd | NULL       | FILE        |       139648405042752 |              203 | WAIT               | read           |           16384 |  NULL |
|        28 |    11168 |        11168 | wait/synch/mutex/innodb/buf_pool_mutex    | buf0lru.cc:1320 | 5744485374336 | 5744485415238 |      40902 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66655496 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11169 |        11169 | wait/synch/mutex/innodb/buf_pool_mutex    | buf0buf.cc:5150 | 5744485590876 | 5744485618144 |      27268 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66655496 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11170 |        11170 | wait/synch/sxlock/innodb/hash_table_locks | buf0buf.cc:5153 | 5744485730424 | 5744485815436 |      85012 |  NULL | NULL          | NULL                          | NULL       | NULL        |              70197624 |              203 | WAIT               | exclusive_lock |            NULL |  NULL |
|        28 |    11171 |        11171 | wait/synch/mutex/innodb/fil_system_mutex  | fil0fil.cc:1032 | 5744486328716 | 5744486357588 |      28872 |  NULL | NULL          | NULL                          | NULL       | NULL        |              66654712 |              203 | WAIT               | lock           |            NULL |  NULL |
|        28 |    11172 |        11172 | wait/io/file/innodb/innodb_data_file      | fil0fil.cc:5778 | 5744486586960 | 5744490523176 |    3936216 |  NULL | NULL          | /var/lib/mysql/bobby/test.ibd | NULL       | FILE        |       139648405042752 |              203 | WAIT               | read           |           16384 |  NULL |
|
... edited for length...

Most of the time seems to be taken up on reads from a file, which is what I expected. If you look at the wait/io/file/innodb/innodb_data_file waits they seem to be 16384 byte reads from the file associated with the table. I could use this information to build a query to show a profile of the time spent by the query based on EVENT_NAME. It would be kind of like a tkprof of an Oracle trace.

This post is just a simple first test. I am new to MySQL tuning so feel free to leave a comment or email me if you have suggestions or corrections related to this post.

Bobby

Please help understand the meaning of the param <b>expiration_secs</b> in <code>sys.dbms_lock.allocate_unique</code> Does it represent the time till which the named DB lock stays on, if not unlocked/commit/rollback by the same session? https:/...