Active Concurrent Database Scripting

 < Day Day Up > 



These scripts do not include trigger code. Use of triggers is easy but contradictory to performance tuning.

Internet OLTP Database Simulation

–every 1 second declare       jobno number;       i integer default 1; begin       for j in 1..10 loop       dbms_job.submit(jobno,'SIMULATE;',SYSDATE,          'SYSDATE+1/86400');       end loop;       commit; end; /     declare       cursor cJobs is select job from user_jobs; begin       for rJob in cJobs loop             dbms_job.remove(rJob.job);       end loop; end; / commit;     create or replace function random(n IN NUMBER DEFAULT 1)    return integer is       random integer; begin       select to_number(to_char(SYSTIMESTAMP,'FF3'))+1 into          random from dual;       random := (random/1000)*n;       if random = 0 then random := 1; end if;       return random; exception when others then       dbms_output.put_line('FUNC: random '||SQLERRM          (SQLCODE)); end; / alter function random compile;     create or replace function getTime(ptime float default 0)     return float is begin    return to_number(to_char(systimestamp,'SSSSS.FF')) –        ptime; exception when others then       dbms_output.put_line('FUNC: getTime '          ||SQLERRM(SQLCODE)); end; / alter function getTime compile;      create or replace procedure getPrice (        pstock_id IN integer default 0       ,psupplier_id OUT integer       ,pprice OUT float ) as begin       –find cheapest price for stock_id       select a.supplier_id, a.price into psupplier_id, pprice       from (             select supplier_id, min(price) as price             from stocksource             where stock_id = pstock_id             group by supplier_id             order by price       ) a where rownum = 1; exception when others then       dbms_output.put_line('PROC: getPrice '          ||SQLERRM(SQLCODE)); end; / alter procedure getprice compile;     create or replace procedure InsertSANDP (pdte IN date    DEFAULT SYSDATE) as       vcustomer_id integer;       vsupplier_id integer;       vstock_id integer;       vorder_id integer;       vstockmovement_id integer;       vtransaction_id integer;       vcheque_id integer;       vqty integer;       vlines integer;       vqtyonhand integer;       vprice float;       vamount float;       vseq# integer;       vmarkup float default 1.01;       vtime float; begin       vtime := to_number(to_char(systimestamp,'SSSSS.FF'));           select max(customer_id) into vcustomer_id from         customer; vcustomer_id := random(vcustomer_id);       select max(stock_id) into vstock_id from stock;       vstock_id := random(vstock_id);       vqty := random(20); vlines := vqty;       select qtyonhand into vqtyonhand from stock          where stock_id = vstock_id;           if vqty - vqtyonhand > 0 then                 vqty := vqty - vqtyonhand; —vqty - vqtyonhand               always +ve             getPrice(vstock_id,vsupplier_id,vprice);             vamount := vqty * vprice;                 insert into orders values(orders_seq.nextval             ,null,vsupplier_id,'P',(vamount*–1),pdte)                   returning order_id into vorder_id;                 insert into transactions                values(transactions_seq.nextval             ,'P',null,vsupplier_id,vorder_id,(vamount*–1),pdte                   ,(select drcoa# from posting                   where posting='PurchaseInvoice')                  ,(select crcoa# from posting                  where posting='PurchaseInvoice'))                  returning transaction_id into                    vtransaction_id;           insert into cashbook values(cheque_seq.nextval             ,(vamount*–1),pdte,'Purchase Invoice             '||to_char(vtransaction_id)                   ,(select drcoa# from posting                   where posting='PurchaseInvoice_CashBook')                   ,(select crcoa# from posting                   where posting='PurchaseInvoice_CashBook')                   ,vtransaction_id)                   returning cheque_id into vcheque_id;           for vseq# in 1..vlines loop                 insert into stockmovement values(stockmovement_                 seq.nextval             ,vstock_id,vqty,vprice,pdte)             returning stockmovement_id into vstockmovement_id;                 insert into ordersline values(vorder_id,vseq#             ,(vprice*–1),vstockmovement_id);                 insert into transactionsline             values(vtransaction_id,vseq#             ,(vprice*–1),vstockmovement_id);                 insert into cashbookline values(vcheque_id,                vseq#,(vprice*–1));           end loop;           dbms_output.put_line('PROC: InsertSANDP             Purchase Complete '||to_char(getTime (vtime)));     end if;           –make sale, gen transaction and pay for it for           vqty – vqtyonhand       getPrice(vstock_id,vsupplier_id,vprice);       vprice := vprice * vmarkup;       vamount := vqty * vprice;           insert into orders values(orders_seq.nextval       ,vcustomer_id,null,'S',vamount,pdte)             returning order_id into vorder_id;           insert into transactions values(transactions_seq.nextval             ,'S',vcustomer_id,null,vorder_id,vamount,pdte             ,(select drcoa# from posting where                posting='SalesInvoice')             ,(select crcoa# from posting where                posting='SalesInvoice'))             returning transaction_id into vtransaction_id;              insert into cashbook values(cheque_seq.nextval             ,vamount,pdte,'Sales Invoice '||to_char(vtrans                action_id)             ,(select drcoa# from posting             where posting='SalesInvoice_CashBook')             ,(select crcoa# from posting             where posting='SalesInvoice_CashBook')             ,vtransaction_id)             returning cheque_id into vcheque_id;           for vseq# in 1..vlines loop                 insert into stockmovement values(stockmovement_                 seq.nextval                   ,vstock_id,(vqty*–1),vprice,pdte)                   returning stockmovement_id into                      vstockmovement_id;                 insert into ordersline values(vorder_id             ,vseq#,vprice,vstockmovement_id);                 insert into transactionsline values                 (vtransaction_id             ,vseq#,vprice,vstockmovement_id);                 insert into cashbookline values(vcheque_id,                vseq#,vprice);           end loop;           dbms_output.put_line('PROC: InsertSANDP Sale          Complete       '||to_char(getTime(vtime)));       commit; exception when others then       dbms_output.put_line('PROC: InsertSANDP '||SQLERRM          (SQLCODE));       rollback; end; / alter procedure InsertSANDP compile;     create or replace procedure UpdateSORP (pdte IN date    DEFAULT SYSDATE) as       vminorder_id integer;       vmaxorder_id integer;       vorder_id integer;       vtransaction_id integer;       vcheque_id integer;       voption integer;       vmod float;       vtime float; begin       –not supposed to execute a stockmovement (too           difficult)       vtime := to_number(to_char(systimestamp,'SSSSS.FF'));             select min(order_id),max(order_id) into             vminorder_id,vmaxorder_id from orders;       vorder_id := random(vmaxorder_id          vminorder_id)+vminorder_id;       select order_id into vorder_id from orders             where order_id = vorder_id;       voption := random(2);       if voption <= 1 then vmod := 1.1;       else vmod := 0.9; end if;           update ordersline set amount = amount*vmod             where order_id = vorder_id;       update orders set dte = pdte, amount =             (select sum(amount) from Ordersline             where order_id = vorder_id group by order_id)       where order_id = vorder_id;           select transaction_id into vtransaction_id from          transactions where order_id = vorder_id;       update transactionsline set amount = amount*vmod       where transaction_id = vtransaction_id;       update transactions set dte = pdte, amount =             (select sum(amount) from transactionsline             where transaction_id = vtransaction_id             group by transaction_id)       where transaction_id = vtransaction_id;           select cheque_id into vcheque_id from cashbook       where transaction_id = vtransaction_id;       update cashbookline set amount = amount*vmod       where cheque_id = vcheque_id;       update cashbook set dte = pdte, amount =             (select sum(amount) from cashbookline             where cheque_id = vcheque_id group by cheque_id)       where cheque_id = vcheque_id;           dbms_output.put_line('PROC: UpdateSORP          Complete '||to_char(getTime(vtime)));       commit;           exception when others then             dbms_output.put_line('PROC: UpdateSORP '||SQLERRM                (SQLCODE));rollback;       end; / alter procedure UpdateSORP compile;     create or replace procedure DeleteSORP(pdte IN DATE DEFAULT    SYSDATE) as       vminorder_id integer;       vmaxorder_id integer;       vorder_id integer;       vtransaction_id integer;       vstockmovement_id integer;       vstock_id integer;       vcheque_id integer;       vqty integer;       vprice float;       vid integer;       vtime float;       type tStockMovement is ref cursor return          StockMovement%rowtype;       cStockMovements tStockMovement;       rStockMovement StockMovement%rowtype; begin           vtime := to_number(to_char(systimestamp,'SSSSS.FF'));           select min(order_id),max(order_id) into          vminorder_id,vmaxorder_id from orders;       vorder_id := random(vmaxorder_id-vminorder_id)+ vminorder_id;          select order_id into vorder_id from orders       where order_id = vorder_id;       select transaction_id into vtransaction_id from          transactions where order_id = vorder_id;       select cheque_id into vcheque_id from cashbook       where transaction_id = vtransaction_id;           begin             open cStockMovements for                   select stockmovement_seq.nextval, sm.stock_id                   ,(sm.qty*–1) as qty, sm.price, pdte                   from stockmovement sm                   where exists(                         select stockmovement_id from                             transactionsline                         where stockmovement_id =                            sm.stockmovement_id and                             transaction_id = vtransaction_id);             loop                      fetch cStockMovements into                          rStockMovement;                      exit when cStockMovements%NOTFOUND;                      insert into stockmovement values                      (rStockMovement.stockmovement_id                      ,rStockMovement.stock_id,rStockMovement.                         qty                      ,rStockMovement.price,rStockMovement.dte);             end loop;             close cStockMovements;       exception when others then             close cStockMovements;       end;           delete from cashbookline where cheque_id =          vcheque_id;       delete from cashbook where cheque_id = vcheque_id;       delete from transactionsline             where transaction_id = vtransaction_id;       delete from ordersline where order_id = vorder_id;       delete from transactions where transaction_id =          vtransaction_id;       delete from orders where order_id = vorder_id;           dbms_output.put_line('PROC: DeleteSORP Complete           '||to_char(getTime(vtime)));       commit;     exception when others then       dbms_output.put_line('PROC: DeleteSORP           '||SQLERRM(SQLCODE));       rollback;     end; / alter procedure DeleteSORP compile;     create or replace procedure simulate as       voption integer;       vrange integer;       vdte date default '01-MAR-03'; begin       voption := random(10); vdte := vdte + random(366);       dbms_output.put_line('PROC: Simulate           '||to_char(vdte)||' '||to_char(voption));       if voption in (1,2,3,4,5,6,7) then InsertSANDP(vdte);       elsif voption in (8,9) then UpdateSORP(vdte);       elsif voption in (10) then DeleteSORP(vdte);       end if; exception when others then       dbms_output.put_line('PROC: Simulate '          ||SQLERRM(SQLCODE)); end; / alter procedure simulate compile; 

Internet OLTP and Reporting Simulation

create or replace procedure Reports (popt IN integer) as       vcustomer_id integer;       vsupplier_id integer;       vorder_id integer;       vtransaction_id integer;       vstock_id integer;       vtime float;       vcnt integer;           type tOrders is ref cursor return Orders%rowtype;       cOrders tOrders;       rOrders Orders%rowtype;           type tOrdersLine is ref cursor return OrdersLine%row          type;       cOrdersLine tOrdersLine;       rOrdersLine OrdersLine%rowtype;           type tTransactions is ref cursor return           Transactions%rowtype;       cTransactions tTransactions;       rTransactions Transactions%rowtype;           type tTransactionsLine is ref cursor return             TransactionsLine%rowtype;       cTransactionsLine tTransactionsLine;       rTransactionsLine TransactionsLine%rowtype;           type tCashbook is ref cursor return Cashbook%rowtype;       cCashbook tCashbook;       rCashbook Cashbook%rowtype;           type tCashbookLine is ref cursor return           CashbookLine%rowtype;       cCashbookLine tCashbookLine;       rCashbookLine CashbookLine%rowtype;           type tCOA is ref cursor return COA%rowtype;       cCOA tCOA;       rCOA COA%rowtype;              type tStockMovement is ref cursor return           StockMovement%rowtype;       cStockMovement tStockMovement;       rStockMovement StockMovement%rowtype;           voption integer;     begin           vtime := to_number(to_char(systimestamp,'SSSSS.FF'));           voption := rand(8);           if voption = 1 then             —print all orders       select max(customer_id) into vcustomer_id from           customer;       vcustomer_id := rand(vcustomer_id);       open cOrders for select * from orders       where customer_id = vcustomer_id;       loop             fetch cOrders into rOrders;             exit when cOrders%notfound;       end loop;       select max(supplier_id) into vsupplier_id from           supplier;       vsupplier_id := rand(vsupplier_id);       open cOrders for select * from orders       where supplier_id = vsupplier_id;       loop             fetch cOrders into rOrders;             exit when cOrders%notfound;       end loop;     elsif voption = 2 then       —print individual orders       select max(customer_id) into vcustomer_id from           customer;       vcustomer_id := rand(vcustomer_id);       select max(order_id) into vorder_id from          orders       where customer_id = vcustomer_id;       vorder_id := rand(vorder_id);       open cOrdersLine for select ol.* from orders o             join ordersline ol on(ol.order_id =                o.order_id)             where o.customer_id = vcustomer_id;       loop             fetch cOrdersLine into rOrdersLine;             exit when cOrdersLine%notfound;       end loop;       select max(supplier_id) into vsupplier_id from           supplier;       vsupplier_id := rand(vsupplier_id);       select max(order_id) into vorder_id from orders       where supplier_id = vsupplier_id;       vorder_id := rand(vorder_id);       open cOrdersLine for select ol.* from          orders o             join ordersline ol on(ol.order_id =                o.order_id)             where o.supplier_id = vsupplier_id;       loop             fetch cOrdersLine into rOrdersLine;             exit when cOrdersLine%notfound;       end loop;        elsif voption = 3 then       —print all transactions       select max(customer_id) into vcustomer_id from           customer;       vcustomer_id := rand(vcustomer_id);       open cTransactions for select * from transactions       where customer_id = vcustomer_id;       loop             fetch cTransactions into rTransactions;             exit when cTransactions%notfound;       end loop;       select max(supplier_id) into vsupplier_id from           supplier;       vsupplier_id := rand(vsupplier_id);       open cTransactions for select * from transactions             where supplier_id = vsupplier_id;       loop             fetch cTransactions into rTransactions;             exit when cTransactions%notfound;       end loop;     elsif voption = 4 then       —print individual transactions       select max(customer_id) into vcustomer_id from           customer;       vcustomer_id := rand(vcustomer_id);       select max(transaction_id) into vtransaction_id             from transactions where customer_id =                vcustomer_id;       vtransaction_id := rand(vtransaction_id);       open cTransactionsLine for select tl.* from           transactions t             join transactionsline tl                   on(tl.transaction_id =                      t.transaction_id)             where t.customer_id = vcustomer_id;       loop             fetch cTransactionsLine into                 rTransactionsLine;             exit when cTransactionsLine%notfound;       end loop;       select max(supplier_id) into vsupplier_id from           supplier;       vsupplier_id := rand(vsupplier_id);       select max(transaction_id) into vtransaction_id             from transactions where supplier_id =             vsupplier_id;       vtransaction_id := rand(vtransaction_id);       open cTransactionsLine for select tl.* from           transactions t             join transactionsline tl                   on(tl.transaction_id =                      t.transaction_id)             where t.supplier_id = vsupplier_id;       loop             fetch cTransactionsLine into                 rTransactionsLine;             exit when cTransactionsLine%notfound;       end loop;     elsif voption = 5 then       —print all invoices       select max(customer_id) into vcustomer_id from           customer;       vcustomer_id := rand(vcustomer_id);       select max(transaction_id) into vtransaction_id             from transactions where customer_id =                vcustomer_id;       vtransaction_id := rand(vtransaction_id);       open cCashbook for select cb.* from          transactions t             join cashbook cb                   on(cb.transaction_id =                      t.transaction_id)             where t.customer_id = vcustomer_id;       loop             fetch cCashbook into rCashbook;             exit when cCashbook%notfound;       end loop;       select max(supplier_id) into vsupplier_id from           supplier;       vsupplier_id := rand(vsupplier_id);       select max(transaction_id) into          vtransaction_id       from transactions where supplier_id =          vsupplier_id;       vtransaction_id := rand(vtransaction_id);       open cCashbook for select cb.* from           transactions t             join cashbook cb                   on(cb.transaction_id =                      t.transaction_id)             where t.supplier_id = vsupplier_id;       loop             fetch cCashbook into rCashbook;             exit when cCashbook%notfound;       end loop;     elsif voption = 6 then       —print individual invoices       select max(customer_id) into vcustomer_id from           customer;       vcustomer_id := rand(vcustomer_id);       select max(transaction_id) into vtransaction_id       from transactions where customer_id =          vcustomer_id;       vtransaction_id := rand(vtransaction_id);       open cCashbookLine for select cbl.* from           transactions t                join transactionsline tl                      on(tl.transaction_id =                         t.transaction_id)                      join cashbook cb                            on(cb.transaction_id =                               t.transaction_id)                            join cashbookline cbl                            on(cbl.cheque_id =                               cb.cheque_id)             where t.customer_id = vcustomer_id;       loop       fetch cCashbookLine into rCashbookLine;             exit when cCashbookLine%notfound;             end loop;       select max(supplier_id) into vsupplier_id from           supplier;       vsupplier_id := rand(vsupplier_id);       select max(transaction_id) into vtransaction_id       from transactions where supplier_id =          vsupplier_id;       vtransaction_id := rand(vtransaction_id);       open cCashbookLine for select cbl.* from           transactions t                join transactionsline tl                      on(tl.transaction_id =                         t.transaction_id)                      join cashbook cb                            on(cb.transaction_id =                               t.transaction_id)                            join cashbookline cbl                                  on(cbl.cheque_id =                                     cb.cheque_id)             where t.supplier_id = vsupplier_id;       loop             fetch cCashbookLine into rCashbookLine;              exit when cCashbookLine%notfound;       end loop;     elsif voption = 7 then       —coa       open cCOA for select c.* from coa c, type t,           subtype s             where c.type = t.type and c.subtype =                s.subtype             order by c.coa#, t.type, s.subtype;       loop             fetch cCOA into rCOA;             exit when cCOA%notfound;       end loop;     elsif voption = 8 then       —stock       select max(stock_id) into vstock_id from stock;       vstock_id := rand(vstock_id);       open cStockMovement for select sm.* from           category c             join stock s on(s.category_id =             c.category_id)             join stockmovement sm on(sm.stock_id =                s.stock_id)       where s.stock_id = vstock_id;       loop             fetch cStockMovement into rStockMovement;             exit when cStockMovement%notfound;       end loop;     elsif voption in (9,10) then       —sorting       select count(*) into vcnt from             (select * from customer order by address);       select count(*) into vcnt from             (select drcoa#, sum(amount)             from transactions group by drcoa#);       —selections from the generalledger and           stockmovement       —tables are avoided here due to reverse index           primary keys end if;     dbms_output.put_line('PROC: Reports Complete '       ||to_char(getTime(vtime)));       simAudit('PROCEDURE','Reports',getTime(vtime),          popt);        exception when others then       dbms_output.put_line('PROC: Reports '||SQLERRM(SQL-       CODE));       rollback; end; / alter procedure Reports compile;     create or replace procedure simulate as       voption integer;       vrange integer;       vdte date default '01-MAR-03'; begin       voption := rand(20); vdte := vdte + rand(366);       dbms_output.put_line('PROC: Simulate ('||to_char          (voption)||')'                ||to_char(vdte)||' '||to_char(voption));       if voption in (1,2,3,4,5,6,7,8,9,10) then           Reports(voption);       elsif voption in (11,12,13,14,15) then        InsertSANDP(vdte,voption);       elsif voption in (16,17,18) then UpdateSORP(vdte,          voption);       elsif voption in (19,20) then DeleteSORP(vdte,voption);       end if; exception when others then       dbms_output.put_line('PROC: Simulate '||SQLERRM          (SQLCODE)); end; / alter procedure simulate compile;
Tip 

Pinning all these procedures into the shared pool using the DBMS_SHARED_POOL package KEEP procedure would be sensible. I have not done so because I am attempting to simulate a tunable environment.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net