Programming Questions - Essay Example

Question1 we have an active 500GB standalone OLTP mysql database that has all of its tables stored in the Innodb engine. Wehave determined that we must convert it all to Myisam for performance reasons. The problem is that our cost of downtime is very high: $20,000 an hour. We would like this migration to use the least downtime possible. Your task here is to devise a project plan, including detailed steps, that describes how you would accomplish this project, especially highlighting the steps in the plan that requires downtime. We have an extra server which is identical to production, and an Innodb Backup license which is used in nightly backups. Both these can be made available if required. How do you proceed? Answer According to the guidelines that are recommended for the implementation, it is good that we test the expected results before we apply the same solution onto the production system. To be able to solve the problem at hand, we shall need to make a restoration for the backup of the production database that is available so that we can be able to perform a test on the server. We shall then to perform the tests that are outlined in the table below so that we can be able to determine the reason why there is an occurrence of the anomalous performance of the database system. Action to be taken Importance of the action The timing of the log file switches needs to be checked. The log Switches are usually very costly Checking for the sql files that have high affinity for statements. SQL Statements need tuning for them to be effective. We need to check SORT_AREA_SIZE The sorting of disks is slow. We must verify whether the tables have been analyzed. The optimizer that is based on cost is not capable of optimizing queries against any of the tables that have been analyzed.. We must ensure that enough memory has been assigned to the buffer cache Oracle can have this problem of being affected by self inflicted memory limitation. We must ensure the library cache has enough memory allocated to it. Oracle can have this problem of being affected by self inflicted memory limitation Limitation of hardware Oracle usually needs resources to be able to accomplish a given task. After we have carried out the task outlined above, it may turn out that the problem persists and we can do the following to eliminate this problem. If we assume that the table spaces that exist were made using 2,000 data block, we can therefore set the DB_8000_CACHE_SIZE in order to come up with an 8,000 block size. This will help to redefine the table and thus move the new 8000 block size tables’ spaces. In this system, we shall almost zero downtime is needed since the tables which have been redefined are restricted in their use and may not be used in tables that belong to the SYSTEM schema   Question 2 Use the file attachment following this question to complete the question: Using the INTERVIEW schema: Table USERS columns: User_id (PK) Email Gender Age Name table SUBSCRIPTIONS columns: SUbscription_id (PK) user_id (UK) (FK from users) subscription_type (UK) active_indicator table TRANSACTIONS columns: subscription_id (PK) (FK from subscriptions) action (PK) timestamp (PK) sample data: USERS user_id email gender age name 1 a@a.com m 30 rob 2 a@a.com m 31 robert 3 b@b.com f 18 lucie 4 b@b.com f 22 lulu 5 c@c.com m 10 kim 6 c@c.com f 18 kim 7 c@c.com f 08 kim 8 d@d.com f 18 JJ 9 d@d.com m 22 Jay 10 e@e.com f 88 Bill 11 e@e.com f 88 Will 12 e@e.com f 60 Will 13 f@f.com m 70 George subscriptions subscription_id user_id subsciption_type active_indicator 1 2 Magazine Yes 2 3 Music CD No 3 3 Magazine Yes 4 3 Video Yes 5 8 Magazine Yes 6 9 Video Yes 7 10 Magazine No 8 13 Magazine yes transactions subscription_id action timestamp 1 Renewal 2002-sep-10 2 Renewal 2002-Jan-01 2 Cancellation 2002-Feb-01 3 Renewal 2002-Aug-20 4 Renewal 2002-Aug-01 4 Renewal 2002-Sep-01 5 Renewal 2002-Aug-01 6 Renewal 2001-Sep-01 7 Renewal 2002-Sep-01 7 Cancellation 2002-Sep-10 Type of business: We are a subscription company where people sign up to received products like CDs, books, magazines, etc. We always try to market new subscriptions to our existing client base, but because we have millions of customers who use the same email address, we like to send our promotional emails only once. QUERY: Some assumptions will need to be made. Please list any such assumptions. How would you generate a list of unique email addresses with the latest name, gender and age for a user with that email? The selection criteria limit the list to users which never subscribed to anything; or; users with inactive subscriptions; or; users with active subscriptions that renewed between Sep 1st and Sep 30th of any year. Your answer should be: a@a.com m 31 robert b@b.com f 22 lulu c@c.com f 08 kim d@d.com m 22 Jay e@e.com f 60 Will Answer A number of assumptions will be made in this problem and this will include: a) Select email, gender,age and name from select maximum (r no. ) over (partition by email order by email) mrno. b) Select a user u* from (select row_number) over(partition by email order user_id) c) Where users do not exist, select a null value from the subscriptions where u.user_id=s.user_id or it exists, we shall select null from the subscriptions s. d) Where we have u.user_id=s.user_id and s.active_indicator =’no’ or it exists, we shall selecta null value from subscriptions s and the transactions t. e) Where u.user_id=s.user_id and s.subscription_id=t.subscription_id and s.active_id=’yes’, and t.action=’renewal’ and to_chart(t.timestamp,’ MON’)=’SEP’ Question 3 Using the INTERVIEW SCHEMA: We require a Stored Procedure to be written that will take the data in the TSOURCE table and complete the following steps: 1. Insert rows not currently in the TDEST table and 2. Uupdate TDEST rows with a matching KEYVAL value with the data in TSOURCE. Answer We can solve the problem described above easily using the SQL merge command as shown below: MERGE INTO TDEST D USING TSOURCE S ON (D.KEYVAL = S.KEYVAL) WHEN MATCHED THEN UPDATE SET D.VAL1 = S.VAL1, D.VAL2 = S.VAL2, D.VAL3 = S.VAL3 WHEN NOT MATCHED THEN INSERT(D.KEYVAL,D.VAL1, D.VAL2, D.VAL3) VALUES(S.KEYVAL,S.VAL1, S.VAL2, S.VAL3); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ||’ rows merged’); COMMIT; END;   Question 4 We have a system where our sales people have notebooks that they take on business trips. They are all configured as mysql slaves and they plug them in every other day to sync them up but sometimes a sales person might not sync up for several days. We use a master and several slaves since a full rebuild would take too long. We used to just remove bin logs older than X days but we had issues with that when a sales person was on an extended trip and tried to sync up after the threshold. What we want to you do to is to write a Perl script, shell script, or stored procedure that would remove bin-logs on the master once it’s 100% certain that they have been applied on all slaves. Please remember that not all slaves will be connected while you verification script is running. How will you accomplish this goal? Answer For us to be able to solve this problem, our interest is in the scheduling of the system that gathers information from the notebooks rather than the content of the notebooks. The following script can be applied to achieve this.  CREATE OR REPLACE FUNCTION NextRunTime RETURN DATE as    nextDate DATE;     nowDate DATE; BEGIN get the current date/time     nowDate := SYSDATE; If the range is between 6AM and midnight then add 20 minutes to it’s next interval run; if (to_char(nowDate,’HH24′) > 6 and to_char(nowDate,’HH24′) < 22) then         nextDate := nowDate  + (20/1440); if the range is after midnight and before 6AM then add 2 hours to it’s next interval run;        elsif (to_char(nowDate,’HH24′) >  0 and to_char(nowDate,’HH24′) < 6) then         nextDate :=  TRUNC(nowDate+2/24,’HH24′);     end if; return nextDate; end;  The following script code can be used to ensure that the information is submitted to the database management system. declare employeeno. number(10); instno number; BEGIN SELECT instance_number INTO instno FROM v$instance; — Submit report for midday interval dbms_report.submit( reportno, ‘statspack.snap;’, to_date(’06/feb/12 00:00:00′, ‘dd/mon/yy HH24:MI:SS’), ‘NextRunTime’, TRUE, instno); — Submit report for 6 am dbms_report.submit( reportno, ‘statspack.snap;’, to_date(’06/feb/12 07:00:00′, ‘dd/mon/yy HH24:MI:SS’), ‘NextRunTime’, TRUE, instno); COMMIT; END; To have the report checked the following day the following script can be applied.  SELECT      SUBSTR(TO_CHAR(report),1,3) report#, SUBSTR(what,1,27) What, TO_CHAR(last_date, ‘dd-MON-YY HH24:MI’) Last_Date, TO_CHAR(Next_Date, ‘dd-MON-YY HH24:MI’) Next_Date, failures fail#, total_time FROM DBA_REPORTS    References  Oracle® Database SQL Reference 10 g Release 2 (10.2) B14200-02 retrieved from http://docs.oracle.com/cd/B19306_01/server.102/b14200.pdf Bob Bryla ,Kevin Loney, 2013, Oracle Database 12c The Complete Reference , Oracle Press pg. 214. Kevin Loney, 2009, Oracle Database 11g, The Complete Reference, Oracle Press pg. 146. Rick Greenwald, Oracle Essentials: Oracle Database pg. 124-127. Ian Abramson, 2008, Oracle Database 12c Install, Configure & Maintain Like a Professional, Oracle Press pg. 112-136.                               Read More