管理者的英文GREENPLUM常用维护脚本总结1

-Information Compiled by Sohail Mian, ---Sr. Consultant (EMC-GP)

-Contributions came from:- Sohail Mian, research

.

GreenPlum Useful Scripts Series Part -1

o

Red: - You have to replace these parameters in the scripts.

1.

Last Analyzed or Vacuum or Create TABLE OR ETC…

2.

Query Idle for long time:

3.

How to find the largest table in the database?

4.

first 5 biggest table in the database

5.

How to calculate database size in disk?

6.

How to calculate table size in disk?

7.

How to find size of the table (not including index)?

8.

How to generate a series of numbers and insert it into a table?

9.

How to count total number of rows in a table?

10.

Total number of rows with a specific column value is not null.

11.

How can I get the maximum value of a column in the table? -- First & second

12.

How can I get the second minimum value of a column in the table?-- First & second

13.

How to view the basic available data types in GP.

14.

Show segments, which are down.

15.

Find Current users:

16.

Checking for Active Sessions (Workload):

17.

Queries that are waiting in a queue

18.

Viewing the List of Databases

1.

Last Analyzed or Vacuum or Create TABLE OR ETC…

?

Select * from pg_stat_operations

where schemaname=

'SCHEMA NAME '

and actionname in ('ANALYZE','VACUUM')

order by statime;

2.

Query Idle for long time:

o

Select * from pg_stat_activity where now()-backend_start >

2700

;

o

Select * from pg_stat_activity order by query_start,backend_start;

3.

How to find the largest table in the database?

?

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

4.

If you want only the first 5 biggest table in the database then append

the above query with limit as:

?

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC

limit 5;

?

?

?

?

relname

–

name of the relation/table.

relpages

- relation pages ( number of pages, by default a page is 8kb )

pg_class

–

system table, which maintains the details of relations

limit 1

–

limits the output to display only one row.

2

[Type text]

5.

How to calculate database size in disk?

?

SELECT pg_database_size(

'Database Name'

); -- size in kb or bytes

?

SELECT pg_size_ pretty(pg_database_size(

'Database Name'

)); -- size in MB

6.

How to calculate table size in disk?

?

SELECT pg_size_pretty(pg_total_relation _size(

'faa.d_airlines'

));

7.

How to find size of the table (not including index)?

?

SELECT pg_size_pretty(pg_relation_size(

'faa.d_airlines'

));

8.

How to generate a series of numbers and insert it into a

table?

?

INSERT INTO

12

(code) VALUES ( generate_series(1,1000));

9.

How to count total number of rows in a table?

?

select count(*) from

table;

10.

Total number of rows with a specific column value is not

null.

?

select count(

airlineid

) from

faa.d_airlines;

11.

H

ow can I get the maximum value of a column in the table?

?

select max(

col_name

) from

table

;

--

First maximum value of a column

i.

select max(

code

) from

12

;

?

SELECT MAX(

col name

) from

table_name

where num < ( select

MAX(

col name

) from

table_name

);

--

2nd maximum value of a column