Category Archives: How-to

How to Clear the Slow Query Log on AWS RDS MySQL/ MariaDB

Tags :

Category : How-to

Get Social!

Here is a super simple little tip for clearing the mysql.slow_log in MySQL or MariaDB when running an RDS on Amazon AWS. 

For more information on AWS RDS, please see: https://aws.amazon.com/codedeploy/

Unfortunately the usual approach of simply DELETing or TRUNCATING data from the table doesn’t work due to a permission error. This is true, even for the AWS created master database user.

Error Code: 1044. Access denied for user 'masteruser'@'%' to database 'mysql'

Luckily, the Amazon AWS team have put together a package that clears out the table for us. 

CALL mysql.rds_rotate_slow_log;

EXECUTE IMMEDIATE MySQL

Tags :

Category : How-to

Get Social!

Unlike recent versions of Maria DB, MySQL does not currently support the command EXECUTE IMMEDIATE.

Essentially EXECUTE IMMEDIATE is shorthand for perparing a statement, executing a statement and then finally deallocating the prepaired statement.

To get round this limitation in MySQL you can create a stored procedure that wraps up the commands required to execute a statement into a procedure so that you can call it as a one-liner.

CREATE PROCEDURE execute_immediate(IN query MEDIUMTEXT)
	MODIFIES SQL DATA
	SQL SECURITY DEFINER
BEGIN
	SET @q = query;
	PREPARE stmt FROM @q;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END

You can then call the procedure as a one-liner like this:

CALL execute_immediate('QUERY GOES HERE');

Run Multiple Bash Commands In Parallel

Tags :

Category : How-to

Get Social!

Bash, whilst great for simple things, can be tricky to use more advanced programming techniques that are easily exposed in things like Java, or Go.

Multithreading is one such problem. I often find myself with a series of tasks to perform that I’d like to run in parallel up to a predefined concurrency threshold.

My recent task which I’ll use as an example was to run multiple curl commands against an endpoint. These commands were standalone in the fact that they could be executed in any order and would benefit from running several API calls at once.

The first step is to create your list of commands in a file. For this, I’ll use the echo and sleep commands to demonstrate.

vi /tmp/myCommands

echo 1 && sleep 2
echo 2 && sleep 2
echo 3 && sleep 2
echo 4 && sleep 2
echo 5 && sleep 2
echo 6 && sleep 2
echo 7 && sleep 2
echo 8 && sleep 2
echo 9 && sleep 2
echo 0 && sleep 2

Once you have your list of commands, it’s time to run them!

cat /tmp/myCommands | while read n; do printf "%q\n" "$n"; done | xargs --max-procs=2 -I LC bash -c LC

The first command cat /tmp/myCommands is simply the path to your list of commands to run. The only other part to worry about is the —max-proxcs=2 attribute of xargs – this is what defines the concurrency and therefore how many ‘threads’ will run at once. xargs will do the rest – each command in your source file will be executed with 2 running at once!

So there you have it – threaded command execution in Bash!

Oracle ORA-02287: sequence number not allowed here

Tags :

Category : How-to

Get Social!

I’ve recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error:

ORA-02287: sequence number not allowed here

I can see why that might not be allowed – each time you select from the view you’ll get a new sequence number making it a bit daft for most circumstances.

I say most because I’ve had a need recently; ETL logic at my current client is held in database views with a boilerplate set of code that wraps around that to perform the table load. That’s fine until you want to fabricate ID’s (SIDs) as part of the load.

Solution

The solution here was to create a utility package to wrap the sequence that could be referenced in the view. See the below example code:

CREATE SEQUENCE seq_test 
  MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
   l_nextval NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
         INTO l_nextval;

   RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS 
SELECT   func_seq_test('seq_test') seq_id
,        dummy
FROM     dual
/

You can now query the view without any pesky ORAs.

SELECT * FROM view_seq_test
/

    SEQ_ID D
---------- -
         1 X

1 row selected.

See the test script here.

Oracle View Sequence Test

Tags :

Category : Supporting Scripts

Get Social!
CREATE SEQUENCE seq_test 
  MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
   l_nextval NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
         INTO l_nextval;

   RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS 
SELECT   func_seq_test('seq_test') seq_id
,        dummy
FROM     dual
/
SELECT * FROM view_seq_test
/
DROP SEQUENCE seq_test
/
DROP FUNCTION func_seq_test
/
DROP VIEW view_seq_test
/

 

Explain Why An Oracle Database Materialized View Does Not Rewrite

Tags :

Category : How-to

Get Social!

Using Oracle Database materialized views for query rewrite, when used in the right way, can really help aid performance with specific queries. It’s one of my favorite ways to quickly help increase performance of specific queries – often queries issued by front end reporting tools.

Materialized views, however, can be finicky in which queries they re-write, if any and the optimiser output doesn’t give away many clues.

Luckily, help is at hand with a small amount of setup and the DBMS_MVIEW.EXPLAIN_REWRITE package.

Setup

First, create the table to store the output created by the DBMS_MVIEW.EXPLAIN_REWRITE package:

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message 
  measure_in_msg        VARCHAR2(30),   -- Measure in current message 
  join_back_tbl         VARCHAR2(30),   -- Join back table in message 
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used 
  reerved2              VARCHAR2(10))   -- currently not used;

Rewrite output

Once the output table is in place we can run the DBMS_MVIEW.EXPLAIN_REWRITE package to begin to understand why queries are/ are not being rewritten.

Run the package and substitute the following parameters:

  • arg0 is the query you’re trying to re-write with the materialized view.
  • arg1 is the materialized view itself.
declare
begin  
    DBMS_MVIEW.EXPLAIN_REWRITE (
      query=>q'[SELECT SUM(value) val FROM performance.bill_detail]',
      mv=>'performance_mvs.mv_bills');
end;
/

Finally, view the result

SELECT * FROM REWRITE_TABLE
« Previous Page — Next Page »

Visit our advertisers

Search

Quick Poll

How often do you change the password for the computer(s) you use?
Results

Recent Posts

Visit our advertisers

Site Links
Blog
Rated Sites

JamesCoyle.net Limited