What queries is Oracle executing, how frequently, and time-taken

Thank you for the detailed explanation of your problem (one of the best laid out questions actually).

WRITELOG is a very common type of wait, so don't worry about it. Looking at the SOS_SCHEDULER_YIELD indicate CPU pressure and also the CXPACKET, it is possible that there must be some missing indexes and you may be retrieving lot of data from the queries for an OLTP system. I suggest you to look at the Missing Indexes DMV and see if there are any indexes (almost sure there will be more than few) that are in the questionable procs.

http://sqlfool.com/2009/04/a-look-at-missing-indexes/

http://troubleshootingsql.com/2009/12/30/how-to-find-out-the-missing-indexes-on-a-sql-server-2008-or-2005-instance-along-with-the-create-index-commands/

Look for Jonathan Kehayias's post on sqlblog.com on this too.

Also, take a look at Parameter sniffing.

http://sommarskog.se/query-plan-mysteries.html

http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

It's NOT a compete answer for your needs but a good starting point. Let us know if you need more details.

Try commenting out the echoing of the uid

create procedure TigAddUser(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8)
begin
    declare res_uid bigint unsigned;

    insert into tig_users (user_id, sha1_user_id, user_pw)
        values (_user_id, sha1(lower(_user_id)), _user_pw);

    select LAST_INSERT_ID() into res_uid;

    insert into tig_nodes (parent_nid, uid, node)
        values (NULL, res_uid, 'root');

    if _user_pw is NULL then
        update tig_users set account_status = -1 where uid = res_uid;
    end if;

    -- select res_uid as uid;
end //

Try throttling the output of the ids in groups of 1250

create procedure TigUserGen(usr_count INT)
begin
    DECLARE cnt,len,echo_count,echo_limit INT;  
    DECLARE usr CHAR(6);  
    SET cnt = 0;
    SET len = 0;
    SET usr = '';  

    SET echo_limit = 1250;    
    SET echo_count = 0;    
    WHILE cnt < usr_count DO  
        SET cnt = cnt + 1;
        SET echo_count = echo_count + 1;
        SET usr = CAST(cnt AS CHAR(6));
        SET len = CHAR_LENGTH(usr);
        CASE len
            WHEN 1 THEN 
                call TigTestAddUser(CONCAT('+39100000', usr), CONCAT('pwd_00000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 2 THEN 
                call TigTestAddUser(CONCAT('+3910000', usr), CONCAT('pwd_0000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 3 THEN 
                call TigTestAddUser(CONCAT('+391000', usr), CONCAT('pwd_000', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 4 THEN 
                call TigTestAddUser(CONCAT('+39100', usr), CONCAT('pwd_00', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            WHEN 5 THEN 
                call TigTestAddUser(CONCAT('+3910', usr), CONCAT('pwd_0', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
            ELSE 
                call TigTestAddUser(CONCAT('+391', usr), CONCAT('pwd_', usr), 'SUCCESS - adding new user', 'ERROR - adding new user');
        END CASE;
        IF echo_count >= echo_limit THEN
            SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
            SET echo_count = 0;
        END IF;
    END WHILE;  
    IF echo_count >= 0 THEN
        SELECT CONCAT('Users Generated : ',cnt) TigUserGenStatus;
    END IF;
end //

delimiter ;

call TigUserGen(200000);