Finding out the full table scans

There could be cases, queries are fetching too many rows by full tables scans and there are huge "consistent gets" causing the use of more cpu cycles. It might be the case that we missed an index or need to repartition tables. So, first of all we have to find out the full table scans.

SET LONG 1000000

COL object_name FORMAT A25
COL object_name FORMAT A25
COL sql_fulltext FORMAT A50

SELECT sp.object_name, dtab.num_rows, sa.sql_fulltext, sa.executions
FROM v$sql_plan sp
JOIN dba_tables dtab ON (dtab.table_name = sp.object_name)
JOIN v$sqlarea sa ON (sa.address = sp.address AND sa.hash_value =sp.hash_value)
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY sa.executions DESC;

Another faster sql version could be -

SELECT sp.object_name
,(SELECT num_rows FROM dba_tables WHERE table_name = sp.object_name AND owner = sp.object_owner) num_rows
,(SELECT sql_fulltext FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) sql_fulltext
,(SELECT executions FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) full_scans
FROM v$sql_plan sp
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY full_scans DESC;

Now we need to apply both technical and business knowledge to find out why those sqls are doing full scans and if re-indexing would be helpful or something out of the box!

Find out tables with Outdated statistics

SQL> SET SERVEROUTPUT ON

SQL>
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.'|| ObjList(i).ObjName || ''|| ObjList(i).ObjType || ''|| ObjList(i).partname);
END LOOP;
END;
/

To collect statistics of a outdated table of SCOTT schema:


exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE', cascade=>true, degree=>2, estimate_percent=>50, method_opt=>'FOR ALL COLUMNS SIZE 1');

To find schema level stats that are stale one can call-


DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT', objlist=>ObjList, options=>'LIST STALE');

Moving table(s) to a different tablespace

Q: What happens when u move a table to a different Tablespace?
Q: How can u move table to a different Tablespace which have long datatype?
Q: How can u move table to a different Tablespace with indexes?

There are a couple of ways in which a table can be moved to a different tablespace:

1) One of them is to perform export/import.
2) Another approach is to use the 'alter table' command with 'move tablespace' clause.

Practice:
CREATE TABLE TEST (
TEST_ID NUMBER(9) NOT NULL, TEST_DESC VARCHAR(10),
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID));

CREATE INDEX TEST_IND_1 ON TEST(TEST_DESC);

Above SQLs creates table and index in user's default tablespace. In our case, the default tablespace is "USERS". Run following SQL to check where these objects are created-

SELECT TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_TABLES
WHERE TABLE_NAME = 'TEST'
UNION ALL
SELECT INDEX_NAME,TABLESPACE_NAME,STATUS FROM USER_TABLES
WHERE TABLE_NAME = 'TEST';

TABLE_NAME TABLESPACE_NAME----------STATUS
----------------------- ----------------------------------------------------------
TEST -------------------- USERS -------------------------- VALID
TEST_IND_1 -------- USERS -------------------------- VALID
PK_TEST -------------- USERS -------------------------- VALID

INSERT INTO TEST VALUES(1,'Hello'); commit;

SQL> SELECT ROWID, TEST_ID FROM TEST;----------- [Retrieve value for TEST_ID and ROWID]

ROWID ----------------------------TEST_ID
-------------------------------------------------------------
AAAQ+eAAEAAAA3tAAA -------------1

SQL> ALTER TABLE TEST MOVE TABLESPACE SLMDATA; [Move TEST table to SLMDATA]

SQL> SELECT ROWID, TEST_ID FROM TEST;--------------------------[Check ROWID value again]

ROWID -------------------------- TEST_ID
--------------------------------------------------------
AAAQ+hAAHAAAAAsAAA ---------- 1

Moving table to new tablespace will make all the indexes on the table unusable.

TABLE_NAME-------------------------- TABLESPACE_NAME ----------STATUS
--------------------------------------------------- -----------------------------------------------------------------------------------------------
TEST ------------------------------------------------SLMDATA ---------------------VALID
TEST_IND_1 ------------------------------------USERS --------------------------UNUSABLE
PK_TEST ------------------------------------------USERS --------------------------UNUSABLE

As, indexes still point to older tablespace and are in 'unusable' status. Indexes in unusable state will prevent any DML activity on the table.

INSERT INTO TEST VALUES(2,'World!');
ERROR at line 1:
ORA-01502: index ‘DECIPHER.PK_TEST’ or partition of such index is in unusable State

Why this happened?
When we moved table to new tablespace, each row of the table got moved and got new ROWID. If we compare the ROWID values before and after the move, we'll realize that ROWID for the same TEST_ID is different. Indexes point to the previous location of the row and not to the current location. If we want to keep the index in the current tablespace (USERS), we just need to issue only rebuild clause. If we also want to move index to the new tablespace then we have to include tablespace clause. Following is the example of both.

ALTER INDEX PK_TEST REBUILD;
ALTER INDEX TEST_IND_1 REBUILD TABLESPACE SLMDATA;

First statement only rebuilds the primary key index without moving it to new tablespace.
Second statement rebuilds the index and also moves it to the other tablespace.

SELECT INDEX_NAME,TABLESPACE_NAME,STATUS FROM User_Indexes
WHERE TABLE_NAME = 'TEST';

INDEX_NAME TABLESPACE_NAME ---------------------STATUS
--------------------------------------------------------------------------------------------------
TEST_IND_1 SLMDATA ---------------------------------------------VALID
PK_TEST USERS --------------------------------------------------------VALID

One thing to remember is that 'MOVE TABLESPACE' does not work if table contains column with LONG or LONG RAW data type. You will run into ‘ORA-00997: illegal use of LONG datatype error. Such tables can be moved to new tablespace using exp/imp command.